1

Situation

PostgreSQL v11

I have a database with a dozen tables. No rows are ever DELETEd or UPDATEd. A bulk of data is INSERTed to all the tables in a 'few' (up to 1,000) transactions every day. Some tables can add tens of GBs of data during the INSERT (the largest has has almost 2 billion rows as of now).

Problem

I have noticed that at some point SELECT queries I use to read the data from the DB stop using index only scans. After some digging it became apparent this is due to visibility map becoming out-of-date. This is confirmed by running VACUUM as it reverts back to using index only scans. However, VACUUM is very expensive in my case (can take over 10 hours for the largest table) and AUTOVACUUM is never triggered as there are no DELETE or UPDATE operations.

I have looked at running VACUUM FREEZE after each transaction but it seems it will need to scan the whole table after each transaction, which again is going to take ages.

Question

What is the best way to mark all the new transactions as visible for append-only PostgreSQL without scanning the whole table every time?

  • Do you have any non-btree indexes? – jjanes Feb 10 at 13:30
  • No, there aren't any – afonja Feb 10 at 13:47
0

You should run VACUUM (FREEZE) occasionally. The longer it doesn't run, the more it has to do, and the longer it will take.

To speed up VACUUM, increase maintenance_work_mem.

| improve this answer | |
  • Then your VACUUM is already fast enough if, as per my updated answer, you are generous with autovacuum_work_mem and run it often enough. – Laurenz Albe Feb 10 at 10:35
  • Thanks, but does not seem that autovacuum_work_mem will make any difference to the manual invocations of either VACUUM or VACUUM FREEZE - The setting has no effect on the behavior of VACUUM when run in other contexts. postgresql.org/docs/11/runtime-config-resource.html – afonja Feb 10 at 10:42
  • Sorry, typo. I meant maintenance_work_mem. Anyway, you will have to run VACUUM there is no way around it. Try to find what is the bottleneck: I/O? CPU? – Laurenz Albe Feb 10 at 10:50
  • It seems that the best approach is to set vacuum_freeze_min_age to 0 and run a normal VACUUM after each transaction. postgresql.org/docs/11/… and dba.stackexchange.com/a/130514/200740 – afonja Feb 10 at 12:18
  • Sure; that is the same as running VACUUM (FREEZE). – Laurenz Albe Feb 10 at 12:26
0

What is the best way to mark all the new transactions as visible for append-only PostgreSQL without scanning the whole table every time?

PostgreSQL doesn't have to scan the parts of the table which are already marked as all visible/all frozen. If there are absolutely no obsolete tuples (which for append-only workloads there should not be any, unless some of your INSERTs have rolled back) then it might not have to scan the indexes either. So I don't think the problem you are worried about actually exists.

However, VACUUM is very expensive in my case (can take over 10 hours for the largest table)

How long had you let it go before running that VACUUM? How long did the next one after that one take? There is nothing inherently wrong with a VACUUM taking 10 hours to complete, if that is a problem you should describe what the problem with it is.

| improve this answer | |
  • I sometimes need to rollback INSERTs - what consequences that would have? Do I need to take that into the account somehow? Considering the low number of daily transactions I was running VACUUM once a month. – afonja Feb 10 at 13:30
  • With rolled back INSERTs, it will need to scan all of the indexes to remove the index entries for those tuples. It is probably this, rather than scanning the table, which takes the most time. Especially for GIN indexes, the scan can be very slow as they are read in logical not physical order. You can run VACUUM (FREEZE, VERBOSE) to see how long each step in the process took. Just because it took 10 hours once doesn't mean it always will (if run more frequently) but it is hard to say without knowing where the time went the first time. – jjanes Feb 10 at 13:40
  • Thanks @jjanes this has been really helpful – afonja Feb 10 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.