Posts in Postgresql (11 found)

Building Barista—Part 2: Formal Verification and TLA+

☕ Welcome to The Coder Cafe! Part 1 of Building Barista covered the architecture. Today, we explore formal verification through a real bug my tests missed. You’ll learn what model checking is, how to read a bit of TLA+, and why it catches the bugs we never think about. I spent a lot of time on formal verification at Google; this time, I pointed it at my own system. Get cozy, grab a coffee, and let’s begin! One Email a Day A quick summary of Barista : it’s a system I built that fetches hundreds of sources, gets a unique prompt per user, and sends that user one email per day with the best sources for them. Making sure Barista sends a single email per user per day is something important to me. If Alice receives the same email twice on a random day, that’s a terrible user experience. As discussed in Part 1 , Barista is a set of independent stages coordinating through Postgres. The last one, , runs every 5 minutes, looks for the users whose send time has passed, and emails each of them the posts picked by the stage. The nominal scenario: The stage starts and notices an email has to be sent to Alice. Barista calls the email provider and receives an ACK. ✅ Barista writes back in Postgres that the email to Alice was sent that day. Now the worst-case scenario: The stage starts and notices an email has to be sent to Alice. Barista calls the email provider and receives an ACK. ❌ Before writing back in Postgres that the email was sent, Barista crashes. The stage starts again and notices an email has to be sent to Alice. Barista calls the email provider again and receives an ACK. ← Duplicate email ✅ Barista writes back in Postgres that the email to Alice was sent that day. How do you prevent that? I picked my email provider for it: Plunk. Why Plunk? Because it accepts an idempotency key. When calling Plunk, I pass a key like : the user and the day. If Plunk receives the same key again, it doesn’t send anything; it replies with a 409. My code reads that 409 as “these posts were already sent” and marks them as sent. No duplicate. It was a conscious decision. Plunk is 10x more expensive per email than AWS SES, which has no idempotency key. I was happy to pay for it. So here was the state: A business decision: no duplicates, even if it costs more. A sound design, with an email provider deduplicating for me. Unit tests, integration tests, end-to-end tests. All green. Great mental model of the code. Pure intellectual control . Now, what if I told you my code had a bug? One that none of my tests caught. I only managed to find it with something called formal verification. Let’s see how. Formal Verification Before becoming an SRE, I worked in Air Traffic Management (I talked about it in Reliability Explained ). It’s a so-called safety-critical domain. You can easily imagine why. In a safety-critical system, green tests aren’t enough. That’s the blind spot of intellectual control: tests only check the scenarios we thought about. And the bugs that hurt are, by definition, the ones we didn’t think about. In domains like this, green tests aren’t the end of the conversation. Some teams go further and prove that certain bugs can’t happen. Airbus, for example, used a static analyzer called Astrée to prove that the flight control software of the A340 and A380 is free of a whole class of runtime errors. So instead of picking scenarios one by one, what if we could check all of them? That’s the idea behind formal verification: describe the system precisely, write down the properties it must respect, and let a tool check that no possible execution breaks them. Note the word: verification . It answers “did we build the thing right?”, meaning does the design respect our properties. Not to be confused with validation : “did we build the right thing?”, meaning are these the properties we actually need. A tool can do the first. The second stays on us. There are several ways to verify a system, from mathematical proofs to running real code on generated inputs. The one I used is model checking . A model checker sees a system as states (a value for every variable) and actions (a step from one state to the next). It starts from the initial state, tries every possible action, then every possible action from each new state, and so on, until it has visited every reachable state. A test follows one path. A model checker walks all of them. That’s why it finds bugs nobody writes a test for. AWS described it in 2015, in a paper on how they use formal methods: one of their DynamoDB bugs had survived design reviews, code reviews, and testing. The shortest sequence of events to trigger it was 35 steps long. At each state, the model checker checks our properties. They come in two flavors: Safety : Something bad never happens (e.g., Alice never receives the same email twice) Liveness : Something good eventually happens (e.g., Alice eventually receives her daily email) When a property breaks, we get a counterexample : the exact sequence of steps that led there. The model checker I used is TLC. It reads models written in TLA+, a language created by Leslie Lamport to describe a system as states and actions. In other words: we describe the system in TLA+, and TLC explores every state it can reach. A TLA+ spec doesn’t look like code. There are no instructions executed one after the other. Instead, each action is a formula that says two things: when the step is allowed (a condition on the current state), and what the state looks like after it (the values in the next state). TLC then tries every allowed action, in every possible order. A few symbols are enough to follow the examples below: means “is defined as” is the value of in the next state and mean “for all” and “there exists” Let’s see it on a simple example. Imagine a cafe with one croissant left and two baristas. When a customer asks for a croissant, a barista does two things: first, they check the display case to see if there’s one left; then, they sell it. The property we want is a safety one (something bad never happens): the cafe never sells a croissant it doesn’t have. In other words, the stock never goes below zero. To model this cafe, we need two variables: : how many croissants are left. It starts at 1. : where each barista is. Each one starts , moves to after checking, and ends . Now, the two actions. The first one is checking the display case: The first two lines are conditions: can only happen if barista is idle and there’s a croissant left. The last two lines describe the next state: is now selling ( means “every other barista stays where they are”), and the stock stays the same. The second action is selling: And the property, written as an invariant that must hold in every state: That’s the whole model. TLC starts from the initial state (one croissant, two idle baristas) and tries every action, in every possible order. In most orders, everything is fine. For example, barista 1 checks and sells, then barista 2 can’t check anymore because the stock is zero. But TLC finds one order that breaks the invariant: Both baristas looked at the display case before either one sold. Both saw a croissant. Both sold it. That’s how to read a counterexample: find the step where it broke (5), then walk back to the decision that made it inevitable (3). The fix is to make checking and selling a single action, so nobody can slip in between. Re-run TLC, and it explores every state without finding a violation. In a cafe, we can merge two steps. Across a network call and a database write, we can’t, and that’s exactly why idempotency keys exist. Now, let’s do the same with Barista. Back to Barista. Years after leaving Air Traffic Management, I wondered: what if I applied the same rigor here? So I wrote a TLA+ spec of the stage. The model was tiny: one user, two posts, a clock with eight slots per day, and two days. The key idea is that it tracks two things separately: what was really delivered to the user, and what Barista believes was delivered. If Barista works, both always agree. The full spec also has a few actions I’m leaving out here: the clock ticks, Rank picks new posts, and moves the next send time forward. Here are the variables (simplified): : the clock. : when user ’s next email is due. : the posts picked for ’s next email. : the idempotency keys Plunk has already seen. : the list of emails really delivered. The truth. : the posts Barista has marked as sent to . What Barista believes. And a simplified version of the action at the time: A few new pieces: defines a local name (here, the key). is a pair, our . adds to a set, and adds to the end of a list. And is a record: an email to containing these posts. Read it line by line. can only run once the send time has passed. If Plunk already knows the key, it replies with a 409 and nothing is delivered. Otherwise, the email goes out. Either way, Plunk remembers the key, and Barista marks the posts as sent. That last line is the one to watch. The real action can also end badly: Plunk delivers the email, then Barista crashes before recording anything. TLC tries that outcome too. Now the property, in the same spirit as the cafe’s : In plain English: Barista’s belief must match the truth. If a post is marked as sent, an email really contained it. I ran TLC. Guess what? Despite all the tests, despite the idempotency key I was paying 10x for, it failed on the first run . Every configuration failed, even the simplest one: no crash, no settings change. Here’s one of the counterexamples, in real times. Alice gets her email at 22:00 UTC: An outage on May 20 eats Alice’s May 21 email. The catch-up send at 01:00 UTC uses key , so the regular send at 22:00 gets a 409, and its posts are marked as sent without being delivered. Alice never gets her May 21 email. Its posts are marked as sent, so Barista will never offer them again. No error, no alert. And it happens 21 hours after everything recovered. The fun part is that I never modeled an outage. Nothing in the spec says Barista can be down. But nothing forces to run on time either: in TLA+, the clock is just another action, and TLC is free to let it tick while waits. An outage, a slow deploy, a job stuck behind another one: for the model, it’s all the same. TLC tried every possible delay and found the one crossing midnight. So what went wrong? Plunk did exactly what it promised. A 409 doesn’t mean “these posts were sent.” It means “something was already sent under this key.” My key was the day of the attempt, not the day the email was for. As long as a send ran on the day it was due, it didn’t matter. The moment one slipped past midnight, it did. Should Barista skip the catch-up, then? No. Alice should still get her May 20 email. The catch-up was right; my reading of the 409 was wrong. Ironically, I was obsessed with sending too many emails, and the very thing I was paying 10x for made me send too few, silently. Would my tests have caught it? Honestly, no. I tested a crash after the ACK, and the idempotency key handled it. I would never have written a test where runs late, past midnight UTC, followed by a regular send the next evening. And that was exactly my fear in the first place. The fix? One key per email instead of one per day. I re-ran TLC. Green. In December 2025, Martin Kleppmann, the author of Designing Data-Intensive Applications , published a post with a bold prediction: AI will make formal verification go mainstream . His argument is mostly about cost. Formal verification has always been expensive. Proving that seL4, an OS kernel of about 8,700 lines of C, was correct took around 20 person-years. For most systems, a bug was cheaper than the proof that would have prevented it. But coding agents are getting good at writing proofs. And a wrong proof isn’t dangerous: the checker rejects it, and the agent tries again. One of the issues with TLA+ has always been making sure the spec is accurate. A TLA+ spec is a model, and a model is only as good as its abstraction. TLC checks the spec, not the code. The hard part is making sure the spec describes what the code actually does. But with the rise of coding agents, especially over the last few months, I feel like this gap is shrinking. An agent can read the code and draft the spec, keep both in sync when the code changes, and turn a counterexample into a failing test against the real code. At Google, I spent a lot of time exploring formal verification, especially on convoluted logic: loops with several paths, retries, error codes. On many occasions, I found bugs despite significant test coverage. Back then, though, writing and maintaining a spec was a real investment, so it was reserved for the parts that truly justified it. That’s what’s changing. When a spec costs an afternoon instead of weeks, the question is no longer “is this part critical enough to deserve a spec?” It becomes “why not?” Does it mean software will be bug-free? No. As Kleppmann also points out, the hard part moves to the specification itself: are we checking the properties we actually care about? That’s the validation question from earlier. But think about what it took to find Barista’s bug: a property of a few lines, and a tool patient enough to try every delay, every order, every midnight. Now extend that. Maybe that’s what software engineering becomes: less time on how the code achieves something, more time on what must hold no matter what. Alice never receives the same email twice. Alice eventually receives her daily email. We write those down; agents write the code and the spec; tools check that the properties hold. Kleppmann compares it to compilers: we don’t read the machine code they produce, and maybe one day, we won’t read the code agents produce either. Will we get there? I don’t know. But it might be worth exploring in your own system: write down its invariants, and see whether a model can show you where they break. If you want to see what this pipeline picks for you, give Barista a try. Describe what you like to read, and your first email lands in your inbox in seconds. It’s free (one post a day), and every new account gets 7 days of the paid plan to try more. Resources Sources How Amazon Web Services Uses Formal Methods, Newcombe et al., CACM, 2015 Systems Correctness Practices at AWS, Brooker and Desai, ACM Queue, 2024 The Astrée Static Analyzer Prediction: AI will make formal verification go mainstream How we continuously validate formal specifications in production - Datadog One Email a Day A quick summary of Barista : it’s a system I built that fetches hundreds of sources, gets a unique prompt per user, and sends that user one email per day with the best sources for them. Making sure Barista sends a single email per user per day is something important to me. If Alice receives the same email twice on a random day, that’s a terrible user experience. As discussed in Part 1 , Barista is a set of independent stages coordinating through Postgres. The last one, , runs every 5 minutes, looks for the users whose send time has passed, and emails each of them the posts picked by the stage. The nominal scenario: The stage starts and notices an email has to be sent to Alice. Barista calls the email provider and receives an ACK. ✅ Barista writes back in Postgres that the email to Alice was sent that day. The stage starts and notices an email has to be sent to Alice. Barista calls the email provider and receives an ACK. ❌ Before writing back in Postgres that the email was sent, Barista crashes. The stage starts again and notices an email has to be sent to Alice. Barista calls the email provider again and receives an ACK. ← Duplicate email ✅ Barista writes back in Postgres that the email to Alice was sent that day. A business decision: no duplicates, even if it costs more. A sound design, with an email provider deduplicating for me. Unit tests, integration tests, end-to-end tests. All green. Safety : Something bad never happens (e.g., Alice never receives the same email twice) Liveness : Something good eventually happens (e.g., Alice eventually receives her daily email) means “is defined as” is the value of in the next state and mean “for all” and “there exists” : how many croissants are left. It starts at 1. : where each barista is. Each one starts , moves to after checking, and ends . : the clock. : when user ’s next email is due. : the posts picked for ’s next email. : the idempotency keys Plunk has already seen. : the list of emails really delivered. The truth. : the posts Barista has marked as sent to . What Barista believes. An outage on May 20 eats Alice’s May 21 email. The catch-up send at 01:00 UTC uses key , so the regular send at 22:00 gets a 409, and its posts are marked as sent without being delivered. Alice never gets her May 21 email. Its posts are marked as sent, so Barista will never offer them again. No error, no alert. And it happens 21 hours after everything recovered. The fun part is that I never modeled an outage. Nothing in the spec says Barista can be down. But nothing forces to run on time either: in TLA+, the clock is just another action, and TLC is free to let it tick while waits. An outage, a slow deploy, a job stuck behind another one: for the model, it’s all the same. TLC tried every possible delay and found the one crossing midnight. So what went wrong? Plunk did exactly what it promised. A 409 doesn’t mean “these posts were sent.” It means “something was already sent under this key.” My key was the day of the attempt, not the day the email was for. As long as a send ran on the day it was due, it didn’t matter. The moment one slipped past midnight, it did. Should Barista skip the catch-up, then? No. Alice should still get her May 20 email. The catch-up was right; my reading of the 409 was wrong. Ironically, I was obsessed with sending too many emails, and the very thing I was paying 10x for made me send too few, silently. Would my tests have caught it? Honestly, no. I tested a crash after the ACK, and the idempotency key handled it. I would never have written a test where runs late, past midnight UTC, followed by a regular send the next evening. And that was exactly my fear in the first place. The fix? One key per email instead of one per day. I re-ran TLC. Green. The Future of Software Engineering? In December 2025, Martin Kleppmann, the author of Designing Data-Intensive Applications , published a post with a bold prediction: AI will make formal verification go mainstream . His argument is mostly about cost. Formal verification has always been expensive. Proving that seL4, an OS kernel of about 8,700 lines of C, was correct took around 20 person-years. For most systems, a bug was cheaper than the proof that would have prevented it. But coding agents are getting good at writing proofs. And a wrong proof isn’t dangerous: the checker rejects it, and the agent tries again. One of the issues with TLA+ has always been making sure the spec is accurate. A TLA+ spec is a model, and a model is only as good as its abstraction. TLC checks the spec, not the code. The hard part is making sure the spec describes what the code actually does. But with the rise of coding agents, especially over the last few months, I feel like this gap is shrinking. An agent can read the code and draft the spec, keep both in sync when the code changes, and turn a counterexample into a failing test against the real code. At Google, I spent a lot of time exploring formal verification, especially on convoluted logic: loops with several paths, retries, error codes. On many occasions, I found bugs despite significant test coverage. Back then, though, writing and maintaining a spec was a real investment, so it was reserved for the parts that truly justified it. That’s what’s changing. When a spec costs an afternoon instead of weeks, the question is no longer “is this part critical enough to deserve a spec?” It becomes “why not?” Does it mean software will be bug-free? No. As Kleppmann also points out, the hard part moves to the specification itself: are we checking the properties we actually care about? That’s the validation question from earlier. But think about what it took to find Barista’s bug: a property of a few lines, and a tool patient enough to try every delay, every order, every midnight. Now extend that. Maybe that’s what software engineering becomes: less time on how the code achieves something, more time on what must hold no matter what. Alice never receives the same email twice. Alice eventually receives her daily email. We write those down; agents write the code and the spec; tools check that the properties hold. Kleppmann compares it to compilers: we don’t read the machine code they produce, and maybe one day, we won’t read the code agents produce either. Will we get there? I don’t know. But it might be worth exploring in your own system: write down its invariants, and see whether a model can show you where they break. If you want to see what this pipeline picks for you, give Barista a try. Describe what you like to read, and your first email lands in your inbox in seconds. It’s free (one post a day), and every new account gets 7 days of the paid plan to try more. Resources Sources How Amazon Web Services Uses Formal Methods, Newcombe et al., CACM, 2015 Systems Correctness Practices at AWS, Brooker and Desai, ACM Queue, 2024 The Astrée Static Analyzer Prediction: AI will make formal verification go mainstream How we continuously validate formal specifications in production - Datadog

