The benefits of PostgreSQL

Overview

There are many relational database management systems (RDBMSs) to choose from if the relational model best represents your data. PostgreSQL is one of the most popular and well-regarded open-source relational databases in the world.

This article will cover some of the primary reasons so many developers choose PostgreSQL for their data management needs. These range from feature availability and standards compliance to community support and project governance.

Robust feature set

One of the primary reasons for choosing to use PostgreSQL is its large list of features. PostgreSQL possesses an incredible number of features related to performance, security, programming extensions, and configuration among others.

Some of the features that PostgreSQL offers include:

• Support for writing database functions using:
  • SQL
  • Tcl
  • Perl
  • Python
  • Java
  • Lua
  • R
  • shell
  • Javascript
• Support for a huge number of data types including:
  • common database primitives like numeric, string, boolean, and datetime types: These are found in most databases and have great support in PostgreSQL.
  • network addresses: PostgreSQL supports various network-related types like CIDR addresses, addresses with subnet masks, and MAC addresses, both for IPv4 and IPv6.
  • geometric types: A variety of types are available to help you define two dimensional objects. These include points, lines, and, segments, as well as complete shapes like boxes, polygons, and circles.
  • monetary types: PostgreSQL's money type stores currency at fixed fractional precision. The type locale-aware and includes automatic output formatting.
  • ranges: Ranges allow you to work natively with ranges of values like, for example, date and time ranges for scheduling.
  • JSONB: PostgreSQL's native support for storing and operating on JSON objects can help you work with both relational and non-relational data in the same system.
  • hstore: The hstore type allows you to work with key-value pairs natively.
  • multidimensional arrays: Arrays are very useful for storing multi-part values that don't have much meaning outside of their specific context.
• Ability to define your own complex types: Defining your own types helps you align your database more closely with the way your data is represented in your applications.
• Full-text search: Full-text search gives you powerful techniques for finding and operating on data in semi- and unstructured text. Search can be fine-tuned to match your expectations for relevancy and matching.
• Robust authentication, access control, and privilege management systems suitable for organizations of any size: PostgreSQL has mature user authentication and authorization functionality to define who can use the system and what each user is allowed to see or do.
• Foreign data wrappers: Foreign data wrappers make it possible to represent and access tables and data in remote servers.
• Views and materialized views: Support for views and materialized views allow convenient, simplified access to data by abstracting the original table structures for information that is frequently queried for together.
• Comments on database objects: The ability to attach comments on tables, databases, columns, and other individual database objects allows you to document decisions or implementation details.
• Write-Ahead Logging to provide point-in-time recovery, failover, and streaming replication: These technologies help ensure that your database remains consistent even if the software crashes, and helps you copy data between systems for scaling and backing up data.
• Support for NoSQL-like behavior such as storing documents using JSONB and key value pairs with hstore: Being able to use multiple database paradigms within a single system can help minimize your administration overhead and improve compatibility between different representations.

You can find out more about the features that PostgreSQL supports with the following resources:

• PostgreSQL feature matrix
• SQL Feature comparison with other databases
• Wikipedia's database comparison tables

Object-oriented database features

One of the most fundamental ways that PostgreSQL is different from most other relational databases comes from its core design.

Most relational databases are best described as Relational Database Management Systems (RDBMSs). RDBMSs are software specifically designed to handle relational databases, where data is stored in table-like structures with predefined columns and data types. Data can be queried, modified, and retrieved using techniques based on relational algebra, usually through structured query language (SQL).

PostgreSQL, on the other hand, is technically an Object-Relational Database Management System (ORDBMS). This means that it has the same relational capabilities as an RDBMS, but additionally has some object-oriented features.

Practically speaking, this means that PostgreSQL allows you to:

• Define your own complex data types
• Overload functions to work with different argument data types
• Define inheritance relationships between tables

These features are powerful tools that help you work with your databases and data using some of the same techniques you might be familiar with when programming. The increased flexibility lets you model different types and relationships within the database system instead of externally within your programs. This can help maintain consistency and enforce intended behavior closer to the actual data.

For more on what PostgreSQL's object-relational approach provides, check out the following resources:

• The "O" in ORDBMS: PostgreSQL Inheritance
• Object Relational Database Management System
• What does PostgreSQL to be ORDBMS mean?
• What is the Difference Between RDBMS and ORDBMS

SQL standards conformance

Another area where PostgreSQL stands out over other relational database systems is its adherence to SQL standards.

SQL standards have been developed by ANSI and ISO groups for the sake of defining minimum functionality and interoperability requirements for SQL implementations. While the specifications provided by these bodies are meant to define the features that SQL systems should provide, due to the complexity and long evolution of the language, strict adherence is not always possible. According to the PostgreSQL documentation, no database currently satisfies all of the requirements outlined by the specification.

Given that caveat, PostgreSQL satisfies more of the SQL specifications than other options. By their own count, they conform to at least 160 out of the 179 core requirements laid out by SQL:2016.

You can find out more about PostgreSQL's SQL conformance with these resources:

• Comparison of SQL compliance between implementations
• PostgreSQL supported features
• Features not yet supported by PostgreSQL

ACID compliance

ACID is an initialism in computer science that stands for atomicity, consistency, isolation, and durability. They represent the key guarantees that database transactions must support to avoid validity errors and maintain data integrity.

ACID compliance is a primary concern for relational databases as it represents the typical expectations for storing and modifying highly structured data. Non-relational databases often attempt to conform to their own standards, often represented by the competing BASE initialism which stands for basically available, soft state, and eventual consistency.

While nearly all relational databases aspire to provide ACID compliance, PostgreSQL has boasted ACID-compliance in its engine since 2001. Since PostgreSQL relies a single database engine, this means that all transactions are fully ACID compliant by default. These guarantees are made without database locks using multiversion concurrency control (MVCC). As a comparison, MySQL only supports full ACID compliance when using their InnoDB or NDB database engines, which can lead to unexpected corruption if other engines are used.

To further information about ACID and PostgreSQL's conformance, check out these resources:

• How Postgres Makes Transactions Atomic
• PostgreSQL Concurrency: Isolation and Locking
• A Primer on ACID Transactions: The Basics Every Cloud App Developer Must Know

Open-source development and community

PostgreSQL is an open-source project managed by The PostgreSQL Global Development Group. It is licensed using The PostgreSQL License, a license recognized by the Open Source Initiative.

While there are many other open-source relational databases, PostgreSQL is developed and managed without a corporate owner or a commercial counterpart. This helps the contributors chart its own path and work on features that the community cares about the most. Professional services for PostgreSQL are provided by companies that often contribute to the project but do not control the development process.

This focus on community-driven development has resulted in great participation from PostgreSQL's users. A large number of high quality extensions and applications are available to enhance the functionality of the core PostgreSQL software. Community developed software can help you administer your PostgreSQL servers, compile business intelligence reports, manage new types of data, and work with PostgreSQL from various programming languages and platforms.

Conclusion

PostgreSQL has gained a great reputation as a powerful, feature-rich choice for relational data. Valuing stability, functionality, and standards conformance, PostgreSQL checks all of the right boxes for many projects. Similarly, if you require flexibility in how you can represent data and want to be able to use a variety of tools and languages, PostgreSQL is also a good choice.

PostgreSQL is notable for offering excellent implementation of core relational features while not limiting itself to the boundaries of traditional RDBMSs. While no database can serve every need, PostgreSQL is an excellent option that is versatile enough to suit many use cases.

FAQ