Picking the right database management system is a difficult task due to the vast number of options on the market. Depending on the business model, one can pick a commercial database or an open source database with commercial support. In addition to this, there are several technical and non-technical factors to assess. When it comes to a relational database management system, PostgreSQL stands at the top for several reasons. The PostgreSQL slogan, The world's most advanced open source database, shows the sophistication of its features and community confidence.
PostgreSQL is an open source object relational database management system. It emphasizes extensibility and competes with major relational database vendors such as Oracle, SQL Server, and MySQL. Due to its rich extensions and open source license, it is often used for research purposes, but PostgreSQL code is also the base for many open source and commercial database management systems such as Greenplum and Vertica. Furthermore, start-ups often favor PostgreSQL due to its licensing costs and because there are a lot of companies that provide commercial support.
PostgreSQL runs on most modern operating systems, including Windows, Mac, and Linux flavors. Also, there are several extensions to access, manage, and monitor PostgreSQL clusters, such as pgAdmin, OmniDB, and psql. PostgreSQL installation and configuration is moderately easy as it is supported by most packaging tools, such as yum and apt. Database developers can easily learn and use PostgreSQL because it complies with ANSI SQL standards. Other than this, there are a lot of resources to help developers learn PostgreSQL; it has a very good documentation manual and a very active and organized community.
PostgreSQL can be used for both OLTP and OLAP applications. As it is ACID compliant, it can be used out of the box for OLTP applications. For OLAP applications, PostgreSQL supports Window functions, FDW, and table inheritance; there are many external extensions for this purpose as well.
Even though PostgreSQL is ACID compliant, it has very good performance as it utilizes state-of-the-art algorithms and techniques. For example, PostgreSQL utilizes MVCC architecture to allow concurrent access to data. In addition to that, PostgreSQL supports both pessimistic and optimistic concurrency control, and the locking behavior can be altered based on the use case. Also, PostgreSQL provides a very good analyzer and advanced features, such as data partitioning using table inheritance and constraint exclusion, to speed up the handling of very large amounts of data. PostgreSQL supports several types of indexes, such as B-Tree, GiN, and GiST, and BRIN indexes. Also, parallel query execution has been supported since PostgreSQL 9.6. Finally, one can use replication to load balance the load to different database clusters.
PostgreSQL is scalable thanks to the many replication solutions in the market, such as Slony and pgpool-II. Additionally, PostgreSQL supports out-of-the-box synchronous and asynchronous streaming replication, as well as logical replication. This makes PostgreSQL very attractive because it can be used to set up highly available and performant systems.
