PostgreSQL is eating the database world
pigsty.io
pigsty.io
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.
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?
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.
It's the ridiculous hoops you have to jump through to spin up a development insurance, a qa/unit test instance, to integrate with build pipelines, to run locally or in the cloud.
If you're trying to diagnose something really hard, like intransigent performance or debugging issues , and you need to spin up a near copy of production to run big tests, well have fun getting that by your license sever or the license audit.
- 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?
Was genuinely curious.
[0]https://www.trustedtechteam.com/collections/microsoft-sql-se...
> CALs offer businesses the flexibility to scale their client access as needed, accommodating a varying number of users or devices accessing SQL Server.
The gall required to write that sentence is astonishing.
Postgres and their org respects you, unlike MSFT.
I mean I'm a big fan of the tech but this seems like a stretch
it's not the case I think
As a 'side effect' of replication, PostgreSQL supports subscribing on change data. What you do with those changes is up to you. There are tools that make it easy to propagate those changes to message queues or other databases.
I totally agree that postgresql offers the foundational bricks that allow easy CDC though
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.
I worked with MSSQLServer 05/08/12 in some projects and the whole suite for the time being was one of the best related in terms of easiness to deploy plus due to the integrations with Integration/Analysis services you could deploy cubes and serving that directly on Excel and if you had some people creative in VBA you could reach a very sophisticated and effective way to provide reports.
I am huge fan of PostgreSQL, but back in 2005/2012 in all organisations that I worked would be very hard to justify 3+ extra FTE because someone was idealistically about Open Source a land costs.
It's a very complicated dimension, especially with databases from vendors that make you go through complicated licensing schemes and audits.
One thing that we need to take into consideration is back in 2005/2012 the ecosystem around the tooling/frameworks and RDBMS was quite fragmented and whether we like it or not, the non-tech industries had their procurements based on corporate vendors.
I was not a decision-maker at that time, but from what I saw, selling Open Source technologies with the addition of FTEs(OPEX) instead of relying on corporate vendors certified by the auditors and having those costs as CAPEX was a very tough sell. On top of that: several folks making hiring decisions back in time, found that it was way simpler to recruit people to work on SQLServer in a less plug&play stack integrated instead of doing a Jenga stack with several technologies that did not talk to each other and inject FTEs on.
Today the Open Source technologies are way more mature (I dare to say largely better) than those of corporate vendors.
I mean, I am sure there are companies with site licenses and it is easy to stand up a new microsoft stack, but I have never worked at any of them, for us it was always fighting with management and the purchasing department to get the licenses we needed. As none of us were really people persons this is exhausting. It was so much easier to stand up a service on linux in comparison. No bureaucracy, no delays just get it and go. So we pushed very hard into the linux and friends stack.
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).
SQL Server has arguably the best query planner/optimizer in the RDBMS space, and it also has tons of management tools and simple integrations in a Windows environment (tight AD integration) that admin very easy. If cost were no object and you need a DB that requires essentially zero babysitting after the initial setup, SQL Server is a better choice than PG.
TSQL takes some getting used to, but it allows for powerful/flexible stored procedures that can replace the vast majority of external business logic (for better or worse, depending on the application architecture.)
That said, PG is definitely more flexible with things like custom types and all the extensions you can add, and SQL Server's better query/clustered index performance isn't likely to make a huge difference in most workloads. The cost of SQL Server is also outrageous, especially run on large (CPU) instances. PG is usually a better choice for cost alone, but SQL Server is not an inferior DB by any means.
I understand there are still loads of enterprises stuck in the windows zone, but ... It's not ideal.
As for query performance, I doubt it id that significant... Well, unless you are constrained by a proprietary db license and approved CPU count / machine size and need to squeeze more from less.
Because if you are on postgres and are bumping into hardware limits, a slightly better query planner is a very short term bandaid.
At that point, you are likely a year past the point where you should take your biggest queries and refactor/architect scale them to dynamo or Cassandra or something like that.
I mean, bragging about active directory integration in 2024. It should be a dumb LDAP / identity provider,not some chain bonding you to Microsoft software in perpetuity.
However, assuming a non-cloud environment (on-prem), SQL Server Standard on a powerful server can go a very long way for about $7-10K in one-time licensing cost. This also buys you product support, which doesn't exist for free for PG.
It definitely has not always been.
https://www.quora.com/Why-is-MySQL-more-popular-than-Postgre... (2010)
It is great though, amazing piece of technology. Not 100% sure you need some specialized data store for your use case? (Most people aren’t). Pick Postgres. You can always switch to SpecializedDB later when pg falls over.
I cannot undersell enough how hard it is to be at being 90% good at everything. Like, you can pick Postgres for these technologies and not get fired for it at first glance and it will carry you to medium-large scale
- time series
- GIS
- Graph
- CRUD/ Jamstack apps (eg Supabase)
- Queues
- and on and on and on
If it even tangentially involves data you can trust that Postgres has some solution that will mostly work with some edge cases unless you are at disgusting levels of scale. Love it. A true jack of all trades
Building/running Postgres in Windows' Linux compatibility layer or within Docker is typically the better option, especially considering that every cloud vender offers Postgres running on a Linux OS, and it's best to have your dev environment match your deployment environment as closely as possible.
With Linux as your starting point, getting up and running with apt-get install mysql vs. apt-get install postgresql was trivially similar in 2010.
It's a fantastic product and I use it everywhere.
My major complaint is that there's no easy way to follow defect statuses because the team has long stubbornly refused to implement a bug tracker[1] and that means I can't just subscribe to a defect to get status updates for an issue I want to watch. Instead I'm expected to follow the entire mailing list or watch changelogs like a hawk. It's incredibly dumb.
[1]: https://www.postgresql.org/docs/current/bug-reporting.html#B...
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.
For any Mac users who don't know about it yet, Postgres.app is amazing: https://postgresapp.com/
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
Postgres is eating the database world
https://news.ycombinator.com/item?id=39711863
5 days ago 138 comments
Edit: included the wrong link
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?
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-...
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.
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?