Stack Overflow
Stack Overflow

Questions tagged [vacuum]

In SQLite and PosgreSQL, the VACUUM command rebuilds the entire database.

232 questions
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
0 answers

Which temp directory is used by SQLite VACUUM

When the SQLite VACUUM command is run pro grammatically, how to find which temporary directory is used while executing the command. I am using sqlite version 3.6.7 I have this question because I get the following exception. So I want to check…
Srinivasan
  • 399
  • 1
  • 2
  • 9
2
votes
1 answer

PostgreSQL vacuuming a frequently updating jsonb field

I have postgres table with jsonb field. Field size is about 2-4kb per row. My application updates 100k rows per day 2000 times (changing 0.1-0.5% of data in field). Autovacuum is off, vacuum full runs every day at night. Vacuum frees about 100-300gb…
owl sleeping
  • 21
  • 1
2
votes
0 answers

How can I see the temp files that Vacuum creates in SQLite while running

I've read the information about where SQLite puts the temp files while vacuuming (https://sqlite.org/tempfiles.html), but I can't find the file while vacuum is running. I looked in SQLite source and it appears the temp file name is vacuum_db.*, but…
user1838913
  • 103
  • 6
2
votes
0 answers

Deleted rows from PostgreSQL table but disk space not released

I run df -h and it says 36Gb used, 9Gb available on my server. I can see that my PostgreSQL db file is 26Gb. I delete millions of rows from my database, roughly half of the data in there. I run df -h and it says the exact same numbers: 36Gb used,…
jsstuball
  • 4,104
  • 7
  • 33
  • 63
2
votes
1 answer

best disk saving strategy for "replacement inserts"

Every day I delete hundreds of thousands of records from a large table, then I do some calculations (with new data) and replace every one of the records that I previously deleted. I thought doing regular vacuum tbl would do the trick. I know it…
mountainclimber11
  • 1,339
  • 1
  • 28
  • 51
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
2
votes
0 answers

TRUNCATE and VACUUM FULL doesn't free space on PostgreSQL server

I imported data into 30 tables (every table also has indexes and constraints) in my PostgreSQL database. After some check I truncated all the tables and performed vacuum full analyze on all the tables. Some space indeed was freed but I`m sure that…
JeyJ
  • 3,582
  • 4
  • 35
  • 83
2
votes
1 answer

PostgreSQL how to find what is causing Deadlock in vacuum when using --jobs parameter

How to find in PostgreSQL 9.5 what is causing deadlock error/failure when doing full vacuumdb over database with option --jobs to run full vacuum in parallel. I just get some process numbers and table names... How to prevent this so I could…
Sasa Bajic
  • 113
  • 1
  • 6
2
votes
1 answer

Strategies to preserve disk space in high input frequency application

I have a requirement to support shrinking of a database which has a data entry rate of 1 entry (approx. 300KB) per second. The database file can reach 3GB. The current database has no auto vacuum feature. Database file space going over a certain…
geejay
  • 5,440
  • 8
  • 50
  • 60
2
votes
0 answers

Why does vacuum reindex on an already sorted table take forever?

I have a stuck 'vacuum reindex' operation and am wondering what may be the cause for it taking such a long time. I recently changed the schema of one of my Redshift tables, by creating a new table with the revised schema and deep copying the data…
matangover
  • 357
  • 2
  • 12
2
votes
2 answers

n_dead_tup vs dead_tuple_count in postgresql?

I initially thought n_dead_tup and dead_tuple_count in PostgreSQL give the same counts. But they seem to be not. I do not quite understand what exactly is difference. Following are my observations: Created a table with 10k rows. Updated all the 10k…
Raviteja
  • 1,892
  • 3
  • 15
  • 12
2
votes
1 answer

SQLite vacuuming / fragmentation and performance degradation

Let's say I periodically insert data into a SQLite database, then purge the first 50% of the data, but I don't vacuum. Do I have something like zeroed-out pages for the first 50% of the file now? If I add another batch of data, am I filling in those…
Calpau
  • 921
  • 10
  • 21
2
votes
2 answers

postgreSQL vacuum temp files?

I've got a "little" problem. A week ago my database was reaching full disk capacity. I deleted many rows in different tables trying to free up disk space. After which I tried running a full vacuum which did not complete. What I want to know is. When…
jorgen
  • 1,217
  • 6
  • 22
  • 41
2
votes
1 answer

VACUUM database and WAL mode

I have SQLite database in WAL mode. Each time I perform a VACUUM on the database, the journal mode reverts to DELETE next time I reconnect. For example: PRAMGA journal_mode; -- returns wal VACUUM; PRAGMA journal_mode; -- returns wal -- disconnect…
Polina
  • 75
  • 1
  • 1
  • 8
1 2 3
…
15 16