Questions tagged [autovacuum]
Questions regarding the postgres autovacuum daemon.
96 questions
0 votes
1 answer
57 views
How does Postgresql autovacuum determine which tables it needs to vacuum and/or analyze?
When the autovacuum daemon kicks off, how does it choose which tables to vacuum? My guess is it runs a query, perhaps like this one which appears to be used by Amazon RDS. I suspect that postgres ...
- 375
4 votes
1 answer
317 views
Postgresql XID wrap around prevention overloading autovacuum
We have a pretty big postgres 14 database with timescaledb installed. Recently we had some issues with MXID and XID wraparound so we started to monitor these as well as autovacuum. What we noticed is ...
- 41
1 vote
1 answer
265 views
How to know, when it is time to vacuum an sqlite database file?
Sqlite, contrary most SQL-compatible DB engine, is working directly on files instead over network sockets. It is also not very good in concurrency, beside that deletion operators are mostly advisory (...
- 2,137
0 votes
0 answers
18 views
Auto-vacuum shows different behaviour in two similar situations. I am unable to understand why?
I have a insert-only table event on PostgreSQL-12 where I am having a high volume of inserts per sec (~2000 per sec). I regularly faced an issue when anti-wraparound auto-vacuum was running on this ...
1 vote
1 answer
100 views
Postgresql last_autovacuum and last_autoanalyze
I have a few tables that ere 1 TB+. The columns last_autovacuum and last_autoanalyze in pg_stat_user_tables have NULLs for these tables. The n_dead_tup to n_live_tup ratio is 0.0001. A few questions, ...
- 1,051
2 votes
2 answers
213 views
Does regular truncate & insert hurt performance?
We have a large (5 TB) PostgreSQL 15 RDS that acts as a data warehouse, data comes in through regular pipelines and then ~20 users run analytic queries on it. Our infrastructure people have access to ...
- 615
2 votes
2 answers
574 views
PostgreSQL: Long running auto vacuum and high wal rate
Recently a vacuum process triggered by auto vacuum daemon(PostgreSQL13) on one of our table took long 1.5hr to complete. During this period there was high WAL rate up to 7MB/sec and high Disk I/O. ...
- 700
0 votes
1 answer
70 views
How to Retrieve and Compare All Settings and Overrides for a Specific Table in PostgreSQL (version 12)?
I am working with a PostgreSQL 12 database and currently investigating a table (table_name in schema schema_name) that has accumulated a significant number of dead tuples. To address this, I am ...
- 136
0 votes
1 answer
609 views
Optimizing XID management in PostgreSQL: How to avoid INSERT locks in tables?
I would like to raise a question regarding the optimal practices for managing transaction age (XID) in a PostgreSQL database. I am currently using the method of performing VACUUM FULL operations on ...
- 1
0 votes
1 answer
98 views
Postgres: how to insert and delete many records and to not run out of space
I have a PostgreSQL installation deployed in Kubernetes (with the CloudNative operator) and allocated a 25GB volume for it. My architecture involves several microservices interacting with various ...
- 141
-1 votes
2 answers
47 views
Auto vacuum for table ASAP
In my app for Postgresql I have a table and my app inserts rows to that table. It could be 2-3 rows, also it could be 100-200k rows. How to do that auto vacuum runs for that table as soon as possible? ...
- 101
0 votes
2 answers
426 views
Sensible value for autovacuum_cost_delay
I'm running a data warehouse on an AWS RDS PostgreSQL instance. Most of the heavy lifting is done during the night-batch, and very often we use a TRUNCATE-rebuild strategy, also for large tables (100M ...
- 369
0 votes
1 answer
245 views
What's the difference between autovacuum general setting and autovacuum_enabled table setting in PostgreSQL?
We have a database that is now on v15 PostgreSQL, but was recently migrated from v11, and before that v9. I believe that many of the settings for our database were preserved from prior versions and ...
- 111
4 votes
1 answer
3k views
High CPU load after massive delete on PostgreSQL server
I've deleted ~65 million rows from a PostgreSQL table with ~75 million rows. As soon as the deletion query completed, the CPU plummeted to 100% for about five minutes. The table from which rows were ...
- 229
0 votes
2 answers
71 views
Suprising gained disk space after backup & restore PostgreSQL databases
We backed up, deleted and restored several databases. Surprisingly, the databases occupy 20% less storage space afterwards. Have we lost data or what could have happened here? Autovacuum runs ...
- 1