CODEWITHSUNDEEP
postgresqlstoragespacevacuum
hb0
hb0

Reputation: 3737

Postgres: Do we always need at least 3-4 times free the space of the biggest table?

we are using Postgres to store ~ 2.000.000.000 samples. This ends up in tables with ~ 500 mio entries and ~100GB Size each table.

What I want to do: E.g. update the table entries: UPDATE table SET flag = true;

After this, the table is twice as big, i.e. 200GB To get the space (stored on a SSD) back we: "VACCUM FULL table"

Unfortunately, this step needs again loads of space which results in the Vacuum to fail due to too little space left.

My Questions: Does this mean, that, in order to make this UPDATE query only once and to get the space back for other tables in this DB we need at least 300-400GB space for a 100GB table?

Upvotes: 0

Views: 341

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246578

In your scenario, you won't get away without having at least twice as much space as the table data would require.

The cheapest solution is probably to define the table with a fillfactor of 50 so that half of each block is left empty, thereby doubling the table size. Then the updated rows can all be in the same block as the original rows, and the UPDATE won't increase the table size because PostgreSQL can use the heap only tuple (HOT) update feature. The old versions will be freed immediately if there are no long running transactions that can still see them.

NOTE: This will only work if the colum you are updating is not indexed.

The downside of this approach is that the table is always twice the necessary size, and all sequential scans will take twice as long. It won't bother you if you don't use sequential scans of the table.

Upvotes: 1

Related Questions