Postgresql – the real difference between vacuum and vacuum analyze on Postgresql

VACUUM does not necessarily make unused space available to the filesystem, it merely makes the blocks re-usable for further INSERTs (or UPDATEs).

If I'm not mistaken the only way to also actively reduce the size on the file system would be a VACUUM FULL, but beware that needs an exclusive lock on the table(s).

Do you expect the table to not get any new rows or updates? Because if it will get new rows, then it doesn't really make sense to try to shrink the files phyiscally now if they are going to be increased again later due to new rows arriving.

When fsync is OFF, the PostgreSQL server will never issue any fsync system call, leaving entirely to the operating system the decision as to which blocks to transfer from write cache to disk, and when. The database engine never knows what has been sync'ed or not. In case of a server crash, it has to be assumed that the database is in an inconsistent state.

When synchronous_commit is OFF, the server issues fsync call, but not necessarily immediately at commit time for each transaction. It may delay them after the commit, for a maximum of wal_writer_delay multiplied by 3. With a default configuration that would be 600ms. This allows to reduce the number of fsync calls, especially for workloads with lots of small transactions. In case of a server crash, the transactions not yet sync'ed in the last 600ms before the crash may be rolled back, but the state of the database is always consistent.