6 ms·

PostgreSQL is eating the database world

108 points by janpio 3y ago

deleted 3y ago
[deleted]
edhelas 3y ago
Good
xnx 3y ago
Dupe: Postgres is eating the database world https://news.ycombinator.com/item?id=39711863 https://news.ycombinator.com/item?id=39711863 5 days ago 138 comments Edit: included the wrong link
- ydant 3y ago
  HN link to above: https://news.ycombinator.com/item?id=39711863 https://news.ycombinator.com/item?id=39711863
roynasser 3y ago
I feel like I've seen this more than I've seen my parents since year!
ralusek 3y ago
Love Postgres, but here's what I wish was different: RDS Proxy/PG Bouncer should be default connection behavior. Ideally no persistent connection at all, more akin to https would be great. Vacuuming is ridiculous. It doesn't make sense to me what could possibly take so long. It also doesn't make sense to me that it needs to be blocking (I understand that it's now parallelizable-ish). Using a comparatively slow interpreted language, I can iterate through millions of items, on disk, and do any number of things, within a few seconds at most. I have had databases with like, a few thousand items, somehow take hours upon hours to vacuum/analyze. Nested transactions would be great. I know there are savepoints but it doesn't work well when dealing with anything in parallel. And finally, my #1 complaint: Please let ME decide when to roll back/invalidate a transaction. If I want to write something like an upsert, maybe my code says "insert this record, and if I catch an unique constraint error, update the record." In Postgres, at the initial insert, because there's an error, it will just invalidate my transaction! I could have done 100 other things in this transaction so far, all invalidated because of a DB error. An error that I was expecting to catch and handle myself at the application level, and now the entire transaction needs to be rolled back. WHY?
- koolba 3y ago
  > RDS Proxy/PG Bouncer should be default connection behavior. Ideally no persistent connection at all, more akin to https would be great. That doesn't make sense. A database connection is inherently stateful as you run multiple commands in a transaction. > Vacuuming is ridiculous. It doesn't make sense to me what could possibly take so long. It also doesn't make sense to me that it needs to be blocking (I understand that it's now parallelizable-ish). Using a comparatively slow interpreted language, I can iterate through millions of items, on disk, and do any number of things, within a few seconds at most. I have had databases with like, a few thousand items, somehow take hours upon hours to vacuum/analyze. Routine vacuuming is not blocking (on VACUUM FULL to reclaim space is blocking). The entire storage approach has its warts, but works well for 99.99% of use cases. I'd argue that write amplification is a much larger problem. > Nested transactions would be great. I know there are savepoints but it doesn't work well when dealing with anything in parallel. What does it mean to work with a transaction in parallel? The A and I in ACID are for "Atomic" and "Isolation". > And finally, my #1 complaint: Please let ME decide when to roll back/invalidate a transaction. If I want to write something like an upsert, maybe my code says "insert this record, and if I catch an unique constraint error, update the record." In Postgres, at the initial insert, because there's an error, it will just invalidate my transaction! I could have done 100 other things in this transaction so far, all invalidated because of a DB error. An error that I was expecting to catch and handle myself at the application level, and now the entire transaction needs to be rolled back. WHY? That's exactly what using a SAVEPOINT does. The default of failing and trashing the connection state (until a ROLLBACK) is a sensible default. It also allows for command pipelining as you can send multiple commands and not worry about partial execution due to intermediate failure. If your application code is repeatedly failing then you should be fixing your application. There are many ways to perform consistent INSERT-or-UPDATE in PostgreSQL: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/current/sql-insert.html#SQL-...
- kstrauser 3y ago
  I agree about PGBouncer. You absolutely want persistent connections, though: establishing a TLS connection is comparatively costly and you don't want to pay it more than you need to. It's been maybe 15 years since I've waited for a vacuum to finish outside of me doing a `VACUUM FULL` on an offline copy as an experiment. It's had subtransactions for years. It has an exception clause so you can catch errors and roll back. In the absence of explicit exception handling, it must roll back a transaction instead of committing who-knows-what to disk. That's the whole point of transactions.
