Stack Overflow
Stack Overflow

Questions tagged [vacuum]

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

232 questions
3
votes
0 answers

How do I observe this recursive function with GHood or Vacuum?

I'm using GHood to watch the execution of this implementation of the partition function and expected to see a binary tree. Instead I get this tree: import Debug.Observe p :: (Observable a, Ord a, Num a) => a -> a -> a p k n | k > n = 0 | k ==…
YHVH
  • 585
  • 4
  • 12
3
votes
0 answers

Is vacuum needed for in-memory sqlite database?

I use an in-memory sqlite database for some queueing of stuff between multiple threads and therefore we have a lot of writes, reads and deletes. But there are never more that a few KB in the database at a time. Now some customers complain memory…
Volker
  • 428
  • 4
  • 15
3
votes
3 answers

Postgres cant vacuum despite enough space left (could not resize shared memory segment bytes)

I have a docker-compose file with postgres: container_name: second_postgres_container image: postgres:latest shm_size: 1g and i wanted to vacuum a table, but got ERROR: could not resize shared memory segment "/PostgreSQL.301371499" to…
Michal
  • 150
  • 3
  • 13
3
votes
1 answer

How to Vacuum FULL on Primary node with Streaming replication in Postgresql 11

On production I have database of size 150 GB. Many rows are deleted from this table and Vacuum is applied on this. Now I need to release unused space from DB to disk for OS. So Vacuum Full needs to be applied. Streaming replication is configured on…
Darshan Shah
  • 157
  • 1
  • 1
  • 15
3
votes
1 answer

Is autovacuum (VACUUM) the reason why this PostgreSQL UPDATE query occasionally takes hours to finish running?

This sql query normally only takes a few minutes to run: update import_parts ip set part_manufacturer_id = pslc.part_manufacturer_id from parts.part_supplier_line_codes pslc where trim(lower(ip.line_code)) = trim(lower(pslc.supplier_line_code)) and…
John Kenn
  • 1,607
  • 2
  • 18
  • 31
3
votes
1 answer

In what order should I call ANALYZE and VACUUM in an SQLite3 database?

In what order should I call ANALYZE and VACUUM in an SQLite3 database? What is the difference if there is any? My use case is to create a database for lookup, so I fill it with data, call ANALYZE and VACUUM and then never modify the database…
Fabian
  • 4,001
  • 4
  • 28
  • 59
3
votes
1 answer

sqlite incremental vacuum removing only one free page

I have changed value of auto_vacuum PRAGMA of my sqlite database to INCREMENTAL. When I run PRAGMA incremental_vacuum; through 'DB Browser for SQlite' application it frees all the pages in the free_list. But when I am running same statement using…
Shashank Choudhary
  • 31
  • 2
3
votes
2 answers

Postgresql - large delete from 500Gb DB / auto-vacuum?

I need to delete around 80% of my 500Gb Postgresql DB. I have successfully run a delete command for around 50Gb of rows so far, and paused before proceeding. (This took a long time, perhaps one hour) I notice that after deleting around 50Gb of data,…
jsstuball
  • 4,104
  • 7
  • 33
  • 63
3
votes
1 answer

Determining the most important tables to ANALYZE

Based on this (https://stackoverflow.com/a/13954487/1543618) answer to a question regarding automated VACUUM FULL of bloated tables, a DBA (who doesn't work here anymore) developed a script to determine which tables to ANALYZE. Note: this is v8.4…
RonJohn
  • 349
  • 8
  • 20
3
votes
3 answers

What does removable mean in Postgres' vacuum output?

I have run a full VACUUM to a Postgres table with 9 million rows and I got this back: INFO: vacuuming "osm.asp_trs_roads_osm" INFO: "asp_trs_roads": found 329992 removable, 9660438 nonremovable row versions in 761738 pages DETAIL: 0 dead row…
user1919
  • 3,818
  • 17
  • 62
  • 97
3
votes
1 answer

PostgreSQL vacuuming a big table

I have Postgres 9.4.7 and I have a big table ~100M rows and 20 columns. The table queries are 1.5k selects, 150 inserts and 300 updates per minute, no deletes though. Here is my autovacuum config: autovacuum_analyze_scale_factor…
Max Selivanov
  • 33
  • 1
  • 4
3
votes
2 answers

Altering column type from int to bigint frees space?

I have table with an int type column, in a table of ~ 15 M rows. OS windows 7 and C disk (where postgres is installed) shows that: 59 GB free of 238 GB Then I changed this column type to bigint: ALTER TABLE mytable ALTER column col TYPE…
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
3
votes
0 answers

Postgresql 9.4 - pg_largeobject size does't correspond with the size of stored objects?

I've got a database which has approximately 43GB. When I count the size of pg_largeobject table it gives me 43GB too: SELECT pg_size_pretty(pg_table_size('pg_largeobject')); When I sum the sizes of all large objects by the following…
Miroslav Vozábal
  • 31
  • 2
3
votes
2 answers

How to get SQLite 'VACUUM' Progress

Is there a way to get the progress of sqlite 'VACUUM'?I am using this line of code here in Java: connection1.createStatement().executeUpdate("VACUUM"); The User(MySelf & I) has to wait from some seconds to some minutes,i know that the actual .db…
GOXR3PLUS
  • 6,877
  • 9
  • 44
  • 93
3
votes
2 answers

How to deal with a stuck PostgreSQL 9.3 VACUUM ANALYZE?

We run PostgreSQL 9.3 on the AWS RDS platform. Every night at 1am we've been running a global VACUUM ANALYZE job. Yesterday we observed severe degradation in performance and as it turned out we had 5 VACUUM ANALYZE processes stuck for the past 5…
Oleg Dulin
  • 1,379
  • 4
  • 13
  • 23
1 2 3
…
15 16