Questions tagged [vacuum]

The vacuum command in postgres releases unused space. Be sure to also include the [postgres] tag, and version tag such as [postgresql-9.6]. Include an operating system tag,

Filter by
Sorted by
Tagged with
19
votes
3answers
24k views

VACUUM FREEZE vs. VACUUM FULL

Can someone explain the difference between these types of VACUUM in PostgreSQL? I read the doc but it just says that FULL locks the tables and FREEZE "freezes" the tuples. I think that's the same. Am ...
3
votes
0answers
491 views

Stubborn autovacuum process slow things dows

We are facing a strange problem since yesterday. In a database of ours there is a partitioned table containing historical data. That is, every week's data goes to a different partition and stays ...
2
votes
3answers
2k views

Vacuum settings for mostly append-only data

I have a table with the following characteristics: We INSERT a few 100k rows each day We never UPDATE the rows We DELETE "old" data once a week From my shallow knowledge of Postgres VACUUM, it seems ...
4
votes
1answer
2k views

PostgreSQL maintenance

I am new to PostgreSQL and want to know more about maintenance options in pgAdmin III. I've read the documentation and now I know that vacuuming frees some space and I need to run it regularly. ...
1
vote
1answer
217 views

Which all system parameters to be considered for standard Vacuum process

We want to run standard vacuum process on our production database which is over 100 GB and have millions of dead tuples. Can anyone suggest what system parameters we need to keep in mind for setting ...
6
votes
1answer
3k views

PostgreSQL strategies to deal with disk filling up

I'm using PostgreSQL (8.4) to store data produced by an application making frequent inserts (in the table structure described below). The database keeps growing with time and, since the newer data is ...
15
votes
2answers
11k views

PostgreSQL difference between VACUUM FULL and CLUSTER

I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx ...
2
votes
1answer
2k views

“Truncate” or “Delete/Vacuum Full” for deleting some of the table rows

I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided ...
1
vote
2answers
759 views

Slow access to table in postgresql despite vacuum

I'm running PG 8.4 I had a table with about 20K records. Any query accessing this table with more than 1 record (e.g. joins) would be very very slow. Even a count would take like 20 seconds. Stats ...
1
vote
1answer
393 views

Postgres not vacuuming 1 table

I have created a table where I store serialized Java objects. They are frequently accessed and costly to create, so that is the reason. Solution works fine to me, however table size grows to 25GB ...
1
vote
1answer
777 views

Is PostgreSQL 9.2 autovacuum sufficient for a busy table?

I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database: CREATE TABLE ch02.markers ( ff_id SERIAL PRIMARY KEY, col1 boolean, col2 smallint, created_at timestamp, lat double precision, ...
1
vote
1answer
2k views

PostgreSQL vacuum duration on a big table

I have a very big table in my postgreSQL (over 300 million rows) that has never been vacuumed. Yesterday I tried a vacuum and analyze (not a full one) and it took about 7 hours to complete. Will it ...
13
votes
1answer
659 views

Disk file effects of delete and vacuum

I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
1
vote
1answer
2k views

How to know there is a maintenance job running on a postgresql database

I have a python script on postgresql database which is fetching records from sql server database and inserting it to postgresql DB after some filtering. this jobs is running by cron every two minutes. ...
3
votes
1answer
3k views

Dropping indexes doesn't seem to release space to the OS. Can it?

I'm running PostgreSQL 9.1. I have noticed really alarmingly bad indexes are taking up a lot of space on my server, e.g: evlampts=# SELECT pg_size_pretty(pg_relation_size('mcdata_2011_07')) AS ...
2
votes
2answers
683 views

pg_relation_size does not show any difference after VACUUM ANALYZE

I wrote a script to backup a postgresql using COPY and then delete those records. I have used pg_relation_size before and after delete. pg_relation_size DELETE VACUUM ANALYZE pg_relation_size But ...

1 2
3