August 12, 2021

What Is PostgreSQL?

Databases
Open Source

PostgreSQL is a popular database option, even with a wide array of open source database choices available

In this article, we give an overview of PostgreSQL, how it works, and explore its benefits and shortcomings as an open source database.

What Is PostgreSQL?

PostgreSQL is an open source object-relational database system that has been around for more than 30 years. Its constant evolution has turned it into a reference in regards to reliability, robustness, and performance. 

It is typically used for applications or systems that require atomicity, consistency, isolation, and durability in their data structures, and can accommodate more complex data structures than typical relational databases.

Let’s quickly go through the history of PostgreSQL to better understand what PostgreSQL is.

History of PostgreSQL

The first PostgreSQL release dates from January 29, 1997. Still, its origins date to around ten years before when Michael Stonebraker, the leader of the Ingres project (that gave origin to PostgreSQL), decided to leave the University of California, Berkeley, in 1982 to build a proprietary version of Ingress.

In 1985, Michael Stonebraker returned to Berkeley to start a new project that would address the challenges related to the database systems at the time. The new project was called POSTGRES, and it aimed to support data types. 

The new feature would allow the definition of data types, and once defined, POSTGRES would entirely manage all the types and respective relationships. Furthermore, when queried, it would return data from the respective tables. The first POSTGRES release dated from June 1989 and included the POSTQUEL query language interpreter influenced by Ingres. 

Finally, in 1994, the POSTQUEL query language interpreter was replaced with a SQL query language interpreter, and Postgres95 was born. The project was renamed PostgreSQL to reflect its support for SQL in 1996 and was released as we now know it in 1997.
 

Get the Guide to Open Source RDBMS

In our new Decision Maker's Guide to Open Source Databases, our experts break down the top open source databases in use today, including the most popular open source RDBMS.

Download Your Copy

Languages Supported by PostgreSQL

PostgreSQL has a mechanism to allow user-defined functions to be written in languages other than SQL or C, procedural languages. The following procedural languages are included in the PostgreSQL standard:

  • PL/pgSQL: allows the creation of functions and trigger procedures and the design of complex conditional computations mechanisms.
  • PL/Tcl: allows the creation of functions similar to how the C language does, but not as powerful. One of its upsides is the addition of the powerful string processing libraries that are available for Tcl.
  • PL/Perl: allows the creation of functions using the Perl language that being an imperative language allows more control over the logic flow.
  • PL/Python: this procedural language allows you to create and execute functions written in Python.

PostgreSQL allows these languages to be integrated using a handler built with the C language responsible for making the bridge between the user-defined function and PostgreSQL core. 

It also has drivers that allow common purpose languages like Java, TypeScript, or Kotlin, to name a few, to connect and interact with the database.

Is PostgreSQL Open Source?

It is open-source under the Portions Copyright, a license similar to the BSD and MIT license. This license allows users to do anything with the code, provided you do not hold them legally liable for problems with the software.

Is PostgreSQL a Relational Database?

Being an object-relational database (ORD), PostgreSQL offers features found in proprietary databases and even adds enhancements to make it a more enticing choice. As an ORD, it is capable of interacting with data between both relational and object-oriented databases. As an open-source relational database, PostgreSQL is the optimal option.

PostgreSQL Features

PostgreSQL contains a comprehensive and complete set of features that go from features for developers to build applications to features dedicated to database system administrators to protect data integrity and build fault-tolerant systems. One of PostgreSQL's strong points is extensibility, allowing users to develop their own data types or functions. Additionally, it has countless features related to internationalization, data types, text search, data integrity, disaster recovery, concurrency, extensibility, performance, reliability, and security.

While this article talks all about what PostgreSQL is, this DBMS is already very popular. Looking at the results for Stack Overflow's 2020 survey for most popular databases among all respondents, we can see that PostgreSQL is the second most popular database right behind MySQL. This ranking reflects data that is collected directly from all surveyors.

results of 2020 database survey from stack overflow

Similarly, looking at the survey results for most popular databases among professional developers, we can see that PostgreSQL grows in the preferences of this category of developers, which means companies are also adopting PostgreSQL as a reliable database for their businesses.

results of 2020 database survey from stack overflow 2

PostgreSQL's community offers many solutions and support, making adoption easier for developers when they have to choose a database technology for their next project.

One other factor is that all cloud providers (AWS, GCP, Azure) support PostgreSQL as a managed service. This can boost productivity and release developers from a lot of the database management part.

PostgreSQL vs Other Database Management Systems

As we saw in the previous section, PostgreSQL is the second most popular database among developers, and the competition is fierce. In this section, we'll look at three distinct alternative databases and compare them with PostgreSQL.

PostgreSQL vs MySQL

MySQL stands in the first position regarding popularity among developers. One of the reasons that contribute to this preference is related to the fact that MySQL is the standard database used in the LAMP (Linux, Apache Web Server, MySQL, and PHP) stack. Still, other reasons make MySQL so popular, like being an open-source and free database management system that has been around since 1995, its lightweight and easy-to-set-up characteristics that can be used in systems that deal with high volumes of data.

When comparing PostgreSQL vs. MySQL, we can see the following highlights:

  • PostgreSQL contains a broader set of features that can handle complex queries against a high volume of data. MySQL is a fast, reliable, and performant database with an easy setup.
  • PostgreSQL is an ORDBMS (Object-Relational Database Management System) where users can take advantage of table inheritance and function overloading features. At the same time, MySQL is just an RDBMS (Relational Database Management System).

