> building and managing an active-active system is extremely complicated
Could Postgres yet evolve to become a Spanner-like multi-writer system?
Thoughts on PostgreSQL in 2024
11–20 of 53 posts
Re: Thoughts on PostgreSQL in 2024
#12> 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 dow…
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…
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
#13> Logical replication allows the real-time streaming of changes from a database into any system that can understand the PostgreSQL logical replication protocol. 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 t…
Do you mean debezium? It’s relatively easy to set that up, what is not trivial is translating data types between various databases.
Re: Thoughts on PostgreSQL in 2024
#14Wondering why nothing is said about compression? For MySQL you have Rocksdb with high ZSTD compression. Is the Postgres solution to just use compressed filesystem like ZFS? I could not find much info when researching Postgress, except for Toast compression, which didn't seem very strong. 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…
I'm not familiar with Rocksdb but if it's just a KV store you want, I'd probably just use Redis with compression. It is hard to beat. Otherwise, Timescaledb offers compressible table chunks using table inheritance. It's really pretty slick, depending on your use-case.
YugabyteDB is a distributed Postgres and their storage layer is called DocDB, a slightly modified RocksDB.
Re: Thoughts on PostgreSQL in 2024
#15I really need to write up my wishlist for PG into a blog post. There is so much to love about it, but it could be so much more. Would love to see some other table formats like column stores, better hash indexes, and tons of SQL improvements - plus something done about connections so we could have so many more without them being resource hogs or taking a long time to connect. Serverless talking to a database sucks.
Re: Thoughts on PostgreSQL in 2024
#16> 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 dow…
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…
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
#17Wondering why nothing is said about compression? For MySQL you have Rocksdb with high ZSTD compression. Is the Postgres solution to just use compressed filesystem like ZFS? I could not find much info when researching Postgress, except for Toast compression, which didn't seem very strong. 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…
I'm not familiar with Rocksdb but if it's just a KV store you want, I'd probably just use Redis with compression. It is hard to beat. Otherwise, Timescaledb offers compressible table chunks using table inheritance. It's really pretty slick, depending on your use-case.
[deleted]
Re: Thoughts on PostgreSQL in 2024
#18Earlier quoted context omitted.
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…
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?
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
#19Earlier quoted context omitted.
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…
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 convic…
> 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
#20I didn't know about extension repositories like https://database.dev/. What less-known extensions are people here using? I'm curious because there might be extensions that simplify common tasks in ways I'm unaware of.