kstrauser 3y ago
Always has been. I've been using PostgreSQL for a decades, and I feel so spoiled. It always Just Works. Not to say there've never been bugs, but compared to anything else with that much surface area, it's a brilliant piece of engineering. It's astonishing how often it's a perfectly fine stand-in for the "right" solution. Need a K-V store to hold a bunch of JSON docs indexed by UUID? Fine. Want to make an append-only log DB? Why not. Should you do those things? Probably not, but unless you specifically need to architect for global-scale concurrent usage, it's likely to work out just fine. For me, it's the default place to stick data unless I have a specific requirement that only something else can meet. I've never once regretted using it to launch a production system, and only a couple of times have needed to migrate off of it due to performance demands. Thanks, PostgreSQL team! You rock.
- ijidak 3y ago
  I hear this often with regards to Postgre. Can't all of the above be said about Microsoft SQL Server as well? What prevents SQL Server from being used in the same cases you mention above?
  - leosanchez 3y ago
    Licensing cost ?
    - kstrauser 3y ago
      Yep. I can use PostgreSQL anywhere I want, for any purpose I want, in any configuration I want, for free. That means I can use one stack from the tiniest of projects up through giant production systems, and also that I never have to wait for budget approval before scaling or launching new staging environments or spinning up a thousand test instances. For me, the onus is on any other DB to convince me that I should use it instead of PostgreSQL. The few times when that's been the case, it's been because we needed something other than a relational database for various specific reasons. At this point I can't think of many reasons I'd use anything else than psql that's in the same category. Like, I can imagine requirements that would send me to Snowflake or Redis or DynamoDB much more easily than things that would nudge me to SQL Server or even MariaDB.
whartung 3y ago
The magic of Postgres (and, quite arguably, MySQL, SQLite, etc.) is simply the idea that a sophisticated RDBMS is ubiquitous. I came from the Old Days when we had to chisel BASIC code into cooling silicon. Having something like a SQL RDBMS just sitting there, busy, or not, maybe just wasting away, ready for any weird nonsense you throw at it, is just a treasure. I have postgres on my mac. I've had postgres on my mac since I've had a Mac, so, what, 2006? I still have DBs on there that are now pushing 17 years old (after several PG version upgrades). I have the space, no reason to delete them. Just there. Old projects, strange experiments, idle. That I have this much capability languishing is amazing. SQL databases used to be a Big Deal. They were large step up from hand coding B-Tree indexes. I remember once we got a call from a client complaining about performance on a system we installed. We popped in, took a look around, and, yea, we dropped the ball. Not a single index was created on their system. It was just the tables. No wonder it was slowing down. 10 minutes of mad index creation later, all was well. If you weren't there in those days, it's remarkable that we had a system where indexes were (mostly) a performance thing, rather than a core thing the entire system was designed around. A paradigm shift in development. SQL DBs were amazing. They were also rare, and expensive. Custom libraries to access them, etc. But also, generic query tools, no code to write to beat on the data, or dump out quick queries, just the SQL front end. Powerful. Capable. So, yea, I held them on a bit of a pedestal. And I can now just let one of those things, with untold modern capability and range, just sit idle on my machine. Just like I can leave a Calculator window open. Waiting for whenever I deign I need to work with it some. Extraordinary.
- CharlesW 3y ago
  > I have postgres on my mac. For any Mac users who don't know about it yet, Postgres.app is amazing: https://postgresapp.com/ https://postgresapp.com/
  - prmph 3y ago
    Can second this. I found it's simply the most straightforward, reliable, low-maintenance way to run Postgres on a mac. The only small hiccup I've encountered is that sometimes you get this error on starting up the Postgres server: "Failed to prune sessions". This error is fixed by running a command like below and then restarting: rm /Users/<user>/Library/Application\ Support/Postgres/var-14/postmaster.pi
irrational 3y ago
We were on Oracle for 15 years. Then the license costs became too burdensome, so we moved to Postgres. Though, it took us two years to make the move. Postgres is amazing compared to Oracle. Faster. More standards compliant. Better error messages. Far simpler to do backups and replication. Etc. Etc. It is quite astonishing how much better Postgres is than Oracle.
finnh 3y ago
The author should remove - or at the very least _credit_, come on - the image used in "The Pendulum of Database Realm" section. It's from Martin Kleppmann's "Designing Data-Intensive Applications", a particularly good O'Reilly book with illustrations by Rebecca Demarest.
eivanov89 3y ago
That's indeed amusing! I just published a post with title "When Postgres is not enough". It seems that the true distributed databases we have evaluated aren't quite Postgres's cup of tea: https://blog.ydb.tech/when-postgres-is-not-enough-performance-evaluation-of-postgresql-vs-distributed-dbmss-23bf39db2d31 https://blog.ydb.tech/when-postgres-is-not-enough-performanc...
da39a3ee 3y ago
Small thing but postgres should deprecate the names it uses for the CLI commands like "adduser" "createuser" etc; they should have a prefix like "pg-adduser" or more modern, be subcommands of a single "pg" or "pgctl" command. Has there been a move toward that yet?

