Most SaaS Postgres slowdowns trace back to a handful of fixable anti-patterns — N+1 queries, missing indexes, and bloated SELECT *. Here's how to diagnose and fix them fast.

Optimising PostgreSQL Query Performance for SaaS Apps

Your hardware budget doubled, your query times barely moved. Sound familiar? Most SaaS teams facing Postgres bottlenecks are not running out of CPU or memory — they are running queries that were never designed to scale. Fix the query patterns, and you often recover 60–80% of lost performance before touching a single server setting.

Here are the top culprits, why they hurt, and exactly how to remediate them.

1. The N+1 Query Problem

N+1 is the silent killer of SaaS database performance. It happens when your application fetches a list of records and then fires a separate query for each row to retrieve related data — one query becomes hundreds or thousands at runtime.

Classic example (Django/Python ORM):

# BAD: fires 1 + N queries
orders = Order.objects.all()
for order in orders:
    print(order.customer.name)  # separate DB hit per order

# GOOD: one query with a JOIN
orders = Order.objects.select_related('customer').all()

Every ORM has an equivalent escape hatch — include in Rails, with in Laravel, joins in Ecto. The fix is almost always a single keyword, but you have to know it is happening first.

How to catch it: Enable query logging in Postgres (log_min_duration_statement = 0 in development) or use a tool like pgBadger or your APM's database tab. If you see the same query template repeating dozens of times per request cycle, you have an N+1.

2. Missing and Misused Indexes

Indexes are the single highest-leverage optimisation available to most teams, yet they are routinely under-applied or applied incorrectly.

Where teams go wrong

No index on foreign keys. Postgres does not automatically index foreign key columns. If you join orders.customer_id to customers.id and customer_id is unindexed, every join scans the entire orders table.
Indexing columns that are never in a WHERE clause. An index that is never used wastes write performance and bloats storage.
Ignoring composite indexes for multi-column filters. An index on (status) does nothing for a query filtering on WHERE status = 'active' AND created_at > now() - interval '7 days'. A composite index on (status, created_at) serves both filters.

The EXPLAIN ANALYZE workflow

Never guess — measure:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC;

Look for Seq Scan on large tables. That is your signal. If you see Rows Removed by Filter in the thousands against a small result set, an index is missing.

Partial indexes for SaaS multi-tenancy

If your SaaS app stores all tenants in shared tables, partial indexes are a game-changer:

CREATE INDEX idx_orders_pending
ON orders (customer_id, created_at)
WHERE status = 'pending';

This index is tiny, stays fresh easily, and serves the most common operational query pattern — filtering active work by tenant.

3. Bloated SELECT *

SELECT * is convenient during development and catastrophic at scale. It forces Postgres to read and transmit every column — including large TEXT, JSONB, and BYTEA fields — even when the application needs two columns.

The real cost is not just network bandwidth. Wide row reads prevent Postgres from using index-only scans, where the engine can satisfy a query entirely from the index without touching the heap (the actual table). As soon as you add SELECT *, that optimisation disappears.

Rule of thumb: Select only the columns your application actually uses. In ORMs, use .only(), .select(), or projection methods. In raw SQL, list columns explicitly.

4. Unparameterised Queries and Plan Cache Misses

Postgres caches query execution plans. When queries are built with string interpolation — embedding literal values directly into SQL — every unique value produces a new query string, busting the cache every time and forcing the planner to re-analyse from scratch.

Use parameterised queries (prepared statements) wherever possible. Beyond performance, this is also the correct defence against SQL injection. Most frameworks do this by default if you use their query builder correctly; the anti-pattern usually appears in raw SQL escape hatches written hastily.

5. Unbounded Queries in Multi-Tenant Environments

A SaaS table shared across thousands of tenants can grow to tens of millions of rows quickly. Queries that lack a LIMIT clause — or, worse, that accidentally omit a WHERE tenant_id = ? filter — will perform full-table scans that are invisible in development (where each tenant has 20 rows) and catastrophic in production (where each tenant has 20,000).

Practical safeguards

Enforce tenant_id filtering at the ORM or repository layer, not in individual query methods.
Add application-level query timeouts (SET statement_timeout = '5s') to prevent runaway queries from holding locks and starving other connections.
Use pg_stat_statements (a built-in extension) to surface the slowest queries by total execution time across all calls — not just the slowest single execution.

-- Enable once per database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find your worst offenders
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

6. Connection Pool Exhaustion Masquerading as Slow Queries

Not every performance complaint is a slow query. If your SaaS app opens a new database connection per request and your user base grows, you will hit Postgres's max_connections ceiling. Queries queue behind connection acquisition, and everything looks slow from the application side.

The fix is a connection pooler — PgBouncer is the standard choice. Configure it in transaction pooling mode, and a handful of actual Postgres connections can serve hundreds of concurrent application threads cleanly.

Building a Performance Culture on Your Team

One-off optimisations decay. The teams that maintain fast Postgres performance treat query review the same way they treat code review:

Slow query alerts in CI or staging (flag any query over 100ms).
EXPLAIN ANALYZE as a standard part of code review for any new database-touching feature.
Migration hygiene — every new foreign key gets an index; every new table gets a documented access pattern.

Database performance is not a DBA problem dropped on a specialist once things are on fire. It is an engineering discipline embedded in the daily workflow.

Why This Matters for Your Project

Whether you are building a B2B SaaS product, an internal operations platform, or a data-heavy mobile backend, database performance directly governs your unit economics and user experience. Slow queries mean higher compute costs, longer response times, and churn. At Code!nk Technologies, the query patterns above account for the majority of performance wins we deliver during database audits — and most of them are resolved within a single sprint. You do not need new infrastructure; you need a disciplined look at what your application is actually asking Postgres to do.