The PostgreSQL® extensions ecosystem is one of its most important features. Learn which extensions to keep an eye on for modern data use cases!
Celeste Horgan
|RSS FeedDeveloper Educator at Aiven
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
PostgreSQL® is by design lightweight and un-opinionated but its killer feature has long been its extensions ecosystem. The extensions ecosystem adapts and customizes PostgreSQL data storage and manipulation use cases, making it suitable for AI, analytics, document data stores and more. This flexibility keeps PostgreSQL viable as an option for any business or startup, as it’s hard to ‘outgrow’ PostgreSQL.
In this post, we’ll go through the PostgreSQL open source ecosystem as it stands today, and some of the extensions we’re most excited about at Aiven.
As mentioned, extensions are a primary feature of PostgreSQL: as such, enabling any extension is as simple as running a CREATE EXTENSION command on a running PostgreSQL database.
If you’ve been a developer for less than 20 years, deploying extension code on a database directly might seem a little risky to you. Modern cloud development best practices state that you should apply changes to a staging environment or cluster to test for issues before applying directly to your production instance. This is especially true when it comes to open source extension/package code, as trust and maintenance of those original packages becomes a large issue.
It’s important to remember that PostgreSQL and its extensions ecosystem were originally created in the 90s. Network bandwidth was expensive, disk storage even more so, and servers weren’t cheap either. In resource constrained environments, deploying directly to production made more sense than it does today. So, for both reasons of historical development practices and the fact that introducing breaking changes to the extension framework would be chaotic for the community, we continue to install new extensions directly on production services.
Aiven definitely has an opinion on this, though: one of the main reasons the company was started was to ease the production, maintenance and management of PostgreSQL databases. As such, we think the main mitigation to this risk is using a managed service. By using a managed PostgreSQL service, you can put the quality vetting of any given extension onto the managed service provider, and help mitigate the risk with things like automatic backups.
TimescaleDB is an open source database that turns your PostgreSQL database into a time series database, and optimizes it for retrieving data using a timestamp. You can read a bit more about it in Aiven’s documentation, in addition to TimescaleDB’s documentation. This may seem like a trivial optimization at first – storing data by time is, ultimately, only a matter of adding a timestamp, right? But especially when used for monitoring workloads, where data is being generated every millisecond, or for analytics dashboarding, where large quantities of data need to be retrieved quickly, TimescaleDB is a lifesaver.
TimescaleDB’s most distinctive feature is its Hypertable concept: hypertables partition one large table into multiple smaller tables, generally by time but also by other attributes. This allows you to retrieve data for a query far more efficiently than you might otherwise, as you can identify and point to specific partitions of data, rather than querying the entire table linearly.
PostGIS is one of PostgreSQL’s most consistently maintained extensions, and it adds support for geospatial objects stored as coordinates. This allows you to do a number of interesting things, such as geofencing, route planning, and other manipulations for geospatial data. PostGIS underpins a number of different open source data projects, most notably OpenMapData.
PostGIS highlights a particularly popular brand of PostgreSQL extension: the addition of new data types and methods to manipulate them. Aiven supports a number of data type extensions to PostgreSQL.
Pgvector is currently the star of the show when it comes to PostgreSQL extensions. This extension adds the basic building blocks for AI and ML applications, vector embeddings. Vectors in high dimensionality space are how most machine learning models, including LLMs, compute similarity and generate semantic meaning. Adding pgvector to your database therefore enables you to index that data and generate insights for your business, recommendations for your customers, and more.
PostgreSQL comes with a number of interesting extensions it can work in tandem with. On the open source PostgreSQL side of things, TimescaleDB’s pgvectorscale extension works with pgvector to add new features. If you use pgvector with AlloyDB Omni, you can also take advantage of Google’s vector extension, which adds additional indexes.
Aiven has a number of tutorials you can follow using pgvector to manipulate data using vector emeddings. Namely, try building a movie recommendation engine using pgvector!
Though Aiven doesn’t support the full range of PostgreSQL extensions out of the box, as a managed service platform, we’re focused on providing the most reliable and secure service possible for our users, and thus any extension we allow on the platform must be carefully vetted by our team of PostgreSQL experts.
If there’s a particular extension you’re interested in, customers with paid services can contact their sales representative or book a demo. Users of Aiven’s free tier PostgreSQL can add their ideas to Aiven Ideas for evaluation by our Product management team.
The PostgreSQL ecosystem benefits from the extensions that many make, but like many open source projects there’s an ongoing question of maintenance and management.
We recently participated in a panel discussion on the PostgreSQL extensions ecosystem, where we discussed the difficulties end users face when selecting and using a PostgreSQL extension.
Currently the PostgreSQL development community is at a crossroads: wide adoption of PostgreSQL extensions means that certain quality and reliability bars must be met, but the nature of extension development is that typically very small (if not solo) development teams are assigned to these projects, and resources are often cut.
At Aiven we believe the best way to mitigate that risk is using a managed PostgreSQL database, rather than running your own, but we’re eager to see where the PostgreSQL community goes when it comes to extension ecosystem maintenance and management.