0 views
Marc Brooker 3 weeks ago

Animations for System Builders: Metastability and Lost Writes

Pretty pictures help us learn, maybe? One of the things I love using LLMs for is helping me understand systems better. In the Strands Decider post I wrote about how I often ask ask me questions to check I understand this . The other technique I’m enjoying right now, and am delighted to see folks around me using too, is building animations. Animations can help us build an intuition for how and why systems work, or how and why they fail. Here’s one example: did you know that a Raft cluster can lose writes if even one of the cluster members has a storage layer that lies about ? When I built Stability Sim , I wanted to help engineers understand metastable failures better. That worked pretty well on an abstract level (e.g. retries and queues make metastability really easy to trigger ), but I still hear doubts that these behaviors happen in real systems. After all, those real systems are production-proven, and wouldn’t we have shaken out the behaviors by now if they existed? Let’s start with Postgres. In this example, captured from traces on real Postgres 16 running in a real MicroVM. Before a load spike, the database is easily able to handle its 20 queries-per-second load, thanks to the whole working set sitting in the page cache. But after the load spike, we’ve filled up a lot of the memory running queries (each takes 12MB), which now run slower because they’re served off the disk rather than the page cache. I like this example, because it shows that even simple, non-distributed systems have these behaviors. What about ? Again, we have load spike triggering the metastable state. Writes, in this case. The overloaded peers look like they’re unavailable, which triggers a storm of application-level resends. I like this example, because is widely-used quality software. Operational experience doesn’t seem to effectively shake out these kinds of issues. When I wrote Garbage Collection and Metastability , a widely-known systems academic told me that it was a fantasy . I’ve seen it happen in the wild in ways I couldn’t share, and I can forgive people for not believing this can happen based on the hand waving in the blog. So it’s nice to have a real world example, picking on again. At the start, we’re ticking along nicely at 500 reads/second, before spiking load to 1000 reads/s. The read spike increases the time spent in GC, increasing average tenure, and making less CPU available for the read workload. This is a self-reinforcing loop, and the system doesn’t recover without intercession. I believe the same thing could happen on a runtime without GC, due to increasing work in the allocator, but haven’t been able to reproduce that yet. The animations here are all generated with Kiro and KiroCrew , mostly with Opus 5.5. The actual results came from a real test environment, which captures the traces that back the animations.

