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.
Postgres wasn't even on my radar until the 9.1 release (2011) rounded out their replication story. MySQL replication had been baking for a decade at that point, despite its other glaring flaws.
Even today, Postgres on Windows isn't a viable target for performance reasons. Postgres is one process per connection. Spawning processes on Windows is slow compared to Unix.
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.
- 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.
> 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).
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.
I would caveat the "Just Works" thing for your specific use-cases. I've seen plenty where PG's query planner falls apart and requires copious coaxing beyond what Oracle, DB2, and MSSQL require in order to do the performant thing.
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.
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.
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:
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.
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.
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?
Readit News
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.
It definitely has not always been.
https://www.quora.com/Why-is-MySQL-more-popular-than-Postgre... (2010)
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.
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?
- 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?
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).
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