News for hackers. Stuff that matters.
Re:Thoughts on PostgreSQL in 2024 by Demiurge on Tuesday January 02, 2024 @07:23PM (#38849086)
> I’ve even talked to users who have workloads that can only be unavailable for 1s – and while I was initially skeptical, when I heard what the workloads were for, I did agree that 1s was a reasonable requirement for them!

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?

Re:Thoughts on PostgreSQL in 2024 by abhibeckert on Tuesday January 02, 2024 @07:40PM (#38849223)
I run a service that sells theatre tickets and, far more critically, a service to check if a ticket is valid or not.

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.

Re:Thoughts on PostgreSQL in 2024 by subarctic on Tuesday January 02, 2024 @08:04PM (#38849403)
Doesn't 1 second of database downtime just mean that a ticket that is scanned during that 1 second window will take 1.5-2 seconds to scan instead of, say, 500ms? Why would that result in 10 minutes of troubleshooting?
Re:Thoughts on PostgreSQL in 2024 by abhibeckert on Tuesday January 02, 2024 @08:20PM (#38849532)
Because when it doesn't work, they'll get an error message, and then they will try to figure out how to fix the error message.

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.

Re:Thoughts on PostgreSQL in 2024 by ses1984 on Tuesday January 02, 2024 @10:19PM (#38850282)
Why would you immediately show an error message instead of some kind of background retry?

Or at the very least the message should tell the operator to retry.

Re:Thoughts on PostgreSQL in 2024 by emeril on Wednesday January 03, 2024 @08:30AM (#38853812)
it isn't downtime he's as concerned about as it's about speed

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)

Re:Thoughts on PostgreSQL in 2024 by jtc331 on Wednesday January 03, 2024 @08:36AM (#38853872)
That doesn’t have to incur actual downtime if you use pgbouncer in front of Postgres and pause the pool.
Re:Thoughts on PostgreSQL in 2024 by arp242 on Tuesday January 02, 2024 @08:11PM (#38849455)
Based on what you've written, for your specific use case I'd probably go with "local SQLite syncing with remote server whenever possible" even if PostgreSQL was 100% perfect. There's just so much that can go wrong with an internet connection, or you end up with "that one entrance where hand-scanners don't quite work because of bad reception in that corner", and stuff like that.
Re:Thoughts on PostgreSQL in 2024 by Demiurge on Tuesday January 02, 2024 @07:55PM (#38849343)
I think I understand the use case, and effectively you're sharding the data with SQLite. This approach is applicable with multiple databases/datastores.

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.

Re:Thoughts on PostgreSQL in 2024 by abhibeckert on Tuesday January 02, 2024 @08:24PM (#38849545)
> However, would you really change your approach if PostgreSQL could be upgraded by a minor version without any downtime?

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.

Re:Thoughts on PostgreSQL in 2024 by Demiurge on Tuesday January 02, 2024 @09:07PM (#38849816)
Right, the local internet can always be an issue too. I think you have a good example of something with high uptime requirements. I get that, but I was kind of wondering specifically why Postgres's downtime would have any significant impact, if it was 1 second. That's hard for me to imagine. I also work in some high resiliency environments but I handle it with queues. No system I ever worked with had a requirement on the DB to have such availability.
Re:Thoughts on PostgreSQL in 2024 by backwardation_b on Tuesday January 02, 2024 @07:47PM (#38849289)
On the theme of downtime, how do you handle durability of your SQLite db’s? What if one of the devices goes down?
Re:Thoughts on PostgreSQL in 2024 by abhibeckert on Tuesday January 02, 2024 @08:54PM (#38849743)
As a primary last resort there should be a printed paper backup of the database, but it's a snapshot of ticket sales that at this point would be an hour old, and there's always a rush of last minute writes to the database.

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.

Re:Thoughts on PostgreSQL in 2024 by spiffytech on Tuesday January 02, 2024 @09:29PM (#38849957)
> Alec Baldwin was shot and killed by a "prop" gun

Minor correction: Alec Baldwin shot and killed cinematographer Halyna Hutchins, and injured director Joel Souza. Baldwin himself wasn't harmed.

Re:Thoughts on PostgreSQL in 2024 by chuckadams on Tuesday January 02, 2024 @09:29PM (#38849959)
> Alec Baldwin was shot and killed by a "prop" gun

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.

Re:Thoughts on PostgreSQL in 2024 by abhibeckert on Thursday January 04, 2024 @12:16AM (#38863312)
Sorry my mistake. I guess the wrong name stuck in my head.

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.

Re:Thoughts on PostgreSQL in 2024 by brvsft on Tuesday January 02, 2024 @09:32PM (#38849981)
> Alec Baldwin was shot and killed by a "prop" gun...

RIP Alec Baldwin

Re:Thoughts on PostgreSQL in 2024 by justinclift on Wednesday January 03, 2024 @01:20AM (#38851319)
Heavily used authentication systems can be near that, as can things like a PG backend to Radius:

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. :(