Back/Module P-9 External Services, Caching Layers, and Deployment
Module P-9·23 min read

ORMs vs. raw SQL, read replicas, materialised views, managed databases, and a production-ready deployment checklist.

P-9 — External Services, Caching Layers, and Deployment

The Practitioner phase has covered SQL patterns, indexes, transactions, schema design, JSONB, access control, full-text search, and performance tuning. This final module ties it together: how PostgreSQL sits inside a production system alongside caching layers, read replicas, ORMs, and managed database services — and what a production-ready deployment actually looks like.

ORMs vs. Raw SQL

Almost every application uses either an ORM or a query builder. Neither is universally better — they make different trade-offs.

What ORMs Get Right

ORMs (Prisma, TypeORM, SQLAlchemy, ActiveRecord) handle the mechanical work well:

  • Type-safe models that match your schema
  • Boilerplate CRUD without writing SQL
  • Migrations generated from schema changes
  • Relationship loading with include/eager_load
  • Connection management

For straightforward CRUD operations — creating users, updating records, simple filtered queries — ORMs are faster to write and harder to get wrong.

What ORMs Get Wrong

ORMs generate SQL you don't control. That becomes a problem in several situations:

Complex aggregations: An ORM asked to compute "revenue by country for the past 90 days, excluding refunded orders, grouped by week" will either generate inefficient SQL or require you to drop to raw SQL anyway.

Window functions: Most ORMs have poor or no support for ROW_NUMBER(), LAG(), LEAD(), RANK(). These require raw queries.

Batch operations: INSERT INTO ... SELECT ..., UPDATE ... FROM ..., DELETE ... USING ... — set-based operations that move data entirely inside PostgreSQL. ORMs tend to model these as row-at-a-time operations with round-trips per row.

Query plan control: You cannot tell an ORM to use a specific index, add LIMIT to a subquery, or restructure a JOIN order. When you need to fix a query plan, you need raw SQL.

The Right Pattern

Use the ORM for CRUD. Use raw SQL for analytics, complex aggregations, bulk operations, and anywhere the ORM-generated query is demonstrably slow.

Most ORMs have an escape hatch:

javascript
// Prisma: raw query
const result = await prisma.$queryRaw`
  SELECT date_trunc('week', created_at) AS week,
         SUM(amount) AS revenue
  FROM orders
  WHERE status = 'completed'
    AND created_at > now() - interval '90 days'
  GROUP BY 1
  ORDER BY 1
`;

// TypeORM: query builder + raw
const result = await dataSource.query(`
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
  HAVING COUNT(*) > 10
`);

The practical rule: start with the ORM, switch to raw SQL the moment you're fighting the ORM to get the query you want.

Read Replicas

A read replica is a PostgreSQL server that receives a continuous stream of WAL from the primary and applies it, maintaining an identical copy of the data. It accepts SELECT queries but not writes.

When Read Replicas Help

  • Reporting and analytics queries that scan large tables and would compete with OLTP queries on the primary
  • Read-heavy workloads where most traffic is SELECT and write throughput isn't the bottleneck
  • Geographic distribution — a replica closer to users reduces read latency
  • Backup source — take backups from the replica to avoid impacting primary performance

When Read Replicas Don't Help

  • Write-heavy workloads — replicas don't offload writes
  • Applications that read their own writes (write then immediately read) — replication lag can return stale data
  • Connection count problems — adding a replica doubles your connection pool management complexity

Replication Lag

Replication is asynchronous by default. After a write on the primary, there is a small window (typically milliseconds, sometimes seconds under load) before the replica reflects it.

sql
-- On the primary: check replication lag
SELECT client_addr,
       state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

-- On the replica: check how far behind the primary
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

For applications where reading stale data is acceptable (analytics, feeds, leaderboards), route reads to the replica freely. For operations where stale reads cause bugs (showing a user their just-submitted form, inventory counts), read from the primary.

Routing Reads

Most connection libraries and ORMs support read/write splitting:

javascript
// Prisma: separate datasource for reads
// DATABASE_URL points to primary (writes)
// DATABASE_REPLICA_URL points to replica (reads)
const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_URL }
  }
});

// In Prisma, use $extends to route specific queries to replica

Or at the infrastructure level, a load balancer like HAProxy or AWS RDS Proxy can route read traffic to replicas automatically.

Materialised Views

A materialised view stores the result of a query as a physical table on disk, refreshed on demand. It is the right tool when:

  • A complex query is run frequently but the underlying data changes infrequently
  • The query takes seconds to compute and you need millisecond response
  • You want to decouple expensive aggregation from query-time computation
sql
-- Create a materialised view of monthly revenue
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  date_trunc('month', created_at) AS month,
  SUM(amount)                      AS revenue,
  COUNT(*)                         AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY 1
