Postgres has always been loved by database administrators, data architects, and developers. In fact, as evidence of the developer consensus around Postgres, it was named the most admired and desired database in Stack Overflow’s 2023 Developer Survey. But recently, more and more organizations are embracing and using Postgres and finding that it meets all of their business needs.
In a world where technology quickly becomes obsolete, how has this relational database been able to stand the test of time? What’s the secret behind Postgres’ success?
Postgres has many outstanding attributes, but three main capabilities make it ideal for today's workloads: (1) extensibility; (2) non-relational data handling; and (3) specialized indexing.
We sat down with EDB Vice President and Postgres Evangelist Bruce Momjian, and EDB CTO Marc Linster to take a closer look at all three of these key Postgres attributes.
Extensibility
Michael Stonebraker, who designed the early relational database system Ingress in the 1970s, had the foresight to realize extensibility – being able to add new data types, indexing methods, aggregates, castes, and stored procedure languages — would be incredibly important.
So extensibility was built into Postgres since its inception.
PostgreSQL is already packed with features, but its extensibility means it’s not limited to those. By installing extensions, you can add new features and capabilities, like new data types, operators, functions, index types and more. Some of these extensions come with PostgreSQL, while others are available from other sources, such as PGXN, Github, or Linux repositories.
“That’s why it’s uniquely able to meet developers’ needs and give them the tools they need to be productive – better than any other relational system,” says Bruce Momjian, EDB’s Vice President and Postgres Evangelist.
Extensions allow Postgers to move seamlessly into data warehouse tasks, store JSON, do full-text searches, work with geographic information systems (GISs) and PostGIS, and handle the data processing we need today — the Internet of Things, web apps, mobile apps, telemetry data, GIS data, and social media text.
EDB CTO Marc Linster notes, “extensibility is one of the most genius thoughts that Stonebreaker put into the architecture.”
Learn more about extensibility
Non-relational
Relational systems have efficiently handled tasks like order entry since the seventies. They were designed to be rigid in terms of what they index and how they index, how they perform and what data types they support.
This structure worked fine when application developers created waterfall models where they designed everything before they started coding. But then agile models became popular, where development occurred incrementally.
With non-relational databases, the agile model was no problem. Developers could simply add extra fields as needed, such as adding extra lines for an international address. But with relational models, it’s more challenging, and adding fields requires a DBA to make the necessary changes.
“You had to create a ticket for the DBA team to add a column in the table. It slowed down the application development” said Marc. “Application developers hated relational databases when they got started in the agile model, because developers didn’t know everything when they got started.”
NoSQL (not only SQL) non-tabular database technologies became increasingly popular as a solution.
“If you look at the rise of NoSQL, which happened maybe seven, eight years ago, people started to say, hey, I have a web app, and I need to store my JSON data that's behind my web form in that native format in the database. Or I have GPS coordinates, and I need to ship a box, and I need to know the distance from this to that. Or I have an Internet of Things case or need a data warehouse,” says Bruce.
“And historically, relational systems struggled with this, because they didn't have the infrastructure around geographic concepts,” Bruce says. ”Relational systems didn't have a way of storing a JSON document that really understood the content of the JSON document, just stored as a string.”
To tackle these challenges, many developers started trying to synchronize relational systems with NoSQL, which is incredibly difficult. For more complex use cases, developers assumed they needed a special data store, like MongoDB or Redis, React, or Elasticsearch.
But PostgreSQL expanded to support non-relational storage with the ability to store and index multiple values, even unrelated ones, in a single database field.
“Around seven years ago,” Bruce says, “we started to say, PostgreSQL is as good as MongoDB and Elasticsearch at this. You don't need to try and patch together some relational stores that really don't want to talk to each other and certainly don't want to synchronize in terms of consistency. You don't need to do that anymore.”
“When Postgres came up with JSON, it got rid of all those issues, and enabled you to do flexible and agile and rapid development in Postgres. Now you had JSON documents, you had relations, you had GIS information,” says Marc. “You’d have a mobile app that communicates with JSON. It would know where it is, and transact business.”
“There's only one database in the world that can do all these things well, and it's Postgres.”
Learn more about non-relational Postgres
Specialized Indexing
One of the reasons the traditional relational systems weren’t able to process the new workloads is because they had very limited indexing capability.
But that has changed, too.
Instead of creating an index to find a geographic location, you can use Postgres to create an index to perform other operations in addition to equality, like finding all the houses inside a particular circle and the distances between them. Indexes in Postgres are tied to the ability to start asking intelligent questions. So not only are you able to locate a house in a particular location, but you can locate other houses that are nearby, and find the next house that’s less than five miles away, etc.
“This is where a lot of the power of Postgres comes from, and why it’s the most used geographic information system today,” says Marc.
Postgres supports a variety of indexes, like Balanced Tree (B-tree) indexes, which allow for quick searching and sorting of data; Block Range (BRIN) indexes, which allow for efficient indexing of many columns; and Generalized Inverted (GIN) indexes, which specialize in the rapid lookup of keys with many duplicates — an area where traditional B-tree indexes perform poorly. It also supports GIN indexes, which are particularly useful for JSON and full text searching; and Generalized Search Tree (GIST) indexes, which allow for efficient indexing of two-dimensional values and range types.
In addition to these indexes, Postgres supports a variety of other data types that we often don't talk about. It has an ESPN data type, it has an IP address data type, and more. This is one of the many reasons developers love it.
“Because Postgres is extendable, we're able to continually add new data types to meet new workloads, as well as new indexing to supplement and make those non relational workloads efficient in a relational system,” says Bruce.
Learn more about specialized indexing
Building the Future of Postgres
With open source, “you're doing the agile model more effectively, where your developers and your users are looking at the same thing all the time,” says Bruce. “And you're harnessing intelligence… even billion-dollar companies can't hire enough people to have the intelligence of what the Postgres community has.”
“You're creating software and meeting people's needs so much faster. That's why all of the infrastructure software today is open source,” he says. You’re also meeting your own needs. And that’s the basis of Postgres’ rapid evolution.
“People develop features for Postgres because they need it,” says Marc. The Postgres builders are also the Postgres users, and vice-versa.
“There's no closed source development tools anymore, there's no closed source compilers, there's no closed source operating systems, there's no closed source middleware,” says Bruce. All that stuff now is open source, because other companies couldn’t compete.”
“Combine this open source efficiency with Postgres' extensibility… that’s your winning combination.”