PostgreSQL Performance Tuning for Analytics: Indexing and Partitioning

Your Acme Shop dashboard loaded in a blink in October. By January the same “page views last 7 days” chart takes long enough that you open another tab. Nothing in the code changed. The events table just grew, and PostgreSQL is now reading far more of it than your query needs.

PostgreSQL performance tuning for analytics is different from tuning an order system. Your table is append-heavy, rarely updated, and almost always filtered by time. Those three facts decide which index types pay off, when partitioning helps, and what vacuum has to do.

This article works on one table you can rebuild yourself: ten million synthetic events in the canonical Acme Shop shape. You will read EXPLAIN ANALYZE output, add B-tree, partial, covering and BRIN indexes, partition by month, and keep a rollup table fresh. The numbers below come from one run on PostgreSQL 18 in a Docker container with default settings. Your hardware will differ, so rerun the queries and trust your own numbers over mine.

This article assumes the events table from PostgreSQL schema design for analytics and the collector from the Node.js analytics API. It does not re-teach column types or query patterns.

PostgreSQL Performance Tuning Starts With a Reproducible Table

Tuning advice without a reproducible table is folklore. Create a scratch database and a table with the same columns as the schema article. This copy leaves out received_at, the generated page_path column and the primary key, so the bulk load stays fast. The lessons hold either way.

SET timezone = 'UTC';

CREATE TABLE events (
  event_id     uuid        NOT NULL,
  event_name   text        NOT NULL,
  occurred_at  timestamptz NOT NULL,
  anonymous_id text        NOT NULL,
  user_id      text,
  session_id   text        NOT NULL,
  page_url     text,
  referrer     text,
  user_agent   text,
  properties   jsonb       NOT NULL DEFAULT '{}'
);

INSERT INTO events
  (event_id, event_name, occurred_at, anonymous_id, session_id, page_url, properties)
SELECT
  gen_random_uuid(),
  CASE WHEN r < 0.80  THEN 'page_view'
       WHEN r < 0.92  THEN 'button_click'
       WHEN r < 0.95  THEN 'form_submit'
       WHEN r < 0.96  THEN 'signup_completed'
       WHEN r < 0.995 THEN 'add_to_cart'
       ELSE 'purchase_completed' END,
  timestamptz '2025-07-01 00:00:00+00' + g * interval '0.7 seconds',
  'anon_' || (g % 200000),
  'sess_' || (g / 20),
  'https://acme-shop.example/products/' || (g % 500),
  jsonb_build_object('amount_cents', (random() * 20000)::int)
FROM (SELECT g, random() AS r FROM generate_series(1, 10000000) AS g) AS s;

ANALYZE events;

The load took about 45 seconds for me and produced a 1.9 GB heap. The generator spreads events across roughly 81 days (1 July to 20 September) in strict time order, as a live collector would. That ordering matters later for BRIN. About 80 percent of rows are page_view and about 0.5 percent are purchase_completed, which is a plausible skew for a store.

Keep SET timezone = 'UTC' in every session you benchmark from. In my experience building event pipelines, a day-bucket chart that is off by one for half the team is almost always date_trunc running in a laptop’s local time zone. Pin it to UTC in the role or the connection string.

Read EXPLAIN ANALYZE Before You Touch Anything

An index is a guess until the planner agrees. Start with the dashboard query and ask PostgreSQL what it did, as described in the PostgreSQL EXPLAIN documentation.

EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', occurred_at) AS day, count(*)
FROM events
WHERE event_name = 'page_view'
  AND occurred_at >= '2025-09-01'
  AND occurred_at <  '2025-09-08'
GROUP BY 1
ORDER BY 1;

With no index, my run produced this plan, trimmed to the lines that matter:

GroupAggregate
  ->  Gather Merge
        ->  Sort
              ->  Parallel Seq Scan on events
                    Filter: (... event_name = 'page_view'::text)
                    Rows Removed by Filter: 3102744
                    Buffers: shared hit=15950 read=227943
Execution Time: 246.714 ms

The query returns about 690,000 rows, roughly 7 percent of the table. Three lines tell you most of the story. Seq Scan means PostgreSQL read the whole heap.

Rows Removed by Filter shows how much of that work it threw away. Buffers: shared read counts pages fetched from disk or the OS cache, and it is the number that predicts how the query behaves when the cache is cold.

