Learn practical SQL & PostgreSQL techniques. Build rock-solid data systems with 'boring' database solutions that deliver reliability without the drama.
Google launched AlloyDB in 2022. They claimed it is fully compatible with PostgreSQL. Can be up to 100 times faster for analytical queries than vanilla Postgres. Four years later, I haven't personally seen it gain significant traction. But it comes in discussions. When people ask me what AlloyDB actually is, I was able to pin point the features, but wasn't really sure what it delivers.…
Here is a query that shows up in every analytics workload: SELECT count ( DISTINCT user_id) FROM events; It looks like the cheapest possible thing: count the distinct users. On a machine with cores to spare you would expect Postgres to throw a few parallel workers at it and be done. It does not. That one keyword, DISTINCT , switches off parallel query for the entire statement, and the larger your…
The first thing you will probably learn about Postgres, if you follow people who don't like Postgres, is that MVCC is bad. The 40-year-old design mistake. It's signatures are everywhere. Bloated tables that double in size, 32-bit transaction counter limit, the never ending struggle with VACCUM, dead tuples nightmares. It comes with credentials, too: Uber measured the write amplification…
TL;DR - RegreSQL 1.0 tested that your queries return the right rows. 2.0 tests that they return them the right way, and it does the checking against production's real statistics instead of your empty dev database, which lies. A migration cleanup dropped an index nobody thought was that important. Every test passed: same rows, same order, green. Three days later the API started timing out on a…
In HOT Updates in Postgres we covered page pruning clean up HOT chains, an elegant shortcut where PostgreSQL reclaims dead tuple space during ordinary reads. All that without waiting for any background process. But pruning is exactly that: a shortcut. It only works within a single page, and only for HOT-updated tuples. For everything else (cold updates that touch indexed columns, plain DELETEs,…
Everyone knows not to store money as a double precision . One can hope. The rule is so well drilled that it has stopped being interesting, and it is also not where the trouble usually starts. The float is already in the schema before anyone weighs in on it: a measurement column someone later sums for a report, telemetry that drifts into a finance dashboard, a third-party feed ingested as double…
A NOT IN query can return the wrong answer without telling you. It is valid SQL, it runs without an error, and it hands back a perfectly well-formed result set that happens to be empty when it should not be. No warning, no hint, nothing in the logs: just zero rows where you expected hundreds, and a database that considers it correct. Almost always the cause is a single NULL sitting somewhere you…
Part one made the core case: pg_stat_statements counts, it doesn't record. It walked through how the queryid jumble fragments one logical query into many rows, how the first-seen text freezes your per-request tags, and how the averages bury the p99 that actually pages you. All of that was about data the extension has and distorts. This part is about the rest: the entries it silently throws…
If not first, pg_stat_statements is one of the most used extensions in the PostgreSQL ecosystem. It ships in contrib and costs almost nothing to use. Most of us turn to it to answer the question: what is the database actually doing? It's genuinely useful. You can use it to get a snapshot of what happened in a given timeframe, and make a faster decision about what to fix. Coming from other…
In earlier posts in this series we established that every heap tuple lives inside a strict 8KB page . Everything else is built on top of that hard limit: MVCC , HOT updates , and indexes that point at (page, line_pointer) . And yet this still works: CREATE TABLE docs (id int PRIMARY KEY , body jsonb); INSERT INTO docs VALUES ( 1 , ( SELECT jsonb_agg(g) FROM generate_series ( 1 , 100000 ) g)); That…
SQL is fun and not at all boring. The latest article by Markus Winand on Order by Has Come a Long Way sent me on quite a journey. First, set up a table called nums with one integer column and four rows: CREATE TABLE nums (a int ); INSERT INTO nums VALUES ( 0 ), ( 1 ), ( 2 ), ( 3 ); Try to guess what these two queries return. SELECT - a AS a FROM nums ORDER BY a; SELECT - a AS a FROM nums ORDER BY…
VIEWs should be the cleanest abstraction SQL, and therefore Postgres, has on offer. I love the concept. The promise of decoupling logical intent from physical storage is perfect on paper. In practice, few things in the database world trigger such a heated debate or carry as much historical baggage. VIEWs mix big promises with false hopes, and the promises rarely survive contact with production.…
In the previous article we watched every UPDATE leave dead tuple behind. The same copy-on-write behaviour shows up from the operational angle in DELETEs are difficult . That's the tradeoff of MVCC and on the heap alone it's tolerable. The problem is the indexes. Every UPDATE in PostgreSQL potentially writes to every index on the table, even when the indexed columns didn't change.…
You run SELECT * FROM orders in one psql session and see 50 million rows. A colleague in another session runs the same query at the same moment and sees 49,999,999. Neither of you is wrong, and neither is seeing stale data. You are both reading the same 8KB heap pages, the same bytes on disk. This is the promise of PostgreSQL's MVCC (Multi-Version Concurrency Control), and it's the…
Not so long ago, the biggest threat to production databases was the developer who claimed it worked on their machine. If you've attended my sessions, you know this is a topic I'm particularly sensitive to. These days, AI agents are writing your SQL. The models are getting incredibly good at producing plausible code. It looks right, it feels right, and often it passes a cursory glance.…
The Common Table Expression , or CTE, is often the first feature developers reach for beyond basic SQL, and often the only one. You write a subquery after WITH , give it a name, and use it in the rest of your query. It only exists for the duration of that query. But the popularity of CTEs usually has less to do with modernizing code and more to do with the promise of imperative logic. For many,…
The previous article showed that PostgreSQL 18 makes optimizer statistics portable, but left one gap open: It's not worth trying to inject relpages as the planner checks the actual file size and scales it proportionally. The planner doesn't trust pg_class.relpages . It calls smgrnblocks() to read the actual number of 8KB pages from disk. Your table is 74 pages on disk but…
In the previous article we covered how the PostgreSQL planner reads pg_class and pg_statistic to estimate row counts, choose join strategies, and decide whether an index scan is worth it. The message was clear: when statistics are wrong, everything else goes with it. Streaming replication provides bit-to-bit replication, so all replicas share the same statistics with primary server. But there was…
Every query starts with a plan, and a slow query is usually the result of a bad one. More often than not, stale statistics are to blame. But how does it really work? PostgreSQL doesn't run the query to find out - it estimates the cost. It reads pre-computed data from pg_class and pg_statistic and does the maths to figure out the cheapest path to your data. In ideal scenario, the numbers read…
If you read previous post about buffers , you already know PostgreSQL might not necessarily care about your rows. You might be inserting a user profile, or retrieving payment details, but all that Postgres works with are blocks of data. 8KB blocks, to be precise. You want to retrieve one tiny row? PostgreSQL hauls an entire 8,192-byte page off the disk just to give it to you. You update a single…
In the article about Buffers in PostgreSQL we kept adding EXPLAIN (ANALYZE, BUFFERS) to every query without giving much thought to the output. Time to fix that. PostgreSQL breaks down buffer usage for each plan node, and once you learn to read those numbers, you'll know exactly where your query spent time waiting for I/O - and where it didn't have to. That's about as…
The work around RegreSQL led me to focus a lot on buffers . If you are a casual PostgreSQL user, you have probably heard about adjusting shared_buffers and followed the good old advice to set it to 1/4 of available RAM. But after we went a little bit too enthusiastic about them on a recent Postgres FM episode I've been asked what that's all about. Buffers are one of those topics…
Starting with arrays in PostgreSQL is as simple as declaring a column as integer[] , inserting some values, and you are done. Or building the array on the fly. SELECT '{1,2,3}' :: int []; SELECT array [1,2,3]; int4 --------- {1,2,3} (1 row) array --------- {1,2,3} (1 row) The official documentation provides a good introduction. But beneath this straightforward interface lies a set of more complex…
Have you ever watched a long running migration script , wondering if it's about to wreck your data? Or wish you can "just" spin a fresh copy of database for each test run? Or wanted to have reproducible snapshots to reset between runs of your test suite, (and yes, because you are reading boringSQL) needed to reset the learning environment? When your database is a few megabytes, pg_dump and…
There is common misconception that troubles most developers using PostgreSQL: tune VACUUM or run VACUUM, and your database will stay healthy. Dead tuples will get cleaned up. Transaction IDs recycled. Space reclaimed. No further action needed. But there are couple of dirty "secrets" people are not aware of. First of them being VACUUM is lying to you about your indexes . The anatomy of storage When…
TL;DR - RegreSQL brings PostgreSQL's regression testing methodology to your application queries, catching both correctness bugs and performance regressions before production. As puzzling as it might seem, the common problem with production changes is the ever-present "AHA" moment when things start slowing down or crashing straight away. Testing isn't easy as it is, but there's a…
One of the most read articles at boringSQL is Time to Better Know The Time in PostgreSQL where we dived into the complexities of storing and handling time operations in PostgreSQL. While the article introduced the range data types, there's so much more to them. And not only for handling time ranges. In this article we will cover why to consider range types and how to work with them. Bug Not…
How many people/services have superuser access to your PostgreSQL cluster(s)? Did you ever ask why your software engineers might need it? Or your BI team? Why those use cases require same privileges as someone who can drop your databases? PostgreSQL historically offered limited options for operational access, and not enough people are aware of the options that do exist. So the common practice…
With First Steps with Logical Replication we set up a basic working replication between a publisher and a subscriber and were introduced to the fundamental concepts. This part covers the operational side: initial data copies, monitoring, evolving publications, and how logical decoding actually works. Initial Data Copy As we demonstrated in the first part, when setting up the subscriber, you can…
PostgreSQL's logical replication streams row-level changes from one PostgreSQL instance to another using a publish-subscribe model. Unlike physical replication, which copies the whole cluster byte for byte, it lets you pick what to replicate and where to send it, which makes it a building block for scaling out, distributing load, and integrating PostgreSQL with the rest of your architecture.…
There are so many ways to connect to PostgreSQL. One of my favourite, yet underutilised is using service definition, the feature of any application using the libpq library. In this article we will explore what service definition is, where and how it can make your life with PostgreSQL much easier. PostgreSQL connection methods PostgreSQL offers several methods to establish connections to databases,…
To honor the name of the site (boringSQL) let's deep dive into a topic which might sound obvious, but it might be never ending source of surprises and misunderstanding. Simple things We can start with the simple statement like SELECT '2025-03-30 00:30' as t; Result: t ------------------ 2025-03-30 00:30 which gives you (and I do hope that's not a big surprise) a simple text literal,…
Database VIEWs are powerful tools that often don't get the attention they deserve when building database-driven applications. They make our database work easier in several ways: They let us reuse common query patterns instead of writing them over and over They give us a place to define business rules once and use them everywhere They help us write cleaner, more organized queries Let's…
Your database is ticking along nicely - until a simple DELETE brings it to its knees. What went wrong? While we tend to focus on optimizing SELECT and INSERT operations, we often overlook the hidden complexities of DELETE. Yet, removing unnecessary data is just as critical. Outdated or irrelevant data can bloat your database, degrade performance, and make maintenance a nightmare. Worse, retaining…
Whether you are designing a standalone application or a microservice, you will inevitably encounter the topic of sharing identifiers. Whether it’s URLs of web pages, RESTful API resources, JSON documents, CSV exports, or something else, the identifier of specific resources will be exposed. /orders/123 /products/345/variants/1 While an identifier is just a number and…
Designing a database schema, whether for a new application or a new feature, always raises a lot of questions. The choices you make can have a big impact on how well your database performs and how easy it is to maintain and scale. Whether you’re just getting started with PostgreSQL or consider yourself a seasoned pro, it’s easy to rely on old habits or outdated advice. In this article, I want to…
Understanding how comfortable someone is with databases and SQL often comes down to the features they use. In PostgreSQL, one such feature that distinguishes more advanced users is the MERGE command, introduced in version 15 and expanded in version 17 (in beta at the time of writing this article). Before MERGE , developers typically relied on INSERT ... ON CONFLICT DO UPDATE for upserts—a method…
Understanding the relationship between data points is crucial. For instance, you might need to identify the most recent orders for each customer or track changes in sensor readings over time. Unlike aggregate functions, which summarise data into a single row, it is window functions that allow you to analyse data while preserving each row’s details. This is the core of the logic, but don’t worry if…
Working with PostgreSQL, and virtually any database system, extends far beyond merely inserting and retrieving data. Many application and business processes, maintenance tasks, reporting, and orchestration tasks require the integration of a job scheduler. While third-party tools can drive automation, you can also automate the execution of predefined tasks directly within the database environment.…
This is the third and final instalment of "Deep Dive into PostgREST". In the first part , we explored basic CRUD functionalities. In the second part , we moved forward with abstraction and used the acquired knowledge to create a simple request/approval workflow. In Part 3, we will explore authentication and authorisation options to finish something that might resemble a real-world…
This article explores the pgrx framework, which simplifies the creation of custom PostgreSQL extensions to bring more logic closer to your database. Traditionally, writing such extensions required familiarity with C and a deep understanding of PostgreSQL internals, which could be quite challenging. pgrx lowers the barrier and allows developers to use Rust, known for its safety and performance,…
Let's recap the first part of "Deep Dive into PostgREST," where we explored the basic functionality to expose and query any table using an API, demonstrated using cURL . All it took was to set up a db-schema and give the db-anon-role some permissions. But unless you are creating the simplest of CRUD applications, this only scratches the surface. In Part 2, we will expand APIs, provide better…
The primary motivation behind boringSQL is to explore the robust world of SQL and the PostgreSQL ecosystem, demonstrating how these "boring" tools can cut through the ever-increasing noise and complexity of modern software development. In this series, I'll guide you through building a simple yet fully functional application—a Time Off Manager. The goal of this project is not only to…
One of the surprises that comes with developing applications and operating a database cluster behind them is the discrepancy between practice and theory, development environment and the production. A perfect example of such a mismatch is changing a column type. The conventional knowledge on how to change a column type in PostgreSQL (and other systems compliant with the SQL standard) is to: ALTER…
As the database size increases and the number of transactions per second rise, you'll inevitably face the challenge of the table bloat. Although PostgreSQL assists as much as possible with its auto-vacuum feature , there will come a time when you will compel whether to run VACUUM FULL . Unless you have option of longish downtime windows, this is not an easy decision. Thankfully, the rich…
Twenty years ago, I would have laughed if you had told me I'd be promoting databases as the technology to turn to. Back then, databases were just a 'dummy' storage for me—a necessary evil, a sentiment shared by many developers. At that point, I felt so strongly about it that I was a trainer for Hibernate, a tool which helped me keep those pesky databases at arm's length. Fast…
While it's true the most problems can be solved by the appropriate use of the index, there are cases where you will just waste resources doing so. For casual developer it might seems like PostgreSQL decided to do its own thing, but when you look behind the scenes it all makes perfect sense. Here's a quick run down of the some reasons why planner might pass on index and rather do things…