Advanced PostgreSQL: Indexing, Partitioning, Replication · Lesson

Managing Partitioned Tables

Learn best practices for adding, dropping, and altering partitions, as well as handling default partitions.

Managing Partitioned Tables

As your database grows, managing partitioned tables becomes crucial. This lesson explores how to dynamically add, remove, and modify partitions to keep your data organized and performant.

We'll cover adding new partitions for future data, detaching old partitions for archiving, and attaching existing tables as new partitions. We'll also look at handling data that doesn't fit any defined partition.

Setting Up a Partitioned Table

Before we manage partitions, let's create a base partitioned table. We'll use a simple sales_data table, partitioned by sale_date using a range partitioning strategy.

This table will serve as our example throughout the lesson.

CREATE TABLE sales_data (
  id SERIAL,
  sale_date DATE,
  amount DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

-- Add an initial partition for December 2023
CREATE TABLE sales_2023_12 PARTITION OF sales_data
FOR VALUES FROM ('2023-12-01') TO ('2024-01-01');

INSERT INTO sales_data (sale_date, amount) VALUES ('2023-12-15', 150.75);
SELECT * FROM sales_data;

All lessons in this course

← Back to Advanced PostgreSQL: Indexing, Partitioning, Replication