Save this output. After each change below, rerun the same statement and compare three things: the scan node, the rows removed, and the buffers read. Execution time alone is noisy because of caching, so treat buffers as the stable signal. This rerun-and-compare loop is the core PostgreSQL performance tuning discipline for an events table.

Estimated versus actual rows

Compare rows= in the estimate with actual rows= on each node. A gap of 10x or more means stale or insufficient statistics. In that case run ANALYZE events, and for skewed columns like event_name consider ALTER TABLE events ALTER COLUMN event_name SET STATISTICS 500. Bad estimates cause bad plans, and no index fixes a planner that is misinformed.

B-tree Indexes for Event Queries

B-tree is the default index type and handles equality and range filters. The common mistake is one index per column, which is the wrong code to show first.

-- Wrong: three single-column indexes
CREATE INDEX ON events (event_name);
CREATE INDEX ON events (occurred_at);
CREATE INDEX ON events (session_id);

PostgreSQL can combine single-column indexes with a bitmap AND, but it often just picks one and filters the rest. On my run, with these three indexes in place, the planner scanned the occurred_at index and discarded 172,231 rows by event_name for the dashboard query. For the rarer add_to_cart variant it read about 25,500 buffers to return 30,140 rows. One composite index avoids that waste and costs about the same to write.

-- Right: equality column first, range column second
CREATE INDEX events_name_time_idx ON events (event_name, occurred_at);

The rule is equality columns first, then the range column. With event_name first, the index narrows to one event type and then walks a contiguous time range. Flip the order and PostgreSQL has to walk the full time range across every event type.

For the add_to_cart count over one week, (event_name, occurred_at) read about 220 buffers on my run. (occurred_at, event_name) read about 4,300.

Rerun the dashboard EXPLAIN statement after a VACUUM. I got an Index Only Scan on events_name_time_idx that read 3,411 buffers instead of about 244,000. Wall-clock time fell only from 247 ms to 154 ms, because both plans still sort 690,000 rows.

Since 80 percent of rows are page_view, an index cannot make this particular chart cheap. A rollup can, as shown below.

The cost is real. Every insert now updates this index too, and mine is 389 MB. Check yours with SELECT pg_size_pretty(pg_relation_size('events_name_time_idx')) and compare it to the table. Each extra index slows ingestion, so link this decision back to async event tracking, where the buffer absorbs write spikes but cannot hide a collector that is permanently write-bound.

Partial and Covering Indexes

Rare events are where partial indexes shine. Purchases are about 0.5 percent of the table, yet the revenue report only reads purchases.

CREATE INDEX events_purchases_idx
  ON events (occurred_at)
  WHERE event_name = 'purchase_completed';

This index holds only the purchase rows. Mine is 1.1 MB, against 389 MB for the composite index, so it is fast to scan and cheap to maintain. The planner uses it only when the query’s WHERE clause implies the index predicate.

That means the query must say event_name = 'purchase_completed' as a literal. A parameterized value like event_name = $1 in a generic prepared plan cannot be proven to match the predicate, so the planner skips the partial index. Test with your real driver.

Covering indexes and index-only scans

A covering index stores extra columns with INCLUDE, so the query never visits the heap. Suppose a funnel step counts distinct sessions that added to cart.