0 views
Andrew Atkinson 4 weeks ago

PostgreSQL in the 2026 Ruby on Rails Community Survey and Book Giveaways

84% of 1,800 surveyed Rails developers use PostgreSQL in production. While PostgreSQL is less popular than Oracle, MySQL, and Microsoft SQL Server today, the gap is shrinking. Win a signed copy of High Performance PostgreSQL for Rails, see below for details. The 2026 version of the Ruby on Rails Community Survey results are now live. Two things caught my eye: how dominant PostgreSQL use is and the learning resources developers found to be most valuable. Among relational SQL databases, 84% of respondents use PostgreSQL in production . That’s wild! The next one is MySQL at 23%. 84% of nearly 1,800 surveyed Ruby on Rails developers use PostgreSQL in production This reflects a multi-year trend not only in the Ruby on Rails community but also in the broader industry among relational databases. According to the DB-Engines Ranking Trend , Oracle, MySQL, and Microsoft SQL Server are all still more popular than PostgreSQL, but declining. In the graph below we can see the growing popularity of PostgreSQL and this trend line implies it may surpass Microsoft SQL Server. MySQL could be next. Popularity of Oracle, MySQL, Microsoft SQL Server and PostgreSQL per DB-Engines Books hold a smaller but steady percentage among learning resources. 37% of respondents in their first two years of learning felt reading whole books was most helpful, while 10% have read parts of a book and found that best. What worked best among respondents was building and deploying their own applications. This makes sense! That’s how it’s always been for me too, nothing beats building something yourself and becoming an owner and operator. Documentation, learning videos, podcasts, books, and blog posts all help support developers looking to improve their skills. I was happy to see High Performance PostgreSQL for Rails ranked #12 among recommended books . Thank you! The book has been published for 2 years now and isn’t being actively promoted. I’m grateful to occasionally hear from readers at events like Rails at Scale Summit that they’ve found the book helpful. Seeing that reflected in the survey results was very fulfilling. High Performance PostgreSQL for Rails ranked #12 among recommended books To celebrate the survey results and the book having been published for 2 years, I’d like to sign and give away a couple of copies. If you’re in the contiguous U.S. and would like to enter, please send me a message through my site in the next couple of weeks. That’s it. I’ll draw two winners at random and ship them their copies. Good luck! Please check out the full 2026 Ruby on Rails Community Survey Results .

