Re: Append only tables - Mailing list pgsql-admin
| From | Keith |
|---|---|
| Subject | Re: Append only tables |
| Date | |
| Msg-id | CAHw75vvg2AYPm7RDdvONPbsyom1eVz5p=kFsie0S=NV=O-c9dg@mail.gmail.com Whole thread Raw |
| In response to | Re: Append only tables (Kurt Roeckx <kurt@roeckx.be>) |
| List | pgsql-admin |
On Sat, Mar 21, 2020 at 4:31 AM Kurt Roeckx <kurt@roeckx.be> wrote:
On Sat, Mar 21, 2020 at 01:44:47AM -0400, Rui DeSousa wrote:
>
>
> > On Mar 20, 2020, at 5:50 PM, Kurt Roeckx <kurt@roeckx.be> wrote:
> >
> > Hi,
> >
> > 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.
> >
> > (auto) vacuum is not happening on the table.
> >
>
> Vacuum is still required as the tuples need to be frozen. Have you vacuumed the table?
I did not manually vacuum them, nor did auto vacuum ever get
triggered, at least not according to what is in
pg_stat_user_tables;
> It could be the result of records being frozen during selects statements; select can freeze tuples that meet the criteria for being frozen.
I guess it's doing that now. iotop shows:
TID PRIO USER DISK READ> DISK WRITE SWAPIN IO COMMAND
10227 be/4 postgres 67.68 G 67.56 G 0.00 % 79.66 % postgres: 12/main: kurt certs [local] VACUUM
> I would recommend vacuuming the table to get the existing records frozen; then I believe you should see fewer updates to the all the base files.
If this is needed, why doesn't autovacuum do this for me? Or do I
need to modify some parameters so that autovacuum does get
triggered?
I actually didn't run any select query on that table yet, it's
still importing the data. Can an insert also trigger the freeze?
Anyway, the vacuum is running, I will let you know if it helps or
not.
Kurt
I wouldn't worry so much about the files themselves simply being touched. Postgres has to do things over time even with old pages to keep the visibility maps up to date. However, you can do things to keep what it does when it touches those files to a minimum.
If at any time this table was getting updates or deletes, then yes, new inserts may be using the free space map to fill in space that vacuum had previously marked as available. If you'd like to clean this "free" space up, you can run a VACUUM FULL on the table which will completely rewrite it and all its indexes, compacting them down into the smallest space possible (basically a defrag). Note this will lock the table for the duration, but since this is now an insert only table, you should rarely, if ever, have to do this again, so it would be worth the outage time right now.
Insert only tables will not trigger autovacuum. Autovacuum is triggered only by counters that are incremented by updates and deletes (see autovacuum_analyze_scale_factor, autovacuum_analyze_threshold, autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold). However, at some point, the xid values on this table will become old enough that autovacuum will kick in to prevent xid exhaustion/wraparound (see autovacuum_freeze_max_age). If you're on at least version 9.6, vacuuming the table, especially a vacuum full, will mark all old pages as frozen so autovacuum should be able to skip right over them. Version 12 improves upon this even more. So this emergency vacuum should have relatively little impact, only depending on how much new data there is.
I'd be careful with cstore at this time unless your data is easily recreatable. Unless something has changed, it provides zero crash safety and cannot be replicated because it's not part of the WAL stream.
Keith
pgsql-admin by date: