peopleanalyst

library / lib6609f4bf54d708c4

Just Use Postgres

Denis Magda · 2025

In a sentence

A hands-on developer guide that reveals the full breadth and depth of PostgreSQL's capabilities—from classic relational features to JSON, full-text search, generative AI, time series, geospatial, and message queuing—arguing that one database is often all you need.

Most developers know Postgres as a reliable relational database, but few realize it can also store and query JSON documents, perform full-text search, power generative AI with vector embeddings, handle time-series data, manage geospatial queries, and even function as a message queue. 'Just Use Postgres!' by Denis Magda is a practical, chapter-by-chapter tour through these capabilities, each grounded in a realistic application scenario (a music streaming service, a pizza chain, a multiplayer game, a smartwatch app, and more). Through runnable Docker-based examples and clear SQL, the book shows application developers exactly how to extend a single Postgres instance—often with an extension—rather than adding a second or third specialized database to their stack. It covers modern SQL features like CTEs and window functions, the full range of index types, the pgvector extension for RAG and similarity search, TimescaleDB for time-series hypertables, PostGIS for geodata, and pgmq for lightweight message queues, closing with honest guidance on when Postgres is not the right tool.

The four lenses

  • Science
  • Statistics
  • Systems
  • Strategy

Tags

f1-systems

The model

A causal model describing how developer knowledge of Postgres capabilities, deliberate selection of extensions, and correct application of indexes and SQL features combine to produce application performance, operational simplicity, and developer productivity outcomes.

Developer Knowledge of Postgres Capabilitiesdesign lever

The degree to which a developer understands the full range of Postgres features—including relational fundamentals, modern SQL, JSON, full-text search, extensions, indexing, and ecosystem tools—beyond basic CRUD operations.

Deliberate Extension Selectiondesign lever

The practice of systematically evaluating the Postgres extension ecosystem before adopting a specialized external database, choosing extensions (e.g., pgvector, TimescaleDB, PostGIS, pgmq) that match the application's non-relational requirements.

Modern SQL Adoptiondesign lever

The extent to which developers use SQL features introduced after the SQL-92 standard—including common table expressions, recursive queries, window functions, JSON operators, and path expressions—rather than relying solely on classic SQL-92 constructs.

Index Strategy Qualitydesign lever

The appropriateness and deliberateness of the indexing decisions made for a database schema, encompassing the correct choice of index type (B-tree, hash, GIN, GiST, BRIN, HNSW, IVFFlat), scope (single-column, composite, partial, covering, functional), and avoidance of both over-indexing and under-indexing.

Data Modeling Balance (Relational vs. JSON)design lever

The degree to which application data is modeled using an appropriate hybrid of normalized relational structures and flexible JSON columns, rather than defaulting entirely to either extreme—pure normalization or pure document storage.

Database-Level Security Designdesign lever

The extent to which database roles, least-privilege grants, and connection restrictions are explicitly defined and enforced in Postgres, complementing application-layer authentication and authorization.

Table Partitioning Strategydesign lever

The design and implementation of table partitioning (range, list, or hash) to isolate recent from historical data, enabling efficient queries over large time-series or high-volume datasets without full table scans.

Database Stack Complexitycontextual condition

The number and heterogeneity of distinct database and data platform systems operated by a team to serve a single application, where higher complexity implies more systems to learn, monitor, secure, backup, and keep consistent.

Query Performanceoutcome metric

The speed and efficiency with which Postgres executes application queries, reflected in actual execution time and the access methods chosen (Index Scan, Index Only Scan, Bitmap Index Scan, Seq Scan) as reported by EXPLAIN ANALYZE.

Data Integrityoutcome metric

The accuracy, consistency, and completeness of data stored in Postgres, maintained through constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY, EXCLUSION), transactions, and MVCC guarantees.

Search Relevanceoutcome metric

The quality and accuracy of search results returned by Postgres full-text search or vector similarity search, reflecting how well retrieved items match user intent, typically measured by recall, precision, or ranking quality (ts_rank scores or ANN recall).

Developer Productivityoutcome metric

