Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a table with 50 million rows and 40+ text columns. Size of the table is around 17GB. Inside a transaction, I add another integer column to the table and update all the rows with corresponding values. After the transaction is done, the size of the table doubles to about ~35GB.

VACUUM FULL ANALYZE brings the size of the table back to 17GB, but it takes about 10 hours to complete! Here are the specs:

24GB RAM, 16 cores, 2.4GHz

postgresql.conf settings -

shared_buffers = 3GB
effective_cache_size = 15GB

All other settings are default.

share|improve this question
What's your question? – Falmarri May 4 '12 at 18:13
Which PostgreSQL version? – a_horse_with_no_name May 4 '12 at 18:48

migrated from stackoverflow.com May 5 '12 at 1:49

1 Answer

up vote 1 down vote

I see nothing unexpected there except that the VACUUM FULL ANALYZE took that long if you version is 9.0 or higher.

  • If you want to avoid the extreme bloat when updating every row in a table, update in smaller chunks and run VACUUM (not FULL) between the updates.
  • If you want to understand why this behaves this way, read up on the MVCC implementation in PostgreSQL.
  • If you want to eliminate bloat faster in pre-9.0 databases, use CLUSTER.
share|improve this answer
Wouldn't CREATE newtable AS SELECT old.*, '0'::int4 ... be faster within the transaction? – vyegorov May 4 '12 at 20:25
@vyegorov: It could be. Sometimes that's not convenient, though, because you would need to deal with primary keys, foreign keys, other constraints, permissions, indexes, etc., manually. If the speed difference is big enough, it could be worth the extra scripting. Rebuilding indexes and re-checking constraints might actually make it slower. – kgrittn May 4 '12 at 20:37
Maybe CREATE TABLE newtable LIKE old; ALTER newtable ...; INSERT INTO newtable ... will be faster, just guessing... – vyegorov May 4 '12 at 20:49
I am using Postgres 8.3.3 and my maintenance_work_mem is set to 16MB, I was thinking to bump it up to 1GB. – phocus May 4 '12 at 20:57
3  
Either way, upgrade your pg version to 8.3.latest NOW. You're missing 15 updates (8.3.18 is the latest) and that's over two years of updates and bug fixes. – Scott Marlowe May 4 '12 at 21:38
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.