The world’s most advanced open source database?! Say whaaat?

Talking to non-Postgres people at #PerconaLive, I rаn into an interesting question: why a slogan on your PostgreSQL Community booth (as well as at postgresql.org) says  “The world’s most advanced”? - What is so advanced?

I’am pretty much sure, that everyone of us, who worked with Postgres for years, has a long list of features that could address such question. For me, however, this list has an obvious number one: ACID transactions support in a way it is implemented in PostgreSQL.

A database server should be build around a transaction machine simply because a database server is, to certain degree, a transactional machine. Keeping transactions outside, as an external API, proved itself being painful. Now, when DBMS creators suddenly realize that a naïve
approach “we are not a bank, we do not need transactions” doesn’t work and try adding pure men’s transaction support at some point on the fly – that’s even worse.

PostgreSQL is famous for it’s robust backup/recovery, easy replication, powerful SQL and tremendous extensibility. As for me, success of those features in many ways based on transaction implementation.

That’s just me though, so what do YOU think makes PostgreSQL  the world’s most advanced open source database?

Comments

  1. I agree on everything said above and would add:

    When compared to "the world's most popular open source database", you could:
    - SQL implementation follows the ANSI standard closely.
    - Arrays - Performant and incredibly useful
    - Choice of of language for stored procedures. JavaScript, R, Perl, Python, etc. in addition to trusty old PL/pgSQL
    - Great choice of datatypes. For example, ranges (under-appreciated), JSON (others now add this but I think PG was the first RDBMS to do so), hstore, etc
    - Relentless focus on quality and data integrity
    - CTEs and window functions.

    I could add more but these are stand-out features for me.


    The clinching argument for me: Richard Hipp who created the excellent SQLite RDBMS puts it very well when he says "PostgreSQL is the best available reference
    platform for SQL". Source: https://www.pgcon.org/2014/schedule/attachments/319_PGCon2014OpeningKeynote.pdf

    ReplyDelete
    Replies
    1. Yes, exactly. This is that long list I've mentioned in the post

      Delete
  2. Extendable for specialization without a loss in the base feature set. PostGIS being the most obvious example and PostBIS being one of the more intersesting ones.

    ReplyDelete
  3. Here are some of the features that make PosthreSQL the most advanced DB for me:
    * Foreign data wrappers make migration ro Postgres easy (except for data/datatype mismatches in MySQL, when "IMPORT FOREIGN SCHEMA" fails)
    * Jsonb type allows making minor changes to the data model without changing the schema too often. This is an excellent solution for building analytical models, several different ones can be tested with parameters stored in an unstructured field and the schema changes for proper parameter storage can be done after the results are compared and the best model is selected.
    * A significant amount of languages for functions, no need to rewrite R models in PL/pgSQL.
    * Advanced constraints.
    * Excellent documentation (this usually applies to tutorials/videos/conference talks also) and friendly community.

    ReplyDelete
  4. >pure men’s transaction support

    Maybe poor men?

    ReplyDelete
  5. I am using the fantastic postGis.
    It is amazing when I do a geographical Join (e.g. st_dwithin) - the optimizer sets the correct join order on the tables.
    I mean - st_dwithin is a function that was added to postgres externally. as is the geography index GIST.
    The fact that the considerations of this function and index are known to the optimizer amazes me.
    In the commercial DB I tested - geographic join order was not optimized. so performance changed in orders of magnitude depending in the parameter order in st_dwithin.

    ReplyDelete

Post a Comment

Popular Posts