PostgreSQL is the worlds’ best database
The title is not clickbait or hyperbole. I intend to prove that by virtue of both design and implementation that PostgreSQL is objectively and measurably a better database than anything currently available, with or without money considerations.
How in the world can I claim and justify such a lofty statement? Read on, gentle nerd. I promise that your time will not be wasted.
Transparent Security
PostgreSQL has a security mailing list. The PostgreSQL project learns about the intrusion vectors at the same time that everybody else does. Nothing is hidden, and anything that is found is worked on at a rate that makes the commercial vendors’ heads spin. Don’t be fooled by shorter defect lists published by the same vendor that provides the software under scrutiny.
This means that all known attack vectors are handled as soon as they are made public. This kind of security responsiveness is not even contemplatable in the commercial market. For commercial vendors, secrecy until the problem can be addressed is vital to the remediation. PostgreSQL gets no such relief, and that’s fantastic for you.
Multi-version Concurrency Control is good for you
PostgreSQL picks a method of concurrency control that works best for high INSERT and SELECT workloads.
It is very easy to design for PostgreSQL, keeping the limitations in mind for the tracking overhead for
UPDATE and DELETE. Mostly, if you respect your data, you should learn to love the data security that PostgreSQL affords you .
DDL participates in transactions using PostgreSQL. Migrations work all the way or none of the way (the worst kind of not working is almost working). Testing harnesses are dead easy to build. Need to reset the testing harness? Just ROLLBACK.
PostgreSQL supports standards compliant forms of transaction isolation, including serialization, read committed, and repeatable read. These methods provide complete ACID compliance.
PostgreSQL does everything
So, you want NoSQL, Riak, REACT, Redis, Mongo, etc.? PostgreSQL does all that. Admittedly not with all the bells and whistles of all of the original products. For example, PostgreSQL doesn’t create new shards for you for any of those. That’s still a manual process. But then again, there’s always pg_partman. . .
You want a column data store? How about hstore? You don’t want to retrain your staff? Plug in the language of your choice and keep trucking. You want partial replication? Streaming Logical replication is for you.
I would have a hard time thinking of a feature that I want that PostgreSQL doesn’t have, or that there isn’t a well known extension to provide.
You want to extract data from other systems? PostgreSQL has the most vibrant collection of federation objects of any database. They call them foreign data wrappers, and you can hook PostgreSQL to an alligator with duct tape and zip ties. Treat anything like it’s your data.
Hook it to a map
The PostGIS community may arguably be bigger than the PostgreSQL community itself. The mapping capabilities of PostgreSQL put it in a class by itself, even compared to very expensive alternatives.
The PostGIS project picked PostgreSQL as a platform because of the ease of extensibility and the extensive data enrichment capabilities. These capabilities are directly exposed for any other project to take advantage of. They are also unanswered by any other vendor, commercial or open source.
Ultimately, you can hook it to anything.
PostgreSQL is growing and leading the way in open source
The PostgreSQL project is one of the most highly visible organizations in open source software. With a huge community and growing at an astronomical rate, any deficiencies that it has now will arguably be defeated in a time frame that other vendors can only dream about.
Additional enterprise quality features are announced literally every day, and the staff to maintain those features is self-selected from a pool of geniuses that every company is hoping to hire, and there just aren’t enough to go around.
PostgreSQL builds solutions that are stable forever
PostgreSQL has logical replication built in to the core. This allows for cross-version migrations. Read that again. You’re not locked in to a specific hardware or software version. The solution can be upgraded indefinitely.
Also, PostgreSQL is supported on many platforms, including the super-stable versions of Linux. Do you need a solution that outlives the typical 3~5 year ROI? PostgreSQL will last you forever, even if you never upgrade the hardware at all. And the fees for that are easy to calculate. $0.
Declarative is better than imperative
Database languages are generally declarative. That is, you write a query in the built in language of your choice, describing the results that you would like to see. The database tries to decode your intentions, and provide the appropriate results. This is the basis of all declarative programming models. In PostgreSQL, this comes down to a mapping of functions to keywords in the SQL language, sometimes with several algorithmic choices for exactly how to implement each declaration.
In the age old argument about the imperitive vs. the declarative5 programming models, it occurs to me that declarative programming is just imperative programming in a thin disguise. Each declarative token in a database query language ultimately maps to one or several algorithms which apply the declaration in imperitive terms. Thus, the impedance mismatch defined by Henrietta is ultimately in the mind of the developer. That is, if the developer would think exactly like the database function programmer thinks, then there would be no mismatch.
So how could a declarative model ultimately be better than an imperitive model, given that one is just a calling feature of the other? Glad you asked, because that brings me to my point.
The PostgreSQL developers are smarter than you. I don’t mean that to be facetious or coy. Literally thousands of contributors have made millions of contributions to the PostgreSQL project, many of them as improvements to the contributions of others. The chances that whatever you thought up on the top of your head is better than what has already been implemented is very low. And, even if your thoughts were better, you should contribute them to the PostgreSQL project for the benefit of all, thus raising the bar for everyone else.
So, what makes PostgreSQL so wonderful then? Worldwide mindshare without corporate considerations. Thousands of developers are working hundreds of thousands of hours to make better algorithmic choices. So your software gets better every release, most usually without having to do anything in particular on your part.
Isn’t that the nature of software in general, you say? Well, yes. But not to anywhere near the extent that it is when the entire world is involved in your project. PostgreSQL enjoys a very prominent place in the open source community. Commercial vendors will never be able to keep up with the rate of change that an open source project can provide at this level. The migrations to open source (and particularly PostgreSQL) are here to prove it.
The features keep rolling in. There are very few things left that commercial vendors can point to as a distinct advantage. Among those things are SMP support, bi-directional replication and external tools. Guess what the community is working on now, and will very likely release in the next few years?
Extend PostgreSQL any way you like
PostgreSQL has a vibrant community of authors that write ancillary software. This includes plugging in any language that you like, and using it to extend PostgreSQL in any way that seems helpful. Do you happen to like perl string handling? Ok, then use that. How about Python map support? Sure, just plug in python and go to town. Want to write web services using a PostgreSQL back end? That’s awesome, and PostgreSQL will help. JSON? Ok. XML? You bet. PostgreSQL has direct support for all of that and infinitely more. If you can think of a language that does it well, then plug that in to PostgreSQL and you can have it on the server side.
You can create your own functions, data types, operators, aggregates, window functions or pretty much anything else. Don’t see a feature you like? Plagiarize and customize it from the source code. You’re free to do that because of the license.
PostgreSQL also provides some hook functions that allow you to extend the database without having to go to programming extremes.
This ability to assimilate any feature of any other language is unique to PostgreSQL. You can provide any feature using any standardized library in existence. You can follow the standards, keep up with changes, still update PostgreSQL while it’s alive, and you can do it all for free.
Go big and go wide
PostgreSQL has several features to make the most of the hardware platform that it’s been given. Parti- tioning, parallel execution, partial indexes, tablespaces, cacheing, and parallel non-blocking maintenance routines (almost everything in PostgreSQL is sprouting the CONCURRENTLY keyword lately).
When that isn’t enough for you, then physical streaming replication will make a bunch of read standby for you on the cheap. Sharding, memcache, queueing, load balancing and connection pooling all work with PostgreSQL. Still not enough? How about logical streaming replication? You want to geoshard the database all over the world, you say? Well, welcome to bi-directional replication.
And the price tag is still at $0.
Index all that
PostgreSQL supports such a huge list of indexes that it boggles the mind to figure out how to use them all. GiST, SP-Gist, KNN Gist, GIN, BRIN, and Btree are all available. And there are more to be had through the extension system, like Bloom filters and others.
PostgreSQL can use these with function driven indexes, partial indexes, covering indexes and full text search. And these extended features are not mutually exclusive. You can use them all at the same time.
Roll it in, Roll it out
Several of the technologies already mentioned make PostgreSQL a fantastic data integration and distribution platform. Multiple forms of replication, combined with multiple forms of federation provide both push and pull technologies for nearly any kind of data system.
These can be combined in infinite configurations to bridge database storage solutions. All that without requiring any ETL/ELT processing package. PostgreSQL just does it. The fastest single source of truth database on the planet does it by not moving the data out of the source system at all. This means that the data is always current, and the response times can be managed.
If you can’t stand the unreliability of the source system or would like a bit better performance on the query side, you can also still cache the data periodically with materialized views, which can be updated while still being queried.
The license is wide open
PostgreSQL has it’s own license that is largely based on the BSD license. This allows for even greater freedom of use and distribution.
The license applies to all of the code of the main project, major contribution extensions, client libraries, connection managers, and most of the associated tools.
It is highly permissive, written in plain english, and not available for purchase.
Fantastic documentation
The PostgreSQL project requires that any developer submitting code will provide documentation for the proposal. This proposal is used to create the documentation for the feature that is made available in several formats. This documentation is also used in the evaluation of the feature itself, and as a reference to develop future features.
All together, this means that PostgreSQL lives on documentation. There are many developers for PostgreSQL that have learned to code in C, how databases work, and how projects are managed by working with the PostgreSQL project. This documentation is second to none.
Test driven development
PostgreSQL is extensively tested. No, that’s not saying it strongly enough. PostgreSQL is exhaustively tested. Every bug is met with a test to verify it’s existence, and code is written to satisfy the test. New features are written by the creation of tests (and documentation) first, then coded until the feature appears.
These tests are integrated into the build farm for regression, so bugs don’t (re)appear in future versions of PostgreSQL. That means that every test (that is still current) is run for every version of PostgreSQL for every build cycle. That’s a lot of testing, and it ensures that PostgreSQL remains the most stable database that is available.
PostgreSQL is only released when ALL of the regression tests pass. This provides for “0 known bug” releases.
Internationalization and Localization
The developers of PostgreSQL come from all over the world. They have been working in many native languages since the inception of PostgreSQL as a college graduate project. Internationalization and localization have been baked into PostgreSQL as a standard practice, not a bolt on as PostgreSQL began to attract a commercial global market.
While PostgreSQL delegates some of the internationalization to the operating system for compatibility reasons, much of the translation is embedded into the system, providing for a seamless language transition experience.
Cloud operation
PostgreSQL works in cloud architectures using ansible, kubernetes, and proprietary tools from multiple cloud vendors. There are several native cloud implementations to choose from to match your architecture.
If you want to treat servers like cattle, not pets, PostgreSQL has you covered in the cloud also.
Standards Compliance
PostgreSQL has been standards focused for the lifetime of the project. Since PostgreSQL originated in a University graduate program, it has been used as a reference implementation for many SQL standards.
PostgreSQL implements SQL/Med, and ANSI SQL.
According to the fantastic documentation, “PostgreSQL supports most of the major features of SQL:2016. Out of 179 mandatory features required for full Core conformance, PostgreSQL conforms to at least 160.” This is more than almost any other database engine.
Language Features
PostgreSQL implements common table expressions (CTE), language control structures (if, for, case, etc.), structured error handling, and all of the goodies you would expect from a mature procedural language.
Are you convinced yet?
I could still talk about the fantastic community of user groups, IRC channels, web sites with solutions, blog articles and mentors. I could wax philosophical about the way that the database is cross-platform, cross-architecture, and cross-culture. There are hours and hours of presentations, videos and lectures.
Or you could just go download it, and see if it is bigger than your imagination. I think you’ll be very pleasantly surprised.
“PostgreSQL gets no such relief, and that’s fantastic for you.”
Isn’t that horrible for me? Now the bad guys know exactly how to hack into my database until the problem is fixed and patched!
Otherwise, amazing and compelling article.