108 points by janpio 3y ago

deleted 3y ago

[deleted]

edhelas 3y ago

Good

xnx 3y ago

Dupe: Postgres is eating the database world https://news.ycombinator.com/item?id=39711863 https://news.ycombinator.com/item?id=39711863 5 days ago 138 comments Edit: included the wrong link

ydant 3y ago
HN link to above: https://news.ycombinator.com/item?id=39711863 https://news.ycombinator.com/item?id=39711863

roynasser 3y ago

I feel like I've seen this more than I've seen my parents since year!

ralusek 3y ago

Love Postgres, but here's what I wish was different: RDS Proxy/PG Bouncer should be default connection behavior. Ideally no persistent connection at all, more akin to https would be great. Vacuuming is ridiculous. It doesn't make sense to me what could possibly take so long. It also doesn't make sense to me that it needs to be blocking (I understand that it's now parallelizable-ish). Using a comparatively slow interpreted language, I can iterate through millions of items, on disk, and do any number of things, within a few seconds at most. I have had databases with like, a few thousand items, somehow take hours upon hours to vacuum/analyze. Nested transactions would be great. I know there are savepoints but it doesn't work well when dealing with anything in parallel. And finally, my #1 complaint: Please let ME decide when to roll back/invalidate a transaction. If I want to write something like an upsert, maybe my code says "insert this record, and if I catch an unique constraint error, update the record." In Postgres, at the initial insert, because there's an error, it will just invalidate my transaction! I could have done 100 other things in this transaction so far, all invalidated because of a DB error. An error that I was expecting to catch and handle myself at the application level, and now the entire transaction needs to be rolled back. WHY?

koolba 3y ago
> RDS Proxy/PG Bouncer should be default connection behavior. Ideally no persistent connection at all, more akin to https would be great. That doesn't make sense. A database connection is inherently stateful as you run multiple commands in a transaction. > Vacuuming is ridiculous. It doesn't make sense to me what could possibly take so long. It also doesn't make sense to me that it needs to be blocking (I understand that it's now parallelizable-ish). Using a comparatively slow interpreted language, I can iterate through millions of items, on disk, and do any number of things, within a few seconds at most. I have had databases with like, a few thousand items, somehow take hours upon hours to vacuum/analyze. Routine vacuuming is not blocking (on VACUUM FULL to reclaim space is blocking). The entire storage approach has its warts, but works well for 99.99% of use cases. I'd argue that write amplification is a much larger problem. > Nested transactions would be great. I know there are savepoints but it doesn't work well when dealing with anything in parallel. What does it mean to work with a transaction in parallel? The A and I in ACID are for "Atomic" and "Isolation". > And finally, my #1 complaint: Please let ME decide when to roll back/invalidate a transaction. If I want to write something like an upsert, maybe my code says "insert this record, and if I catch an unique constraint error, update the record." In Postgres, at the initial insert, because there's an error, it will just invalidate my transaction! I could have done 100 other things in this transaction so far, all invalidated because of a DB error. An error that I was expecting to catch and handle myself at the application level, and now the entire transaction needs to be rolled back. WHY? That's exactly what using a SAVEPOINT does. The default of failing and trashing the connection state (until a ROLLBACK) is a sensible default. It also allows for command pipelining as you can send multiple commands and not worry about partial execution due to intermediate failure. If your application code is repeatedly failing then you should be fixing your application. There are many ways to perform consistent INSERT-or-UPDATE in PostgreSQL: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/current/sql-insert.html#SQL-...
kstrauser 3y ago
I agree about PGBouncer. You absolutely want persistent connections, though: establishing a TLS connection is comparatively costly and you don't want to pay it more than you need to. It's been maybe 15 years since I've waited for a vacuum to finish outside of me doing a `VACUUM FULL` on an offline copy as an experiment. It's had subtransactions for years. It has an exception clause so you can catch errors and roll back. In the absence of explicit exception handling, it must roll back a transaction instead of committing who-knows-what to disk. That's the whole point of transactions.

kstrauser 3y ago

Always has been. I've been using PostgreSQL for a decades, and I feel so spoiled. It always Just Works. Not to say there've never been bugs, but compared to anything else with that much surface area, it's a brilliant piece of engineering. It's astonishing how often it's a perfectly fine stand-in for the "right" solution. Need a K-V store to hold a bunch of JSON docs indexed by UUID? Fine. Want to make an append-only log DB? Why not. Should you do those things? Probably not, but unless you specifically need to architect for global-scale concurrent usage, it's likely to work out just fine. For me, it's the default place to stick data unless I have a specific requirement that only something else can meet. I've never once regretted using it to launch a production system, and only a couple of times have needed to migrate off of it due to performance demands. Thanks, PostgreSQL team! You rock.

