PostgreSQL Does Not Free Up Physical Space After DELETE

The above is a simple table with a corresponding index that we are going to use to describe the data deletion flow using two methods: DELETE and TRUNCATE, and how those methods affect physical space after completion.

Impact of DELETE and TRUNCATE on disk space

There are scenarios where it becomes necessary to occasionally delete all the data from a table. To achieve this, you can use either DELETE or TRUNCATE operation.

DELETE operation may be more favorable when there are frequent queries against the table that is being cleaned up. That’s because data is removed without waiting for all transactions to complete, allowing them to still access the table rows which were marked for deletion.

In comparison, TRUNCATE operation violates the strict MVCC (multiversion concurrency control) semantics. This may lead to inconsistency between the contents of the truncated table and other tables in the database.

A problem arises when dealing with tables containing gigabytes of data. PostgreSQL does not release the freed memory back to the operating system after DELETE operation. To make it happen, you have to request it explicitly through the execution of VACUUM FULL, as shown in Figure 2.

TRUNCATE operation, on the other hand, immediately reclaims physical memory after completion, as shown in Figure 3.

Hands-on storage space comparison before/after operations applied

Let’s create a table and compare the storage space before and after applying both operations.

First, create initial table with some data and check its size:

CREATE TABLE books (id serial PRIMARY KEY, title VARCHAR (255) UNIQUE NOT NULL);

INSERT INTO books (title) VALUES ('Software Architecture: The Hard Parts');
INSERT INTO books (title) VALUES ('Fundamentals of Software Architecture');
INSERT INTO books (title) VALUES ('High Output Management');
INSERT INTO books (title) VALUES ('The History of Philosophy');
INSERT INTO books (title) VALUES ('Cassandra: The Definitive Guide');

SELECT pg_size_pretty(pg_total_relation_size('books'));
pg_size_pretty
----------------
40 kB
(1 row)

Next, execute TRUNCATE operation and check the size:

TRUNCATE TABLE books;

SELECT pg_size_pretty(pg_total_relation_size('books'));
pg_size_pretty
----------------
16 kB
(1 row)

Now, do the same with DELETE operation:

DELETE FROM books;

SELECT pg_size_pretty(pg_total_relation_size('books'));
pg_size_pretty
----------------
40 kB
(1 row)

As observed, the table size after TRUNCATE is 16 kB, when after DELETE it is still 40 kB. Operating system thinks that the occupied disk space is still in use after DELETE, while TRUNCATE immediately reclaims it.

DELETE + VACUUM FULL to emulate TRUNCATE behavior

You can achieve a similar behavior to TRUNCATE using DELETE. Execute DELETE followed by VACUUM FULL, as shown below. Keep in mind that because VACUUM FULL places an ACCESS EXCLUSIVE lock, it may take some time to acquire that lock initially.

VACUUM (FULL, ANALYZE) books;

SELECT pg_size_pretty(pg_total_relation_size('books'));
pg_size_pretty
----------------
16 kB
(1 row)

Furthermore, when executing VACUUM FULL, it utilizes additional disk space roughly equivalent to the size of the table. That is because the previous copy of the table cannot be released until the new one is completed.

Summary

Use TRUNCATE to occasionally remove data from large tables. It is simpler, faster and avoids followup VACUUM operations.

Use DELETE for smaller tables. Run or automate execution of VACUUM FULL on a timely basis.

Further reading

Routine Vacuuming - gives more details about VACUUM operations and their automation.

PostgreSQL truncation speed - detailed answer about the difference between DELETE and TRUNCATE operations.