Why Use PostgreSQL?

PostgreSQL is a free and open-source database management system. It’s commonly referred to as an alternative to MySQL or MariaDB.

Are you thinking about using PostgreSQL for your own project? Or are you researching databases to use for a project at work? Are you trying to convince someone else why PostgreSQL is a good choice?

If so, read on to find out why using PostgreSQL is a good idea.

Why Use PostgreSQL?

PostgreSQL is a good choice for a database because it’s free, open-source, highly customisable, has regular releases, many useful features for developers, and several companies offer cloud-hosted PostgreSQL databases.

Let’s take a look at many more reasons.

 

Free and Open Source

The first thing that many developers think of when they think of PostgreSQL is that it’s a free database. You can download the database management system from the Postgres website and set it up pretty easily, and there’s no need to pay any license fees.

Other free alternative databases are MySQL and MariaDB. However, not all databases are free. Both Oracle and SQL Server are licensed databases, meaning you need to pay to use them commercially.

PostgreSQL is also open-source. This means the source code is available online and can be changed by anyone. It follows a Git source control model, meaning there is a process to ensure it’s not corrupted or broken by anyone.

The advantages of having an open-source database are:

• Easier to find and fix issues, as you can access the code instead of waiting for the vendor to investigate it
• Community-developed, meaning it can be developed and supported without a company.
• Add your own features, if there’s something you want to add that doesn’t exist.

To find out more about how it compares to MySQL, check out this guide.

 

Runs On Many Operating Systems

Many databases run on some but not all operating systems. PostgreSQL runs on a wide range of operating systems:

• BSD (FreeBSD, OpenBSD)
• Linux (Red Hat family, Debian, Ubuntu, SuSE, and others)
• macOS
• Solaris
• Windows

This is great as it lets you have a range of server setups and local development environments. Personally, it’s helpful for me as I run a MacBook, which does not natively support Oracle or SQL Server (I run a virtual machine for this, which I’ve written about here).

 

Many Data Types

PostgreSQL includes many data types for working with and storing data.

It supports a range of data types you would expect from a database:

• bigint
• character
• character varying (equivalent to VARCHAR)
• date
• integer
• text
• timestamp

There’s also support for a boolean data type. This is very helpful as we often need to store boolean data in a table. Most other databases don’t support boolean and we have to use an INT(1) or VARCHAR(1) to support this (as I’ve written about here).

There are many more data types available, such as “cidr” for storing IP addresses, “path” for storing coordinates, XML, UUID, circle, and box. For a full list of data types, check out the PostgreSQL documentation here.

 

Includes Range Data Types

Have you ever needed to store a range of data in a database?

Some examples could be:

• A start date and end date for a product, promotion, event, or any other type of record
• A minimum and maximum range of numbers
• A set of scores

To support ranges in other databases, this would mean:

• Setting up two columns for a minimum and maximum value
• Implementing logic somewhere (constraint, trigger, application code) to ensure valid values, valid ranges, non-overlapping ranges

However, in PostgreSQL, you can use a feature called “range data types”.

This is a very useful feature, as just about every application I’ve worked on includes some kind of range data.

You can store the values in one column, using one of the predefined data types. For example, the tsrange data type allows for a range of timestamp values.

An example of a table could be:

CREATE TABLE promotion (
id INT,
promo_period TSRANGE
);
INSERT INTO promotion (id, promo_period)
VALUES (201, '[2019-04-01 07:00, 2019-04-28 20:00]');

This will allow you to store a single value that defines the range. You can then perform checks on this value using PostgreSQL-specific syntax.

 

Great JSON Support

JSON (JavaScript Object Notation) has been in PostgreSQL for a while. However, since version 9.2 (released in 2012), native JSON has been supported. In version 9.4 (released in 2014), the JSONB data type was added, which greatly improves the indexing ability.

Many databases either focus on storing relational data in tables or document data as JSON. MongoDB, for example, is a document database, and MySQL is a relational database. Some relational databases allow JSON support, like PostgreSQL, but you often needed to make a decision on whether to use a document database or a relational database.

With PostgreSQL, you have the option of both. You can use the relational database features or the JSON storage features like a document database. It may not have the same advanced features that pure document databases have, but the JSON support in PostgreSQL is pretty good.

 

Custom Data Types

In PostgreSQL, you can define custom data types. If you can’t store what you need using one of the built-in data types, you can create your own.

