For me, this project is the biggest FLOSS success story after Linux, xBSD and the GNU stuff. Great quality, amazing team. The best RDBMS that exists today and that includes commercial ones (imo).
"Before I start, a disclaimer: I Postgres, I’ve been a PG Applicative DBA for the better part of the last decade, and as a programmer I absolutely love the PG code base. . . It should also be said that Oracle’s products are insanely expensive, and yes, their business practices have been called into question quite a few times in the past.
Having said all that, and coming from many years of experience as both an Oracle DBA and PG DBA - comparing Oracle Database to Postgresql is almost like comparing a Cessna to an F-22. Only the F-22 can also transform into a Super Hercules and a helicopter.
Putting aside the usual issues of bugs and related incidents (both have had their fair share), the Oracle Database is a technological marvel. I can list off of the top of my head a number of capabilities Oracle has had for almost 20 years that PG either doesn’t have or just might have in the near future:
1. On commit fast-refresh materialized views,
2. Query Rewrite to Materialized Views,
3. Direct and asynchronous I/O,
4. Parallel Query Execution,
5. Oracle RAC - Multi Master without Replication,
6. Zero downtime upgrade,
7. No autovacuums,
8. Flashback,
9. Integrated In-Memory Database,
10. Autonomous Indexing,
"
etc.
The Reddit post is a good summary and worth a read, imho.
I've been working with PostgreSQL professionally for almost 20 years, I also worked professionally with Oracle for around 10 with some overlap between those time frames.
I can vouch for your comment largely. I love PostgreSQL and expect to work with it pretty much exclusively for professional RDBMS needs into a fairly distant future (with perhaps some SQL Server)... but there are features that were in Oracle back in the earlier 00's that PostgreSQL is only within the past few years catching up to. Partitioning, materialized views, etc. even though PostgreSQL is got these things now, the features are still not a feature-full and mature as they were in Oracle back then.
And speaking of SQL Server, the tooling and reporting capabilities there are something that I haven't seen achieved in the PostgreSQL ecosystem yet (to the point where my go-to client interface is still good old psql for lack of anything really better).
So, I think the kudos coming to the PostgreSQL team whenever a post like this surfaces here and elsewhere is definitely earned. But perspective on how far there is left to go is warranted and should be exciting to those that are building the future of PostgreSQL... that not all problems are solved and that you're not just there to oil the cogs laid down by generations past.
That is an important comment. I really like Postgres, as it is the only free RGDB allowing complex SQL (MySQL is improving), and is also reliable, but some people need to realize that the F22/Cessna comparison is not exagerated.
I professionally use both Postgres and Oracle, for many years, and can confirm that Oracle is so much more advanced.
In addition to the features you listed, I can think of : real-time query monitoring, active session history, inline function definition in SQL, global partitioned indexes, in-memory column store, much more advanced parallel execution capabilities, adaptive plans during query execution, bloom-filters, optimizer baselines, SQL macros, Exadata for tuning at the hardware level, Golden Gate for replication, Enterprise manager for monitoring...and a lot more actually.
This post shows how developers (and people in general) have a difficult time with emotional forbearance when it comes to objective technological critique and being able to put aside (and recognize) personal bias.
The author had to put so much upfront to satiate and calm the crowd before embarking on his review.
We shouldn't have to do that.
We should operate with surgical precision on the way we argue and judge others. Try to double-check when we assume something of others. That does not mean we shouldn't talk about how unethical Oracle is, just that those two discussions can take place simultaneously without drama.
The more senior I become in my career and the older I get, the more I realize that the entire world operates like this. It is grim.
And to use most of these you need a Oracle consultant for 120 per hour and a 30k per cpu upgrade license.
These guys are professional robbers. They bribed Toronto city hall policitians for a 30 million dollar thing including a well known hockey player.
If you are abstracting the SQL into an ORM you can't take advantage of many of the features anyway.
Was involved in a 10,000 tps payment solution that used Postgresql to grow a 400 million company. And worked at banks where they would pay for oracle and never use half of the features. Relying on hardware based solutions for fail over and replication.
If it's attempting to leverage many Oracle features, there might be some other good solutions to the problem as well - people don't have to use RDBMS to solve everything.
But, I was told (by Cantrill and echoed by HN), that Oracle is a rotten company that "ships mediocrity"?
Do they actually have the best RDBMS in the industry in terms of performance and feature set? (I guess this is a hard thing to answer given how these things can be fairly subjective)
Maybe the real problem is that it's better, but still not worth the cost (both money, and the cost of being an Oracle customer).
Name me one VM that is remotely as advance as GraalVM.
I dont like Oracle as a business. People hate them for suing Google on open source / Copyright and API. But one Google is not without its fault in the case and two, whether you like or hate a company has nothing to do with its technical capability.
It seems in the tech industry, some company can do no wrong, and some company can do no right.
I have dealt with Oracle in the enterprise space, and endured their audit-turned-blackmail monetization strategies. They have a mixed bag of tech, some excellent, some mediocre - but their business practices are absolutely revolting and predatory.
I also love postgresql but lets not get ahead of ourselves! It's great for SQL and as a traditional relational database, but there are plenty of use cases that are better served using other models (especially if you care about _time_ as a concept, then Datomic or crux is probably better suited) [although there are attempts on making bitemporality accessible in postgresql too, like https://github.com/scalegenius/pg_bitemporal]
There is a professionally developed time series extension for Postgres. I haven’t tried it personally but it might be “good enough” for quite a few use cases just like Postgres’ full text search can be “good enough” without having to bring in the machine gun that is ElasticSearch/Solr.
I brought timescale (a postgres extension that brings time series to postgres) to my current IoT job, and it has been the biggest engineering win of my career. The tooling, community, extensions, and quality of postgres + very high performance inserts + reads has reduced costs for the business dramatically versus the previous cassandra + sql server setup. We have tens of billions of events in multiple timescale hypertables, with many thousands of inserts per second running on r5.xl nodes in k8s (4 vcpu, 30gb ram) and they don't break a sweat.
Right, and it's probably "good enough" for most use cases. But, as always, it can't cover _everything_. I'm just making an argument against the sweeping argument that "postgresql is for everyone and everything"
Sorry for the typo. I was just reading the thread on my mobile while walking, my clear mistake.
However it's obvious the well-meant on the thread above.
Most of the core team is employed by enterprise Postgres companies (EnterpriseDB, Crunchy Data, 2nd Quadrant, Citus Data). I think they're allowed to put a portion of their workweek towards Postgres.
> Anyone can share what makes PostgreSQL so successful? Maybe other projects can learn adopt a few of these good practices.
Since honesty is a theme of this thread, let's be real here. PgSQL is popular now only because Oracle killed MySQL.
This is not to denigrate the work of the Postgres team -- it's easily one of the best-managed open-source projects and my admiration for them is immense -- but ~95% of the time, the tech stack is chosen as a fashion statement.
MySQL was fashionable until Oracle showed up. Postgres has been trucking along for decades and has essentially won by default. I don't know if there are many great marketing insights to take away, other than "stay out of the way of the $50B gorilla".
Absolutely don't agree. Sure, Mysql mostly became very popular with the rise of PHP in the early 2000's, but Postgres was popular in many less visible industries.
In my 'world', MySQL was seen as an amateur database engine, and never really lost that label. There was some serious hesitation towards Postgres back-then, but eventually it became a good alternative to the commercial RDBMS's. I know we eventually switched everything over. Until the mid 2000's - our server-side software was still sold as a 'blackbox', managed by us, with no mention of it running linux and postgres, but eventually these became more accepted technologies, and towards many of our clients even a selling point. The rise of Redhat in the enterpricy world helped a lot with that.
Yes, Postgres adoption and development really picked up steam once Oracle acquired MySQL and quickly confirmed everybody's fears: they bought it primarily to ensure that MySQL stayed something of a "toy" database.
But the "fashion" thing is needless, insulting, and not even correct in my opinion. As far as mindless fashion trends in the industry go, traditional RDBMS's like Postgres are almost the antithesis of fashion.
I should clarify. I'm not saying that Postgres is unworthy or that its adoption is fashion-driven -- it's part of the 5-10% that is driven by practicality. That is only the case now because, as you said, there is no fashionable conventional RDBMS anymore. It's all about transferring as much money as humanly possible to Jeff Bezos now. :)
The grandparent was asking what we can learn from Postgres in terms of driving adoption, as I understood it. And the reality is that most of Postgres's recent adoption uptick is because there really aren't many other options. It's not a good example of driving adoption in a crowded market, etc.
My apologies for being unclear before, and thanks for pointing it out so I can try to clarify.
Personally, I think MySQL died when Oracle got involved with it. The momentum was sucked right out of the project.
The great think about Postgres is it has huge momentum and the team are delivering new features. Maybe Apache has less momentum because it's matured and doesn't need much momentum.
From a momentum point of view I see Postgres as the biggest FLOSS project. I see no point in choosing another RDBMS right now. Time to short ORA stock
> Personally, I think MySQL died when Oracle got involved with it. The momentum was sucked right out of the project.
This statement is completely baseless, and you clearly haven't followed the MySQL developments of the last 10 years. It's ironic that around the time Sun was acquired, MyISAM was still a thing; MySQL has gone a long way since then.
Independently of a value judgement of "how good PGSQL/MySQL are" (I'm not making any implication in this sense), MySQL's development has been proceeding steadily, with no particular change (on the engineering side, at least). Oracle has actually put considerable effort both on the engineering and marketing side, of the 8.0 release (but again, I don't make any implication about the value of the products per se).
As much as I vastly prefer PostgreSQL, I will tell you that MySQL is much more preferred in enterprise settings, probably 8 to 1 in the environments I've seen.
> As much as I vastly prefer PostgreSQL, I will tell you that MySQL is much more preferred in enterprise settings, probably 8 to 1 in the environments I've seen.
That's largely because enterprises often have big investments in SQL server, OracleDB, and/or DB2, and are only using open source engines for more lightweight purposes, and/or as part of cloud transitions where they are just taking vendor default options or whatever options was supported when they came on or longest.
At least, that's my experience working in enterprise and being literally the single voice urging even considering pros and cons before using MySQL-by-default with no particular rationale in a transition effort (which resulted in us using Postgres.)
I work as a consultant in the enterprise space, and I've seen a shift in recent years towards Postgres.
I believe this has largely been driven by cloud - developers are now more able to choose the components they want to work with, instead of being told what to work with. There are of course plenty enterprises that are strict here, mandating cloud hosted SQL Server for example, but the general relaxation of constraints has struck me as a very pleasant surprise.
I've yet to come across MySQL being used in a cloud-based system, but I'm seeing Postgres more and more. When I do see MySQL, it's part of on-prem services that are considered "legacy".
> That's likely a holdover from when mysql had a better replication story than postgresql.
What is the Galera-equivalent in the Postgres world? There's BDR, but the latest versions are closed source.
We use Galera in a bunch of places because it's fairly straight forward to get an HA cluster going, and with keepalived, we can point a front-end to a vIP that fails over automatically if one system goes sideways.
afaik, MySQL never had a better replication story than PostgreSQL, you've always had to use Percona's add-ons to get something workable. Even then (many years ago), we had multiple data loss incidents that were precipitated by widespread internal confusion over the bizarre intricacies of `binlog_format`.
Like most software that gains adoption, MySQL made some very broad claims about their software's capabilities and never really delivered on them, at least not in a way that would be considered production-ready by Serious Persons(TM).
That's the crux of MySQL v. PgSQL, Linux v. BSD, etc. Good engineers spend their time building good software and are generally too focused on that to spend much time going around and making outlandish promises. Postgres has benefited from Oracle's intentionally-bad stewardship over MySQL, but it doesn't usually work that way.
I've experienced this as well, and it's almost always because the tooling for MySQL is "better." I don't necessarily agree, but Sequel Ace (formerly Sequel Pro) is hard to beat.
The DB support in Jetbrains IDEA is awesome, they also have it as a stand alone IDE called Data Grip, could be woth a shot? https://www.jetbrains.com/datagrip/
Do people ever look at how much more they're paying for Aurora? I've saved companies thousands of dollars per month just by switching them to standard RDS instances for systems that see tiny load and were only on Aurora because that's "the super good AWS-optimized version, duh".
The added benefit of this is that you then know what software you're running, instead of Amazon's fly-by-night hackjob. We've hit several real-world bugs that were either incorrect backports of something that had been patched years before upstream, or wholly new issues caused by whatever they're doing to make Aurora auroar.
ORA is a services/consulting company, now. The DB is important but company will easily survive its death (which will never happen) look at IBM big iron for similar story.
I think MySQL is still significantly more widely deployed. And Postresql wouldn't be ahead of things like Firefox, apache, etc in terms of successful FLOSS. The world wouldn't really be that much different if postgres didn't exist. But it would be significantly worse if Firefox or apache never existed
can be swapped out for another floss database pretty easy without any real impact. internet in general would be significantly worse right now if FF or apache never took off
Measuring the importance of Postgres is tricky. I’ve noticed that a lot of new DB services use Postgres tech at some level. Cockroach DB is a good example and arguably a pretty important project.
I don't know why this comment is downvoted so much. Every company I've worked for uses MySQL (or a MySQL fork, eg. MariaDB or Percona). I've never seen an installation of Postgres used in production at any company I've worked for. I've been waiting for a decade to start using it, but there have always been three issues that made Postgres an unacceptable choice for my coworkers and me, at every single company I've worked for:
1. The lack of built-in replication, requiring third-party plugins based on slow triggers, etc. Postgres finally added, after many years, off-the-shelf replication, resolving this concern.
2. No equivalent to MySQL's "utf8mb4_unicode_ci" or "utf8mb4_unicode_520_ci". Postgres finally added, after many years, the "und-x-icu" collation, resolving this concern.
3. Lack of a multi-threaded model, meaning every new client connection forks a new child process, utterly decimating performance and scaling. Expecting users to throw a connection pool/proxy server in front of every Postgres installation to work around that bottleneck is unacceptable; a proper multi-threaded model needs to be provided off-the-shelf. The developers continue to fight back on this issue, and the fact is they are wrong.
If and when that final point is resolved, I might finally be able to put together a proposal showing all of the benefits and features present in Postgres compared to MySQL; and with those above three points all resolved, I might actually be able to gain traction and support with the other senior developers, and win a plea to management.
> I've never seen an installation of Postgres used in production at any company I've worked for.
many well known companies use postgresql in forward facing and internal systems. my company during 911 led the deployment of critical paging infrastructure run on postgresql.
> To make it easier to take advantage of PostgreSQL’s extensibility, PostgreSQL 13 adds the concept of a "trusted extension," which allows database users to install extensions that a superuser marks as "trusted." Certain built-in extensions are marked as trusted by default, including pgcrypto, tablefunc, hstore, and more.
Yep. Every time I've tried to run pg_restore as a non-superuser (e.g. against a DBaaS instance, where only the cloud-host's ops staff has true superuser privileges), the inclusion of CREATE EXTENSION commands in the restore stream blocks the whole process. IIRC they added a specific flag in pg_restore to skip CREATE EXTENSION calls, just so people could do this. Now – given that in DBaaS environments, "trusted" extensions would be the only ones exposed to the user anyway — you'll likely be able to pg_restore as an arbitrary user in these envs with ease!
But that's not the only gain from this. The need to be a superuser to CREATE EXTENSION was the last thing standing in the way of many SQL client applications initializing their own schema objects (i.e. running DDL migrations) as a limited user (e.g. one with DDL privileges limited to a particular schema.)
-----
This is the first time that I feel PG is maybe approaching the point where users can truly "do their own thing" within the DB, without a superuser-privileged account interceding to help them along.
As such, PG13 will be a big boon for DB data security... a few versions from now, when it's old enough to be mandated as the minimum requirement for these stacks :)
Right now, apps are either deployed to use a PG user with ~full privileges over the DB (even if it's not technically a superuser), where migrations are run automatically on runtime startup, as the same account that accesses data at runtime (exploit vector!); or apps are deployed to use a DML-only user in prod, but migrations get run manually by ops staff under a separate, fully-privileged user/superuser.
I bet that, once people start to depend on this change, we'll start to see applications that offer the ability to configure them with one migration-time DB user, and a separate runtime DB user; neither of which are fully-privileged; and where the migration-running happens in a separate context (e.g. as a sidecar container) designed to allow migrations to be run automatically, while isolating the migration-time DB creds from the runtime environment.
One cool feature is LIMIT WITH TIES so that if there are more rows with the same value sorted on by what would have been the last row, it shows all these rows of the same value.
At my last job, I ran about 50 servers running Oracle Standard Edition. (Enterprise Edition would have taken each $25k license to something like $600k for our dual core servers). I was Sysadmin, not a developer, so I have different pain points.
I will never look back. There were so many things that we wanted to do, but were 'enterprise only' features. Like rebuild/create indexes online. We had a DB with a 1TB table. rebuilding that, while locking the table, would mean something like a 14 hour outage. With postgresql, online index creation is right there.
Oracle didn't support a proper incremental backup, except in enterprise edition. (and RMAN was a pain in the ass of arcane commands and black magic). pgBarman makes backups so simple for us.
Standby's on Oracle are good for disaster recovery. But you have to manually ship the logs, apply them, monitor it, and delete old logs. There were multiple thousand line korn shell scripts we utilized, and lots of cronjobs. With Postgresql, its stream based, and my standby follows production by less than 100ms most of the time. Plus, I can connect to the standby, and run read-only queries on it (we run large reports on standby, so we don't affect production) That is also possible in Oracle, with 'enterprise' edition (data-gaurd).
We filed a few ticket with Oracle Support. We never got any answers. I don't think we ever got an support person at Oracle that had been there more than a year. Even when we escalated.
Pretty easy to find "support" for Postgres too and more convenient. e.g. submit a request on CodeMentor and you'll have you several solid devs willing to help you, live, in a matter of minutes.
Well, there's the old IBM adage, adapted: nobody has ever gotten fired for choosing oracle. And within some industries that might be true.
Anecdotally, I've met someone who said his interview with a startup went completely downhill when he suggested using oracle. Can't say I blame a startup for shutting down that idea immediately.
This is fantastic! Love the parallel vacuum feature, which should help my team’s sanity with a write-heavy workload.
Does anyone know when the zheap storage system will be released? This system will apparently obviate the need for vacuums all together since old row version information will be kept in an undo log instead of in the heap. This will be a game changer for us.
It's a bit more complicated than that. You will also notice another release note item in PG13 that says "Allow inserts, not only updates and deletes, to trigger vacuuming activity in autovacuum", which is because vacuum is not only about cleaning up deleted or rolled-back rows, but also transaction ID wraparound. Making that go away is also a dream of many, but it's a different (or additional) project than a new storage system.
There are a few projects I absolutely could not live a professional life without: PostgreSQL is one, SQLite is another. All my appreciation and congratulations on a great release!
Are there any PG providers that allow you to do a seamless upgrade, from, say, 12 -> 13? I know logical replication allows you to do this, but with some caveats. Curious what other people's experience is.
AWS supports relatively seamless (but not automatic) major version upgrades, though because the database engine may have backward compatibility issues they recommend (but don't require) you do a lot of steps which make it less-than-seamless but safer.
They don't try to automatically handle breaking changes for you.
You don't go from zero to mastery in one book. Three years minimum, double or triple that if you're doing other stuff as well.
To start, Stepháne Faroult's SQL Success will give you a solid grounding in using RDBMSes as an application programmer. It's not Postgres specific, but the learning you need to do isn't tied to one vendor.
But as sibling said, you'll be using the documentation as well. PostgreSQL has documentation that is nearly up to the standards that hardware engineers expect and rely on.
Adding to the above, Schönig's Mastering PostgreSQL 11 will help you advance from intermediate to high-intermediate PostgreSQL programmer, as will Faroult's The Art of SQL. Karwin's SQL Antipatterns is useful supplementary reading also.
I've looked through my kindle, and I wouldn't bother with much else.
How do people keep track on all the features that are added in RDMS? When you know the basic stuff like SELECT / JOIN / indexes ect ... how do you stay relevent with new things added?
I work for a fintech company and I'm not much of a SQL guy but I often hear how we've reached some limit of what PostgreSQL can do, and every update is a huge deal, even the patch versions.
So I guess it's tech like any other. The more you know and use, the more you're aware of the recent developments. Like pretend you know enough javascript to build small promotional websites with some animations as you scroll down the page. You probably don't care, and are not aware, of the recent developments around optional chaining operators.
Not a direct answer to your question, but there are two ways that I usually learn about new Postgres features.
1. Through blog posts like this
2. Needing something in the moment and googling.
To elaborate on #2, I learned about partial indices when I was at a situation at work when I needed to optimize indices for a heavily used table. Google eventually brought me to their excellent docs and SO posts that mentioned them.
New features often come handy if you have a very specific need and usually you don't really need them at all and you can stick to common-SQL features. You often discover them when you search online looking for some blogs and stackoverflow threads, and then someone mentioned "you can get this done using XYZ in psql", and then you start reading what's that about.
read the table of contents of the RDBMS you use. twice. do it again next day first thing.
then read the chapters you didn't understand the title or haven't heard about the concept. this might take a while if you only know about select and join and create index - abstractions SQL provides are more leaky than rusty buckets and the manual is there to tell you everything about these and how to work with them.
then it's 'just' a matter of keeping up with the delta by reading release notes.
You don't necessarily need to. You might just transparently use them in a lot of cases. In others you'll just read the docs when you need to do something specific.
I heard someone say the other day that they would skip this version and wait for the next one. Just because of the version number. I found it somewhat sad and very poor judgment, but after thinking about it on my way to home, I think a lot of people will do the same.
It would be interesting to keep an eye on the number of downloads from containers and packaging systems to see if this is the case.
Many of those users have equal or greater lunancy just with different numbers... Including buying or not buying specific houses due to the address or physical layout etc etc.
I don't know why mrmonkeyman's reply on Chinese having something against the number 4 was downvoted into oblivion although I suspect it was because of the all-to-common knee-jerk reaction against 'racism'. What he said is true, 4 is the most unlucky number in Chinese because it sounds like the word for 'death'. Buildings in China often don’t have a 4th floor, some of them skip all floor numbers which contain '4' so they don't have a 14th or 24th etc. floor. The number 13 has also gained an ominous reputation in China so it is unlikely that help will arrive from China to combat any 13-related superstitions.
PostgreSQL v8, v8.8 or (in the far far future) v88 will be popular though since 8 is the most lucky number - it sounds like the word for 'wealth'.
I've never not installed software because of the version number.
But this made me realize that Oracle skipped from database version 12 to 18, and eBS from 12 to fusion.
I would say this is more of a reflection of an organization's motivations. Postgres doesn't have the same marketing and commercial motivations as Oracle.
Thank you.