Scaling Down to Scale Up: Mastering PostgreSQL Partitioned Tables

Introduction

At ManoMano, data is the lifeblood of our marketplace. As we scale, our tables don’t just grow linearly; they grow exponentially. We rely heavily on PostgreSQL on AWS RDS to handle this load. However, there comes a tipping point in every database's life where a table becomes simply "too big to fail"—or rather, too big to query efficiently.

When indices stop fitting in RAM and VACUUM processes start timing out, it is time to look at a strategy that involves breaking things to fix them: Table Partitioning.

The Problem: The Monolith Table

Imagine a generic orders table. In the early days, a SELECT * FROM orders WHERE created_at = 'today' is instantaneous. But fast-forward a few years: that table holds hundreds of millions of rows.

Even with proper indexing, maintenance operations become a nightmare. Autovacuum can’t keep up, leading to table bloat. Updates slow down. Backups take longer. In the AWS RDS ecosystem, this translates to consumed IOPS and burned Burst Balance.

The solution isn't always "get a bigger instance." Often, the solution is Divide and Conquer.

What is Partitioning?

PostgreSQL allows you to split what is logically one large table into smaller physical pieces called partitions. To the application (our microservices), it still looks like one table. But under the hood, Postgres stores the data in separate files.

The most common strategy we see in e-commerce is Range Partitioning (usually by time, e.g., one partition per month) or List Partitioning (e.g., by country or category).

CREATE TABLE orders (
    id uuid,
    order_date date not null,
    total_amount decimal,
    customer_id int
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_y2024m01 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

The Wins: Why We Partition

When implemented correctly, partitioning provides massive architectural advantages.

1. Performance via Partition Pruning

This is the “killer feature.” If you query WHERE order_date = '2024-01-15', the Postgres Query Planner is smart enough to know it only needs to look at the orders_y2024m01 partition. It completely ignores the terabytes of historical data from 2020. This drastically reduces I/O operations on RDS.

2. Maintenance & Bulk Deletion

Deleting old data in a standard table is expensive. DELETE FROM orders WHERE date < '2020-01-01' generates massive Write Ahead Logs (WAL), locks rows, and requires subsequent vacuuming. With partitioning, you simply DROP or DETACH the partition. It is an instantaneous metadata operation. Zero bloat.

3. Storage Tiering

In an AWS context, you can get creative. You might keep “hot” partitions (current month) on high-performance storage, while moving “cold” partitions (historical data) to a different tablespace backed by cheaper storage classes, optimizing your AWS bill.

The Trade-offs: It’s Not a Silver Bullet

At ManoMano, we believe in “Right Tool for the Right Job.” Partitioning introduces complexity that must be managed. If you blindly partition everything, you will suffer.

1. The Unique Key Constraint

This is the biggest “Gotcha” for developers. In a partitioned table, any unique constraint (including the Primary Key) must include the partition key.

If you partition by created_at, you cannot have a Primary Key just on order_id. It must be a composite key (order_id, created_at). This often requires refactoring application logic (Hibernate/JPA entities) to handle composite IDs.

2. Cross-Partition Updates

If you update a row in a way that changes its partition key (e.g., moving an item from category_A to category_B in a list-partitioned table), Postgres has to physically delete the row from one table and insert it into another. This is much more expensive than a standard update and can lead to row-movement errors if not configured correctly.

3. Query Limitations

While “Partition Pruning” is great, it only works if you use the partition key in your WHERE clause. If you run SELECT * FROM orders WHERE customer_id = 500 (without the date), Postgres must scan every single partition. If you have 50 partitions, that’s 50 index scans. This can sometimes be slower than scanning one giant index.

But not all data is time-series. Let’s look at a different beast: the Seller Catalog

Alternative Strategy: Hash Partitioning for Seller Isolation

While Range Partitioning is king for time-series data, it falls short when access patterns aren’t time-based. At ManoMano, our B2B sellers constantly manage their own catalogs. A seller only cares about their products, identified by a seller_id.

In a massive offers table containing millions of products from thousands of sellers, we use Hash Partitioning. Since seller_id is a Long, it is a perfect candidate for hashing.

-- Partitioning by Hash for even distribution
CREATE TABLE offers (
    id uuid,
    seller_id int8 not null,
    product_name text,
    price decimal,
    stock_quantity int
) PARTITION BY HASH (seller_id);
-- Create 4 partitions (modulus 4)
CREATE TABLE offers_p0 PARTITION OF offers 
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE offers_p1 PARTITION OF offers 
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ... and so on

Why Hash Partitioning Wins Here

• Optimized “My Catalog” Views: When a seller logs into their back-office to view their stock, the query always includes WHERE seller_id = X. Postgres hashes the ID, determines exactly which partition (bucket) holds that seller's data, and ignores the others. This mimics the performance of having a small, dedicated table for that seller.
• Even Data Distribution: Unlike Range Partitioning, where the “current month” partition gets all the write traffic (creating a hotspot), Hash Partitioning distributes incoming offers across all underlying tables evenly. This balances the I/O load on our AWS RDS storage.
• Scalability: If the table grows too large, we can re-hash and split partitions further, ensuring no single file exceeds a manageable size (e.g., 100GB), keeping maintenance operations like VACUUM fast.

Conclusion: Matching Schema to Access Patterns

Partitioning in PostgreSQL is not just a storage optimization; it is a strategic architectural choice that must mirror your business logic.

We have seen two distinct paths:

1. Range Partitioning for data with a clear lifecycle (like orders), where the goal is efficient archiving and optimizing for "recency."
2. Hash Partitioning for data with high-cardinality tenancy (like offers), where the goal is performance isolation and ensuring that a specific seller’s dashboard loads instantly, regardless of the platform's total size.

However, this power comes with responsibility. Partitioning requires a shift in how we handle constraints in the database.

Before you split your tables, ask yourself:

• What is the dominant access pattern? Is it a date range (reporting, history) or a specific ID lookup (seller catalog)?
• How do I handle data lifecycle? Do I need to bulk-delete old data (Range), or does the data live forever but needs load balancing (Hash)?
• Can my application handle the constraints? Am I ready to adapt my Primary Keys to include the partition key?

Robust business code isn’t just about clean code or solid unit tests; it’s about designing a database schema that understands how the business uses the data.