PostgreSQL: A Sophisticated and Robust Open-Source ORDBMS [REVIEW]

June 6, 2017 12:12 pm Published by

postreSQL review

What is PostgreSQL?

PostgreSQL is an open-source object-relational database management system (ORDBMS). PostgreSQL, or “Postgres”, is widely considered one of the most sophisticated and robust databases available. Despite being free to use, Postgres has a reputation for data integrity and is able to compete in terms of quality, scalability, and reliability with commercial products like Oracle.

Postgres is ACID compliant. It runs on nearly any operating system (UNIX, Linux, Windows, etc.) and there are programming interfaces for a range of languages including C/C++, Java, .Net, Perl, Python, Ruby, Tcl, and Open Database Connectivity (ODBC).

Features

  • Point in time recovery
  • Multiversion concurrency control (MVCC)
  • Asynchronous replication
  • Tablespaces
  • Table inheritances
  • Hot standby
  • Automatic Data Compression
  • Complex SQL queries
  • Write ahead logging (WAL)
  • Conforms with ANSI-SQL:2008 standards

For users that want to write their own code, PostgreSQL supports four standard procedural languages: PL/pgSQL, PL/Tcl, PL/Perl and PL/Python. Some non-standard procedural languages which are supported can be found at the Postgres website.

History of PostgreSQL

Professor Michael Stonebraker returned to UC Berkeley after a sabbatical spent developing his previous project, Ingres, as a commercial venture. He created what he dubbed POSTGRES to iron out some problems he’d found with other database systems.

Version 1 was released in 1989, but it wasn’t until Marc Fournier took over in 1996 that POSTGRES moved to a non-academic development server. The code was cleaned up and given a new name, PostgreSQL. After the release of v6 in 1996, the PostgreSQL Global Development Team was established to oversee Postgres. The PGDT still maintains Postgres on a primarily volunteer basis.

Strengths

Postgres has a strong emphasis on accuracy. It supports advanced features normally found in commercial software like Oracle, and the PGDT is continually identifying and fixing problem areas. More than 70 performances have been added through recent releases.

A broad slate of data types are supported by Postgres: numeric, floating-point, string, boolean, date, uuid, monetary, enumerated, geometric, binary, network address, bit string, text search, xml, json, and array. Users would be hard pressed to find similar variety in another program, especially an open-source one.

Weaknesses

Because of its dedication to accuracy, early versions of Postgres suffered from some speed and complexity issues. It’s also not very forgiving to new users because of an intolerance for casually written queries.

The 9.0 release brought the speed up to (maybe past) MySQL and others. However, the requirement for technical competence remains. On the bright side, error messages in Postgres are specific to the mistake instead of returning a generic “This function is impossible”.

Some features, like merge, don’t have specific counterparts in Postgres. Experienced users tend not to mind as there are technical workarounds, but this is a further reflection of the database’s skill requirement.

Real-life Applications

PostgreSQL is a relational database used by developers to store and manage website information. Because it can be accessed by multiple users simultaneously, it’s useful for things that will be shared online such as collaborative reports or blog posts.

Postgres is used by companies like PayPal, Microsoft, Etsy, oDesk, and Skype.

It tends to be popular among internet and telecommunications companies as well as small-to-medium organizations in general.

A few real-world examples:

  • Sony’s Massively Multiplayer Online games (MMOs) rely on Postgres.
  • Yahoo uses a customized version to store the data that fuels its behavioral analysis engines.
  • Skype has hundred of databases, mainly used for their web store, billing and similarly centralized services.

Future Outlook

The beta for v10 was just released in May with some Big Data features like native table partitioning, additional parallelism, and faster analytics queries as well as some tweaks meant to lay the groundwork for logical replication.

41% of those who switched from a commercial product to Postgres reported first-year cost savings upwards of 50%. It remains to be seen whether these savings can tempt enough users from the top-ranked RDBMS Oracle to shift PostgreSQL higher than the #4 spot.

If you need developers who are experts in PostgreSQL, share with us your challenges and we’ll help come up with the right solution.

Request a Consultation

Categorized in: ,