Most "we need a bigger database" conversations are actually "we need the right index" conversations. Postgres scales further than most teams assume when the query patterns, indexes, and schema are treated as a design surface instead of an afterthought.
Index the Query, Not the Table
An index should be built to match how a table is actually queried, not added speculatively to every foreign key. A composite index on (tenant_id, created_at) serves a "recent items for this tenant" query far better than two separate single-column indexes ever could, because Postgres can use the composite index to satisfy both the filter and the sort in one pass. The habit worth building is checking EXPLAIN ANALYZE on the actual production query shape before assuming an index is needed at all — indexes aren't free; every one slows down writes and takes storage.
B-tree Isn't Always the Right Index
B-tree is the default and the right choice for equality and range queries, but it's not the only tool. GIN indexes serve full-text search and JSONB containment queries (@>) far better than a B-tree ever would. BRIN indexes are dramatically smaller and faster to maintain than B-tree for naturally ordered, append-only data like time-series event logs, where a full B-tree is overkill for the access pattern.
Partitioning for Time-Series and Large Tables
Once a table holding events, logs, or time-series data grows past tens of millions of rows, declarative partitioning by range (typically by month) keeps queries fast by letting Postgres skip entire partitions that fall outside a date filter (partition pruning), and makes data retention trivial — dropping an old partition is instant, compared to a DELETE that has to scan and vacuum millions of rows.
CREATE TABLE events (
id bigserial,
tenant_id uuid NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_07 PARTITION OF events
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');N+1 Queries Kill at Scale
The single most common application-level performance bug is fetching a list, then querying per row for related data inside a loop — fine at 10 rows, catastrophic at 10,000. Prisma's include, batched findMany with WHERE id IN (...), or a proper join solve this at the query layer. This is worth catching in code review specifically, since it's invisible in local testing with a handful of rows and only shows up under real production data volume.
Connection Pooling Is Not Optional
Postgres connections are expensive relative to a typical web request's lifetime; a serverless or high-concurrency app opening a raw connection per request will exhaust the database's connection limit long before it exhausts CPU. A pooler (PgBouncer, or the pooling built into most managed Postgres providers) sitting between the application and the database is a baseline requirement past a handful of concurrent users, not a scaling optimization for later.
EXPLAIN ANALYZE Discipline
Guessing at query performance is unreliable; reading EXPLAIN ANALYZE output is not optional for anyone writing production queries. The two numbers that matter most: whether the planner chose a sequential scan on a large table (a strong signal a needed index is missing), and where actual row counts diverge sharply from the planner's estimates (a strong signal that table statistics are stale and need an ANALYZE).
Wise Hustlers designs schemas and indexing strategy around the real query patterns from day one, because the fastest database migration is the one a team never has to run under production load.