What's coming to PostgreSQL ? 🐘
Tags: webdev, programming, opensource, database
Published: 2024-04-30 15:03:16


PostgreSQL, the powerful, reliable and highly extensible open-source relational DBMS, has been **a mainstay in the database world for over three decades**, gaining a loyal following among developers and organizations alike, for **an ever-growing number of use case**. 

<img src="https://media.giphy.com/media/v1.Y2lkPTc5MGI3NjExeDBweGhnd3RtancydHozaGxwbmoxOHExdTh0dXJvYmR5dTlpYXdiZCZlcD12MV9pbnRlcm5hbF9naWZfYnlfaWQmY3Q9Zw/wwKoFueqHUQ2WWGHDE/giphy.gif" >

In the past couple years, amidst the increasing importance of "global-scale" P/IaaS like Vercel and Cloudflare, many MySQL or SQLite-based solutions like **Planetscale, Turso, and Cloudflare D1** have been making waves with their **serverless architecture, global redundancy promises, unlimited storage and easy scalability**.

At the same time, PostgreSQL is undergoing a series of transformations that could redefine its role in the database landscape.
In this article, we'll explore some of the exciting developments shaping the future of PostgreSQL, with a focus on the contributions of companies like Supabase, Neon, ParadeDB or AWS.

---

## Separation of Compute and Storage

One of the most significant advancements coming to the PostgreSQL ecosystem lies in the separation of compute and storage. Companies like Neon, AWS, and recently Supabase are pioneering this approach, which brings major benefits:

- **Horizontal Scalability:** Just add more compute nodes to allow the system to handle increased load without hitting performance walls.
- **Cost-Effective Scaling:** The separation allows for independent scaling of compute and storage resources based on specific workload requirements, optimizing costs and possibly offloading storage to S3.
- **Seamless Replication and High Availability:** The decoupled architecture simplifies replication and enables high availability setups.
- **Branching and Improved Developer Experience:** The storage layer can be easily forked and attached to new compute instances, facilitating branching and enhancing the development workflow.

For a deeper dive into the benefits and implications of this architecture, I recommend exploring this insightful [blog post from David Gomes](https://davidgomes.com/separation-of-storage-and-compute-and-compute-compute-separation-in-databases/) and the linked resources.

Neon is already offering a production-ready postgres serverless offering using this technology, all open source, jump to their [hello-world](https://neon.tech/blog/hello-world) post for an overview of their approach.

---

## Pluggable Storage and Improved Table Engines

At the heart of every database lies its table engine.
In Postgres 12, a basic support for a **"Pluggable Storage"** was introduced, as a way to **define different table engines for different tables in the same database**.

This unlocks the potential to use a single PostgreSQL instance for various use cases, from OLAP and AI vector search to log search, without compromising performance or storage efficiency.

However the initial implementation had limitations, stalled and didn't fully achieve its goals.

Some solutions like CitusData's columnar storage leveraged this initial API but came with significant [restrictions](https://docs.citusdata.com/en/v11.1/admin_guide/table_management.html#limitations) over core PostgreSQL features.

Enter [Oriole](https://www.orioledb.com/).
[Acquired by Supabase](https://supabase.com/blog/supabase-aquires-oriole) officially in April 2024, Oriole is designed as a drop-in replacement for the existing storage engine, bringing notable performance improvements and advanced features. It reduces I/O (enabling decoupled storage and compute), supports page-level data compression, and introduces index-organized tables. 

```sql
create extension orioledb; -- enable the extension

create table posts (
  id int8 not null,
  title text not null,
  body text not null,
  primary key (id)
) using orioledb;    -- Specify the storage format
```

The Oriole team is actively driving contributions around Pluggable Storage, building upon the efforts initiated in PostgreSQL 12 to create **a more integrated extension ecosystem**, supporting many more advanced extensions and use cases on standard PostgreSQL instances. 

---

**A few words about us**

Our project, [Meteroid](https://meteroid.com/), is a modern and **open-source billing platform** that focuses on business intelligence and actionable insights.

To allow our users to self-host without our complete saas-focused infrastructure, including a Clickhouse cluster, we've been researching options to leverage Postgres more intensively for all our different use cases. We figured we would share some of our findings about the ecosystem through this blog post.

If you have a minute, please consider [starring us on Github ⭐️](https://github.com/meteroid-oss/meteroid) ! This would help us a lot ❤️

---

## Extending PostgreSQL: OLAP, AI, Search, and Beyond 

The PostgreSQL extension ecosystem has seen remarkable achievements lately. Two notable examples are:

- [`pgvector`](https://github.com/pgvector/pgvector): This community-driven effort provides a vector data type and **search functionality for high-dimensional vectors**. Despite its relatively small codebase, `pgvector` rivals the performance of many specialized vector databases.

- [ParadeDB](https://www.paradedb.com/): Implemented in Rust, this open-source PostgreSQL extension(s) delivers outstanding results in **both OLAP** (closing the gap with ClickHouse) **and Search** (outperforming Elasticsearch in some benchmarks) use cases.

With the new Pluggable Storage model, these extensions and many others could be easily added to any PostgreSQL instance, potentially even interacting with each other, unlocking powerful combinations and enabling PostgreSQL to handle a **wide range of workloads** efficiently.

Many other projects exist around the postgres community, to cover emerging use cases. A couple examples :  

- [electric-sql](https://electric-sql.com/) (the people behind CRDTs) is building a sync layer between postgres and local/embeded SQLite database to build **local-first apps**, a paradigm shift toward dynamic and instant local apps

- AWS RDS team created [**Trusted Language Extensions**](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_trusted_language_extension.html) (pg_tle), an initiative to make postgres extensions more accessible to users relying on a cloud managed database. This is a pretty huge step for the open-source/self-hosted community, as until then **building on top of an extension** could make the deployment a lot more complex.

---

## A couple of resources to delve deeper

- [Scaling Postgres](https://www.youtube.com/@ScalingPostgres), a youtube podcast from Creston Jamison with a lot of discussions and news curation across the postgres ecosystem 

- [pg is eating the db world](https://medium.com/@fengruohang/postgres-is-eating-the-database-world-157c204dcfc4) from @fengruohang , an extensive blog post to understand the unique position of Postgres in the ecosystem

---

## The Future may be both Unified and Distributed

As applications become increasingly global and complex, the need for a **distributed, multi-purpose database** is more pressing than ever. 

The **incredibly extensible core** of Postgres, alongside the **massive community and shared efforts** from many players across the industry, has led to impressive advancements. From the separation of compute and storage to a new Pluggable Storage, and the growing number of highly powerful extensions across languages and domains, Postgres is poised as a **strong contender** for a global, holistic database solution, to meet the demands of modern software development.

The future of PostgreSQL may not just be about being a reliable and feature-rich database, but about becoming **a catalyst for building applications that are fast, scalable, and adaptable** to the ever-changing needs of businesses and users.

<img src="https://media.giphy.com/media/v1.Y2lkPTc5MGI3NjExN3FhZmQ2OHRlYmplOWhwYzE0OGptdnA1cmdlemFjdHVyYzAzOGlhaCZlcD12MV9pbnRlcm5hbF9naWZfYnlfaWQmY3Q9Zw/cgd0FGjcugRArA2O8y/giphy.gif" >

---

I hope you enjoyed this read ! If you found it helpful, don't forget to give [our repo](https://github.com/meteroid-oss/meteroid) a star! Your support means the world to us.

{% cta https://github.com/meteroid-oss/meteroid %} ⭐️ Star Meteroid ⭐️ {% endcta %} 

Cheers !