Linked Questions

8
votes
2answers
13k views

Why wouldn't VACUUM ANALYZE clear all dead tuples?

We run a "manual" VACUUM ANALYZE VERBOSE on some of our larger tables after we do major DELETE/INSERT changes to them. This seems to work without issue although sometimes a table's VACUUM job will run ...
7
votes
2answers
8k views

how do you prevent dead rows from hanging around in postgresql?

I have production and staging RDS instances on amazon, and staging's data is a direct copy of production so both instances have duplicate data. Doing a EXPLAIN ANALYZE SELECT * from my_table WHERE ...
3
votes
2answers
2k views

How to optimize a table for a very high-frequency updates?

I have a table that contains a list of tasks that need to be run periodically: applaudience=> \d+ maintenance_task Table "...
3
votes
2answers
1k views

Alternative to CLUSTER without table lock

I'm facing performance degradation and increasing storage usage as a result of frequent new and updated records causing index and storage fragmentation. VACUUM doesn't help much. Unfortunetely ...
3
votes
2answers
20k views

Very slow execution of a simple query

I try to create a report for my data, but it is really slow on a big table. The table structure is: CREATE TABLE posts ( id serial NOT NULL, project_id integer, moderation character varying(...
0
votes
2answers
26 views

Size of data in DB is much larger than size of data dumped to disk

I have a table that contains 99.9% of the data in the database. When I query the size of the table and database, I get: mydb=# SELECT pg_size_pretty( pg_database_size('mydb') ); pg_size_pretty ------...
19
votes
1answer
15k views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: create table t(id serial); insert into t default values; insert into t default values; ...
10
votes
1answer
4k views

Reclaim disk space from dropped column without downtime

I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used. The docs suggest doing a table rewriting ...
10
votes
1answer
15k views

Can I do VACUUM FULL to pg_largeobject table?

I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from ...
5
votes
1answer
22k views

Postgres command failed: No space left on device

I'm running a command to create a new column on a fairly large table (about 315 million rows). This used more disk space than I expected and it has completely filled up my SAN. The command failed with ...
2
votes
1answer
3k views

Time taken by VACUUM FULL to reclaim space

After receiving some insightful guidance in a previous post here, I'm going to run VACUUM FULL on 4 PostgreSQL 9.3.10 tables. Table sizes are: 1) links_publicreply: ~30M rows, 9 columns, 3 indexes (...
2
votes
1answer
4k views

How to reduce size of PostgreSQL table after dropping columns?

I have a PostgreSQL database. One table is very large. I want to extract a TEXT column into a separate table and see how much I can reduce the size. The problem is that the size appears to stay the ...
1
vote
1answer
39 views

Copying Data into another table and removing older data should I do in batches and should I remove indexes and FK constraints?

I am looking to remove older data from a table which consists of 889 million rows of data. I have a script but I am trying to make it more robust as removing approximately 418 million rows of data to ...
1
vote
1answer
81 views

Strategies for reducing data to summaries within a table over time?

I have a large data set where large numbers of entries differ only by timestamp. In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data ...
1
vote
1answer
6k views

PostgreSQL, UPDATE CASE statement uses huge amount of space

I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well. I am using ...

15 30 50 per page