# PostgreSQL and Elasticsearch performance at scale (partitioning, tuning, ILM)

> Freelance PostgreSQL and Elasticsearch performance engineer. Table partitioning past 1 TB, query tuning, replicas, pgvector, and index lifecycle management for search at scale.

URL: https://fedme.dev/services/postgres-elasticsearch
Provided by: Federico Meini (federico.meini@gmail.com)

## 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.

## What you get

- A clear diagnosis of why things are slow, backed by EXPLAIN plans, pg_stat_statements and real numbers.
- Partitioning and retention strategies for large, append-heavy tables, rolled out without downtime.
- Query and index tuning that fixes the queries that actually hurt, not all of them.
- Elasticsearch clusters with sensible shard sizes, hot/warm/cold tiers and index lifecycle policies that control cost.

## 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.

## Related

- [Real-time and distributed systems](https://fedme.dev/services/realtime-systems)
- [AI agent orchestration and evals](https://fedme.dev/services/ai-agents-evals): RAG and vector search on Postgres.

## Ways to engage

- **Database health check**: A one-week review of your Postgres or Elasticsearch setup, with a prioritised list of fixes and their expected impact.
- **Partitioning / migration project**: Plan and execute a zero-downtime move to partitioned tables or a new index strategy, including backfills and rollback plans.
- **On-call performance help**: A block of hours to help when a query or cluster is on fire.

## Stack

PostgreSQL, Declarative partitioning, pg_stat_statements, Read replicas, pgvector, Elasticsearch, Index lifecycle management, BigQuery, Ecto

## FAQ

### 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.

## Contact

Email federico.meini@gmail.com · https://fedme.dev/contact