[HN Gopher] PostgreSQL is eating the database world
___________________________________________________________________
PostgreSQL is eating the database world
Author : janpio
Score : 59 points
Date : 2024-03-19 19:45 UTC (3 hours ago)
(HTM) web link (pigsty.io)
(TXT) w3m dump (pigsty.io)
| edhelas wrote:
| Good
| xnx wrote:
| Dupe:
|
| Postgres is eating the database world
|
| https://news.ycombinator.com/item?id=39711863
|
| 5 days ago 138 comments
|
| Edit: included the wrong link
| ydant wrote:
| HN link to above: https://news.ycombinator.com/item?id=39711863
| roynasser wrote:
| I feel like I've seen this more than I've seen my parents since
| year!
| ralusek wrote:
| 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 wrote:
| > 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-...
| kstrauser wrote:
| 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.
| mdavidn wrote:
| You should read the documentation for INSERT ... ON CONFLICT.
|
| https://www.postgresql.org/docs/current/sql-insert.html#SQL-...
|
| I'm not sure what's happening with your VACUUM. It does not
| lock the table without the FULL parameter. Or perhaps your
| tables have too many indexes?
| kstrauser wrote:
| 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 wrote:
| 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 wrote:
| Licensing cost ?
| kstrauser wrote:
| 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.
| datavirtue wrote:
| Eye watering licensing costs. A small company I was at
| increased the number of cores on the SQL Server VMs, the CFO
| pissed off and fired a few DBAs, Microsoft audit ensues, and
| we had to find an extra $1MM in the budget for SQL Server
| licensing.
| wiredfool wrote:
| Licensing per core makes it difficult to really take over the
| world.
| FridgeSeal wrote:
| - cost
|
| - only recently runs on Linux
|
| - cost
|
| - all sorts of MSSQL specific features and syntax (@@ is
| unhinged and you can't convince me otherwise)
|
| - Postgres docs are better
|
| - Postgres has a massive ecosystem of extensions (see PostGIS
| alone!)
|
| - did I mention the cost?
|
| - Postgres has wider range language support: basically every
| language I've ever used has a PG library. Not the case for
| MSSQL. Additionally some of the MSSQL libs are real bad, the
| Python one is basically like "use this ancient odbc lib lol"
| it's great from .net and awful from everywhere else.
|
| - licensing and running costs, because these cannot be
| overstated.
|
| - features like CDC locked behind _expensive_ licenses, that
| you get out of the box with Postgres.
|
| Need I say more?
| ijidak wrote:
| Got it. Appreciate that. That makes sense.
|
| Was genuinely curious.
| willcipriano wrote:
| On the cost front do you still need a cal[0] for every
| user/device?
|
| [0]https://www.trustedtechteam.com/collections/microsoft-
| sql-se...
| mixmastamyk wrote:
| Buried one of the most important reasons--FLOSS.
|
| Postgres respects you, unlike MSFT.
| VeejayRampay wrote:
| since when do you have CDC out of the box with PG though?
|
| I mean I'm a big fan of the tech but this seems like a
| stretch
| bananapub wrote:
| this comment seems unrelated to the comment you're
| replying to - they didn't say it included CDC, they said
| that if you want it on MS SQL it will cost lots of money.
| MissTake wrote:
| _Need I say more?_
|
| Yeah - cost. :)
|
| Cost is the reason I'm moving everything I can off our
| existing MSSql Servers before our next renewal comes up in
| a years time.
|
| We did our due diligence and tested all manner of existing
| expensive queries and whilst there are a handful we just
| can't get to run as fast as MSSql Server on Postgres, they
| only run slightly longer on average (as in 9 or 10 minutes
| as opposed to 6 or 7) and only at night.
|
| However, given the cost differential ($50k+ vs $0), we can
| live with this.
| marcosdumay wrote:
| > Can't all of the above be said about Microsoft SQL Server
| as well?
|
| No.
|
| MS SQL doesn't just keep working, needs real hardware to run,
| doesn't handle noSql work anywhere as well, and has many
| small bugs that pop-up here or there. Besides, it's a quite
| visible expense - that would be ok if it gained you anything.
|
| And just as impacting but on a different dimension, it
| requires much more query optimization, and its language is
| just awful when compared to Postgres (even though it's
| probably the next best thing out there).
| whartung wrote:
| 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.
| irrational wrote:
| 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 wrote:
| 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.
___________________________________________________________________
(page generated 2024-03-19 23:01 UTC)