Stack Overflow
Stack Overflow

Questions tagged [vacuum]

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

232 questions
3
votes
2 answers

postgres vacuum didn't change bloatiness

Here are the top bloated tables before and after running a manual vacuum operation on all the tables in a heroku managed postgresql 9.2 database. As you can see, not much has changed, some waste has even increased... What could the reason be? Is…
Jonathan Livni
  • 101,334
  • 104
  • 266
  • 359
3
votes
1 answer

How to reclaim space occupied by unused LOBs in PostgreSQL

I have a medium sized database cluster running on PostgreSQL 8.3. The database stores digital files (images) as LOBs. There is a fair bit of activity in the database cluster, a lot of content is created and deleted in an ongoing manner. Even though…
sola
  • 1,498
  • 14
  • 23
3
votes
1 answer

Correct order of Postgres maintenance

I have a big table which is suffering from Index bloating because a lot of rows are frequently updated. I'm also in the process of deleting a big number of rows. What would the correct order of executing the following tasks in order to recover disk…
Gothmog
  • 871
  • 1
  • 8
  • 20
3
votes
1 answer

How to run the example code for a gem such as Vacuum?

I'm new to rails, and looking to set up a site that uses Amazon's product API. I'm used to using the API in PHP but keen to move to Ruby if at all possible. I've been trying the various Amazon product API gems, Ruby/AWS, Amazon-ECS, and now Vacuum.…
Don H
  • 891
  • 2
  • 15
  • 24
2
votes
1 answer

Postgres wraparound prevention on unchanged table

Postgresql documentation on vacuum states that Frozen row versions are treated as if the inserting XID were FrozenTransactionId, so that they will appear to be “in the past” to all normal transactions regardless of wraparound issues, and so such…
Peter
  • 932
  • 5
  • 19
2
votes
2 answers

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 database_id='123'; -- returns ~17.2 million The query…
Johnny Metz
  • 5,977
  • 18
  • 82
  • 146
2
votes
2 answers

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…
Neil C. Obremski
  • 18,696
  • 24
  • 83
  • 112
2
votes
1 answer

Postgres - pg_statistic vacuum timeout

In my Aurora Postgres server I continuously see a vacuum timeout every minute for the following: autovacuum: VACUUM pg_catalog.pg_statistic I tried doing it manually and got following output: INFO: vacuuming "pg_catalog.pg_statistic" INFO: …
adnan kamili
  • 8,967
  • 7
  • 65
  • 125
2
votes
2 answers

What is difference between pg_squeeze and pg_repack?

I've been working on optimizing tables in database. One of our table requires monthly vacuuming because of cleaning up processes.pg_squeeze Table size can get upto 25 GB. As this table is used by production users, we can't afford downtime every…
vivekpadia70
  • 1,039
  • 3
  • 10
  • 30
2
votes
2 answers

What will happen if you insert 5 billion rows as independent transactions? What will the vaccum do to the xmin column?

I understand that xmin is limited to 2^32-1, about 4 billion entries. Vacuum cleans up dead tuples, postgres maintains a transaction for every update to a row and those are cleaned up as a process of vacuum. Lets say, i am just inserting 5 billion…
Ash_this
  • 53
  • 5
2
votes
1 answer

Postgres DB size 3 time bigger that expected

Every night we are truncating most of the tables in the DB and inserting data from remote databases. The total size of data that we are inserting every night is about 300 GB. The problem is that DB size is much bigger: SELECT pg_size_pretty(…
Anna Rybachkova
  • 21
  • 1
2
votes
1 answer

When and why should i trigger pg_stat_reset()?

I am trying to understand how to monitor and tune postgresql performance. I started with exploring tables pg_stat_all_tables, pg_stat_statements in order to gather information about live tuples, dead tuples, last autovacuum time etc. There were some…
lukhol
  • 318
  • 2
  • 6
  • 18
2
votes
2 answers

Index size after autovacuum

Good day. I was reading Postgres official documentation related to Vacuum process and Reindex routine. Some sentences were unclear for me so I want to clarify them.(Postgres documentation for version 12) First of all. I did understand that…
Almas Abdrazak
  • 3,209
  • 5
  • 36
  • 80
2
votes
1 answer

How to do full vacuum on table with lot of dead rows w/o disk space?

Will try to describe the problem: We have a table "stories", current statistics: total_relation_size: 188 GB indexes only size: 52 GB n_tup_del: 274299085 n_live_tup: 368846049 We currently have ~6 gigabytes left on our SSD. Now we mounted another…
Roman Ganoshenko
  • 55
  • 1
  • 5
2
votes
1 answer

Reclaim space from Core Data SQLite store with NSPersistentContainer?

My app implements a web cache using Core Data with an SQLite store (a bit like NSURLCache but with more control over what is cached and when it can be purged from the cache). I can purge old data from the cache when it gets too big, but of course…
Son of a Beach
  • 1,733
  • 1
  • 11
  • 29
1 2 3
…
15 16