#postgresql

Data normalization in PostgreSQL, written with Timescale. Normal forms, when to denormalize instead, and how TimescaleDB changes the tradeoffs.

As developers, querying in PostgreSQL for hierarchical data is difficult. SQL is a declarative programming language, but our brains are trained to think imperatively. Recursive queries using Common Ta…

You may have heard that 'data is the new oil.' By itself, data is unrefined and not valuable, but given processing and refinement, it becomes precious. We gain insights into our products, applications…

Foreign keys in PostgreSQL are a fundamental way of defining relationships between tables. Splitting data across multiple tables is foundational to relational databases and data normalization. However…

How to read PostgreSQL EXPLAIN output: the plan, operations, row counts, and timing, and using it to figure out why a query is slow.

A PostgreSQL security guide written with Timescale. What to lock down, why it matters, and the ways attackers get in when you don't.

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 temporary tables but with their own superpowers.

Tablespaces are a PostgreSQL feature that gets easily overlooked but can provide substantial speed improvements and cost-saving potential. As our database grows, tablespaces become a powerful tool in…

How we design and lay out our tables is one of the most important decisions in developing effective and efficient PostgreSQL databases. A database schema is the architecture or structure of the data i…

Fast time-series graphs in PostgreSQL, written with Timescale. Using materialized views and continuous aggregates for real-time analytics.

Using PostgreSQL's generate_series function to spin up mock data: integer sequences, date ranges, and time-series data for testing and demos.

Tackling table bloat on large PostgreSQL tables, written with Timescale. Spotting bloat, tuning VACUUM and AutoVacuum, and partitioning with chunks.

Shrinking a PostgreSQL database, written with Timescale. Pages and tuples, how bloat accumulates, routine vacuums, compression, and retention policies.

You don't need Neo4j for graph data. Walking through nodes, edges, and recursive traversals in plain Postgres, with working SQL.