ORDER BY 1;

-- Index it for fast lookups
CREATE INDEX idx_monthly_revenue_month ON monthly_revenue (month);

-- Query it — instant, regardless of how large the orders table is
SELECT * FROM monthly_revenue WHERE month >= '2024-01-01';

-- Refresh when underlying data changes
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Refresh without blocking reads (requires a UNIQUE index on the view)
CREATE UNIQUE INDEX idx_monthly_revenue_month_uniq ON monthly_revenue (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

REFRESH MATERIALIZED VIEW CONCURRENTLY is critical in production — a regular refresh locks the view for the duration of the refresh. CONCURRENTLY builds a new version alongside the old one and swaps atomically, so readers never block. It requires a unique index.

When to Refresh

Common patterns:

  • Scheduled refresh: A cron job or pg_cron refreshes the view every hour/day
  • Trigger-based refresh: A trigger fires on the source table after significant data changes
  • On-demand: The application calls REFRESH when it needs fresh data for a specific operation
sql
-- Using pg_cron to refresh hourly
SELECT cron.schedule('refresh-monthly-revenue', '0 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue');

Caching with Redis

PostgreSQL is fast, but not cache fast. A cache hit from Redis returns in under a millisecond. A well-optimised PostgreSQL query on warm data typically takes 1-50ms. For data that:

  • Is read far more than it is written
  • Is expensive to compute
  • Does not need to be perfectly fresh

...a cache layer in front of PostgreSQL makes sense.

The Caching Pattern

javascript
async function getUserProfile(userId) {
  const cacheKey = `user:profile:${userId}`;

  // 1. Check cache
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // 2. Miss: query PostgreSQL
  const user = await db.query(
    'SELECT id, name, email, avatar_url FROM users WHERE id = $1',
    [userId]
  );

  // 3. Store in cache with TTL
  await redis.setex(cacheKey, 300, JSON.stringify(user));  // 5 min TTL

  return user;
}

Cache Invalidation

The hard problem with caching is keeping the cache consistent with the database. The simplest strategy: invalidate the cache entry when the underlying data changes.

javascript
async function updateUserProfile(userId, data) {
  await db.query('UPDATE users SET name=$1, email=$2 WHERE id=$3',
    [data.name, data.email, userId]);

  // Invalidate: next read will re-fetch from DB
  await redis.del(`user:profile:${userId}`);
}

For complex data with many possible cache keys, TTL-based expiry (let stale cache expire naturally) is simpler and often acceptable if a few minutes of staleness is tolerable.

What Not to Cache

  • Data that must be perfectly consistent (bank balances, inventory counts during checkout)
  • Data that changes every request (per-user, per-session counters)
  • Data that PostgreSQL already serves fast from shared_buffers — you're adding a network round-trip for no benefit

Managed Database Services

Running PostgreSQL yourself means managing backups, PITR (point-in-time recovery), failover, upgrades, and replication. Most teams on cloud infrastructure are better served by a managed service.

The Main Options

AWS RDS PostgreSQL / Aurora PostgreSQL

  • Automated backups, PITR, multi-AZ failover, read replicas
  • Aurora offers higher throughput with a custom storage layer
  • RDS Proxy for connection pooling at the managed layer
  • Higher cost than self-managed EC2

Google Cloud SQL for PostgreSQL

  • Similar managed feature set to RDS
  • Strong integration with GKE and GCP services

Supabase

  • Managed PostgreSQL with built-in Auth, Storage, Realtime, and REST/GraphQL APIs
  • RLS-based auth model (P-6 is directly applicable)
  • Excellent for full-stack applications that want PostgreSQL without ops work
  • pgvector, full-text search, and most extensions available

Neon

  • Serverless PostgreSQL with branch-per-environment workflow
  • Scales to zero when idle — cost-effective for development/staging databases
  • Branch databases for PRs is a standout feature

Railway / Render

  • Simple managed PostgreSQL for small-scale applications and prototypes

Choosing Between Them

For production applications with significant data or traffic: RDS or Aurora (AWS-native), Cloud SQL (GCP-native), or Supabase (if you want the full platform).

For development speed and small-scale production: Supabase or Neon.

For large-scale with custom requirements: self-managed on EC2/GKE with Patroni for HA.

Production Deployment Checklist

Before a PostgreSQL-backed application goes to production:

Database Configuration

sql
-- Verify these are set appropriately in postgresql.conf / managed service settings:
-- shared_buffers = 25% of RAM
-- effective_cache_size = 50-75% of RAM
-- random_page_cost = 1.1 (SSD) or 4.0 (HDD)
-- work_mem = 16-64MB (tune per workload)
-- max_connections = sized for your pooler, not your app connections
-- log_min_duration_statement = 500 (log slow queries)
-- log_lock_waits = on
-- shared_preload_libraries = 'pg_stat_statements'

Schema and Migrations

  • All foreign keys have indexes on the referencing column
  • All columns used in WHERE, JOIN ON, or ORDER BY on large tables have appropriate indexes
  • Indexes created with CONCURRENTLY in production migrations
  • NOT NULL constraints on required columns
  • CHECK constraints on bounded-value columns (amounts > 0, status IN (...))
  • TIMESTAMPTZ used for all timestamps (not TIMESTAMP WITHOUT TIME ZONE)
  • Migrations are reversible (or you have a rollback plan)
  • Migrations tested on a production-scale data copy before deployment

Access Control

  • Application connects with a least-privilege role (no superuser, no CREATEDB)
  • Separate roles for app, migrations, and read-only analytics
  • Passwords rotated from defaults; connection strings in environment variables or secrets manager
  • RLS enabled on all tenant-scoped tables if multi-tenant

Connection Management

  • PgBouncer (or managed equivalent) in front of PostgreSQL
  • pool_mode = transaction for web applications
  • Connection pool sized to default_pool_size = max_connections * 0.8 / num_app_instances
  • Application uses connection timeouts and handles connection errors with retry

Backups and Recovery

  • Automated backups enabled with verified retention policy
  • PITR (point-in-time recovery) enabled — essential for "we accidentally deleted data" scenarios
  • Backup restore tested — a backup you've never restored is an untested backup
  • WAL archiving configured if self-managed

Monitoring

  • pg_stat_statements enabled and connected to your monitoring tool
  • Alerts on replication lag (if using replicas)
  • Alerts on connection count approaching max_connections
  • Alerts on table bloat (autovacuum falling behind)
  • Slow query log shipping to a log aggregator
  • Disk usage alerts (PostgreSQL does not shrink disk usage automatically)

Application-Level

  • All user input goes through parameterised queries (never string concatenation into SQL)
  • Long-running transactions are bounded with statement timeouts
  • Application handles serialization failure errors with retry for SERIALIZABLE transactions
  • Database connection pool is closed gracefully on application shutdown

Statement Timeouts

One of the most important production safety nets: prevent runaway queries from holding locks or consuming resources indefinitely.

sql
-- Kill any query that takes longer than 30 seconds
ALTER ROLE app_service SET statement_timeout = '30s';

-- Or per-session
SET statement_timeout = '30s';

-- For migrations (need more time):
SET statement_timeout = '0';  -- no timeout
ALTER TABLE large_table ADD COLUMN new_col TEXT;

Set statement_timeout on your application role in production. Without it, a rogue query or accidental full-table scan can hold a lock and block other operations for minutes.

The Full Architecture Picture

A production PostgreSQL deployment for a typical web application:

┌─────────────────────────────────────────────────────┐
│  Application Servers (multiple instances)            │
│                                                      │
│  ORM (CRUD) + Raw SQL (analytics/complex queries)    │
│  Redis cache (hot/computed data)                     │
└──────────────────┬──────────────────────────────────┘
                   │
          ┌────────▼────────┐
          │   PgBouncer      │  ← transaction pooling
          │  (pool = 20-50)  │     1000 app conns → 20 pg conns
          └────────┬─────────┘
                   │
     ┌─────────────▼──────────────┐
     │      PostgreSQL Primary     │  ← all writes
     │  max_connections = 100      │
     │  shared_buffers = 4GB       │
     └──────────┬──────────────────┘
                │  streaming replication
     ┌──────────▼──────────────────┐
     │      PostgreSQL Replica     │  ← analytics reads
     │  (async, eventual)          │     BI tools, reports
     └─────────────────────────────┘

This setup — application → PgBouncer → primary, with a replica for analytics — handles the vast majority of production workloads.

Summary

The Practitioner phase is complete. You now have the full picture:

SQL patterns (P-1) — CTEs, window functions, upserts, the patterns used weekly. Indexes (P-2) — when to add them, composite and partial indexes, the write cost trade-off. Transactions (P-3) — isolation levels, safe concurrent operations. Schema design (P-4) — normalisation, the right types for money and time, migrations. JSONB (P-5) — semi-structured data inside relational tables. Access control and RLS (P-6) — role hierarchies, multi-tenant isolation at the database layer. Full-text search (P-7) — search without Elasticsearch. Performance tuning (P-8) — reading EXPLAIN ANALYZE, eliminating N+1, PgBouncer. Deployment (P-9) — caching layers, read replicas, materialised views, managed services, production checklist.

The Architect phase goes deeper: storage internals, MVCC mechanics, WAL, autovacuum, locking internals, and the subsystems that explain every production behaviour you've observed but not fully understood.

PreviousModule P-8: Performance Tuning for Application EngineersNext Module P-10: Zero-Downtime Schema Migrations

Discussion