Postgres Best Practices for Modern Web Apps

Most developers reach for PostgreSQL because it’s free, reliable, and battle-tested. But choosing it is the easy part. The real challenge is using it well — designing schemas that hold up under changing requirements, writing queries that stay fast as data grows, and managing connections in a way that doesn’t quietly kill your app’s performance.

This article covers the PostgreSQL best practices for web apps that matter most in production.

Schema Design: Start Structured, Stay Flexible

Good PostgreSQL schema design starts with a clear separation between what you know matters and what might matter later.

For fields you’ll filter, sort, or join on — user IDs, timestamps, status flags, foreign keys — use proper typed columns with constraints. For everything else, a JSONB column gives you the flexibility of a document store without abandoning relational integrity.

CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  metadata   JSONB DEFAULT '{}'::jsonb
);

This hybrid approach is one of the most practical modern PostgreSQL features available. You get schema enforcement where it counts and flexibility where requirements are still evolving. When a JSONB field starts appearing in WHERE clauses regularly, promote it to a real column and add a constraint. That migration is straightforward and worth doing.

One thing to avoid: storing everything in JSONB from the start. It feels flexible until you need to enforce a NOT NULL constraint or add a foreign key — and then you’re rewriting half your schema anyway.

PostgreSQL Indexing Strategies That Actually Help

Indexes are where most PostgreSQL performance tips converge. The defaults are fine to start, but production workloads need deliberate choices.

B-tree indexes handle most equality and range queries. In recent PostgreSQL versions (18+), the planner can perform skip scans on multicolumn B-tree indexes, which means column order matters less than it used to — but leading with your most selective column is still the safer default.

GIN indexes are the right choice for JSONB columns and full-text search:

CREATE INDEX idx_user_metadata ON users USING GIN (metadata);

This makes queries like WHERE metadata @> '{"plan": "premium"}'::jsonb fast even on large tables.

Partial indexes are underused and often the best tool for filtering active or recent records:

CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;

Avoid indexing every column. Each index adds write overhead and consumes memory. Index what your queries actually filter on, verify with EXPLAIN ANALYZE, and remove indexes that aren’t being used.

Connection Management for Web Workloads

PostgreSQL’s connection model is stateful and relatively expensive to establish. Each connection spawns a dedicated process on the server. At scale, hundreds of short-lived connections from a web app will degrade performance noticeably.

The fix is a connection pooler sitting between your app and the database. PgBouncer is the standard choice. In transaction mode, it multiplexes many app connections over a much smaller number of real database connections.

This matters especially in serverless environments (Lambda, Vercel, Cloudflare Workers) where each function invocation may try to open a new connection. Without pooling, you’ll hit PostgreSQL’s max_connections limit under moderate traffic. See the PostgreSQL documentation on connection settings for details.

Safe Migrations in Production

Schema changes are where things go wrong. The safest pattern is to make changes additive first: add a new column with a default, backfill data, then drop the old column in a later deployment.

Avoid ALTER TABLE operations that lock the table under load. Adding a NOT NULL column without a default, for example, rewrites the entire table in older PostgreSQL versions. In PostgreSQL 11+, adding a column with a non-volatile default no longer requires a table rewrite — but it’s still worth checking what your migration tool actually generates before running it in production.

Always run migrations inside transactions where possible, and test rollback paths before deploying.

Query Performance: Find Slow Queries First

Don’t guess at performance problems. Enable pg_stat_statements to track query execution statistics across your workload:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Then query it to find your slowest or most-called queries:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Once you have a target, use EXPLAIN (ANALYZE, BUFFERS) to understand what the planner is doing and whether an index would help.

Conclusion

PostgreSQL rewards the developers who take schema design, indexing, and connection management seriously from the start. These aren’t advanced topics — they’re the baseline for running a web app in production without surprises. Start with typed columns and constraints where they matter, add JSONB only where flexibility is genuinely needed, and index based on actual query patterns rather than assumptions. Pair that with a connection pooler and a disciplined migration strategy, and you’ll avoid the most common pitfalls that trip up production applications.

FAQs

Gain control over your UX

See how users are using your site as if you were sitting next to them, learn and iterate faster with OpenReplay. — the open-source session replay tool for developers. Self-host it in minutes, and have complete control over your customer data. Check our GitHub repo and join the thousands of developers in our community.