Question: How do you create a partitioned table in PostgreSQL?

Answer

Partitioning in PostgreSQL is a technique to divide a large table into smaller pieces, called partitions, which can help improve performance for certain types of queries. From PostgreSQL 10 onwards, it supports declarative partitioning, making it easier to manage partitions. Here's how to create a partitioned table:

Step 1: Create a Parent Table

First, you need to define the parent table, specifying the partitioning strategy (by range or by list).

Example of Range Partitioning

Let's say you have a sales data table that you want to partition by date.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) PARTITION BY RANGE (sale_date);

Example of List Partitioning

Alternatively, if you want to partition your data by regions represented by country codes:

CREATE TABLE sales_by_region (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    region_code CHAR(2) NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) PARTITION BY LIST (region_code);

Step 2: Create Partitions

After defining the parent table, you can create individual partitions. Each partition will contain data that matches specific criteria based on the partition key.

For Range Partitioning

Here, partitions are created for different quarters of a year.

CREATE TABLE sales_q1 PARTITION OF sales
    FOR VALUES FROM ('2020-01-01') TO ('2020-04-01');
CREATE TABLE sales_q2 PARTITION OF sales
    FOR VALUES FROM ('2020-04-01') TO ('2020-07-01');
CREATE TABLE sales_q3 PARTITION OF sales
    FOR VALUES FROM ('2020-07-01') TO ('2020-10-01');
CREATE TABLE sales_q4 PARTITION OF sales
    FOR VALUES FROM ('2020-10-01') TO ('2021-01-01');

For List Partitioning

Partitions here are based on region codes.

CREATE TABLE sales_north_america PARTITION OF sales_by_region
    FOR VALUES IN ('US', 'CA');
CREATE TABLE sales_europe PARTITION OF sales_by_region
    FOR VALUES IN ('GB', 'FR', 'DE');

Considerations

• When inserting data into a partitioned table, PostgreSQL automatically routes the data to the appropriate partition based on the partition key.
• Indexes, foreign keys, and triggers defined on the parent table are not automatically inherited by partitions. They need to be explicitly created on each partition if needed.
• Analyze your query patterns and access frequencies to determine the optimal partitioning strategy for your use case.

Partitioning can greatly enhance the performance and manageability of large tables when used appropriately. It allows for faster data access and can also speed up maintenance tasks such as backups and deletions.