PostgreSQL vs Oracle

Oracle is a proprietary database and one of the most adopted relational database management systems. It's been around since 1979, but its popularity has been decreasing, as we can see in the previous section, where it ranks in the 8th position in the developer's preferences, way behind PostgreSQL. The decrease in popularity can be connected to the increasing adoption of open-source databases like PostgreSQL by developers and businesses due to their robustness and low costs.

When comparing the two databases in a nutshell, we can see the following highlights:

  • Like PostgreSQL, Oracle contains a comprehensive set of features that can handle complex queries against a high volume of data.
  • Oracle’s costs are enormous compared with PostgreSQL when it comes to licensing and support.
  • PostgreSQL has a vast community behind it based on its open-source nature. Oracle is a proprietary system, and its evolution and support are directly connected to the Oracle Corporation roadmap.

PostgreSQL vs MongoDB

When comparing to the databases we have seen before, MongoDB is the most different one. It is a NoSQL (Not Only SQL), schema-free document database.

Document-based databases process data as JSON-type documents and store them inside collections. As a result, the data structure is not rigid like traditional relational databases (which use tables and relations) – this means that the database can give a higher level of flexibility when it comes to the data structure.

When comparing PostgreSQL vs. MongoDB, we can see the following highlights:

  • MongoDB is a NoSQL database and stores documents inside collections, while PostgreSQL is a relational database and stores records inside tables.
  • PostgreSQL has ACID (Atomicity, Consistency, Isolation, and Durability) properties by default, while MongoDB can implement them.
  • MongoDB can scale horizontally due to its distributed architecture where multiple components collaborate across platforms, while PostgreSQL scales vertically.

Benefits of Using PostgreSQL

As we have seen in the previous sections, PostgreSQL is a robust database that has been around for quite some time. So, let's have a look at some benefits provided by PostgreSQL.

image listing the advantages of postgresql

ACID Support

Short for atomicity, consistency, isolation, durability, ACID is known as a set of properties relating to database transactions intended to guarantee data validity even with things like errors, power failures and other incidents.

Community Support

With its community fixing significant issues and backing up the discussion and adoption of new features, the adoption of PostgreSQL becomes more secure and tends to evolve with the contribution of a strong engineering community behind it. It enjoys the robustness of a large global community of users that work around the clock developing the package and tools to go with it.

Security

From user authentication to secure TCP/IP connections, PostgreSQL offers multiple security mechanisms that combined can protect data in a highly effective way.

Scalable

Being hosted in an on-premises environment or inside a cloud provider, PostgreSQL can handle large amounts of data and grow without affecting query performance and data integrity.

Fast Data Processing

PostgreSQL offers fast data processing and support for native ANSI SQL language syntax, which makes for easier maintenance and scalability.

Support for B-Trees, Genetic Algorithms, and GIN Indexes

With support for B-Trees, Genetic Algorithms, and GIN Indexes, PostgreSQL can further accelerate the previously-mentioned full-text searches.

Who Should Use PostgreSQL?

PostgreSQL adoption has been growing in recent years, from startups to big companies like Microsoft, LinkedIn, PayPal, Bloomberg, Nokia, or BMW. To name a few, PostgreSQL is a database that can suit the needs of any business that needs a relational database.

Any company whose business will rely on a relational database to store its data has on PostgreSQL a reliable solution. One argument to back this decision is that PostgreSQL is widespread across different business sectors, from tech to automotive companies, passing by the financial industry.

Use Cases for PostgreSQL

PostgreSQL is widely adopted across different sectors. In this section, let's go through some use cases for each industry.

Financial Sector

Transactionality is a crucial feature for any application working in the financial sector. With its ACID (Atomicity, Consistency, Isolation, and Durability) properties PostgreSQL is a perfect candidate for a database that needs to support high volumes of financial transactions and keep its performance and integrity while doing so.

Big Data for R&D

With its capacity to grow consistently and store large amounts of data, You can use PostgreSQL in investigation-based projects where vast amounts of data need to be stored, manipulated, and serve as a data source for data scientists to develop their algorithms.

Most algorithms need to be high-performing. PostgreSQL's efficiency and low latency guarantee that the database won't be a bottleneck for the entire solution when feeding data into the algorithm.

Logistics

When thinking about a supply chain that can cross the globe carrying goods, keeping track of the data involved in that supply chain is a massive effort. Like we have seen before, PostgreSQL databases can grow in size and handle a high amount of transactions while maintaining data integrity. This is a crucial feature for a reliable supply chain where the type of stored data can go from product catalog to GPS coordinates of the trucks making the deliveries, passing by the product prices and payment registries.

Web Applications

With the wide adoption of web applications, most websites need to handle hundreds of thousands of requests per second and still be responsive while delivering data to all their customers. PostgreSQL can offer the capacity to respond to that magnitude of request and still handle terabytes of data with one single instance.

Final Thoughts

Every open source database has its pros and cons. PostgreSQL’s extensibility, vibrant community, ACID transaction support, and open source status make it a popular choice among relational and object-relational databases. But PostgreSQL also isn’t a one-size-fits-all database. It’s not going to be a fit for every project, just like RDBMS aren’t typically the right choice for horizontal scaling.

In the end, finding the right database is wholly dependent on your application or system needs.

Get Guidance and Support for Your Open Source Databases

From PostgreSQL support to Cassandra support, our team of experienced Enterprise Architects can help to guide and support your open source data journey. Speak with an expert today to see how we can help your team achieve its goals with database support.

TALK TO AN EXPERT

Additional Resources