Re: Does PostgreSQL cache all columns of a table after SELECT?

On Mon, 2023-06-05 at 14:15 +0200, Tim Schwenke wrote:
> Hello,
>
> I have the following table with the following columns:
>
> large_a: text (few dozen characters up to a few hundred)
> large_b: text (few dozen characters up to a few hundred)
>
> The table has several million rows. The DB is running on a large machine.
>
> I perform the following query:
>
> select large_a from table;
>
> The first query takes a few minutes. Afterwards I see that the cache in memory has grown. Next query only takes a few seconds.
>
> What I want to know:
>
> Does the cache also contain large_b? Or is only large_a cached? Assumption is that memory is large enough to fit everything.

It depends.

If "large_a" and "large_b" are TOASTed, then they will only be cached if the column
is explicitly selected.

For shorter attributes, the whole row is in one piece, and all columns will get cached.

However, for sequential scans that are larger than a quarter of shared buffers,
PostgreSQL uses a ring buffer to avoid blowing out the cache. In that case, most
data won't be cached for the next query.

Yours,
Laurenz Albe