Federico MeiniReal-time systems, AI agents & more
IT
Work with me(opens in a new tab)

PostgreSQL & Elasticsearch at scale

Get your database fast again, and keep it fast as data grows.

At Turn.io I partitioned production PostgreSQL tables of more than 1 TB and live Elasticsearch indices while message data grew by millions of rows a day. I can do the same for your slow queries, bloated tables and expensive search clusters.

Talk about your project (opens in a new tab)
postgres · messageslast query –rows –size –
Same data, same query. Partitioning turns “scan a terabyte” into “scan one month”, and retention into a metadata operation.
elasticsearch · index lifecycletotal –all-hot ––
primaryreplicasnapshot-backedillustrative $/GB-month, fully loaded
Rollover keeps shards a healthy size; ILM moves ageing data to cheaper hardware without anyone touching it.

Messaging data grows faster than anything else

Every message, status update and event is a row. At Turn.io message data grew by millions of rows a day, and the tables that powered inboxes, analytics and search slowly became the bottleneck. Vacuum took longer, indexes stopped fitting in memory, and deleting old data locked things up.

I partitioned production PostgreSQL tables of more than 1 TB to get the performance back, and did the same for live Elasticsearch indices, with index lifecycle policies that move older data to cold storage.

What I do

Diagnose. pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS), bloat and vacuum stats, index usage, lock contention. For Elasticsearch: shard sizes, heap pressure, slow logs, mappings.

Fix the queries that matter. Better indexes (partial, covering, multicolumn in the right order), rewritten queries, keyset pagination, fewer N+1s from the ORM.

Partition large tables. Choose a partition key that matches your queries, migrate without downtime, create future partitions automatically, and make retention a cheap DETACH PARTITION ... CONCURRENTLY instead of a huge DELETE.

Scale reads. Read replicas, routing read-only traffic, and understanding replication lag.

Tame search costs. Rollover, hot/warm/cold tiers, shard sizing, force-merge and ILM policies that keep Elasticsearch fast for recent data and cheap for old data.

How I approach it

  1. Measure first. No tuning without a baseline and a target.
  2. Fix the top offenders. In most systems a handful of queries cause most of the pain.
  3. Plan migrations like deploys, with small steps, reversible changes and a rollback path for each.
  4. Leave the team with the tools (dashboards, runbooks, conventions) to keep it fast.

Ways to engage

01

Database health check

A one-week review of your Postgres or Elasticsearch setup, with a prioritised list of fixes and their expected impact.

02

Partitioning / migration project

Plan and execute a zero-downtime move to partitioned tables or a new index strategy, including backfills and rollback plans.

03

On-call performance help

A block of hours to help when a query or cluster is on fire.

Questions

When should I partition a PostgreSQL table?

When a table is large and mostly append-only (events, messages, logs), most queries filter on a time range or tenant, and you need to delete old data cheaply. Partitioning is not a general speed-up, so the partition key has to match how you query.

Can a large table be partitioned without downtime?

Usually yes. Common approaches are attaching the existing table as the first partition behind a CHECK constraint, or dual-writing and backfilling in batches. Indexes are built concurrently on each partition and then attached. The details depend on your constraints and traffic.

What does Elasticsearch index lifecycle management do?

ILM automates what happens to indices as they age. It rolls over the write index at a size or age, moves older indices to cheaper warm and cold nodes, shrinks or force-merges them, and finally deletes them. It is the main lever for search cost.

Do you also work with pgvector?

Yes. I use pgvector for RAG and semantic search where keeping vectors next to relational data in Postgres is simpler than running a separate vector database.

Related writing

Other services