Stack Overflow
Stack Overflow

Questions tagged [vacuum]

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

232 questions
4
votes
3 answers

how to automatically determine which tables need a vacuum / reindex in postgresql

i've written a maintenance script for our database and would like to run that script on whichever tables most need vacuuming/reindexing during our down time each day. is there any way to determine that within postgres? i would classify tables…
Justin Lawrence
  • 613
  • 2
  • 7
  • 17
4
votes
1 answer

Can I run a PostgreSQL Vacuum Once every 1-2 minutes?

I am considering various MVCC-capable databases for an upcoming project and PostgreSQL came onto my radar. The requirements of my program involve a sequence roughly like the following: Read some information from the current version of the database,…
MindJuice
  • 4,121
  • 3
  • 29
  • 41
4
votes
0 answers

100% CPU load on AWS Aurora Postgres cluster after batch-deleting records

We have a scheduled cleanup job that removes records that have a timestamp in the past. We select them in batches of 10’000 and delete them by primary key (composite key integer and timestamp). This job might delete up to 200’000 records within a…
Tobi
  • 41
  • 2
4
votes
1 answer

Speed up autovacuum in Postgres

I have a question regarding Postgres autovacuum / vacuum settings. I have a table with 4.5 billion rows and there was a period of time with a lot of updates resulting in ~ 1.5 billion dead tuples. At this point autovacuum was taking a long time…
Rio
  • 107
  • 1
  • 9
4
votes
1 answer

How to vacuum database tables in a Spring Boot application?

My current attempt (according to this answer) looks as follows: @Service class VacuumDatabaseService( private val entityManager: EntityManager ) { fun vacuumAllTables() { val session =…
Tobias Hermann
  • 9,936
  • 6
  • 61
  • 134
4
votes
2 answers

auto vacuum vs vacuum in postgresql

Postgresql has the functionality of Vacuum for recollecting the space occupied by dead tuples. Auto vacuum is on by default and runs according to the configuration setting. When I check the output of pg_stat_all_tables i.e. last_vacuum and…
YogeshR
  • 1,606
  • 2
  • 22
  • 43
4
votes
1 answer

Postgresql explicit VACUUM vs. auto-VACUUM: Differences? Recommendations?

Quick question from a PostgreSQL (relative) newb: We run a batch process that, as its final step, deletes most of the previous batches. Disk space is a concern, so we need to ensure that PostgreSQL cleans up after itself. Other than forcing…
Jim Burnell
  • 948
  • 3
  • 9
  • 21
4
votes
0 answers

Tuning autovacuum for update heavy tables

I have a very update heavy table. The problem is that the table grows a lot, as the autovacuum can not catch up. The autovacuum kick in every 2 minutes, so it is running file. I have an application which is making like 50k updates(and a few…
Georgi
  • 372
  • 3
  • 15
4
votes
2 answers

Programmatically execute VACUUM command using Sqlite3 and C++?

I am trying to programmatically execute the VACUUM command in C++ using the Sqlite3 library for C++. See reference at C-language Interface Specification for SQLite. Can someone give a code snippet of how to do this? I tried calling this but it gives…
Hiten Naresh Vasnani
  • 518
  • 1
  • 4
  • 17
4
votes
4 answers

PostgreSQL Long VACUUM

I am currently cleaning up a table with 2 indexes and 250 million active rows and approximately as many dead rows (or more). I issued the command VACCUM FULL ANALYSE from my client computer (laptop) to my server. It has been going about its business…
Nicholas Leonard
  • 2,566
  • 4
  • 28
  • 32
4
votes
1 answer

Amazon Redshift VACUUM runs per schema or per database?

Does VACUUM; with no other arguments run per database or per current schema on amazon redshift? The reason I am asking this is because when VACUUM completes on one schema and I change the default schema, and run it again, it takes a whole hour to…
Martin Taleski
  • 6,033
  • 10
  • 40
  • 78
4
votes
1 answer

Postgresql: Autovacuum partitioned tables

We have a very large table that was partitioned into monthly tables. We have no autovacuum parameters set in the postgresql.conf file, so it's on by default with default parameters. The past months tables table_201404, table_201403 do not get…
dsmorey
  • 453
  • 1
  • 5
  • 16
4
votes
2 answers

Why does $dbh->do('VACUUM') fail with Perl's DBD::SQLite?

I want to do VACUUM at a certain time on a SQLite database under Perl, but it always says DBD::SQLite::db do failed: cannot VACUUM from within a transaction So how do I do this? my %attr = ( RaiseError => 0, PrintError => 1, AutoCommit => 0 ); my…
Galaxy
  • 1,862
  • 1
  • 17
  • 25
4
votes
1 answer

Ruby on Rails: How do I use a Gem? (Vacuum Amazon API)

I found an Amazon Gem that I want to use https://github.com/hakanensari/vacuum/. I've only ever used gems with in depth tutorials or following a RailsCast. I'm looking for tips on using gems I find online. I'll explain the steps I'm doing, and…
david
  • 582
  • 5
  • 13
4
votes
1 answer

sqlite vacuum with django

after insert and delete lots records into sqlite, the size of sqlite db file keeps growing, is there any way to use django vacuum the tables? some settings that let sqlite auto-vacuum or manually write own django command to vacuum Updated: I use…
pinkdawn
  • 1,023
  • 11
  • 20
1 2
3
…
15 16