CREATE INDEX events_cart_cover_idx
  ON events (event_name, occurred_at) INCLUDE (session_id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(DISTINCT session_id)
FROM events
WHERE event_name = 'add_to_cart'
  AND occurred_at >= '2025-09-01' AND occurred_at < '2025-09-08';

Look for Index Only Scan and a low Heap Fetches count. On my run it read 221 buffers with zero heap fetches and finished in 7 ms. Index-only scans depend on the visibility map, which vacuum maintains.

On a freshly loaded table, Heap Fetches will be high until you run VACUUM events. Therefore a covering index that seems to do nothing is usually a vacuum problem, not an index problem.

Two limits apply. INCLUDE only takes plain columns, so you cannot cover properties->>'amount_cents'. And wide covering indexes cost disk. In practice, I add covering columns only to the one or two queries that run hundreds of times a day.

For JSONB properties, a GIN index on the whole column is tempting and usually too large. If you filter on one key often, an expression index on that key is smaller. If you filter on it constantly, promote it to a real column, as discussed in the schema article.

BRIN Indexes for Time-Ordered Appends

A BRIN (block range) index stores a min and max value for each range of table pages. The PostgreSQL BRIN documentation describes it as built for very large tables where a column correlates with physical row position. An append-only events table inserted in time order is the textbook case.

CREATE INDEX events_time_brin ON events USING brin (occurred_at);

SELECT correlation FROM pg_stats
WHERE tablename = 'events' AND attname = 'occurred_at';

SELECT pg_size_pretty(pg_relation_size('events_time_brin')) AS brin_size,
       pg_size_pretty(pg_relation_size('events_name_time_idx')) AS btree_size;

The correlation value should sit close to 1, and mine was exactly 1. The size comparison is the payoff: my BRIN index is 80 kB, while the B-tree on (event_name, occurred_at) is 389 MB. BRIN also adds almost nothing to insert cost, which matters on a hot write path.

Reads are a different story. A time-only count over one week used the BRIN index, read about 21,000 buffers and finished in 66 ms. A B-tree would read far fewer pages. BRIN trades read precision for a tiny, cheap index.

BRIN is lossy. It tells PostgreSQL which page ranges might contain matches, then the executor rechecks every row in them. You will see Bitmap Heap Scan with Heap Blocks: lossy=... in the plan. You can tune pages_per_range at creation: smaller ranges give tighter filtering and a bigger index.

The failure mode is lost correlation. A mistake I have seen in production is a backfill job that reloaded last month’s events out of order, after which BRIN ranges overlapped and the index stopped filtering anything. If you backfill, insert in time order or rebuild with REINDEX. Likewise, BRIN does nothing for the event_name filter, so combine it with a partial or composite index when both matter.

Native Partitioning by Month

Indexes make reads cheaper. Partitioning makes whole ranges of data cheap to ignore or remove. The PostgreSQL partitioning documentation covers range, list and hash methods, and range on occurred_at fits events.

events (partitioned parent, RANGE on occurred_at)
 |-- events_2025_07   [2025-07-01, 2025-08-01)
 |-- events_2025_08   [2025-08-01, 2025-09-01)
 |-- events_2025_09   [2025-09-01, 2025-10-01)
 |-- events_default   (catches anything out of range)

Query: WHERE occurred_at >= '2025-09-01' AND occurred_at < '2025-09-08'
Planner prunes: only events_2025_09 is scanned.

Here is the partitioned version of the table. This extends the definition from the schema article: the primary key must now include the partition key.

CREATE TABLE events_p (
  event_id     uuid        NOT NULL,
  event_name   text        NOT NULL,
  occurred_at  timestamptz NOT NULL,
  anonymous_id text        NOT NULL,
  user_id      text,
  session_id   text        NOT NULL,
  page_url     text,
  referrer     text,
  user_agent   text,
  properties   jsonb       NOT NULL DEFAULT '{}',
  PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_p_2025_07 PARTITION OF events_p
  FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
CREATE TABLE events_p_2025_08 PARTITION OF events_p
  FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE events_p_2025_09 PARTITION OF events_p
  FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
CREATE TABLE events_p_default PARTITION OF events_p DEFAULT;

INSERT INTO events_p SELECT * FROM events;

CREATE INDEX ON events_p (event_name, occurred_at);
ANALYZE events_p;

An index created on the parent is created on every partition, and new partitions inherit it. Rerun the dashboard query against events_p and read the plan. Only events_p_2025_09 should appear, and the others are absent because the planner pruned them.

Confirm that enable_partition_pruning is on, which is the default. On my run the plan read the same 3,410 buffers as the unpartitioned table with the same index, so pruning removed other months from the plan without changing the cost of this query.

The unique constraint catch

The documentation is explicit that a unique or primary key constraint on a partitioned table must include all partition key columns. So the key is (event_id, occurred_at), and PostgreSQL cannot enforce that an event_id is unique across months.

For idempotent ingestion this is acceptable. A client retry sends the same event_id and the same occurred_at, so ON CONFLICT (event_id, occurred_at) DO NOTHING still drops the duplicate. If your insert statement from earlier articles targets ON CONFLICT (event_id), update it.

PostgreSQL rejects the old form with “there is no unique or exclusion constraint matching the ON CONFLICT specification”. A replay that regenerates occurred_at would slip through, so never rewrite timestamps on retry.

Why partition at all

Partitioning rarely speeds up a query that an index already serves. Its real benefits are operational:

• Retention: DROP TABLE events_p_2025_07 removes a month instantly. A DELETE of the same rows writes dead tuples, bloats indexes and floods the WAL.
• Smaller indexes per partition: hot recent partitions stay in memory.
• Cheaper maintenance: vacuum and reindex run per partition.
• Cold storage: detach an old partition with ALTER TABLE ... DETACH PARTITION and export it, which pairs well with moving analytics to a data warehouse.

The costs are also real. Someone must create next month’s partition before the first event of the month arrives, or rows land in the default partition. Creating the partition afterwards then fails until you move those rows out.

Schedule that with cron or a job in your app, or use the pg_partman extension. Also, too many partitions slow planning, so monthly beats daily until you have a reason.

As a rule of thumb, skip partitioning until the table is large enough that deletes or vacuum hurt, or until you have a hard retention policy. Before that, a good index does the job.

Rollup Tables for Repeated Aggregates

The fastest scan is the one you skip. If the dashboard asks for page views per day per page a thousand times, compute it once. This is the daily_pageviews rollup from the schema article, with its refresh query pointed at the partitioned table. Because the tables here do not have the generated page_path column, the query derives the path with the same expression.

CREATE TABLE daily_pageviews (
  day        date   NOT NULL,
  page_path  text   NOT NULL,
  views      bigint NOT NULL,
  visitors   bigint NOT NULL,
  PRIMARY KEY (day, page_path)
);

-- Idempotent refresh for the last two UTC days
INSERT INTO daily_pageviews (day, page_path, views, visitors)
SELECT (occurred_at AT TIME ZONE 'UTC')::date,
       COALESCE(substring(page_url from '^https?://[^/?#]+(/[^?#]*)'), '/'),
       count(*),
       count(DISTINCT anonymous_id)
FROM events_p
WHERE event_name = 'page_view'
  AND occurred_at >= date_trunc('day', now() AT TIME ZONE 'UTC') - interval '1 day'
GROUP BY 1, 2
ON CONFLICT (day, page_path) DO UPDATE
  SET views = EXCLUDED.views,
      visitors = EXCLUDED.visitors;

The synthetic data ends on 20 September, so the WHERE window above matches nothing when you run it later. For the first fill, replace the lower bound with '2025-07-01'. Then run it every few minutes.

Because it recomputes whole days from raw events and uses ON CONFLICT DO UPDATE, a rerun gives the same result. That property saves you when a job fails halfway. The GET /v1/stats/pageviews endpoint can then read the rollup, which holds a few thousand rows per day instead of millions.

The trade-off is staleness and double bookkeeping. Late events, those that arrive after you computed their day, are the usual bug. Recomputing the last two days covers most clients.

Events delayed longer need a periodic wider rebuild. By contrast, a plain MATERIALIZED VIEW is simpler, but REFRESH recomputes everything unless you structure it carefully, and a concurrent refresh needs a unique index.

Vacuum and Bloat on Append-Heavy Tables

Bloat comes from dead tuples, and a pure append table creates almost none. The routine vacuuming documentation explains why vacuum still matters here: it sets visibility map bits, freezes old transaction IDs and updates statistics.

Since PostgreSQL 13, autovacuum also triggers on inserts alone, through autovacuum_vacuum_insert_threshold and its scale factor. Older versions left insert-only tables unvacuumed for long periods, which hurt index-only scans. If you run an older release, check this before blaming your indexes.

Where do dead tuples come from in analytics? Mostly from the derived tables. A sessions row is rewritten whenever the refresh job touches it, and a rollup row is rewritten on every refresh.

Those tables bloat even though events does not. Watch them directly:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

For hot rollup and session tables, lower the autovacuum scale factor per table and set a lower fillfactor so updates stay on the same page:

ALTER TABLE daily_pageviews SET (
  fillfactor = 80,
  autovacuum_vacuum_scale_factor = 0.02
);

A mistake I have seen in production is an hourly job that did UPDATE sessions for every row to recompute duration. It rewrote the entire table each run, and the table grew several times larger than its live data. The fix was to compute duration in a query over events and update only changed sessions.

How Real Systems Do This

TimescaleDB builds on the same idea as this article: it automatically partitions time-series data into chunks by time and adds compression and continuous aggregates on top. Its continuous aggregates are managed rollups, much like the table above. If you outgrow hand-managed partitions on PostgreSQL, it is the closest step that keeps your SQL.

Plausible Analytics and PostHog moved their event storage to ClickHouse, a column store, because scanning narrow columns over billions of rows suits that engine better than a row store. That is the same destination described in the warehouse article. Most teams reach it only after PostgreSQL tuning stops being enough.

The common pattern is staged: raw events in a time-partitioned table, rollups for dashboards, and an export of old partitions to cheaper storage. You can build every stage with the features covered here.

Decision Framework

1. Is the query slow? Run EXPLAIN (ANALYZE, BUFFERS) and read the scan node, rows removed and buffers before changing anything.
2. Are estimates wrong by 10x or more? Run ANALYZE and raise statistics on skewed columns first.
3. Does one filter shape dominate? Add a composite B-tree with equality columns first and the range column last.
4. Does the query target a rare event? Use a partial index.
5. Is the table append-only and ordered by time? Add BRIN on occurred_at if B-tree size or write cost hurts.
6. Does the same aggregate run constantly? Build a rollup before adding more indexes.
7. Do you have a retention policy or maintenance pain? Partition by month.

When NOT to Use This

• Under a few million rows: a sequential scan is fast, and extra indexes only slow your collector. Measure first.
• Dashboards that scan billions of rows across many columns: this is a column-store workload. Tuning PostgreSQL further delays a move you will need anyway, so read the warehouse article instead.
• Teams without operational capacity: partition management, rollup jobs and vacuum tuning are ongoing work. If nobody owns them, a managed analytics product costs less than the outages.

Common Mistakes

• Indexing every column separately, which multiplies write cost and still leaves the planner without a good path.
• Putting the range column first in a composite index, so the equality filter cannot narrow the scan.
• Using BRIN after out-of-order backfills, which leaves ranges overlapping and the index useless.
• Forgetting to pre-create next month’s partition, so rows pile up in the default partition.
• Keeping ON CONFLICT (event_id) after partitioning, which fails because the unique key now includes occurred_at.
• Running date_trunc without a pinned time zone, which shifts daily buckets between environments.

Key Takeaways

• PostgreSQL performance tuning starts with reading EXPLAIN (ANALYZE, BUFFERS) before and after a change, and comparing buffers rather than wall-clock time.
• Match composite index column order to your filters: equality first, range last.
• Use partial indexes for rare events and BRIN for time-ordered appends.
• Partition by month for retention and maintenance, not as a default speedup.
• Partitioned tables need the partition key inside every unique constraint, so adjust your idempotent insert.
• Make rollups idempotent by recomputing recent days with ON CONFLICT DO UPDATE.
• Watch dead tuples on sessions and rollups, not on the append-only events table.

FAQ

How do I speed up PostgreSQL queries on a large events table?

Run EXPLAIN (ANALYZE, BUFFERS) to find the slow node. Then add a composite B-tree index matching your filter, use a partial index for rare event types, and precompute rollups for repeated aggregates. Partitioning by month helps with retention more than raw speed.

When should I use a BRIN index instead of a B-tree?

Use BRIN when a column correlates with physical row order, such as occurred_at in an append-only table. It is far smaller and cheaper to maintain. However, it is lossy and degrades after out-of-order backfills, so check pg_stats.correlation first.

Should I partition my PostgreSQL events table?

Partition when you need cheap retention (dropping old months) or when vacuum and reindex on one huge table hurt. For a table of a few million rows with good indexes, partitioning adds work without much benefit.

Why does partitioning break ON CONFLICT on event_id?

PostgreSQL requires unique constraints on a partitioned table to include the partition key. Your key becomes (event_id, occurred_at), so the conflict target must list both columns. Retries still dedupe because they carry the same timestamp.

Conclusion

PostgreSQL performance tuning for analytics rewards a small set of habits: read the plan, match indexes to filters, partition for operations, and precompute what repeats. Each step has a price in writes, disk or maintenance, so make the table prove it before you pay.

Rule of thumb: measure first, index second, partition third, and roll up before you scale up. Next, learn to trigger automated actions from analytics events.

Last updated on 9 October 2026.