3 ms·
We have tables with 5B rows and don't use partitioning (yet). I do recommend testing and optimising the vacuum settings for large or often updated tables.
by andruby 8y ago
We have tables with 5B rows and don't use partitioning (yet). I do recommend testing and optimising the vacuum settings for large or often updated tables.
- sandGorgon 8y agocould you share some info about the vacuum settings ? especially for tables that have bulk updates
- gldalmaso 8y agoI believe parent is referring essentially to the auto vacuum scale factor parameter (https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR https://www.postgresql.org/docs/9.6/static/runtime-config-au...) For a big table with frequent writes, the default is too much (20% dead tuples).
- andruby 8y agoWe increased "vacuum_cost_limit" globally and decreased "autovacuum_vacuum_scale_factor" and "autovacuum_analyze_scale_factor" for large tables. Most of them by a factor 4~5x. The docs explain the different settings: https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html https://www.postgresql.org/docs/9.6/static/runtime-config-au... When tweaking performance settings, always benchmark, especially with your specific load characteristics.