Thoughts on PostgreSQL in 2024
jkatz05.com
jkatz05.com
I'm surprised nothing was mentioned around pgBouncer/connection pooling. Having more native support for general connection pooling has been a requested feature in Postgres for a while, and many popular DBMS have it built-in. Just how some of what Patroni offer should be better bundled in (as the author argues), I think the same could/should be true for pgBouncer
My Christmas wish for 2024-2025 is that we have an industry standard format for replication. A simple transform (hopefully) of either WAL or logical replication, that we can feed into backup software, search, analytics, whatever.
There's something out there that fulfills the duties of Kafka and half a dozen other tools (at least two of which don't exist yet due to significant barriers to entry) with about a quarter of the code. We just need to carve it out of the block of marble.
I’m still predicting we’re getting a Kafka clone in Rust at some point. No idea who or where from but I’m predicting/hoping.
The world is moving to events. I’d be surprised if 2024 was “the year of events” but I’m sure it’ll be that year sometime in the next 5 years or so.
I want to speak WAL everywhere I go. Let me talk WAL to my vector database, and my OLAP, I want to talk WAL to NoSQL, so what if the schema isn’t enforced, I still want a logical representation of it.
I tested it on the chinook music db, I was able to fully replicate a table, although I admit it’s barely functional as a cache because it doesn’t handle relationships or primary/secondary keys and I don’t think it does anything with unique constraints either, but with that said it is functional. It will take a table from Postgres and put it into every region supported by Fly.io and with embedded replicas you can just push it through onto the users device if you want. I jumped out of my chair and was laughing like an idiot the first time I got it to stream the entire chinook music DB from a Postgres creation script straight into SQLite all over the world, all while running postgresql on localhost inside Docker. I can taste the future. XD
What could this be? Some crypto exchange is based on PostgreSQL?
I generally don't understand this kind of a requirement. Downtime, to me, is inability to have an end to end transaction. During downtime, to me, it is possible to queue writes and cache reads, to some extent.
I can understand if there is something in the middle, in the database layer, that needs to happen, but what would it be, that requires sub 1s latency, at that level?
Tickets are checked, by scanning a QR code, as people walk though the door. Not only do people hate sitting in their seat waiting (or worse, standing in line waiting)... you also need to pay a couple hundred employees to stand around and do nothing while waiting - ballpark cost of wages might be $5 for every second you can cut from the process of people walking through the door. On top of that wages cost, people waiting for the show to start don't buy drinks at the bar. A lot of events make more money on drink sales than ticket sales... often a high percentage of ticket sale revenue goes to whoever has a copyright claim to the show. They often get a cut of drink sales too, but it's a smaller cut.
So there are strong incentives to start scanning tickets as late as you possibly can before the performance actually starts. And if there's 1 second of downtime... it might trigger ten minutes of troubleshooting (turn it off and on again, etc) by a dozen people.
A show starting ten minutes late can be _really_ bad. If the show prep starts at 6pm and show pack up finishes at 11pm, that's five hours where some people are working non stop throughout that time - it's not really OK. Live events are dangerous, workplace deaths are far too common and some of the steps taken to try to prevent them don't really leave room for someone to take a meal break during a show. Alec Baldwin was shot and killed by a "prop" gun, in part because crew took a meal break at a critical time on the set, which interrupted routine safety checks... stuff like that can, and does, happen in theatre too.
There are union rules around breaks that everything is planned around and because delays happen in the real world there are financial penalties in place to discourage delays. So instead of $5 per second, that ten minutes might cost you $20 per second... in other words a $12,000 cost that wasn't in your budget... and the producer will have to explain why they ran $12k over budget - keep in mind the show might not actually make $12k profit in a single night's performance. Sometimes they don't even make a profit when they run smoothly.
All of that means your one second of downtime will be followed a formal written incident report and meetings to discuss what happened, and those meetings will inevitably include investigating alternative ticket services. We know little things like that can cost us a fortune, because we win new customers all the time when they tell us they left a competitor after a single brief period of downtime (our biggest competitor recently had a bug where buying tickets didn't work in the shitty browser that ships with certain cheap android phones... they fixed it relatively quickly but we gained a lot of new customers anyway).
Sure - it's only that critical from, say, 7:30pm to 7:55pm in a single building... an hour of downtime at 10am wouldn't be noticed... but if it's a global service, with dozens of cities in every timezone, then that critical state is happening 24/7/365.
One second of downtime will absolutely cause a measurable, and large, cost to your company profits. We deployed a major new system for ticket scanning in February last year. It worked perfectly, we haven't made any changes since February - other than just testing it out, and we're starting to deploy it over the next two months. That's how reluctant we are to break things.
We considered PostgreSQL, but decided it's not reliable enough.
We're using SQLite - with literally thousands of databases — every hand held scanning device as well as the servers run their own database. The servers themselves have a separate database for each individual event (and there are redundant servers too, so multiple server side databases per event). Also the database for critical things like scanning tickets is a separate database to less critical things, like payment records, event details, etc.
PostgreSQL is better than SQLite for the servers at least in a bunch of ways - but the big advantage of SQLite is flexibility - we can do a phased deployment where from the moment we start selling tickets (a few months before the actual event) to the moment of the actual event, there will be no deployments at all to that database unless we think a bug will directly effect the event. A deployment today will typically only apply to events that go on sale starting tomorrow.
Every business case has different needs, but skim reading this article on the future of Postgres, it sounds like they're working to improve some of the reasons I chose not to use it for this specific case.
The better organised venues write those changes down with a pen, so it's at least up to date, but modern venues have several entrances to get people through the doors faster, so most people won't have the up to date paper backup.
Our system uses a cross platform app on ordinary smart phones to scan tickets. So if a device goes down, you just need to get your hands on another phone.
There's a limited feature set version of the QR code scanner (scan code, screen flashes red or green) that just runs in a web browser on any device with a half decent camera. All you need is the secret URL for the tonights event — which we also expose via a QR code.
However, would you really change your approach if PostgreSQL could be upgraded by a minor version without any downtime?
> One second of downtime will absolutely cause a measurable, and large, cost to your company profits.
I want to believe, but I really am not conviced that 1 second downtime to restart PostgreSQL out of 24/7/365 would make more of a dent on your companys profits, as dedicating two weeks of developers time on optimizing query performance and mobile app performance to potentially improve average response time of your apps by multiple percentage points.
Dunno. I wasn't so much answering the "who wouldn't use Postgres" question, I was more answering "who could possibly care about 1 second of downtime" question.
I seriously considered Postgres, and I will reconsider that choice regularly.
There are things we have in SQLite right now that would absolutely be better in Postgres, but for the moment we've decided those benefits don't justify the overhead of two completely different database system.
> I really am not conviced that 1 second downtime to restart PostgreSQL out of 24/7/365 would make more of a dent on your companys profits
Our customers are not the people buying tickets. Our customers are the people selling tickets, and our profit is a percentage of their annual revenue. They're moderately sized companies with hundreds of staff.
Which means a single dissatisfied person, at the wrong level of management, can cost us hundreds of thousands of dollars. Downtime is inevitable and we don't claim to offer 100% uptime... but we try really really hard to be as close to that as possible. That includes encouraging people to print, on dead tree paper, a copy of their ticket sales database five minutes before they open the doors for a performance. But that copy is always out of date, since they sell tickets after opening the doors, so it's not a great backup.
These aren't tech savvy people. You don't hire an IT person to stand at a door scanning a thousand QR codes for 20 minutes three nights a week.
Or at the very least the message should tell the operator to retry.
he could build in behavior to retry/wait for several seconds before erroring out but his selling point is speed seemingly (with little risk of sale negatives/positives/theft)
Minor correction: Alec Baldwin shot and killed cinematographer Halyna Hutchins, and injured director Joel Souza. Baldwin himself wasn't harmed.
Alec Baldwin is alive and well and is the guy who shot someone with a REAL gun, which had nothing to do with anyone’s lunch breaks.
Using real guns is totally normal, because they fire real explosives (so you get a muzzle flash) which can and occasionally do kill people - the only difference between a proper gun and the ones they use in movies is you need to be a lot closer to the movie weapon to be killed by it.
What's not normal is a lack of adequate safety checks around handling of the weapons and there were severe complaints about inadequate breaks for crew and even unconfirmed reports of other people injured by prop guns prior to that day on the same film set. It can be a really difficult issue to deal with - you have literally thousands of people to coordinate.
Exact details of what happened in the Alec Baldwin case are unclear, manslaughter chargers are still winding through the courts, and they include a charge of tampering with evidence which means we may never find out what really happened.
What I can say, working in the acting industry, is I have often been tired (back to back 22 hour shifts where you're forced to eat and drink while working will do that to you) and I've made mistakes while operating in those conditions and seen other people make mistakes that resulted in serious injuries. Managing breaks is a real problem in the industry and technical difficulties are one of the most common causes of delays.
RIP Alec Baldwin
https://en.wikipedia.org/wiki/RADIUS
I was working with a proprietary Radius system a while ago that used PG for it's database backend. That system had been around for many years and had (smaller) national telcos/ISPs as customers.
When people's internet routers turn on and authenticate to their ISP, this is the kind of system it talks too.
The steady state load for this system just over 300k transactions per second (24/7), sometimes peaking to over double that during busy times.
The most memorable problem was pgBadger (reporting tool) often running out of memory on 256GB (ram) nodes because perl seems to have extremely poor (non-existent?) garbage collection. :(
I have a huge DB which would take many TB but now runs on a 500GB footprint. Thanks to Rocksdb/MySQL. Pondering a move to Postgress, but seemed like a step down in this regard.
Otherwise, Timescaledb offers compressible table chunks using table inheritance. It's really pretty slick, depending on your use-case.
If you want large datasets (petabytes) you really need to look elsewhere to something with better compression support or that tiers its data off to S3.
It's possible that with bcachefs we're like a decade away from "good fs in mainline kernel with fs compression" but right now it's not a great situation.
And a lot of data is already compressed (images, etc) so might increase your compute cost without any storage savings.
Filesystems are often the wrong level in the stack to do compression. You want individual control over what data is compressed.
I agree with this point, but I think those online compression algorithms add very little overhead, so it is mostly about if Postgres adds enough other benefits compared to your current DB.
The bigger issue potentially is that ext4 is faster than zfs and btrfs itself regardless of compression.
It's not just compute power, I was generalising with that statement. The real world speed hit can be as bad as read speeds an order of magnitude slower than if you disable compression.
You really only want to compress data where it makes sense to compress it.
data is compressed and decompressed in blocks..
1. Oracle. 2. Oracle MySql 3. Microsoft SQL Server 4. Postgres.
The ranking has been constant over Jan 2024, December 2023 and January 2023. So no change.
Postgres did have the biggest increased score in Jan 2023, yet the change did not change its position in the ranking.
A lot of it is highly suspect in my opinion. First off, they don't specifically say how they consider Postgres for some of their factors. E.g. in their "Mentions on system websites" section they say
> In order to count only relevant results, we are searching for <system name> together with the term database, e.g. "Oracle" and "database".
But I'd expect Postgres mentions to be split a lot between "Postgres" and "Postgresql", and it's not clear they included both terms.
More importantly, the vast majority of these factors are trailing indicators. Even if no same person would use Oracle for a new installation, their existing giant (and very difficult to migrate) install base means you'll see tons of Oracle mentions in LinkedIn profiles and job reqs for decades.
Also, the number of postgres weird edge cases and ad hoc hacks in SQL feels lower.
If my experience generalizes, this ranking will score you higher the more support you need. Well working software generates less noise and is measured as less popular as a result.
Then again, the methodology isnt measuring what you’d expect. Like usage.
Could Postgres yet evolve to become a Spanner-like multi-writer system?