CODEWITHSUNDEEP
postgresqlpostgresql-9.6
Testie
Testie

Reputation: 43

Cleaning up files from table without deleting rows in postgresql 9.6.3

I have a table with files and various relations to this table, files are stored as bytea. I want to free up space occupied by old files (according to timestamp), however the rows should still be present in the table.

Is it enough to set null to bytea field? Will the data be actually deleted from the table this way?

Upvotes: 1

Views: 265

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246093

In PostgreSQL, updating a row creates a new tuple (row version), and the old one is left to be deleted by autovacuum.

Also, larger bytea attributes will be stored out-of-line in the TOAST table that belongs to the table.

When you set the bytea attribute to NULL (which is the right thing to do), two things will happen:

  • The main table will become bigger because of all the new tuples created by the UPDATE. Autovacuum will free the space, but not shrink the table (the empty space can be re-used by future data modifications).

  • Entries in the TOAST table will be deleted. Again, autovacuum will free the space, but the table won't shrink.

So what you will actually observe is that after the UPDATE, your table uses more space than before.

You can get rid of all that empty space by running VACUUM (FULL) on the table, but that will block concurrent access to the table for the duration of the operation, so be ready to schedule some down time (you'll probably do that for the UPDATE anyway).

Upvotes: 2

Related Questions