We're using Postgres 9.2 on Windows to store low-frequency timeseries data: we're inserting around 2000 rows per second every second 24 hours, 7 days a week with no downtime. There is a DELETE
that runs on the table every 10 minutes or so to keep the length of the table to a fixed number of days. This ends up being a fairly stable 900 million rows. (For those interested, SELECT
, INSERT
, DELETE
are all performant).
As such the DELETE
, whilst deleting rows is not freeing up disc space. For that we need VACUUM
to run.
I've queries the pg_stat_user_tables
and VACUUM
appears not to have ever run.
What I understand from various docs (http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html):
- we appear to have auto-vacuum on, and it is running on other tables.
- auto-vacuum doesn't run
FULL
, and shouldn't require an exclusive lock on the table.
Does anyone have any thoughts why auto-vacuum is not running? Is this purely because the table is continuously busy?
And it is worthwhile running VACUUM
after every DELETE
in this case (which runs every 10 minutes)?
Edit:
Query using the SQL from the SO link below:
-[ RECORD 2 ]---+---------------------------
schemaname | stats
relname | statistic_values_by_sec
last_vacuum |
last_autovacuum |
n_tup | 932,315,264
dead_tup | 940,727,818
av_threshold | 186,463,103
expect_av | *
and raw output:
-[ RECORD 3 ]-----+---------------------------
relid | 501908
schemaname | stats
relname | statistic_values_by_sec
seq_scan | 12
seq_tup_read | 4526762064
idx_scan | 29643
idx_tup_fetch | 2544206912
n_tup_ins | 1573896877
n_tup_upd | 0
n_tup_del | 941176496
n_tup_hot_upd | 0
n_live_tup | 688858417
n_dead_tup | 940727818
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2014-08-09 01:36:21.703+01
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 69
select * from pg_stat_user_tables
for this table (use\x
in psql for a nicely formatted output)