Posted inDatabase

Scaling PostgreSQL to Billions of Rows: A Practical Guide to Partitioning

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Breaking the 1.2 Billion Row Wall

I once worked on a logging system where a single logs table swelled to 1.2 billion rows. Simple analytical queries that previously finished in milliseconds began dragging for 15 to 20 seconds. Routine maintenance like VACUUM or adding an index became high-stakes operations that lasted through the weekend. This is the inevitable scaling wall you hit when data outgrows the efficiency of a single physical table.

While many teams jump to complex NoSQL clusters when they hit this point, PostgreSQL’s declarative partitioning often provides a cleaner path. It allows you to split one giant logical table into smaller, physical chunks. This architectural shift keeps your indexes small, your queries lean, and your maintenance windows manageable.

Setting Up Range Partitioning

PostgreSQL handles partitioning through the PARTITION BY clause. Time-series data is the most common candidate for this approach. Imagine an orders table receiving 5 million new rows every month. Without partitioning, the index eventually becomes too large to fit in RAM.

First, we define the parent table. Note that this table acts as a template; it doesn’t store data itself.

CREATE TABLE orders (
    order_id SERIAL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC,
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);

A crucial detail here: the partition key (order_date) must be included in the primary key. Once the parent exists, you can create the actual storage buckets for specific months:

-- Partition for January 2024
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Partition for February 2024
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

When you insert a row with a January date, PostgreSQL routes it to the orders_2024_01 table automatically. During a query, the planner uses “Partition Pruning.” If your WHERE clause filters for January, the engine ignores every other partition. Instead of scanning a 500GB table, it might only touch a 10GB slice.

Choosing Your Strategy

Range partitioning is the standard for dates, but it isn’t the only tool available. Depending on how you access your data, List or Hash partitioning might be more effective.

1. Range Partitioning

Use this for dates, timestamps, or sequential IDs. It is perfect for data with a natural lifecycle. Dropping a month of old data via DROP TABLE orders_2023_01 is nearly instantaneous. It avoids the massive I/O overhead and transaction log bloat caused by a standard DELETE command.

2. List Partitioning

This works best for categorical data like regions or department IDs. If your application heavily filters by country_code, you can isolate data by geography to keep queries local to specific regions.

CREATE TABLE users (id INT, name TEXT, country_code TEXT) 
PARTITION BY LIST (country_code);

CREATE TABLE users_vn PARTITION OF users FOR VALUES IN ('VN');
CREATE TABLE users_us PARTITION OF users FOR VALUES IN ('US');

3. Hash Partitioning

Hash partitioning is ideal for distributing I/O load when there is no logical range. If a sessions table is so busy that a single disk can’t handle the writes, use Hash partitioning to spread the data across multiple physical files using modulus logic.

CREATE TABLE web_sessions (session_id UUID, data TEXT) 
PARTITION BY HASH (session_id);

-- Splitting data into 4 equal buckets
CREATE TABLE web_sessions_0 PARTITION OF web_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE web_sessions_1 PARTITION OF web_sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- Repeat for remainders 2 and 3...

Indexing and Maintenance Realities

Managing a partitioned database requires a shift in how you think about indexes. PostgreSQL does not support “Global Indexes” that span across all partitions. When you define an index on the parent table, PostgreSQL creates a local index for every partition. This keeps individual index trees shallow and fast, but you must include the partition key in your queries to ensure the planner knows which index to use.

Automation with pg_partman

Creating tables manually is a liability. If you forget to create next month’s partition, your application will fail the moment the clock strikes midnight. I recommend using the pg_partman extension. It automates partition creation and handles data retention based on a schedule you define.

-- Automating hourly partition management
SELECT partman.create_parent('public.server_logs', 'event_time', 'native', 'hourly');

Efficient Archiving

Partitioning makes data archival seamless. You can “detach” a partition to turn it into a standalone table. This allows you to move cold data to cheaper storage or dump it to S3 without impacting the performance of the live parent table.

ALTER TABLE orders DETACH PARTITION orders_2023_01;

Lessons from the Field

After migrating several high-traffic systems to this architecture, I’ve identified a few common pitfalls to avoid:

  • Avoid Over-Partitioning: I have seen developers create daily partitions for tables that only grow by 10,000 rows a day. This creates thousands of small files that actually confuse the query planner. Aim for partitions that are between 1GB and 10GB in size.
  • Commit to the Partition Key: You cannot easily change the partition key once the table is live. Choose the column most frequently used in your WHERE clauses, as this is what drives performance.
  • Unique Constraints: Any UNIQUE or PRIMARY KEY must include the partition key. This can be a headache for schemas that rely solely on global UUIDs, so plan your ID strategy early.
  • Vacuuming: Partitioning doesn’t replace VACUUM, but it does make it more efficient. The autovacuum process can work on smaller, individual tables, which reduces the duration of lock contention on your hardware.

Partitioning isn’t a universal fix for every slow query, but for datasets reaching the billion-row mark, it is a vital architectural pattern. By breaking your data into manageable pieces, you regain control over your database’s performance and long-term health.

Share:
Tags: