In the past, enterprises have typically used Oracle as their go-to database system (or, for some in Windows-based workshops, Microsoft SQL). Over that time, a number of other database systems were being used on a smaller scale, including PostgreSQL. But more recently, the database landscape has changed significantly, and now PostgreSQL has found its place on the main stage, now sporting advanced and modern features and performance.
If you have dismissed PostgreSQL in the past, here’s why you should take another look.
PostgreSQL is reliable
PostgreSQL uses a Write-Ahead Log to protect against system crashes, so that if a transaction has committed that hasn’t yet been written to the database, the Write-Ahead Log is replayed, and the transaction is then committed.
PostgreSQL is extensible
PostgreSQL already has many features built-in, but it’s not limited to those. By installing extensions, you can add new features to provide new functionality. It's this system that allows major projects such as PostGIS to work with PostgreSQL. Extensions can add new data types, operators, functions, index types, and more. Some of these extensions come with PostgreSQL, while others are available from other sources, such as PGXN, Github, or Linux repositories.
PostgreSQL is fast
With most releases, PostgreSQL has a new performance-enhancing feature, allowing for better efficiency with partition handling, parallel functionality, indexing speed, and the removal of concurrency barriers such as those that cause transactions to wait for an operation to complete.
So now you can reindex an index without blocking anything, change the data type of a column without necessarily waiting for data to be rewritten, and attach a partition without making anything wait. Indexes are faster while taking up less space, multi-column statistics are supported for more advanced query planning, covering indexes means very fast query results without having to even read the table, and Just-in-Time compilation speeds up expression evaluation. There are many, many more improvements in pretty much every area.
PostgreSQL has advanced features
PostgreSQL supports a huge number of features, many of which aren’t available on other database systems. Just some examples of these include:
- Indexes that support different collations, types, access methods, and operators.
- Parallel functionality across queries, indexes, index-creation, foreign table scans, and partitioned tables.
- Extensive support for JSON data, including dedicated data types, indexes, functions, and SQL/JSON functionality.
- Writeable Common Table Expressions, allowing users to build up clear, modular, and sophisticated queries that write to the database.
- An extension system that has a vast library available.
- User-defined data types and aggregates.
PostgreSQL is secure
PostgreSQL supports SCRAM-SHA-256 authentication, which can be used instead of the older and less secure MD5 method. But many other options are available as well, such as LDAP, Active Directory, RADIUS, certificate, pluggable authentication modules, and several others. So users can be managed by whichever method suits the company’s policies.
PostgreSQL is standards-compliant
PostgreSQL follows the SQL standard for the vast majority of features. This makes it easier to migrate to and more predictable to plan migrations for.
PostgreSQL has a lot of support available
PostgreSQL is built by a large community with people from all over the world, many from companies dedicated to PostgreSQL. This community provides help through official mailing lists, IRC channels, blog posts, and answering questions on popular tech forums. And because these people designed, developed, and tested PostgreSQL’s features, they are the foremost experts on it.
But not only is a community on-hand to help, there are also various companies who provide dedicated support for PostgreSQL and its proprietary variants.