Common Use Cases

The default backend database

Every major web framework — Rails, Django, Phoenix, Next.js with Prisma, Spring Boot — defaults to PostgreSQL. User tables, sessions, products, posts, comments, payments: PostgreSQL handles everything a typical SaaS needs without breaking a sweat. If you can't immediately name a reason to choose something else, choose Postgres.

Where it shines especially well

Geospatial — PostGIS turns Postgres into a first-class GIS database. Map services, delivery routing, location-based search.
Analytics-on-OLTP — window functions, materialised views, and CTEs let one Postgres serve mid-scale BI without copying to a warehouse.
AI embeddings — pgvector keeps embeddings next to the entities they describe; you don't need a separate vector store for under ~10M embeddings.
Time-series — TimescaleDB extends Postgres with hypertables, compression, and continuous aggregates.
Message queues, audit logs, feature flags — frequently good enough as plain Postgres tables with the right indexes.

Where you graduate to specialised stores

Petabyte analytics → columnar warehouse (Snowflake, BigQuery, ClickHouse). Sub-millisecond cache → Redis. Massive write fan-out (millions of writes/sec) → Cassandra/ScyllaDB. Pure event log → Kafka. The rule: graduate when you have measured pressure, not when you suspect future pressure.

Code

Typical SaaS query·sql

-- Active users this month — every SaaS dashboard has this exact shape.
SELECT COUNT(DISTINCT user_id)
FROM   sessions
WHERE  created_at >= DATE_TRUNC('month', CURRENT_DATE);

Geospatial — coffee shops within 500m·sql

SELECT name, address
FROM   shops
WHERE  ST_DWithin(
         location,
         ST_MakePoint(-122.42, 37.77)::geography,
         500
       );

Embedding similarity with pgvector·sql

SELECT id, title
FROM   documents
ORDER  BY embedding <=> '[0.12, -0.04, ...]'::vector
LIMIT  5;

The default backend database

Where it shines especially well

Where you graduate to specialised stores

Code

External links

Exercise

Progress

Comments 0