Stack Overflow
Stack Overflow

Questions tagged [vacuum]

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

232 questions
46
votes
8 answers

PostgreSQL - how to run VACUUM from code outside transaction block?

I am using Python with psycopg2 and I'm trying to run a full VACUUM after a daily operation which inserts several thousand rows. The problem is that when I try to run the VACUUM command within my code I get the following…
Wayne Koorts
  • 10,861
  • 13
  • 46
  • 72
44
votes
5 answers

How to vacuum sqlite database?

I want to know how to vacuum sqlite database. I tried a syntax MANUAL VACUUM command for the whole database from command prompt: $sqlite3 database_name "VACUUM;"; But it's giving error as: near "database_name": syntax error. and also AUTO…
meghalee
  • 441
  • 1
  • 4
  • 3
43
votes
5 answers

What does it mean to vacuum a database?

As referenced by this Firefox bug, what does the act of vacuuming a database accomplish? Is this operation supported by all modern database software, or only certain ones?
An̲̳̳drew
  • 13,375
  • 13
  • 47
  • 46
40
votes
1 answer

Do I need to reindex after vacuum full on Postgres 9.4

I am using Postgres 9.4. I just ran vacuum full. I read about the differences between vacuum and vacuum full and considered a lot if I should run vacuum or vacuum full. As far as I can say, I required vacuum full and my db size came down from 48 GB…
Akshar Raaj
  • 14,231
  • 7
  • 51
  • 45
35
votes
2 answers

Why is it a vacuum not needed with Mysql compared to the PostgreSQL?

I am more familiar with PostgreSQL than MySQL. Have encountered wraparound Id failure once with the PostgreSQL db and then understood the importance of vacuuming in the db. Actually, that was such a massive overhead work to deal with(and it was with…
RunningAdithya
  • 1,656
  • 2
  • 16
  • 22
29
votes
2 answers

How do I know if the statistics of a Postgres table are up to date?

In pgAdmin, whenever a table's statistics are out-of-date, it prompts: Running VACUUM recommended The estimated rowcount on the table schema.table deviates significantly from the actual rowcount. You should run VACUUM ANALYZE on this…
Beibei
  • 586
  • 1
  • 4
  • 6
24
votes
2 answers

Refreshing materialized view CONCURRENTLY causes table bloat

In PostgreSQL 9.5 I've decided to create a materialized view "effects" and scheduled an hourly concurrent refresh, since I wanted it to be always available: REFRESH MATERIALIZED VIEW CONCURRENTLY effects; In the beginning everything worked well, my…
Alechko
  • 1,406
  • 1
  • 13
  • 27
21
votes
1 answer

Database table size did not decrease proportionately

I am working with a PostgreSQL 8.4.13 database. Recently I had around around 86.5 million records in a table. I deleted almost all of them - only 5000 records are left. I ran reindex and vacuum analyze after deleting the rows. But I still see that…
VJ Vélan Solutions
  • 6,434
  • 5
  • 49
  • 63
18
votes
2 answers

PostgreSQL: How to enable autovacuum?

How does one enable autovacuum in PostgreSQL? I understand the purpose, I just can't find a simple answer regarding how to enable it.
JTW
  • 3,546
  • 8
  • 35
  • 49
16
votes
2 answers

Space required for VACUUM FULL table

From the PostgreSQL 10.4 manual regarding a full vacuum: Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are…
Thrasi
  • 418
  • 1
  • 5
  • 15
16
votes
3 answers

Redshift VACUUM cannot run inside a transaction block on SQLWorkbenchJ

I have got a: VACUUM cannot run inside a transaction block error on SQLWorkbenchJ in Redshift, but I already commit all transactions before this.
Ting Jia
  • 197
  • 1
  • 1
  • 6
15
votes
2 answers

How to efficiently vacuum analyze tables in Postgres

I had a huge query running on postgres and one of the join tables always did a sequential scan. There is an index on the constraint column and postgres just didn't use it. I ran a VACUUM ANALYZE, and then the postgres query plan indicates that an…
Ramanan
  • 461
  • 1
  • 5
  • 8
11
votes
4 answers

Amazon Redshift at 100% disk usage due to VACUUM query

Reading the Amazon Redshift documentatoin I ran a VACUUM on a certain 400GB table which has never been vacuumed before, in attempt to improve query performance. Unfortunately, the VACUUM has caused the table to grow to 1.7TB (!!) and has brought the…
Maxim Kogan
  • 820
  • 2
  • 7
  • 10
11
votes
3 answers

How to VACUUM a Core Data SQLite db?

By design, Core Data does not issue a VACUUM SQL command to its SQLite database(s), as detailed here. I'm creating a Core Data application that'll store, and later delete, large binary files (2-10MB in size) in a SQLite db. Over time this will…
Dave
  • 12,408
  • 12
  • 64
  • 67
10
votes
1 answer

Why does vacuum analyze change query plan while analyze does not?

I wanted to utilize the power of index-only scans in Postgres and experimented with one table: CREATE TABLE dest.contexts ( id integer NOT NULL, phrase_id integer NOT NULL, lang character varying(5) NOT NULL, ranking_value double precision, …
greatvovan
  • 2,439
  • 23
  • 43
1
2 3
…
15 16