High Performance PostgreSQL for Rails

Retiring Unneeded Partitions

Over time, as old partitions are no longer needed in PostgreSQL, you’ll be able to archive the data and remove the partitions.

Get the partition name and then call DETACH using the CONCURRENTLY keyword to avoid interrupting any concurrently executing queries.

This example shows a SQL statement to detach the partition named trip_positions_202209:

sql/detach_partition_concurrently.sql

ALTER TABLE trip_positions
DETACH PARTITION trip_positions_202311 CONCURRENTLY;

As you saw earlier, a detached partition becomes a regular PostgreSQL table. The detached partition may be reattached later, as long as the schema definition does not change, or the partition can be emptied (using TRUNCATE) or dropped entirely.

Try ...