ijidak 3y ago
I hear this often with regards to Postgre. Can't all of the above be said about Microsoft SQL Server as well? What prevents SQL Server from being used in the same cases you mention above?
- leosanchez 3y ago
  Licensing cost ?
  - kstrauser 3y ago
    Yep. I can use PostgreSQL anywhere I want, for any purpose I want, in any configuration I want, for free. That means I can use one stack from the tiniest of projects up through giant production systems, and also that I never have to wait for budget approval before scaling or launching new staging environments or spinning up a thousand test instances. For me, the onus is on any other DB to convince me that I should use it instead of PostgreSQL. The few times when that's been the case, it's been because we needed something other than a relational database for various specific reasons. At this point I can't think of many reasons I'd use anything else than psql that's in the same category. Like, I can imagine requirements that would send me to Snowflake or Redis or DynamoDB much more easily than things that would nudge me to SQL Server or even MariaDB.

whartung 3y ago

The magic of Postgres (and, quite arguably, MySQL, SQLite, etc.) is simply the idea that a sophisticated RDBMS is ubiquitous. I came from the Old Days when we had to chisel BASIC code into cooling silicon. Having something like a SQL RDBMS just sitting there, busy, or not, maybe just wasting away, ready for any weird nonsense you throw at it, is just a treasure. I have postgres on my mac. I've had postgres on my mac since I've had a Mac, so, what, 2006? I still have DBs on there that are now pushing 17 years old (after several PG version upgrades). I have the space, no reason to delete them. Just there. Old projects, strange experiments, idle. That I have this much capability languishing is amazing. SQL databases used to be a Big Deal. They were large step up from hand coding B-Tree indexes. I remember once we got a call from a client complaining about performance on a system we installed. We popped in, took a look around, and, yea, we dropped the ball. Not a single index was created on their system. It was just the tables. No wonder it was slowing down. 10 minutes of mad index creation later, all was well. If you weren't there in those days, it's remarkable that we had a system where indexes were (mostly) a performance thing, rather than a core thing the entire system was designed around. A paradigm shift in development. SQL DBs were amazing. They were also rare, and expensive. Custom libraries to access them, etc. But also, generic query tools, no code to write to beat on the data, or dump out quick queries, just the SQL front end. Powerful. Capable. So, yea, I held them on a bit of a pedestal. And I can now just let one of those things, with untold modern capability and range, just sit idle on my machine. Just like I can leave a Calculator window open. Waiting for whenever I deign I need to work with it some. Extraordinary.

CharlesW 3y ago
> I have postgres on my mac. For any Mac users who don't know about it yet, Postgres.app is amazing: https://postgresapp.com/ https://postgresapp.com/
- prmph 3y ago
  Can second this. I found it's simply the most straightforward, reliable, low-maintenance way to run Postgres on a mac. The only small hiccup I've encountered is that sometimes you get this error on starting up the Postgres server: "Failed to prune sessions". This error is fixed by running a command like below and then restarting: rm /Users/<user>/Library/Application\ Support/Postgres/var-14/postmaster.pi

irrational 3y ago

We were on Oracle for 15 years. Then the license costs became too burdensome, so we moved to Postgres. Though, it took us two years to make the move. Postgres is amazing compared to Oracle. Faster. More standards compliant. Better error messages. Far simpler to do backups and replication. Etc. Etc. It is quite astonishing how much better Postgres is than Oracle.

finnh 3y ago

The author should remove - or at the very least _credit_, come on - the image used in "The Pendulum of Database Realm" section. It's from Martin Kleppmann's "Designing Data-Intensive Applications", a particularly good O'Reilly book with illustrations by Rebecca Demarest.

eivanov89 3y ago

That's indeed amusing! I just published a post with title "When Postgres is not enough". It seems that the true distributed databases we have evaluated aren't quite Postgres's cup of tea: https://blog.ydb.tech/when-postgres-is-not-enough-performance-evaluation-of-postgresql-vs-distributed-dbmss-23bf39db2d31 https://blog.ydb.tech/when-postgres-is-not-enough-performanc...

da39a3ee 3y ago

Small thing but postgres should deprecate the names it uses for the CLI commands like "adduser" "createuser" etc; they should have a prefix like "pg-adduser" or more modern, be subcommands of a single "pg" or "pgctl" command. Has there been a move toward that yet?