PostgreSQL 10: partitions I can finally use
DROP TABLE events_2017_10. That is how you delete October now, and it is the reason I am reading PostgreSQL 10 release notes on a Thursday evening instead of waiting a year like usual. Partitioning existed before, through inheritance, CHECK constraints and an insert trigger you wrote yourself and hoped was right. Now the database owns it: CREATE TABLE events ( id bigserial NOT NULL, created_at timestamptz NOT NULL, payload jsonb ) PARTITION BY RANGE (created_at); CREATE TABLE events_2017_10 PARTITION OF events FOR VALUES FROM ('2017-10-01') TO ('2017-11-01'); The win is the data lifecycle. An events table grows forever, and deleting a year of history with DELETE is a night of I/O plus a bloated table in the morning. With partitions, retiring a month is one DROP. Instant. And a query that filters by created_at visits only the partitions in range, the planner skips the rest. ...