-- Range Partitioning (PostgreSQL)

CREATE TABLE orders (

order_id BIGINT,

order_date DATE NOT NULL,

total DECIMAL

) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders

FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- List Partitioning

CREATE TABLE sales (

sale_id BIGINT,

region VARCHAR(20) NOT NULL,

amount DECIMAL

) PARTITION BY LIST (region);

CREATE TABLE sales_us PARTITION OF sales

FOR VALUES IN ('US', 'CA', 'MX');

-- Hash Partitioning

CREATE TABLE users (

user_id BIGINT,

name VARCHAR(100)

) PARTITION BY HASH (user_id);

CREATE TABLE users_p0 PARTITION OF users

FOR VALUES WITH (MODULUS 4, REMAINDER 0);

PARTITION BY RANGE (column) - split by value ranges

PARTITION BY LIST (column) - split by specific values

PARTITION BY HASH (column) - split by hash function

CREATE TABLE name PARTITION OF parent FOR VALUES - create partition

Partition pruning - automatic optimization that skips irrelevant partitions

Native partitioning with PARTITION BY, excellent partition pruning

Native partitioning with PARTITION BY, good support

Partitioned tables with partition functions and schemes

Advanced partitioning with automatic partition management

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value