113 questions
- Bountied 0
- Unanswered
- Frequent
- Score
- Trending
- Week
- Month
- Unanswered (my tags)
2
votes
4
answers
103
views
Postgresql stops using filter index
I have a very simple table for a task queue and keep taking records out one by one. If I add the filter_selector after filling/resetting the table, postgresql will use it and run super fast. But if I ...
1
vote
1
answer
295
views
Increasing autovacuum_vacuum_cost_limit to prevent multiXactID depletion
I have a large PostgreSQL database that is used by a web server. The server often receives a steady load of requests (up to ~1000rps), where each request makes an insert of a single row into several ...
0
votes
0
answers
87
views
Postgres vacuum transaction idles for days
A few days ago we received the following error messages and started vacuum/analyze manually:
ERROR: database is not accepting commands to avoid wraparound data loss in database "xxxx"
HINT: ...
1
vote
2
answers
263
views
How is manual ANALYZE different from being scheduled by autovacuum
We encountered a situation where CPU usage hit 100% due to a frequently running query. The issue resolved after performing a manual ANALYZE on the table involved in the query. However, I noticed the ...
0
votes
1
answer
972
views
How can I prevent aggressive autovacuuming from locking an insert-only table in Postgres 13?
I am running a Postgres 13 instance with an insert-only table. When the application writes to this table it manually acquires a "SHARE ROW EXCLUSIVE" lock (as a form of concurrency control ...
5
votes
1
answer
386
views
Does sqlite3 ever reclaim space on its own, without running VACUUM?
I was working with a large (3.22GB) SQLite3 database containing a lot of binary data. I updated a number of rows in the largest table in the database, replacing a binary column in each row with a ...
0
votes
2
answers
164
views
How to resolve unkown session "startup" after Vacuum full
Last night we did a vacuum full operation on partitioned tables.
A few hours after the vacuum full, I found cpu rising with an unknown session called "startup" when queried by top command.
We cannot ...
0
votes
0
answers
265
views
Regular (AUTO)VACUUM in Postgres database does not release disk space taken by vacuum overhead
I have running Postgres instance (version 16.1) in AWS with one table that has following schema:
Column
Type
Modifiers
user_id
integer
not null default nextval('profile_user_id_seq'::regclass)
...
-1
votes
3
answers
9k
views
Temporary shutdown of autovacuum during rush hour [closed]
I have been asked to temporarily disable autovacuum in PostgreSQL 14.3. From 7 a.m. to 5 p.m., autovacuum is to be turned off, and at other times it is to be turned back on. I intend to achieve this ...
0
votes
2
answers
1k
views
Why have Postgres tables doubled in size after cancelling a DELETE?
I ran a simple delete query on a telemetries table to clear up some data but cancelled as it was taking really long. I noticed that after I cancelled the query some of the tables (all partitions of ...
0
votes
1
answer
472
views
Postgres autovacuum stops working after relfrozenxid age hits autovacuum_freeze_max_age
We use postgres 11 in Azure cloud as a temporary store. Two tables in particular receive large number of inserts and deletes (hundreds of millions) in lots of small transactions with heavy lock ...
0
votes
2
answers
1k
views
Autovacuum does not start. How to understand the reason? Cost-based auto vacuum is already disabled
PostgreSQL v12. Database cluster size: 12+ Tb (30+ databases 100-600Gb each). Server: 40 cores, 128Gb ram.
For some reason, autovacuum stopped running. How to understand the reason?
Yesterday, the ...
1
vote
2
answers
976
views
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 ...
0
votes
1
answer
59
views
PostgreSQL autovacuum Statistic Level For Table Column
We can list tables' s custom options why checking pg_class' s reloptions columns. For example; like this post.
select relname, reloptions
from pg_class
where relname = 'a_table';
Yet, I couldn' t ...
0
votes
0
answers
373
views
postgres vacuum_freeze_min_age vs autovacuum_freeze_min_age
I fail to understand why pg_settings parameter vacuum_freeze_min_age is named differently than the table parameter autovacuum_freeze_min_age. Isn't the purpose of this setting on table level to ...