Improve Performance with Range Partitioning in PostgreSQL

When a PostgreSQL table grows too large, queries often take longer. So how can we make them faster? One answer is table partitioning -> breaking a big table into smaller parts.

PostgreSQL gives us three options: range, list, and hash partitioning. In this lesson, we’ll focus on range partitioning.

What Is Range Partitioning?

Range partitioning splits data based on the values of a column. Each partition covers a range of values. For example, if we partition by transaction_date, we can keep transactions from each year in a different table.

This helps because:

• Queries targeting a specific range scan only the relevant partition.
• Maintenance tasks (like archiving old data) are easier.
• Indexes remain smaller and faster.

1. Creating a Partitioned Table

Let’s start with a simple base table definition:

CREATE TABLE company.transactions (
transaction_id BIGSERIAL,
order_id BIGINT,
transaction_date DATE NOT NULL,
amount NUMERIC(10,2)
) PARTITION BY RANGE (transaction_date);

Here:

• PARTITION BY RANGE (transaction_date) tells PostgreSQL that rows will be split based on the transaction_date.

2. Defining Partitions

Next, we define child tables that hold the actual data:

-- Transactions before 2025
CREATE TABLE company.transactions_before_2025
PARTITION OF company.transactions
FOR VALUES FROM (MINVALUE) TO ('2025-01-01');

-- Transactions from 2025
CREATE TABLE company.transactions_2025
PARTITION OF company.transactions
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

• MINVALUE means everything earlier than 2025-01-01.

• The second partition stores rows between 2025-01-01 and 2025-12-31.

• You can keep adding yearly partitions as your system grows.

Note in DbSchema: PostgreSQL partitions are child tables that inherit from the parent. In DbSchema, they appear under the parent table in the explorer, not as separate tables in the diagram.

You can list them with the following query:

SELECT inhrelid::regclass::text AS partition_name
FROM pg_inherits
WHERE inhparent = 'company.transactions'::regclass;

3. Inserting Data

Inserting rows works the same way as in a normal table. PostgreSQL automatically routes each row to the right partition:

INSERT INTO company.transactions (order_id, transaction_date, amount) VALUES
(201, '2024-12-20', 450.00),
(202, '2025-01-15', 320.50),
(203, '2025-02-05', 125.00);

• The first row goes to transactions_before_2025.

• The other two go to transactions_2025.

Checking Which Partition Holds the Data

After inserting rows, you can query the child tables directly to see where the data went:

SELECT * FROM company.transactions_before_2025;

Result:

SELECT * FROM company.transactions_2025;

Result:

Notice how:

• The row with 2024-12-20 went into transactions_before_2025.

• The rows with dates in 2025 went into transactions_2025.

4. Querying Partitioned Tables

Queries look exactly the same as before:

SELECT ROUND(SUM(amount), 2) AS total_amount
FROM company.transactions
WHERE transaction_date >= DATE '2025-01-01';

Result:

5. Adding New Partitions

When a new year begins, simply create another partition:

CREATE TABLE company.transactions_2026
PARTITION OF company.transactions
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

This way, your table scales with time.

Partitioning in PostgreSQL with DbSchema

When you create a partitioned table in PostgreSQL, DbSchema helps you see and manage the parent table structure directly in the diagram.

Create Table

Result: Visual Table

For example, here is the transactions table defined as partitioned by range on transaction_date:

In the Columns tab, you can review the fields.

In the Options tab, DbSchema clearly indicates that the table is partitioned:

Partition Scripts

Partition Settings

This is important to understand:

• DbSchema shows the parent table (transactions) and its properties.
• The child partitions (transactions_before_2025, transactions_2025, …) are not drawn as separate tables in the diagram, since they inherit the same structure.
• Instead, you manage them through SQL or by exploring the database metadata.

Why This Is Helpful

In DbSchema you won’t see each partition as a separate table, but it still makes your work easier:

• The diagram gives a visual overview of the parent table and shows it as partitioned.
• You can run SQL queries in the editor to check partitions and their data.
• Create HTML5 documentation so your team knows which tables are partitioned.
• With Schema Compare, adding a new partition (like transactions_2026) is simple — update the design and sync it.

Try it yourself with DbSchema. Download here.