Stack Overflow
Stack Overflow

Questions tagged [autovacuum]

Postgres Autovacuum Daemon

PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands. When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples. These checks use the statistics collection facility; therefore, autovacuum cannot be used unless track_counts is set to true. In the default configuration, autovacuuming is enabled and the related configuration parameters are appropriately set.

104 questions
2
votes
0 answers

Very slow query with PostgreSQL

I have a very serious problem with PostgreSQL 14. In practice, every 5 days or so my tables, or rather some, are corrupted. When I launch a simple query, it does not execute and it remains stuck.`enter code here` This DB receives many write-to's in…
Leone Miceli
  • 21
  • 3
2
votes
0 answers

Azure Postgres AUTOVACUM AND ANALYZE THRESHOLD - How to change it?

I am coming again with another Postgres question. We are using the Managed Service from Azure that uses autovacuum. Both vacuum and statistics are automatic. The problem I am getting is that for a specific query, when it is running at specific…
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
2
votes
1 answer

Understanding auto-vacuum and when it is triggered

We've noticed one of our tables growing considerably on PG 12. This table is the target of very frequent updates, with a mix of column types, including a very large text column (often with over 50kb of data) - we run a local cron job that looks for…
joe
  • 305
  • 1
  • 4
  • 9
2
votes
2 answers

Why autovacuum process takes so much memory and swap memory?

We have a product log database which only produces insert sql。 But we found that The autovacuum process took so much memory。 it takes about 16 GB at peak, And it is happend about every 2 month。The folloing is the detail information, Any one know…
francs
  • 8,511
  • 7
  • 39
  • 43
2
votes
1 answer

PostgreSQL11 space reuse under high delete/update rate

We are evaluating PostgreSQL 11.1 for our production. Having a system with 4251 updates per second, ~1000 deletes per second and ~3221 inserts per second and 1 billion transactions per day, we face a challenge where PostgreSQL does not reuse its…
Aliza Abulafia
  • 31
  • 2
2
votes
1 answer

Need clarification related to Autovacuum configuration parameters

What is the difference between "autovacuum_naptime" and "autovacuum_vacuum_cost_delay"? What is the difference between "autovacuum launcher process" and "autovacuum worker process"? How to control the number of "autovacuum launcher process" and…
Raghavendra
  • 519
  • 1
  • 11
  • 25
2
votes
1 answer

Why autovacuum is not running

If the parameter autovacuum is on, and the parameter track_counts is on also - why it's look like the autovacuum has never worked? select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables where…
user2671057
  • 1,411
  • 2
  • 25
  • 43
2
votes
3 answers

why writes in a table prevent vacuums in another?

Having READ COMMITTED isolation level, idle transactions that have performed a write operation will prevent vacuum to cleanup dead rows for the tables that transaction wrote in. That is clear for tables that were written by transactions that are…
alostale
  • 770
  • 1
  • 11
  • 21
1
vote
2 answers

How can i tune autovacuum configs for pg toast tables?

I have a pg_toast table which has a lot of dead tuples accumulating. The autovacuum job on it runs for a very long time. I wanted to tune the autovacuum config, to make it more aggressive. When i'm trying to set the config I get the following…
sinu john
  • 31
  • 2
1
vote
1 answer

Vacuum of empty table takes 3 seconds

Running vacuum on some of our systems takes 3 seconds for an empty table: create table t (c int); vacuum t; -- 3 seconds vacuum t; -- 3 seconds vacuum t; -- 3 seconds ... On my local installation it takes only milliseconds. Why is it so slow on…
Peter
  • 932
  • 5
  • 19
1
vote
1 answer

Autovacuum and partitioned tables

Postgres doc tells that partitioned tables are not processed by autovacuum. But still I see that last_autovacuum column from pg_stat_user_tables is populated with recent timestamps for live partitions. Does it mean that these timestamps are set by…
user3714601
  • 1,156
  • 9
  • 27
1
vote
1 answer

Is Postgres autovacuum process blocking reading and writing the table

We have a job that needs to insert millions of records through a REST API. We found that once after inserting a few millions records, the service hangs and stops responding. I suspect the problem is with the Postgres DB, so I grab the log (from AWS…
GeauxEric
  • 2,814
  • 6
  • 26
  • 33
1
vote
0 answers

postgresql 11 - is autovacuum disabled on tables?

I found this query to see if autovacuum is diabled on a given table. I have autovacuum and statistics collector enabled on the postgresql. SELECT reloptions FROM pg_class WHERE relname='my_table'; reloptions ---------------------------- …
Falcon
  • 47
  • 6
1
vote
1 answer

Effect of vacuum freeze in Postgresql

I am using postgresql for cloudSQL on GCP. One table is almost in the process of inserting. (Theoretically more than 10 million per day) Auto vacuum was performed when the data was about 10 billion. Also, while auto vacuum was running, other…
Hyungsik Jo
  • 146
  • 1
  • 14
1
vote
1 answer

Is estimated row count accurate when only inserts are done in a table?

We use PostgreSQL for analytics. Three typical operations we do on tables are: Create table as select Create table followed by insert in table Drop table We are not doing any UPDATE, DELETE etc. For this situation can we assume that estimates…
Pankaj Lal
  • 307
  • 1
  • 12
1 2
3
4 5 6 7