TOAST : partial detoasting : only a small part of a toasted value(for pgpointcloud) - Mailing list pgsql-general

From Rémi Cura
Subject TOAST : partial detoasting : only a small part of a toasted value(for pgpointcloud)
Date
Msg-id CAJvUf_suVAfzV_XB2oCoLPFUQ9mFxDYuZGLMLn_bBD1Sdp75zw@mail.gmail.com
Whole thread Raw
Responses Re: TOAST : partial detoasting : only a small part of a toasted value (for pgpointcloud)
List pgsql-general
Hi dear list,
I have a tricky question about TOASTED memory in Postgres related to the [pgpointcloud](https://github.com/pgpointcloud/pointcloud) extension. (using Postgres 11 if it matters)

So the pgpointcloud store sometimes very large groups of points into one row (TOASTED), something along few kB to few MB. TOAST would be in EXTERNAL mode (no compression).

Sometimes we only want to access a part of this data (one or several blocks within the full data).
From what I  understand, for the moment it requires to fetch all the data from disk, then de-toast it, then select only the part of the data we are interested in.

Yet I think it is possible to detoast only a subset of the data (and thus fetch only some part of the data), considering that the [doc on toast](https://www.postgresql.org/docs/11/storage-toast.html) says
> " Use of EXTERNAL will make substring operations on wide text and bytea columns faster
> (at the penalty of increased storage space) because these operations are optimized
> to fetch only the required parts of the out-of-line value when it is not compressed. "

So my question is how does it work, how easy would it be to implement for pgpointcloud?

Many thanks for your time and help
Remi-C




pgsql-general by date:

Previous
From: Rich Shepard
Date:
Subject: Re: Move cluster to new host, upgraded version
Next
From: Adrian Klaver
Date:
Subject: Re: Move cluster to new host, upgraded version