0 views
Andrew Atkinson 2 months ago

PostgreSQL 18: 23x Faster Inserts With UUID v7

We recently switched to version 7 (v7) uuid primary keys and saw significantly faster inserts for some tables. The databases were running Postgres 18.4 and mostly used v1 with some v4 uuid values for primary keys. Changing the column default involved running a single alter table command, but did require an exclusive lock on the table, blocking everything including selects. To solve that, we used a short lock timeout and lots of retries. The biggest speedup was 23x faster average execution time for a multi-row insert query called 12,000 times per minute on a table with billions of rows. This September and October I'll be in Austin, TX and NYC, check my Book page for upcoming appearances. The system uses UUID primary keys throughout. I typically recommend starting with bigint and sequences over UUID v4 primary keys , although here uuid v1 was used. Insert performance is not as bad for v1 compared with v4. Still though, v7 brings better performance than both for inserts and can also result in smaller indexes with fewer page splits meaning less CPU and IO. What drives bad performance for v4 and to a lesser extent v1? Let’s do a quick refresher. As new table rows are inserted and a primary key is defined, primary key values are maintained in sorted order in a b-tree index. Just like table rows, index entries in Postgres are stored in fixed size 8kb pages. Postgres needs to know in which page to place the new index entry. For sorted order, the first bytes of new uuid values are compared. For v4 given new values are very random and not monotonically increasing (they lack “monotonicity”), values can be earlier or later, meaning they’re unlikely to be placed into the same recently accessed page. This is bad for caching! When new values are monotonically increasing, the recently accessed page is “hot” in the Postgres buffer cache (in memory copy of the on-disk page). When Postgres is not able to use the hot index page for the newly inserted value, that page could be outside the buffer cache, not in the OS cache, and ultimately result in a much slower disk read which increases latency. Besides the worse insert performance, since v4 values are scattered to more pages, this means there are more “page splits” when new inserts are attempted in full pages. Page splits cause more latency from increase WAL and IO. We experimented and benchmarked with v1, v4, and v7 uuid formats and we leveraged the research and write-ups from external sources like the ones below. Benchmarks are great, but what kind of real world results did we see? We decided to make this the new default unless v4 was needed for more randomness. After all qualified tables were changed, I began going through insert queries for each changed table. For many of the tables, there wasn’t an obvious change. However, for a handful we saw an immediate and significant improvement. I picked 5 with speedups of 6x, 8x, 9x, 20x, and 23x. The PgAnalyze graphs for the 23x, 9x, and 6x queries are shown below. Showing PgAnalyze insert query graphs for tables A, B, C: Table A - 23x reduction. 0.7ms to 0.03ms, 12000 calls/min Table B - 9x reduction. 0.6ms to 0.07ms, 2000 calls/min Table C - 6x reduction. 0.50ms to 0.08ms, 9500 calls/min Now that we’ve seen the results, let’s talk about how this was done and the challenges. The UUID values came from various sources: We replaced most of these with the function in Postgres 18. To do that, we needed to run a single statement per table. The statement ran fast, so no problem, right? One wrinkle we found was that modifying the column default while fast, required an lock. This lock type conflicts with every read and write operation including regular statements. For our highest queried tables, they’re queried constantly, so this was a problem. There was almost never a “window” to perform this operation, and we didn’t want to take downtime for this switch. While heavily queried tables were a challenge, infrequently queried tables did not pose a problem for this alter table statement at all. For those, we could use our migrations framework (Active Record in Ruby on Rails) and perform the using a regular old migration. For those, we did add some safeguards, by creating an explicit transaction and using to control timeout values. We’d set short timeouts for the to give up quickly if it didn’t work or ran too long. For the higher activity tables, we’d need some retries. We’d use a manual psql session: The would commit if it grabbed the lock within 50ms, or we’d get an error that the was reached. The benefit of the manual approach was we could retry until successful and backfill a Rails migration to keep everything in sync. A more sophisticated solution might have automated retries within Ruby. However, for our most heavily queried tables, we wanted even more control over the retries. How did we do that? Sometimes one or two retries would do the job. Great, we’d move on. However, for our most heavily queried table that didn’t work. What ended up working was using the same strategy of retries, but just adding more sophistication with looping and backoffs. Claude helped me cook up the PL/pgSQL looping retry function below, I did some testing and was ready to try it. It has these features: By using the function above, we were able to find a small window to perform the after several dozen quick retries! In cases where even many retries won’t work, and we don’t want downtime, we may be left with needing to actively monitor lock holder queries and to cancel them (assuming that’s ok). Thanks to Ants Aasma from the community PostgreSQL Slack for this idea. We didn’t end up needing to do this, but here was my prep for this. It’s still useful to review lock holder queries. First we’d inspect live queries: And identify queries holding locks: If we find them, we could cancel them to create a window to run our . That could mean bad user experience so you’d need to figure that out for your database. We’d likely want to stack up our operation to occur immediately after. Fortunately we didn’t end up needing to do this, but I’d be interested to hear the stories from others with heavily queried databases. Since uuid v7 values use a timestamp in their first bits, this timestamp can be easily decoded. This can be viewed as “leaking” or exposing the creation time of the record via that timestamp, which could be a downside for your database. You’ll have to decide that. v4 UUIDs do not expose the creation time. We found some significant speedups for insert queries after switching to primary keys, for a relatively low effort change. A nice ROI. The only wrinkle was the exclusive lock required, but we solved that with short lock related timeouts and many retries. Although this didn’t benefit 100% of our tables, the gains for some were significant and uuid v7 has become our new default choice for uuid primary keys. Thanks to the Postgres core team for creating this new capability within Postgres. The availability in core made it possible to adopt on AWS RDS which supports a limited amount of extensions. Thanks for reading, and until next time. How Sequential UUIDv7 Boosts Ingestion Performance Simplicity and power of UUID v7 PostgreSQL UUID Performance: Benchmarking Random (v4) and Time-based (v7) UUIDs The function from the module The function added in Postgres 13 that generates v4 UUIDs natively UUID v4 values sent by a client application, which meant the column default function was not used Try up to 50 times (max attempts is configurable) Add a pause in between retries, with a jittered backoff of 50-250ms

