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
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
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
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
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
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
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
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
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.
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
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
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
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:
text
This setup — application → PgBouncer → primary, with a replica for analytics — handles the vast majority of production workloads.
Summary
That covers the ground from SQL patterns through production deployment:
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.
Two more Practitioner modules remain before the Architect phase: P-10, Zero-Downtime Schema Migrations (changing a live schema without an outage) and P-11, Connection Pooling Failure Modes (the PgBouncer/serverless/edge failure modes only P-8 touched the surface of). After those, the Architect phase goes deeper still: storage internals, MVCC mechanics, WAL, autovacuum, locking internals, and the subsystems that explain every production behaviour you've observed but not fully understood.
Next: P-10 — Zero-Downtime Schema Migrations →
Knowledge Check
A development team notices their dashboard loading slowly. The dashboard displays "Total Revenue by Month" and calculates this on every page load by querying a payments table with 50 million rows: SELECT date_trunc('month', created_at), sum(amount) FROM payments GROUP BY 1. They decide to create a Materialized View to fix the performance. Which of the following implementation details is critical to ensure the dashboard remains fast and available in production without blocking read queries during refresh?
An e-commerce application uses an ORM for all database interactions. During a major promotional event, the application experiences a severe performance degradation. You profile the application and discover it is executing thousands of individual UPDATE queries per second to deduct inventory quantities after checkout. Which raw SQL feature should replace the ORM's row-at-a-time updating to resolve the bottleneck?
Your team wants to implement a Read Replica to improve performance. Under which of the following scenarios is adding a Read Replica the *wrong* architectural choice and likely to introduce bugs?
Test your knowledge with more question sets
Sign in to access a wider variety of questions and get notified when new practice sets are added to this module.
// Prisma: raw queryconst 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 + rawconst result =await dataSource.query(` SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 10
`);
-- On the primary: check replication lagSELECT 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 primarySELECTnow()- pg_last_xact_replay_timestamp()AS replication_delay;
// Prisma: separate datasource for reads// DATABASE_URL points to primary (writes)// DATABASE_REPLICA_URL points to replica (reads)const prisma =newPrismaClient({datasources:{db:{url: process.env.DATABASE_URL}}});// In Prisma, use $extends to route specific queries to replica
-- Create a materialised view of monthly revenueCREATE MATERIALIZED VIEW monthly_revenue ASSELECT date_trunc('month', created_at)ASmonth,SUM(amount)AS revenue,COUNT(*)AS order_count
FROM orders
WHEREstatus='completed'GROUPBY1ORDERBY1;-- Index it for fast lookupsCREATEINDEX idx_monthly_revenue_month ON monthly_revenue (month);-- Query it — instant, regardless of how large the orders table isSELECT*FROM monthly_revenue WHEREmonth>='2024-01-01';-- Refresh when underlying data changesREFRESH MATERIALIZED VIEW monthly_revenue;-- Refresh without blocking reads (requires a UNIQUE index on the view)CREATEUNIQUEINDEX idx_monthly_revenue_month_uniq ON monthly_revenue (month);REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
-- Using pg_cron to refresh hourlySELECT cron.schedule('refresh-monthly-revenue','0 * * * *','REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue');
asyncfunctiongetUserProfile(userId){const cacheKey =`user:profile:${userId}`;// 1. Check cacheconst cached =await redis.get(cacheKey);if(cached)returnJSON.parse(cached);// 2. Miss: query PostgreSQLconst user =await db.query('SELECT id, name, email, avatar_url FROM users WHERE id = $1',[userId]);// 3. Store in cache with TTLawait redis.setex(cacheKey,300,JSON.stringify(user));// 5 min TTLreturn user;}
asyncfunctionupdateUserProfile(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 DBawait redis.del(`user:profile:${userId}`);}
-- 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'
-- Kill any query that takes longer than 30 secondsALTER ROLE app_service SET statement_timeout ='30s';-- Or per-sessionSET statement_timeout ='30s';-- For migrations (need more time):SET statement_timeout ='0';-- no timeoutALTERTABLE large_table ADDCOLUMN new_col TEXT;