If you want to free up space on the file system, either VACUUM FULL or CLUSTER can help you. You may also want to run ANALYZE after these, to make sure the planner has up-to-date statistics but this is not specifically required.
It is important to note using VACUUM FULL places an ACCESS EXCLUSIVE lock on your table(s) (blocking any operation, writes & reads), so you probably want to take your application offline for the duration.
In PostgreSQL 8.2 and earlier, VACUUM FULL is probably your best bet.
In PostgreSQL 8.3 and 8.4, the CLUSTER command was significantly improved, so VACUUM FULL is not recommended — it’s slow and it will bloat your indexes. `CLUSTER will re-create indexes from scratch and without the bloat. In my experience, it’s usually much faster too. CLUSTER will also sort the whole physical table using an index, so you must pick an index. If you don’t know which, the primary key will work fine.
In PostgreSQL 9.0, VACUUM FULL was changed to work like CLUSTER, so both are good.
It’s hard to make predictions, but on a properly tuned server with commodity hardware, 9 million rows shouldn’t take longer than 20 minutes.
- See the documentation for CLUSTER.
- PostgreSQL wiki about VACUUM FULL and recovering dead space