0 views
Andrew Atkinson 5 months ago

Beta Testing PostgreSQL With Docker

The Postgres community values feedback from testing of Beta releases, and with Docker it’s been easier to get pre-release versions up and running. With the recent announcement of PostgreSQL 19 Beta 1 , let’s get that running and test some of the new capabilities. First, you’ll need to install Docker for your OS! Grab the version needed for your OS and processor architecture, for example ARM or AMD/Intel/x86. On MacOS run or in your Terminal to learn more about your hardware details. For Windows check Install Docker Desktop on Windows Official Postgres images for Docker Postgres are limited to fully released versions. Fortunately @yosifkit created a PR to add 19 Beta 1 (merged by @ tianon ) with instructions for how to use to build pre-release versions. This command downloads and builds : With that built, I could invoke with . I named mine . I also passed the env vars below based on how I run other Docker Postgres containers (these options may not be necessary). The final command: To check if it’s running, I run . For logs I’d run: . The container is running and the logs have what we want: “database system is ready to accept connections”. Let’s connect to the database using psql on the container: We should see output like showing version 19: Great. Let’s try out some things in 19. 19 Added a new system view for checking out locks. Let’s try it out: We get a lot of new data like counts, and more. What about the new extension? First let’s load it and then create a table to experiment with: With that in place we can show the output via with a new parameter: I wonder why the rows estimate is 2550 by default? Let’s run . After doing that, it looks more sensible with a estimate of 1: The extension gained new capabilities in 19. Let’s try it out: Oops, we need to add it to first. We can see that’s currently not the case: One way to do that with is the parameter as follows: Now we see what we want in : We have not yet enabled the extension though given doesn’t list it. Let’s do that: Now shows it, and we’re ready to query it. One of the additions is tracking the use of prepared statements. Let’s create a basic table and prepared statement. Create table again if needed: Create a simple prepared statement and execute it. The goal here is for to increment the field. Now let’s execute it: Did it work? It worked! We see was incremented. This looks very useful to monitor the use of prepared statements. Please give this a shot and experiment with new features in Postgres 19! Add 19.x builds (currently beta 1)

