Re: Append only tables

On Fri, 2020-03-20 at 22:50 +0100, Kurt Roeckx wrote:
> I have a few tables that are append only. Thre are only gets insert
> and select queries, never update or delete.
>
> What I see is that every file is still being updated. It's
> currently about 500 GB big, and every of that almost 500 files has
> been touched the past 24 hours.
>
> I assume that that the free space map is being used, and that it
> still finds places where it can insert a row in one of the files.
>
> (auto) vacuum is not happening on the table.

This is probably the first reader setting hint bits on the table rows.

To determine whether a row is visible or not, the first reader has
to consult the commit log to see if the xmin and xmax special columns
of the row belong to committed transactions or not.

To make life easier for future readers, it will then set special
flags on the row that provide that information without the requirement
to consult the commit log.

This modifies the row, even if the data don't change, and the row
has to be written again.

> Is there a way I can turn off this behaviour, and that it really
> only writes to the last few pages?

You can explicitly read or vacuum the new rows, that will set the
hint bits.

But, as has been explained, at some point the table will have to receive
an anti-wraparound vacuum that will freeze old rows.

So the best you can do is to VACUUM (FREEZE) the table after you load
data. Then the table should not be modified any more.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com