Table Partitioning Large Laravel Apps: Postgresql Performance Guide

TL;DR

Table partitioning splits a large PostgreSQL table into smaller physical sub-tables (partitions) based on a partition key, while presenting a single logical table to the application. For Laravel apps with tables exceeding 100 million rows, partitioning enables: faster queries (the planner skips partitions that can’t contain matching rows), more efficient vacuuming (each partition is vacuumed independently), easier data archival (drop a partition instead of DELETE), and parallel sequential scans across partitions. Impact: Date-range queries scan only relevant monthly partitions (95% I/O reduction), data archival via DROP PARTITION takes milliseconds instead of hours, and vacuum runs independently per partition keeping each one lean. This guide walks through the problem, the solution, and how to verify the improvement with real metrics.

The Problem

Your Laravel SaaS application logs every API request to an api_logs table. After 2 years, the table has 800 million rows and is 200GB. Queries with date filters still scan the entire table. Archiving old data requires DELETE operations that generate billions of dead tuples. VACUUM takes hours. You need to partition by month so queries only scan relevant partitions and old data can be dropped instantly.

This is a common scenario in production applications that have been running for months or years. The performance degradation is often gradual, making it hard to notice until it becomes a serious issue affecting users or operational costs.

How It Works

Table partitioning splits a large PostgreSQL table into smaller physical sub-tables (partitions) based on a partition key, while presenting a single logical table to the application. For Laravel apps with tables exceeding 100 million rows, partitioning enables: faster queries (the planner skips partitions that can’t contain matching rows), more efficient vacuuming (each partition is vacuumed independently), easier data archival (drop a partition instead of DELETE), and parallel sequential scans across partitions. PostgreSQL supports range, list, and hash partitioning natively since version 10.

Understanding the underlying mechanism is key to applying this optimization correctly and knowing when it applies to your specific situation versus when a different approach is needed.

Solution

The following before/after comparison demonstrates the complete solution. Study the comments carefully — they explain the reasoning behind each configuration choice.

Before

-- Monolithic table with 800M rows
CREATE TABLE api_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    endpoint TEXT NOT NULL,
    method VARCHAR(10),
    status_code INT,
    response_time_ms INT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_api_logs_created ON api_logs (created_at);
CREATE INDEX idx_api_logs_user ON api_logs (user_id, created_at);

-- Problem: even with index, query touches enormous table
-- Archiving 12 months of data:
DELETE FROM api_logs WHERE created_at < '2024-01-01';
-- Takes 6+ hours and generates 400M dead tuples

After

-- Partitioned table by month
CREATE TABLE api_logs (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    endpoint TEXT NOT NULL,
    method VARCHAR(10),
    status_code INT,
    response_time_ms INT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create partitions for each month
CREATE TABLE api_logs_2025_01 PARTITION OF api_logs
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE api_logs_2025_02 PARTITION OF api_logs
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- ... create partitions for each month

-- Default partition catches any rows not matching defined ranges
CREATE TABLE api_logs_default PARTITION OF api_logs DEFAULT;

-- Indexes are created per-partition automatically
CREATE INDEX ON api_logs (created_at);
CREATE INDEX ON api_logs (user_id, created_at);

-- Automate partition creation with pg_partman extension:
CREATE EXTENSION IF NOT EXISTS pg_partman;
SELECT partman.create_parent(
    p_parent_table := 'public.api_logs',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3  -- create 3 months ahead
);

-- Archive old data instantly:
DROP TABLE api_logs_2023_01;  -- instant! no dead tuples, no vacuum

-- Query only scans relevant partitions (partition pruning):
EXPLAIN ANALYZE
SELECT * FROM api_logs
WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';
-- Only scans api_logs_2025_03, skips all other partitions

Performance Impact

Date-range queries scan only relevant monthly partitions (95% I/O reduction), data archival via DROP PARTITION takes milliseconds instead of hours, and vacuum runs independently per partition keeping each one lean

Here are the measured results from applying this optimization in a production environment:

These numbers will vary based on your specific data volume, hardware, and query patterns, but the relative improvement should be consistent. Always measure before and after in your own environment to confirm the impact.

When to Use This

This optimization is most effective when:

• Your application matches the problem scenario described above
• You’ve confirmed the bottleneck with monitoring or profiling tools
• The data volume is large enough that the optimization makes a meaningful difference

It may not be the right fit if your tables are small (under 100K rows), your queries are already fast (under 10ms), or the bottleneck is elsewhere in your stack (application code, network, or client-side rendering).

Key Takeaways

• Measure first: Always profile before optimizing — the bottleneck may not be where you think it is
• Test in staging: Apply the optimization in a staging environment with production-like data before deploying
• Monitor after: Set up dashboards tracking the metrics above so you can verify the improvement and catch regressions

Frequently Asked Questions

Can I use table partitioning with Laravel migrations?

Laravel’s Schema builder doesn’t support PARTITION BY natively, but you can use DB::statement() in migrations to create partitioned tables. For automated partition management, install the pg_partman extension and call its functions from a scheduled Artisan command. The tpetry/laravel-postgresql-enhanced package also adds partitioning support to Laravel’s migration builder.

How does partition pruning work?

When a query includes a WHERE condition on the partition key (e.g., created_at BETWEEN …), PostgreSQL’s planner checks which partitions could possibly contain matching rows and excludes the rest from the scan plan. This is called partition pruning. It works with =, <, >, BETWEEN, and IN operators. You can verify it by running EXPLAIN and checking which partitions appear in the plan — excluded partitions don’t show up at all.