1

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.

2
  • What's your question?
    – Falmarri
    Commented May 4, 2012 at 18:13
  • Which PostgreSQL version?
    – user1822
    Commented May 4, 2012 at 18:48

1 Answer 1

1

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.
6
  • Wouldn't CREATE newtable AS SELECT old.*, '0'::int4 ... be faster within the transaction?
    – vyegorov
    Commented May 4, 2012 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
    Commented May 4, 2012 at 20:37
  • Maybe CREATE TABLE newtable LIKE old; ALTER newtable ...; INSERT INTO newtable ... will be faster, just guessing...
    – vyegorov
    Commented May 4, 2012 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
    Commented May 4, 2012 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. Commented May 4, 2012 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.