PostgreSQL PARTITION BY: Window Functions and Declarative Partitioning

Understanding PARTITION BY in PostgreSQL

In PostgreSQL, PARTITION BY appears in two distinct contexts. In window functions, it divides rows into groups over which an aggregate or ranking function operates without collapsing the result set. In declarative table partitioning, it defines how a parent table's rows are distributed across child tables based on a key or expression. These are separate features that share a keyword, and the right one depends on whether you are analyzing data or designing a table's physical layout.

PARTITION BY in Window Functions

The syntax looks like this: ROW_NUMBER() OVER (PARTITION BY column ORDER BY sort_column). The PARTITION BY clause splits the result set into partitions, and the window function runs independently inside each. ORDER BY inside OVER is not optional for most window functions — it controls the logical row order within the partition. Without it, PostgreSQL returns non-deterministic results for ranking and numbering functions.

Common Window Functions with PARTITION BY

• ROW_NUMBER() — assigns a unique integer per partition, starting at 1.
• RANK() and DENSE_RANK() — assigns ranks with gaps or no gaps after ties.
• SUM(), AVG(), COUNT() — compute aggregates per partition while keeping all rows.
• FIRST_VALUE(), LAST_VALUE() — retrieve boundary values within a partition.
• LAG() and LEAD() — access previous or subsequent rows relative to the current row.

Example: Running Total Per Group

Suppose you have a sales table with region and amount columns. To get a running total per region:

SELECT region, sale_date, amount, SUM(amount) OVER ( PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM sales;

The query returns every row while keeping the sum scoped to its region. You can replace PARTITION BY with no clause to compute over the entire result set, or add multiple columns to partition by more than one dimension.

Declarative Table Partitioning

PostgreSQL supports range, list, and hash partitioning. The PARTITION BY clause lives on the parent table definition and determines how rows route to child tables. This is a physical design decision, not a query-time calculation like window partitioning.

Range Partitioning

Rows map to partitions based on a column falling within a defined range. Typical use cases include partitioning by date for time-series data or by numeric ID ranges. Example:

CREATE TABLE measurements ( id BIGINT, recorded_at TIMESTAMPTZ, value DOUBLE PRECISION ) PARTITION BY RANGE (recorded_at);

CREATE TABLE measurements_2024 PARTITION OF measurements FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

List Partitioning

Rows map to partitions by matching discrete values. Useful for categories, regions, or status codes:

CREATE TABLE orders ( id BIGINT, status TEXT, amount NUMERIC ) PARTITION BY LIST (status);

CREATE TABLE orders_pending PARTITION OF orders FOR VALUES IN ('pending', 'awaiting_shipment');

Hash Partitioning

Rows distribute evenly across partitions based on a hash of the partition key. This works well for even data distribution when no natural range or list boundary exists:

CREATE TABLE events ( id BIGINT, tenant_id INT, payload JSONB ) PARTITION BY HASH (tenant_id);

CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);

With hash partitioning, you must specify the modulus and remainder for each child table. Changing the number of partitions later requires rewriting data.

Practical Guidance

Use window PARTITION BY when you need group-aware calculations without losing individual rows. Use table PARTITION BY when tables grow large enough that pruning, maintenance, or storage policies matter. Declarative partitioning can improve query performance through partition pruning, but it adds DDL complexity. Queries that do not include the partition key in a WHERE clause may scan all partitions, which can be slower than a single large table.

For both features, test with representative data volumes. Window function performance depends on sorting and memory configuration, while partitioning performance depends on pruning effectiveness and index design on child tables.