6 ms·

Thoughts on PostgreSQL in 2024

124 points by sjmiller609 3y ago

hinkley 3y ago
> 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 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.
- rad_gruchalski 3y ago
  Do you mean debezium? It’s relatively easy to set that up, what is not trivial is translating data types between various databases.
- tristenharr 3y ago
  This would be amazing. Anybody who plugs into the PostgreSQL replication protocol is on my watchlist. I’ve been playing with https://github.com/turso-extended/pg_turso https://github.com/turso-extended/pg_turso which lets you replicate PostgreSQL tables into SQLite on fly.io using Turso. 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.
  - tristenharr 3y ago
    Also, sidenote although pg_turso is experimental I added text, varchar, uuid, timestamp, timestamptz, json and jsonb to the supported types ~1 month ago for anyone who saw it in the past but that was holding them back, don’t let it be what stops you from hacking on 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
jurgenkesker 3y ago
Wondering 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, but seemed like a step down in this regard.
- mrj 3y ago
  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.
  - karmakaze 3y ago
    RocksDB is a fork of Google's LevelDB, a KV store using Log Structured Merge Tree (LSM tree) which is great for high write workloads. The MySQL storage engine that uses RocksDB is called MyRocks[0]. [0] http://myrocks.io/ http://myrocks.io/
  - rad_gruchalski 3y ago
    YugabyteDB is a distributed Postgres and their storage layer is called DocDB, a slightly modified RocksDB.
  - deleted 3y ago
    [deleted]
  - philippemnoel 3y ago
    I think bringing RocksDB into Postgres as a Table access method would really be a powerful feature, as an alternative to heap
- insanitybit 3y ago
  This is what keeps me from postgres for large amounts of data. I use postgres to store metadata or small data (ie: "users logged in currently", "customers", etc). 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.
Demiurge 3y ago
> 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?
- abhibeckert 3y ago
  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.
refset 3y ago
> building and managing an active-active system is extremely complicated Could Postgres yet evolve to become a Spanner-like multi-writer system?
- klysm 3y ago
  Is spanner really practical for most people? From my narrow understanding it relies on some specialized clocks and networking
  - riku_iki 3y ago
    cocroachdb probably better example
- ddorian43 3y ago
  It can be done like https://github.com/yugabyte/yugabyte-db/ https://github.com/yugabyte/yugabyte-db/ has.
netcraft 3y ago
I 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.
xwowsersx 3y ago
I didn't know about extension repositories like https://database.dev/ 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.
- neoecos 3y ago
  https://pgxman.com/ https://pgxman.com/
ThinkBeat 3y ago
Confusing https://db-engines.com/en/ranking https://db-engines.com/en/ranking 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.
- TheGeminon 3y ago
  That confused be a bit at first too, but that ranking seems to be for how widely they are used (not sure the exact metric), not the ranking of how good they are (which is the dbms of the year page)
- hn_throwaway_99 3y ago
  Their methodology is here: https://db-engines.com/en/ranking_definition https://db-engines.com/en/ranking_definition 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.
  - hyperman1 3y ago
    As someone who has worked with Oracle, mssql and postgres: If it gets complex I tend to use a search engine for oracle or mssql, for postgres I go straight to their official docs. Postgres documentation quality is very good and honest. 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.
philippemnoel 3y ago
This is a great post. As an extension developer myself (pg_bm25), seeing more native support for search-related functionalities that extensions can harness would be really great. 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
malablaster 3y ago
“The document discusses where PostgreSQL is heading in 2024 and beyond. It focuses on three key areas: availability, performance, and developer features. For availability, the author sees logical replication and reducing downtime for upgrades and maintenance as important goals. Performance improvements may come from asynchronous I/O, parallel recovery, and maximizing resource usage on large instances. On developer features, priorities include supporting more of the SQL/JSON standard and making migration from other databases easier. The author also notes opportunities around PostgreSQL extensions, community building, mentorship, and diversity/inclusion. Overall, the community is actively working to improve PostgreSQL in many dimensions.” –Kagi summarizer.
Deborahluis 3y ago
[dead]
theresaheger0 3y ago
[dead]

