The Postgres guide
15 articles.
Fundamentals
The building blocks: schemas, keys, normal forms, and the query patterns you will reach for every week.
How to Design Your PostgreSQL Database: Two Schema Examples
How we design and lay out our tables is one of the most important decisions in developing effective and efficient PostgreSQL databases. A da…
Understanding Foreign Keys in PostgreSQL
Foreign keys in PostgreSQL are a fundamental way of defining relationships between tables. Splitting data across multiple tables is foundati…
How to Use PostgreSQL for Data Normalization
Data normalization in PostgreSQL, written with Timescale. Normal forms, when to denormalize instead, and how TimescaleDB changes the tradeof…
How to Use a Common Table Expression (CTE) in SQL
Common Table Expressions (CTEs) are my go-to tool when writing complex SQL queries. They can be thought of as working like sub-queries or te…
Recursive Query in SQL: What It Is, and How to Write One
As developers, querying in PostgreSQL for hierarchical data is difficult. SQL is a declarative programming language, but our brains are trai…
Understanding SQL Aggregate Functions
You may have heard that 'data is the new oil.' By itself, data is unrefined and not valuable, but given processing and refinement, it become…
Performance
Reading plans, taming bloat, and cutting disk usage when the table stops fitting in your head.
Explaining PostgreSQL EXPLAIN
How to read PostgreSQL EXPLAIN output: the plan, operations, row counts, and timing, and using it to figure out why a query is slow.
Wrangling Table Bloat in Large PostgreSQL Tables
Tackling table bloat on large PostgreSQL tables, written with Timescale. Spotting bloat, tuning VACUUM and AutoVacuum, and partitioning with…
How to Reduce Your PostgreSQL Database Size
Shrinking a PostgreSQL database, written with Timescale. Pages and tuples, how bloat accumulates, routine vacuums, compression, and retentio…
Understanding PostgreSQL Tablespaces
Tablespaces are a PostgreSQL feature that gets easily overlooked but can provide substantial speed improvements and cost-saving potential. A…
Creating a Fast Time-Series Graph With Postgres Materialized Views
Fast time-series graphs in PostgreSQL, written with Timescale. Using materialized views and continuous aggregates for real-time analytics.
Data patterns
Shapes that do not look relational at first: graphs, adjacency structures, and quick mock data.
Postgres: The Graph Database You Didn't Know You Had
You don't need Neo4j for graph data. Walking through nodes, edges, and recursive traversals in plain Postgres, with working SQL.
Unraveling Graph Structures: Exploring Adjacency Matrices
How adjacency lists and adjacency matrices represent graphs, with tradeoffs and SQL examples for storing a DAG inside a relational database.
Easily Generate Mock Data with PostgreSQL
Using PostgreSQL's generate_series function to spin up mock data: integer sequences, date ranges, and time-series data for testing and demos…
Production
What to lock down before the database faces real users and real attackers.