How do I keep PostgreSQL fast as my SaaS grows and query volume increases?

To keep PostgreSQL fast as your SaaS grows, focus on four things: good schema design, good query patterns, good indexing, and good operational habits. As load increases, most performance problems come from a small number of bad queries or missing indexes, not PostgreSQL itself.

1. Design the schema for the way you query

• Normalize sensibly, but don’t over-normalize if it creates lots of joins on hot paths.
• Use the right data types:
  • uuid or bigint for IDs, not text
  • timestamptz for timestamps
  • boolean instead of int flags
• Keep rows small. Large rows reduce cache efficiency.
• Avoid storing frequently changing fields in the same wide “core” table if they don’t belong there.

2. Index for real queries, not guesses

• Add indexes for your most common WHERE, JOIN, ORDER BY, and GROUP BY patterns.
• Use composite indexes in the right column order.
  • Put equality filters first, then range filters, then sort columns.
• Use covering indexes (INCLUDE) when they let PostgreSQL satisfy queries from the index alone.
• Don’t over-index:
  • Every index slows inserts, updates, and deletes.
  • Remove unused indexes regularly.

Example

If you often run:

SELECT id, created_at
FROM orders
WHERE tenant_id = $1 AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

A good index might be:

CREATE INDEX ON orders (tenant_id, status, created_at DESC) INCLUDE (id);

3. Write efficient queries

• Avoid SELECT * on hot paths.
• Always paginate large result sets.
• Prefer keyset pagination over OFFSET for deep pages.
• Don’t run the same query repeatedly in loops if you can batch it.
• Be careful with LIKE '%term%' on large tables; consider pg_trgm or full-text search.
• Avoid unnecessary subqueries and complicated ORM-generated SQL if it’s inefficient.

Keyset pagination example

Instead of:

... ORDER BY created_at DESC LIMIT 50 OFFSET 5000;

Use:

... WHERE created_at < $last_seen_created_at
ORDER BY created_at DESC
LIMIT 50;

4. Keep tenants isolated logically and performance-wise

For SaaS, multi-tenancy is a common scaling issue.

• Make sure most queries include tenant_id.
• Consider tenant-scoped composite indexes.
• For very large tenants, consider:
  • partitioning
  • separate databases
  • moving “noisy” tenants to dedicated infrastructure

5. Use connection pooling

PostgreSQL does not like huge numbers of idle connections.

• Use PgBouncer or a similar pooler.
• Keep application connection counts reasonable.
• Tune your pool sizes so you don’t overload PostgreSQL with too many concurrent queries.

6. Monitor the slow stuff

You can’t optimize what you can’t see.

• Enable and review:
  • pg_stat_statements
  • slow query logs
  • autovacuum logs if needed
• Watch:
  • slow queries
  • CPU saturation
  • I/O wait
  • buffer cache hit rate
  • lock contention
  • dead tuples / bloat

7. Keep autovacuum healthy

PostgreSQL performance degrades if tables bloat or dead rows pile up.

• Make sure autovacuum is running effectively.
• For busy tables, tune autovacuum thresholds per table if needed.
• Don’t disable it.
• Run VACUUM (ANALYZE) when needed, especially after large data changes.

8. Partition only when it helps

Partitioning can help with:

• very large tables
• time-series data
• tenant isolation at scale
• easier data retention/deletion

But partitioning also adds complexity, so don’t do it too early. Use it when:

• queries only need a subset of data
• old data can be dropped quickly
• a table is too large for maintenance to be cheap

9. Use caching strategically

Cache reads that are:

• expensive
• repeated often
• okay to be slightly stale

Options:

• application cache
• Redis
• materialized views for precomputed aggregates

Don’t cache everything; first make the query/index design solid.

10. Scale reads and heavy workloads

When read traffic grows:

• add read replicas
• route read-only traffic there
• keep in mind replication lag for strongly consistent reads

For heavy analytics:

• consider moving reporting queries off the primary
• use pre-aggregation tables or a warehouse if needed

11. Test before production hurts

• Load test with realistic data volume and concurrency
• Test query plans with EXPLAIN (ANALYZE, BUFFERS)
• Re-check plans after statistics or data distribution changes

12. Common anti-patterns to avoid

• Missing tenant_id in multi-tenant queries
• Large OFFSET pagination
• Too many indexes
• N+1 query patterns
• Unbounded UPDATE/DELETE without indexes
• Using PostgreSQL as both OLTP and analytics engine for everything

Practical growth checklist

If you want the shortest path to keeping PostgreSQL fast:

1. Enable pg_stat_statements
2. Find top slow/frequent queries
3. Add the right indexes
4. Fix pagination and N+1 queries
5. Use PgBouncer
6. Keep autovacuum healthy
7. Add read replicas when reads dominate
8. Partition only for large tables with clear benefit

If you want, I can also give you:

• a PostgreSQL SaaS performance checklist
• a query/index review template
• or a step-by-step scaling plan from 1 to 1000 customers