Keeping Postgres tidy with partitioning

If you are in doubt whether partitioning is a useful tool with this one example I'm hoping you won’t wonder any further.

Let’s assume we have some historical data, such as logs, jobs, actions, events, metrics or something else, that stored in the database.

=# select pg_size_pretty(pg_relation_size('history_log'));
 pg_size_pretty
----------------
 155 GB


=# select count(*) from history_log;
   count
------------
 2102342910


At some point, we decide to clean old events.

=# delete from history_log where updated_at < '2018-11-01';
DELETE 1885782465
Time: 327220.719 ms (12:05.221)


The query would take twelve minutes to complete. However, during this action there is a less noticeable process that takes place - query would generate certain amount of WAL that will then need to be transferred into all standbys.

Ok, let’s check how many rows there would be in the table.

=# select count(*) from history_log;
   count
-----------
 216560445


=# select 100 * 1885782465::bigint / 2102342910;
 ?column?
----------
       89

It seems we deleted something around of 89% of the table, but let’s check its size.

=# select pg_size_pretty(pg_relation_size('history_log'));
 pg_size_pretty
----------------
 155 GB


Huh, the size hasn’t been changed?!

The thing is, Postgres never performs real deletion. It just marks rows as removed. Later on, space occupied by these "removed" rows will be cleared by vacuum and the available space can again be used for new rows, however, this space still belongs to a table. In some rare circumstances, table can be partially truncated and the free space can be returned to the file system.

Using partitioning for storing historical data can work wonders. It would allow us to drop old data quickly, without overhead related to WAL generation, so it would immediately free up space.

Comments

You might also be interested in

Deep dive into Postgres stats: pg_stat_progress_vacuum

Deep dive into postgres stats: pg_stat_bgwriter

Deep dive into postgres stats: pg_stat_replication