124 points by sjmiller609 3y ago

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

rad_gruchalski 3y ago
Do you mean debezium? It’s relatively easy to set that up, what is not trivial is translating data types between various databases.
tristenharr 3y ago
This would be amazing. Anybody who plugs into the PostgreSQL replication protocol is on my watchlist. I’ve been playing with https://github.com/turso-extended/pg_turso https://github.com/turso-extended/pg_turso which lets you replicate PostgreSQL tables into SQLite on fly.io using Turso. 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.
- tristenharr 3y ago
  Also, sidenote although pg_turso is experimental I added text, varchar, uuid, timestamp, timestamptz, json and jsonb to the supported types ~1 month ago for anyone who saw it in the past but that was holding them back, don’t let it be what stops you from hacking on 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

jurgenkesker 3y ago

Wondering 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, but seemed like a step down in this regard.

mrj 3y ago
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.
- karmakaze 3y ago
  RocksDB is a fork of Google's LevelDB, a KV store using Log Structured Merge Tree (LSM tree) which is great for high write workloads. The MySQL storage engine that uses RocksDB is called MyRocks[0]. [0] http://myrocks.io/ http://myrocks.io/
- rad_gruchalski 3y ago
  YugabyteDB is a distributed Postgres and their storage layer is called DocDB, a slightly modified RocksDB.
- deleted 3y ago
  [deleted]
- philippemnoel 3y ago
  I think bringing RocksDB into Postgres as a Table access method would really be a powerful feature, as an alternative to heap
insanitybit 3y ago
This is what keeps me from postgres for large amounts of data. I use postgres to store metadata or small data (ie: "users logged in currently", "customers", etc). 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.

Demiurge 3y ago

> 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?

abhibeckert 3y ago
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.

refset 3y ago

> building and managing an active-active system is extremely complicated Could Postgres yet evolve to become a Spanner-like multi-writer system?

klysm 3y ago
Is spanner really practical for most people? From my narrow understanding it relies on some specialized clocks and networking
- riku_iki 3y ago
  cocroachdb probably better example
ddorian43 3y ago
It can be done like https://github.com/yugabyte/yugabyte-db/ https://github.com/yugabyte/yugabyte-db/ has.

netcraft 3y ago

I 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.

xwowsersx 3y ago

I didn't know about extension repositories like https://database.dev/ 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.

neoecos 3y ago
https://pgxman.com/ https://pgxman.com/

ThinkBeat 3y ago

Confusing https://db-engines.com/en/ranking https://db-engines.com/en/ranking 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.

TheGeminon 3y ago
That confused be a bit at first too, but that ranking seems to be for how widely they are used (not sure the exact metric), not the ranking of how good they are (which is the dbms of the year page)
hn_throwaway_99 3y ago
Their methodology is here: https://db-engines.com/en/ranking_definition https://db-engines.com/en/ranking_definition 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.
- hyperman1 3y ago
  As someone who has worked with Oracle, mssql and postgres: If it gets complex I tend to use a search engine for oracle or mssql, for postgres I go straight to their official docs. Postgres documentation quality is very good and honest. 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.

philippemnoel 3y ago

This is a great post. As an extension developer myself (pg_bm25), seeing more native support for search-related functionalities that extensions can harness would be really great. 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

malablaster 3y ago

“The document discusses where PostgreSQL is heading in 2024 and beyond. It focuses on three key areas: availability, performance, and developer features. For availability, the author sees logical replication and reducing downtime for upgrades and maintenance as important goals. Performance improvements may come from asynchronous I/O, parallel recovery, and maximizing resource usage on large instances. On developer features, priorities include supporting more of the SQL/JSON standard and making migration from other databases easier. The author also notes opportunities around PostgreSQL extensions, community building, mentorship, and diversity/inclusion. Overall, the community is actively working to improve PostgreSQL in many dimensions.” –Kagi summarizer.

Deborahluis 3y ago

[dead]

theresaheger0 3y ago

[dead]