Is PostgreSQL ready for the future?

Postgres and I

Personally I prefer Open Source software over the commercial ones alone because of the accessibility. Postgres fulfills this requirement easily and is therefore easy to setup locally by native installation or via docker container. For a long time a relational database was just there for me to persist my entities and retrieve them later. Soon I stumbled over the question why all this must be so complicated. I need to create and maintain a schema, think about indices, write SQL and in general this relational thing was against the quick win. So as a lazy programmer I was fascinated by NoSQL solutions like Neo4J (Graph), MongoDB (Document), Cassandra (Column) or Redis (Key/Value). Such concepts seems to be easy at the beginning but soon you will realize that there is much more manual work required if you don’t want to make any compromises in the overall package . So I always came back to the relational database Postgres and learned more and more that is an Egg-laying jack of all trades.

One database to rule them all

One problem with NoSQL solutions is that they are only doing one thing very good. This means I never get everything I need from one technology and have to combine it with other ones. The implications are bigger then you think.

• Having multiple different databases make it necessary to integrate the data models with each other. That can be hard on a consistent and type safe level.
• Each database is a separate part of infrastructure that the developers and operators must learn and maintain.
• On the client side you have different drivers and client libraries that all have their own quirks and doing the same thing differently. As example manage the authentication or maintaining the connection pool.
• All these means more dependencies that make you less agile in the long run.

On a big scale you have to accept such disadvantages but it is a myth that Postgres is slow or does not provide the features that you like from the NoSQL databases. For many use-cases and apps a relational database is still a good (or the best) match. In many cases they seem to be slow that is not the fault of the database itself, more of wrong or bad usage (as example through the usage of complex ORM software). Me too, I was suspect if Postgres is a good choice if you plan with a very big success of your app and then the need of scaling it to the sky. But that is a wrong fear of many reasons. Currently I learned that as example Stack Overflow is implemented and run as a monolith! So a good, simple design and quality is a big factor.

No NoSQL needed

The great thing about Postgres is that it can fulfill very different needs. In the end all these different NoSQL databases are just a storage layer with some functionality above it (aren’t all applications like this?). Nothing that would not be possible with the infrastructure that Postgres provides. Less systems make your IT architecture much simpler. Here is a list with examples what could be replaced by Postgres these days:

• Key-value store – HStore is the Postgres SQL API to query Key Value data.
• Document store – FerretDB lets your Postgres work as a replacement for MongoDB.
• Time series – Timescale provides an Postgres extension that optimizes the database for time series use-cases.
• Graph database – Apache Age extends the Postgres database with the functionality to manage and query graph data.
• Search engine – ParadeDB
• Vector store – pgvector allows efficient vector similarity searches in Postgres.
• Spatial store – PostGIS
• Event queue – pgmq transform Postgres into a Event Queue like AWS SQL.

Postgres as a service in the cloud

To scale an application it is necessary to provide a flexible amount of the resources that are necessary to fulfill the load. The cloud provides these resources in the form of memory and compute power. The key of high scalable applications is the decoupling of these two aspects. As example often an object storage like AWS S3 is used to store the data and serverless functions like AWS Lambdas are used to represent the compute part.

These concepts are also adapted by modern implementations of databases. AWS provides as example Aurora Servless as such product.

Microsoft has aquired Citus Data. Beside the separation of data and compute they use sharding to gain performance.

Beside the big cloud players there are innovative service providers with more scalable Postgres implementations. Neon provides an implementation written in Rust that also brings other innovations like the possibility to branch the database like developers are doing it with their source code via Git. This allows as example to create a branch of the production database in seconds to run some debugging experiments. Doesn’t that sound like the future, does it?

Another player is Tembo where also Rust is the programming language of choice. Tembo tries to bring a better experience to many aspects of Postgres. There are different preconfigurations that are optimized for special use-cases like analytical workloads or machine learning. They implement a fresh ecosystem around the Postgres database. This means as example Trunk, a package manager to manage extensions, or pgmq that provides functionality similar to AWS SQS.

All these providers follow the Database as a service (DBaaS) principle and make the setup of a new database as easy as a few clicks.

Learning Postgres is worth it

If you don’t want do something wrong then I can recommend you to learn SQL. Despite what your main programming language is, SQL is always a powerful addition. And if you have read this article until here then you know that I would also recommend you to learn more about Postgres. You will stumble upon many useful details that eventually will suprise you. Here are some examples:

• With Postgres you have to VACUUM your database like you have to vacuum your room. But there could be a future without vacuuming.
• With CREATE STATISTICS you can tell your database things that your database couldn’t figure out by itself to optimize queries. A use-case for that are columns that behave in relation to each other but that isn’t obvious from the tables schema.
• The LISTEN and NOTIFY commands are the building blocks to implement the observer pattern between your clients and services.
• Do you need JSON? Here you go: SELECT json_agg(result.*) FROM (<your SELECT query>) AS result. With json_agg you receive the result as a JSON Array String. As example if you have an API that just provide data from a database, then you could write the JSON String received directly from the database to your response and save the boilerplate implementations through the other layers in your application.

Yes it is. Postgres is ready for the future!

Postgres is mature, Postgres is adapted to the cloud, Postgres is powerful, Postgres is fun. So yes Postgres is ready for the future!