Per-Table Autovacuum Tuning

autovacuum_analyze_scale_factor = .10
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = .20
autovacuum_vacuum_threshold = 50
vacuum initialization = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * number of rows)
analyze initialization = autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor * number of rows)
autovacuum_analyze_scale_factor = .05
autovacuum_analyze_threshold = 500
autovacuum_vacuum_scale_factor = .10
autovacuum_vacuum_threshold = 500
(800,000,000 rows * .2) + 50 = 160,000,050
(800,000,000 rows * .1) + 500 = 80,000,500
mydb=# select * from pg_stat_all_tables where relname = 'mytable';
-[ RECORD 1 ]-------+------------------------------
relid | 32307
schemaname | public
relname | mytable
seq_scan | 25
seq_tup_read | 5042494092
idx_scan | 199790800
idx_tup_fetch | 3373819424
n_tup_ins | 342119654
n_tup_upd | 752699033
n_tup_del | 167007870
n_tup_hot_upd | 281342612
n_live_tup | 286999790
n_dead_tup | 9050790
n_mod_since_analyze | 762072
last_vacuum | 2017-08-17 16:14:25.447345-05
last_autovacuum | 2018-08-24 13:17:35.88936-05
last_analyze | 2018-08-16 20:32:13.265396-05
last_autoanalyze | 2018-08-31 07:43:45.551732-05
vacuum_count | 1
autovacuum_count | 43
analyze_count | 29
autoanalyze_count | 129
psql -U postgres -d mydb -c "COPY (select now() AS gather_time, * from pg_stat_all_tables where relname = 'mytable') To STDOUT WITH CSV DELIMITER ','" >> mytable_stats.csv
ALTER TABLE public.mytable SET (autovacuum_analyze_scale_factor = 0, autovacuum_vacuum_scale_factor = 0, autovacuum_analyze_threshold = 400000, autovacuum_vacuum_threshold = 100000 );

--

--

--

Database Admin w/ OmniTI & Player of Way Too Many Video Games

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Scrum Gone Wrong (part 1)

Top VMware Tools to Simplify Daily Administrative Tasks

A Guide on How to Mount Amazon S3 as a Drive for Cloud File Sharing

Create Elastic Watcher and integrate with PagerDuty

Keeping up with the Dependashians

On clouds and business

Simple architecture for Docker Swarm and Traefik footprints

Rails Internationalization (I18n) — Seven Best Practices That You Should Know About

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Keith Fiske

Keith Fiske

Database Admin w/ OmniTI & Player of Way Too Many Video Games

More from Medium

Memcached Auto Discovery

AEP Criticality for IoT Application Creation

iot devices

Resolving Elastic Search Yellow/Red status

MongoDB Query Performance Analysis using Pymongo — Part 3