Postgresql – How to reduce size of PostgreSQL table after dropping columns

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.

1. No the only thing close to that is when you compile the server with the --with-segsize switch, this might help if your table is taking up more space than a gig and your file system can handle a single file being over a gig. If your inserting 20 gigs it will have to create 20 files if you don't use this switch. If your file system can handle a file over a gig you can just set it to a large value most likely see some benefit, worst case a small benefit.

2. Take a look at CLUSTER http://www.postgresql.org/docs/9.1/static/sql-cluster.htmland FILLFACTOR http://www.postgresql.org/docs/9.1/static/sql-createtable.html, http://www.postgresql.org/docs/9.1/static/sql-createindex.html

Note that FILLFACTOR can be applied to both tables and indexes.