254 questions
Advice
1
vote
3
replies
38
views
Safe to run "VACUUM FREEZE" on template1 or template0 databases in postgres?
I run periodic VACUUM FREEZE on my main database to avoid aggressive vacuuming during busy times, wondering if it's safe to run freeze on template databases too before they reach the threshold (200M ...
- 7,577
0
votes
0
answers
40
views
Reclaim unused space with minimal memory
I am working with a sqlite database consisting of two tables, a headers table and a data table. The size of headers is negligible and the size of data is roughly 75GB (~465Million rows). The database ...
- 93
0
votes
0
answers
112
views
Databricks - VACUUM Not Deleting Files No Longer Referenced by Transaction Logs
I have created an external table in one of my Databricks notebooks. Below is the code used to create the table:
dataset = spark.read.table('catalog.schema.input_table')
dataset_in_scope.write....
- 515
0
votes
0
answers
79
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: ...
- 397
0
votes
0
answers
64
views
uncertain about postgresql lob migration data
So we have a springboot app using a postrgressql db.
We used to have some data that were mapped as CLOBs and BLOBs. Since we decided to move away from large objects we migrated the database and while ...
- 33
0
votes
1
answer
70
views
Vacuuming and analyzing redshift tables
import logging
import boto3
import sys
import argparse
from urllib.parse import urlparse
import os
import signal
import time
try:
sys.path.append(os.path.join(os.path.dirname(__file__), "lib&...
- 17
0
votes
0
answers
420
views
pgadmin SSL certificate error when performing maintence
When I use pgamin v4 8.11 to vacuum a table on a remote server not in my local network it fails with the error:
psql: error: connection to server at "xx.xxx.xx.xx", port 5432 failed:
SSL ...
- 225
0
votes
2
answers
156
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 ...
- 1
0
votes
1
answer
699
views
How to schedule a time for maintenance tasks for Delta Live Tables?
Here is the documentation from databricks
Delta Live Tables performs maintenance tasks within 24 hours of a table being updated. Maintenance can improve query performance and reduce cost by removing ...
- 1
0
votes
1
answer
124
views
Understanding Index-Only Scan Behavior with JSONB Columns in PostgreSQL 13
I have a query involving a JSONB column in PostgreSQL, structured as follows:
select ((option ->> 'fruit_id'))
from table1 t1
where ((option ->> 'fruit_id')) = '1'
limit 10
This query is ...
0
votes
0
answers
37
views
Database Integrity Concerns: Missing FK Constraints and Vacuum Process Implications
Version - PostgreSQL 10.21, compiled by Visual C++ build 1800, 64-bit
Platform - Windows
We are currently experiencing issues in the database where foreign key (FK) constraints are missing, leading to ...
0
votes
1
answer
954
views
Is Aggressive Auto vacuum impacting Query performance?
On postgres 14, we observed a 4x spike in the frequency of auto vacuum with correlation to our usual morning increase in production traffic. Database logs showed "Aggressive autovaccum to prevent ...
1
vote
1
answer
4k
views
PostgreSQL `set statement_timeout = 0;` doesn't seem to work – why?
I'm connecting to PostgreSQL using command
psql "host=localhost port=6432 dbname=mydatabase user=myuser password=mypassword connect_timeout=5"
and I want to run vacuum analyze but the ...
- 16.4k
0
votes
1
answer
529
views
Redshift VACUUM effect on concurrent queries
I have a process that runs VACUUM manually on a list of redshift tables on a daily basis to maintain consistent query performance. But sometimes, vacuuming one table takes about 2 hours. Is this ...
- 169
0
votes
0
answers
360
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 ...
- 1,417
1
vote
1
answer
404
views
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 ...
- 1,417
2
votes
2
answers
1k
views
Did postgres vacuum improve my query plan
I have a table store_record with 45 million records. I want to run a simple count query on the largest database_id. Note I have an index on database_id.
SELECT COUNT(*) FROM store_record WHERE ...
- 5,847
0
votes
0
answers
247
views
Postgresql : Maintaining the database and knowing more about relation/impact with each other Autovacuum , backend_xmin, datfrozenxid
I am newbie to Postgresql, My project is in financial transactions having a few tables with huge transaction data which will have frequent insert/update/delete on it.
Initially when I started, came ...
1
vote
1
answer
303
views
Flutter Sqflite Runtime Vacuum
I'm using sqflite database as asset database that contains one table which size is around 5MB. User activities are changing some of the fields in that table.
I'm considering to apply scheduled vacuum ...
- 101
0
votes
1
answer
4k
views
Postgres autovacuum to prevent wraparound (difference between usual autovacuum)
I've got a Postgres 11 database.
The database has logical dumps (REPLICA using streaming replication).
Am I wright in thinking that if I've got tables in which information
is inserted, but not ...
- 309
4
votes
2
answers
4k
views
Is it possible to run VACUUM FULL for a short while and get some benefit?
Is it possible to run PostgreSQL 11's VACUUM FULL for a short while and then get some benefit? Or does cancelling it midway cause all of its progress to be lost?
I've read about pg_repack (https://aws....
- 20.6k
3
votes
1
answer
4k
views
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....
- 1,271
1
vote
0
answers
399
views
Is there a way to VACUUM an sqlite3 database in parts to avoid OperationalError: database or disk is full/reduce size of sqlite3 databse
I have a rather large sqlite3 database that I've built/populated through python over a few months. I have done many inserts/deletes etc while putting it together and it now uses the majority of my ...
- 3,395
0
votes
1
answer
427
views
Vacuum HDF5 dataset (to remove rows of data and resize)
Let say I have HDF5 dataset with maxshape=(None,1000), chunk=(1,1000).
Then whenever I need to delete a some row I just zero-it (many):
ds[ix,:] = 0
What is the fastest way to vacuum-zeroth-rows ...
- 7,536
1
vote
0
answers
1k
views
Why VACUUM FULL doesn't shrink the size of my table?
I'm struggling with a bloated table that I'm unable to shrink. It has just 6 rows but its size is 140MB+ and it's continously updated\deleted by quick transactions. I tried using VACUUM and VACUUM ...
