Linked Questions

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 ------...
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 ...
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 ...
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; ...
122
votes
5answers
107k views

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
21
votes
2answers
51k views

PostgreSQL "size of temporary files"

I've imported data into a new database (about 600m rows of timestamp, integer, double). I then created some indexes and tried to alter some columns (got some out of space issues), the database is ...
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 ...
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 ...
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
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 "...
39
votes
4answers
40k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
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 (...
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 ...
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 ...
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 ...

15 30 50 per page