0 views
Andrew Atkinson 1 years ago

Short alphanumeric pseudo random identifiers in Postgres

In this post, we’ll cover a way to generate short, alphanumeric, pseudo random identifiers using native Postgres tactics. These identifiers can be used for things like transactions or reservations, where users need to read and share them easily. This approach is an alternative to using long, random generated values like UUID values, which have downsides for usability and performance. We’ll call the identifier a and store it in a column with that name. Here are some example values: In database design, we can use natural or surrogate keys to identify rows. We won’t cover the differences here as that’s out of scope. For our identifier, we’re going to generate it from a conventional surrogate primary key called . We aren’t using natural keys here. The is intended for use outside the database, while the primary key is used inside the database to be referenced by foreign key columns on other tables. Whle is short which minimizes space and speeds up access, the main reason for it is for usability. With that said, the target for total space consumption was to be fewer bytes than a 16-byte UUID. This was achieved with an primary key and this additional 5 character generated value, targeting a smaller database where this provides plenty of unique values now and into the future. Let’s get into the design details. Here were the desired design properties: Additional details: Here are the functions used: This function obfuscates the integer value using exclusive or (XOR) obfuscation. Main entrypoint function: This converts the obfuscated value into the alphanumeric value, used within . This is “base 62” with the 26 upper and lower case characters, and 10 numbers (0-9). Reverses the back into the original integer. Used within : For a length of 5 with this system, we can create up to around ~1 billion unique values. This was sufficiently large for the original use case. For use cases requiring more values, by storing 6 characters for then up to ~56 billion values could be generated, based on a primary key. Let’s create a sample table with an primary key with a generated identity column. Besides the use in the identity column, we’ll again use the keyword to create a column for the . The column uses the column as input, obfuscates it, encodes it to base 62, producing a 5 character value. How do we guarantee conforms to our expected data properties? Constraints! For an existing system, we could add a unique index first as follows: Then we can add the unique constraint using the unique index, along with the constraint: Let’s insert data into the table: Let’s query the data, and also make sure it’s reversed (using the function) properly: Let’s compare the time spent inserting 1 million rows into an equivalent table without the column or value generation. That took an average of 2037.906 milliseconds, or around 2 seconds on my machine. Inserting 1 million rows with the took an average of 6954.070 or around 7 seconds, or about 3.41x slower. Note that these times were with the indexes and constraints in place on the table in the second example, but not the first, meaning their presence contributed to the total time. Summary: Creating this identifier made the write operations 3.4x slower for me locally, which was an acceptable amount of overhead for the intended use case. Compared with random values, the pseudo random remains orderable, which means that lookups for individual rows or ranges of rows can use indexes, running fast and reliably even as row counts grow. We can add a unique index on the column like this: We can very that individual lookups or range scans use this index, by inspecting query execution plans for this table. https://github.com/andyatkinson/pg_scripts/pull/15 Feedback on this approach is welcomed! Please use my contact form to provide feedback or leave comments on the PR. Future enhancements to this could include unit tests using pgTAP for the functions, packaging them into an extension, or supporting more features like case insensitivity or a modified input alphabet. Thanks for reading! A fixed size, 5 characters in length, regardless of the size of the input integer (and within the range of the data type) Fewer bytes of space than a data type An obfuscated value, pseudo random, not easily guessable. While not easily guessable, this is not meant to be “secure” Reversibility back into the original integer Only native Postgres capabilities, no extensions, client web app language can be anything as it’s within Postgres Non math-heavy implementation is stored using not not , following recommendations for best practices PL/PgSQL functions, native Postgres data types and constraints are used, like UNIQUE, NOT NULL, and CHECK, and a stored generated column. Converts integers to bits, uses exclusive-or (XOR) bitwise operation and modulo operations. Did not set out to support case insensitivity now, possible future enhancement Did not try to exclude similar-looking characters (see: Base32 Crockford below), possible future enhancement Uses a Hexadecimal key (make this any key you want) Sets a max value for the data type range , which is just under 1 billion possible values. This was enough for this system and into the future, but a bigger system would want to use Converts integer bytes into bits gets a constraint and , so we know we have a unique value A constraint is added to validate the length Base32 Crockford - An emphasis ease of use for humans: removing similar looking characters, case insensitivity. ULID - Also 128 bits/8 bytes like UUIDs, so I had ruled these out for space consumption, and they’re slightly less “usable” NanoIDs at PlanetScale - I like aspects of NanoID. This is random generation though like UUID vs. encoding a unique integer.