You can then create a that uses this type, and store data in the column. It’s a similar concept to a nested table or even a separate joined table.

If you want to store your data in the same table and not have to use separate columns or a separate table, then a custom data type can be useful.

 

Highly Conformant to SQL Standards

According to PostgreSQL, they support 160 of the 179 mandatory features for SQL:2016 core conformance. No other database is fully compliant.

This means it uses a lot of the standard SQL features and making it easy to migrate to and from PostgreSQL if needed. It’s also easy to learn, as you know what to expect when using the SQL standard functions and features.

A partially-completed list is available on Wikipedia here.

 

Replication Features

PostgreSQL has a set of replication features built-in to the database. Database replication means the database will copy its data to a secondary database automatically.

This means:

• If the primary database is unavailable, the secondary database becomes active, reducing downtime
• The two (or more) databases could serve the same data, which allows the overall system to share the load (also called “load balancing”).

This feature makes PostgreSQL more useful as a production-ready database for more than just small personal projects.

 

Window Functions

Window functions in SQL are one of the most underrated and unknown features. They are supported in Oracle, and SQL Server has a partial implementation of them.

PostgreSQL has implemented window functions to allow you to make full use of them.

I’ve written a guide on window functions here.

What are window functions?

They are a set of aggregate functions that allow you to calculate the result of an aggregate over several rows, but still display the details of individual rows.

For example, you could display all student records, their final test score, as well as the average test score for their age or group. This allows for a lot of flexibility in your queries and results and can simplify your application code and SQL code.

 

Common Table Expressions

Common Table Expressions are a feature that lets you simplify your queries. If you have a large query with subqueries inside it, you can define the subqueries with a name, and use that name in your main query.

This can improve readability and performance.

It’s a great feature I’ve used in both Oracle and SQL Server. I’ve written more about Common Table Expressions here.

 

Custom Languages and Functions Inside the Database

If the built-in functions are not enough, you can create your own.

Oracle has its own procedural language called PL/SQL, and also supports other languages such as Java. SQL Server offers T-SQL.

PostgreSQL also has its own procedural language called Procedural Language PostgreSQL: PL/PGSQL. This works in a similar way to PL/SQL and T-SQL and allows you to write custom functions in the database.

You can also use other languages in PostgreSQL such as Python, Ruby, and R. This is a great feature if you’re familiar with those languages and want to include them.

 

Regular Releases

PostgreSQL offers regular releases of its database. While the database is open source, there are still releases which offer stable versions of the database.

A major release is targeted once per year. Several minor releases are made to the database to fix any bugs or security issues.

The latest major version of PostgreSQL is 12.1 (as of December 2019). This was released in November 2019.

Version 11.6 is the latest version of v11, and 10.11 is the latest version of v10.

Many other databases also have regular releases (Oracle, SQL Server, and MySQL), but not as often as PostgreSQL. That’s another advantage of being free and open source.

 

Foreign Data Wrapper

A useful feature in PostgreSQL is called “foreign data wrapper”. It lets you create a foreign table which refers to an external PostgreSQL database.

This can be useful if you have two or more PostgreSQL databases and want to use the data in a single database. Rather than exporting and importing data, you can use the Foreign Data Wrapper feature to achieve this, simplifying your code.

 

Cloud-Hosted PostgreSQL

The movement to cloud services is a popular one and many companies are doing it. With the rise of Google Cloud, Amazon AWS, and Microsoft Azure, the features and security are improving.

There are many companies that offer cloud-hosted PostgreSQL databases. This makes it easy to set up a database for your application, rather than deploying it on your own server.

Some examples are:

• Heroku
• Amazon RDS for PostgreSQL
• Google Cloud SQL
• Microsoft Azure
• Enterprise DB
• Scalegrid
• ElephantSQL
• Compose

 

Extensions

There are any extensions available for PostgreSQL, which allow you to easily add on to the existing functionality of the database.

You can create your own extensions to address your own needs or the needs of your team or company.

You can also download community-developed extensions. The PostgreSQL website has a range of them listed here, such as:

• HypoPG: add hypothetical indexes for performance testing
• OpenFTS: a full-text search engine
• PostGIS: adds support for geographic objects

 

Conclusion

PostgreSQL is a great database for any project you’re working on. It’s primarily designed as a relational database, but can be considered for its document storage features as well. It’s free, open-source, has great features and performs well.