3 ms·

Sorry for being a bit off topic, but anyone out there who is using PostgreSQL in production, how do you manage tables with lots of updates? Is auto-vacuum doing

Sorry for being a bit off topic, but anyone out there who is using PostgreSQL in production, how do you manage tables with lots of updates? Is auto-vacuum doing good enough job for you or do you have to run “vacuum full” regularly?

pageald 8y ago
For our use case, we found the best approach was to clone all of the data to a temporary table with indices and constraints disabled, perform the updates, re-enable indices and constraints, and then replace the production table with the temporary table. This only works if you are able to update your data in bulk, and if some lag time in your updates is OK. This also has the benefit of never locking your production table. In situations where real-time updates are important, the key is to minimize your indices as much as possible. Read up on heap only tuples (HOT). If that all isn't enough, maybe consider sharding your database. Never run VACUUM FULL; it locks too aggressively. Let autovacuum do the job.
gnulinux 8y ago
vacuum full locks everything, never use that. We do vacuum analyze table_name depending on tuple count.
yardstick 8y ago
If you are updating lots of rows, specify a low fill factor, both indexes and tables. It will avoid table bloat from dead pages by reusing existing pages for updates rather than appending the latest row version to a new page (and avoids the associated index update) Eg Create table (...) with (fillfactor=30) https://www.postgresql.org/docs/current/sql-createtable.html https://www.postgresql.org/docs/current/sql-createtable.html
Erwin 8y ago
If you end up needing full vacuuming to reduce bloat, there's also the pg_repack extension (it's even supported on RDS). The extension essentially creates a new table without the block and replaces the original one, keeping track of changes to the table using trigger; so the exclusive lock of VACUUM FULL is not needed for quite the long time.
icheishvili 8y ago
We have busy tables with lots of record churn some which receive 2000 xacts/s. I’ve found that they will never shrink down until you do a full vacuum but regular vacuum is good at reusing pages so tables won’t outgrow their max size during busiest periods. We never run full vacuums because we’ve never found it to be necessary.

pageald 8y ago

For our use case, we found the best approach was to clone all of the data to a temporary table with indices and constraints disabled, perform the updates, re-enable indices and constraints, and then replace the production table with the temporary table. This only works if you are able to update your data in bulk, and if some lag time in your updates is OK. This also has the benefit of never locking your production table. In situations where real-time updates are important, the key is to minimize your indices as much as possible. Read up on heap only tuples (HOT). If that all isn't enough, maybe consider sharding your database. Never run VACUUM FULL; it locks too aggressively. Let autovacuum do the job.

gnulinux 8y ago

vacuum full locks everything, never use that. We do vacuum analyze table_name depending on tuple count.

yardstick 8y ago

If you are updating lots of rows, specify a low fill factor, both indexes and tables. It will avoid table bloat from dead pages by reusing existing pages for updates rather than appending the latest row version to a new page (and avoids the associated index update) Eg Create table (...) with (fillfactor=30) https://www.postgresql.org/docs/current/sql-createtable.html https://www.postgresql.org/docs/current/sql-createtable.html

Erwin 8y ago

If you end up needing full vacuuming to reduce bloat, there's also the pg_repack extension (it's even supported on RDS). The extension essentially creates a new table without the block and replaces the original one, keeping track of changes to the table using trigger; so the exclusive lock of VACUUM FULL is not needed for quite the long time.

icheishvili 8y ago

We have busy tables with lots of record churn some which receive 2000 xacts/s. I’ve found that they will never shrink down until you do a full vacuum but regular vacuum is good at reusing pages so tables won’t outgrow their max size during busiest periods. We never run full vacuums because we’ve never found it to be necessary.