Alright, let’s get our hands dirty. You’ve defined your partition strategy—the grand architectural blueprint. Now we build the actual rooms and hallways. This is where the magic happens, but also where you can paint yourself into a corner if you’re not careful. I’m here to make sure that doesn’t happen.

The core act of creation is the CREATE TABLE ... PARTITION BY statement. This is your point of no return, where you bake your partitioning method (RANGE, LIST, HASH) and key directly into the table’s DNA. Get this right, and everything else flows smoothly. Get it wrong, and you’re looking at a DROP TABLE and starting over. No pressure.

The Initial Creation: Setting the Stage

Let’s create a table partitioned by range on a sale_date column. Notice the PARTITION BY RANGE clause. This tells PostgreSQL, “Hey, this isn’t a normal table; it’s a partitioned table, and here’s the rulebook.”

CREATE TABLE sale (
    id SERIAL,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

Boom. You now have a parent table. It looks like a table, it feels like a table, but if you try to INSERT data into it, PostgreSQL will just stare at you blankly. It’s a hollow shell, a concept. It has no partitions yet, so it has nowhere to put your data. This is the most common “oh crap” moment. You’ve created the container, but no drawers to put anything in.

Building the First Partitions (The Drawers)

Now, we create the actual partitions. These are real tables that will hold your data. The critical part is the VALUES FROM ... TO clause, which defines the boundaries for each partition. The lower bound is inclusive; the upper bound is exclusive. This is a classic source of off-by-one errors. Remember: TO '2023-02-01' means “up to, but not including, February 1st, 2023.” Data from January 31st goes in; data from February 1st does not.

-- Partition for January 2023 data
CREATE TABLE sale_202301 PARTITION OF sale
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- Partition for February 2023 data
CREATE TABLE sale_202302 PARTITION OF sale
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

Why do it this way? It creates clean, non-overlapping ranges. A given value of sale_date can exist in one and only one partition. This is what makes the query planner’s life easy and your queries fast.

The Art of the Attach

Maybe you’re a genius and thought ahead, or maybe you’re migrating an old, bloated monolith table into a partitioned paradise. In either case, you can ATTACH an existing, regular table as a new partition. This is incredibly powerful. Let’s say you have an old table sale_202212 full of last December’s data.

First, the table’s definition must be compatible with the parent. Same columns, same types. An INDEX on the partition key in the child table is a very, very good idea, but PostgreSQL won’t check for it. That’s on you.

-- First, ensure the existing table has the right structure and a constraint.
-- This CHECK constraint is VITALLY IMPORTANT. It's how the planner knows which data is in this table.
ALTER TABLE sale_202212
    ADD CONSTRAINT sale_202212_check
    CHECK (sale_date >= '2022-12-01' AND sale_date < '2023-01-01');

-- Now, attach it. This will validate the CHECK constraint to ensure no data violates the partition bounds.
ALTER TABLE sale
    ATTACH PARTITION sale_202212
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

The ATTACH operation will hold a strong lock on both the parent table and the table being attached. Plan this operation during a maintenance window if the table is large. The database must scan the entire table you’re attaching to verify that the CHECK constraint holds true. If you try to attach a table with a row for January 3rd into a December partition, the operation will fail. This is a good thing! It protects the integrity of your data.

Indexing: Do Not Forget This. Seriously.

Here’s the thing that will get you every time: indexes do not automatically inherit from the parent table. I know, it’s a bit of a design flaw. When you create a partition of a table, it inherits the column definitions, but not the indexes, triggers, or foreign keys. You must create them on each partition individually.

-- You should have done this on the parent for planning, but it must be applied to each child.
CREATE INDEX ON sale_202301 (sale_date);
CREATE INDEX ON sale_202302 (sale_date);

Best practice? Automate this. Write a script to generate the CREATE INDEX statements for your new partitions. If you forget, your queries will still be correct, but they’ll perform a sequential scan on that partition, utterly defeating the purpose of partitioning. It’s like buying a sports car and then forgetting to put the engine in one of the cylinders.