0 views
Shayon Mukherjee 1 years ago

Another look into PostgreSQL CTE materialization and non-idempotent subqueries

A few days ago, I wrote about a surprising planner behavior with CTEs, DELETE, and LIMIT in PostgreSQL, a piece I hastily put together on a bus ride. That post clearly only scratched the surface of a deeper issue that I’ve since spent way too many hours exploring. So here are some more formed thoughts and findings. The core issue revisited Let’s quickly recap: when using a query like the following, the planner might execute your query in ways you don’t expect.

0 views
Shayon Mukherjee 2 years ago

Stop Relying on IF NOT EXISTS for Concurrent Index Creation in PostgreSQL

As a developer, you might have encountered situations where creating an index in PostgreSQL fails due to lock timeouts. In such scenarios, it’s tempting to use the IF NOT EXISTS as a quick fix and move on. However, this approach can lead to subtle and hard-to-debug issues in production environments. Let’s understand how PostgreSQL handles concurrent index creation When we initiate CREATE INDEX CONCURRENTLY, PostgreSQL first creates an entry for the index in the system catalogs (specifically in pg_index) and marks it as invalid.

0 views
hyPiRion 2 years ago

Implementing System-Versioned Tables in Postgres

There's no official support for system-versioned tables in Postgres yet, and the temporal extensions aren't supported on Azure/AWS/GCP. Let's implement it ourselves with 3 triggers and an index.