The rate at which developers can design, implement, debug, and ship database-backed application features, influenced by the clarity of SQL queries, the simplicity of the database stack, and the availability of built-in Postgres capabilities that reduce custom application-layer code.

Operational Simplicityoutcome metric

The ease with which a team can deploy, monitor, back up, scale, and maintain the database infrastructure, which increases as the number of distinct database systems decreases and as managed Postgres services or well-understood extensions replace specialized platforms.

Application Correctnessoutcome metric

The degree to which application behavior is free from data-related bugs such as dirty reads, constraint violations, dangling foreign keys, and race conditions, enabled by correct use of Postgres transactions, isolation levels, and constraints.

How they connect

  • postgres capability knowledge → predicts extension selection
  • postgres capability knowledge → predicts modern sql adoption
  • postgres capability knowledge → predicts index strategy quality
  • postgres capability knowledge → predicts data modeling balance
  • extension selection − influences stack complexity
  • stack complexity − influences operational simplicity
  • stack complexity − influences developer productivity
  • modern sql adoption → influences query performance
  • modern sql adoption → influences developer productivity
  • index strategy quality → predicts query performance
  • index strategy quality → influences search relevance
  • data modeling balance → influences query performance
  • security design → influences application correctness
  • partitioning strategy → predicts query performance
  • data integrity → predicts application correctness
  • query performance → influences developer productivity
  • search relevance → influences developer productivity
  • operational simplicity → influences developer productivity

The story

The reader An application developer, software engineer, or architect who already uses (or is evaluating) Postgres and wants to understand how far it can stretch—so they can build modern applications without multiplying their database stack.

External problem

The developer needs to support use cases—JSON documents, full-text search, AI/vector search, time series, geospatial, message queuing—that seem to require specialized databases beyond the Postgres instance already running in production.

Internal problem

They feel uncertain and overwhelmed: uncertain about Postgres's real capabilities, and overwhelmed by the idea of learning, operating, and paying for multiple database systems.

Philosophical problem

It is unjust that developers are pushed toward complexity by incomplete knowledge of a tool they already own and trust.

The plan

  1. Start Postgres in Docker in under a minute and connect with psql.
  2. Master standard RDBMS capabilities: schemas, tables, transactions, constraints, joins, functions, triggers, views, and roles.
  3. Adopt modern SQL (CTEs, recursive queries, window functions) to write cleaner, more powerful queries.
  4. Learn to read execution plans with EXPLAIN ANALYZE and choose the right index type for each query pattern.
  5. Use jsonb operators, JSON path expressions, and GIN indexes to handle document workloads inside Postgres.
  6. Implement full-text search with tsvector/tsquery, ranking, highlighting, and GIN/GiST indexes.
  7. Install extensions via Docker images and explore the broader ecosystem (pgcrypto, pg_cron, foreign data wrappers, etc.).
  8. Add pgvector to generate, store, and similarity-search vector embeddings; build a RAG pipeline with an LLM.
  9. Use TimescaleDB hypertables, time_bucket, gap-filling, and continuous aggregates for time-series data.
  10. Query points, polygons, and line segments with PostGIS and optimize with spatial GiST indexes.
  11. Implement message queues with LISTEN/NOTIFY, table-based queues, or pgmq.
  12. Apply the five optimization tips from Appendix A; consult Appendix B for honest 'when not to use Postgres' guidance.

Success

  • The developer confidently evaluates Postgres extensions before adopting a new database, reducing operational complexity.
  • Applications handle JSON, search, AI, time-series, and geospatial requirements within a single Postgres instance.
  • Query performance is predictable and measurable because the developer knows how to read execution plans and choose indexes.
  • The team ships features faster because they leverage one deeply understood, battle-tested platform rather than juggling multiple systems.
  • The developer can implement RAG, vector similarity search, and AI-powered features without a separate vector database.

At stake

  • The team adds a document database, a search engine, a vector store, and a time-series database—each requiring separate expertise, monitoring, backups, and licensing costs.
  • Query performance degrades as data grows because indexes were never analyzed or optimized.
  • Sensitive data is exposed because security was only enforced at the application layer, not at the database role level.
  • Complex multi-database architectures introduce consistency bugs, increased latency, and operational toil that slow the entire team down.

Related in the library