2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / 2ndQuadrant3 / PostgreSQL is the worlds’ best database
PostgreSQL is the world’s best database
Kirk Roybal

PostgreSQL is the worlds’ best database

March 23, 2020/4 Comments/in 2ndQuadrant, Kirk’s PlanetPostgreSQL, PostgreSQL /by Kirk Roybal

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.

Tags: mvcc, PostgreSQL, security
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
4 replies
  1. Joseph
    Joseph says:
    January 4, 2021 at 8:53 pm

    “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.

    Reply

Trackbacks & Pingbacks

  1. 10 coisas que odeio no PostgreSqL – DIG|ME says:
    April 8, 2021 at 9:08 pm

    […] um pouco de febre. Este tópico do Hacker News que cobre uma peça intitulada “PostgreSQL é o melhor bancode dados do mundo”, estourando nas costuras com sicophants bajulando elogios incondicionales, […]

    Reply
  2. 10 Things I Hate About PostgreSQL (2020) – READING LIST says:
    April 6, 2021 at 9:45 am

    […] database has reached a bit of a fever pitch. This Hacker News thread covering a piece titled “PostgreSQL is the worlds’ best database”, busting at the seams with fawning sycophants lavishing unconditional praise, is a perfect example […]

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
WITH a little help from my friends Developing PostgreSQL for Windows - Part 3 Developing PostgreSQL for Windows, Part 3
Scroll to top
×