postgresql partition by

PostgreSQL supports table partitioning using the `PARTITION BY` clause. Partitioning is a technique to divide a large table into smaller pieces called partitions. Each partition is stored separately and can be accessed and managed independently.

The `PARTITION BY` clause is used to specify the partition key column or columns. The partition key is used to determine which partition a row belongs to.

Here is an example of partitioning a table by a date column:

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

This creates a table called `sales` with a partition key of `sale_date`. The `PARTITION BY RANGE` clause specifies that the table is partitioned by ranges of values of the `sale_date` column.

To create partitions for the `sales` table, you can use the `CREATE TABLE` command with the `PARTITION OF` clause:

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

This creates four partitions for the `sales` table, one for each quarter of the year. Each partition is defined using the `FOR VALUES FROM` clause with a range of dates.

When you insert data into the `sales` table, PostgreSQL will automatically route the data to the correct partition based on the partition key value. This can result in faster queries and better performance for large tables.