I have a database with a dozen tables. No rows are ever
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).
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
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.
What is the best way to mark all the new transactions as visible for append-only PostgreSQL without scanning the whole table every time?