0 views
Pinaraf's website 13 years ago

Review – “Instant PostgreSQL Starter”

Thanks to Shaun M. Thomas , I have been offered a numeric copy of the “ Instant PostgreSQL Backup ” book from Packt publishing, and was provided with the “ Instant PostgreSQL Starter ” book to review. Considering my current work-situation, doing a lot of PostgreSQL advertising and basic teaching, I was interested in reviewing this one… Like the Instant collection ditto says, it’s short and fast. I kind of disagree with the “focused” for this one, but it’s perfectly fine considering the aim of that book. Years ago, when I was a kid, I discovered databases with a tiny MySQL-oriented book. It teaches you the basis : how to install, basic SQL queries, some rudimentary PHP integration. This book looks a bit like its PostgreSQL-based counterpart. It’s a quick travel through installation, basic manipulation, and the (controversy) “Top 9 features you need to know about”. And that’s exactly the kind of book we need. So, what’s inside ? I’d say what you need to kick-start with PostgreSQL. The installation part is straight forward : download, click, done. Now you can launch pgadmin, create an user, a database, and you’re done. Next time someone tells you PostgreSQL ain’t easy to install, show him that book. The second part is a fast SQL discovery, covering a few PostgreSQL niceties. It’s damn simple : Create, Read, Update, Delete. You won’t learn about indexes, functions, advanced queries here. For someone discovering SQL, it’s what needs to be known to just start… The last part, “Top 9 features you need to know about”, is a bit more hard to describe. PostgreSQL is a RDBMS with included batteries, choosing 9 features must have been a really hard time for the author, and I think nobody can be blamed for not choosing that or that feature you like : too much choice… The author spends some time on pg_crypto, the RETURNING clause with serial, hstore, XML, even recursive queries… This is, from my point of view, the troublesome part of the book : mentioning all these features means introducing complicated SQL queries. I would never teach someone how to do recursive queries before teaching him joins, it’s like going from elementary school to university in fourty pages. But the positive part is that an open-minded and curious reader will have a great teaser and nice tracks to follow to increase his knowledge of PostgreSQL. Mentioning hstore is really cool, that’s one of the PostgreSQL feature one have to know… To sum up my point of view about this book : it’s a nice book for beginners, especially considering the current NoSQL movement and people forgetting about SQL and databases. It’s a bit sad we don’t have more books like this one about PostgreSQL. I really hope Packt publishing will try to have a complete collection, from introduction (this book) to really advanced needs ( PostgreSQL High Performance comes to mind) through advanced SQL queries, administration tips and so on… They have a book about PostgreSQL Server Programming planned next month, I’m really looking forward to this one.

0 views