12 Days of PostgreSQL Tools and Projects
PostgreSQL, the open-source enterprise relational database, has an incredibly mature ecosystem with over 35 years of active development. But, with its colossal reputation (it was voted this year’s most loved database in the Stack Overflow Developer Survey) and hardcore community of SQL-loving fans, it is sometimes easy to miss some of its unique features.
It’s no secret that Timescale ♥️ PostgreSQL. We built our products on it, keeping the experience developers know and love while significantly expanding PostgreSQL’s functionality and boundaries.
We’re also an integral part of this community: we have surveyed it, contributed to it, encouraged others to do the same, and, more recently, even commented on it. However, I know that, as developers, we don’t always have the time to look into new technologies.
Therefore, as a little holiday present for the PostgreSQL community, I’d love to present some additional (and useful!) PostgreSQL projects. That is why I put together a list of 12 database extensions (bundled or not), one for each day of the holidays. Enjoy!
PostGIS
One of the best-known extensions for PostgreSQL is probably PostGIS, a spatial database extension that adds support for geographic objects to the database engine. With PostGIS, running locational queries is a blast!
Bundled: No
pg_stat_statements
While performance optimization should never be the first thing on your mind, you will eventually need every query to run as quickly and efficiently as possible. pg_stat_statements collects and provides statistics of all queries being executed on the server and provides that information for later analysis.
Bundled: Yes
pg_repack
A database with many changes gathers a lot of “dust” over time, which will eventually be collected through the VACUUM process. pg_repack provides similar functionality to VACUUM FULL but without using an exclusive lock on the table, keeping the table online for other operations.
Bundled: No
Apache AGE
PostgreSQL is a very versatile base for all sorts of data models, and the proof is in the Apache AGE (A Graph Extension) pudding, which adds graph capabilities and Cypher queries to PostgreSQL. This enables queries that JOIN between graph data models and typical relational data models to get the best of both worlds.
Bundled: No
pgroonga
pgroonga integrates the full-text search engine Groonga into PostgreSQL, making it available as a full-text search index on text columns. While PostgreSQL offers some support for full-text search, it only supports alphabetic languages, missing others such as Mandarin or Japanese.
Bundled: No
postgres_fdw
For various reasons, such as security or replication, sometimes it is best to split data between different databases. When querying data, it’s obviously possible to query the different databases independently. With Foreign Data Wrappers, however, you can connect databases together.
postgres_fdw provides the option to connect different PostgreSQL servers and transparently query them.
Bundled: Yes
ZomboDB
Another full-text search index integration for PostgreSQL is ZomboDB. The major difference is that ZomboDB is built on Elasticsearch, which is already available in many company infrastructures. Reusing an existing technology decreases the complexity and lowers the management cost.
Bundled: No
Citus
While PostgreSQL provides scalability using features such as tablespaces and even postgres_fdw, Citus offers full clustering and sharding of multiple PostgreSQL instances, including automatic rebalancing and query parallelization across all cluster nodes.
Bundled: No
pldebugger
Bringing the calculation to the data is a common strategy, but that requires writing database functions. Debugging issues is quite a job. pldebugger adds remote debugging capabilities to PostgreSQL and enables breakpoints, watching values, and running pgplsql functions line by line.
Bundled: No
pgcrypto
Password hashing, async and synchronous encryption, and random data generation functionality are provided by the bundled pgcrypto extension. This includes elements such as advanced encryption standards (AES), private-public key encryption, and salt or universally unique identifiers (UUIDs).
Bundled: Yes
pgMemento
Many industries and companies have high standards for security and the auditing of system accesses. pgMemento brings automatic audit trails to PostgreSQL, collecting audit events for data and schema changes.
Bundled: No
pg_strom
Last but not least, pg_strom is the dream of every data analyst, providing automatic GPU program generation based on the provided SQL statement. With that, you can leverage the power of thousands of cores per chip and accelerate the processing of massive datasets.
Bundled: No
Making the Best of the Best: PostgreSQL
As mentioned, the PostgreSQL ecosystem is vast, and finding the most interesting extensions can take time and effort. I hope you found something new along our list and enjoyed learning about them.
That said, there are even more extensions and other projects across the PostgreSQL landscape that are helping to make PostgreSQL a bit better day by day. So keep your eyes open!
Finally, I can’t help but add that TimescaleDB is one of them, granting time-series superpowers to our beloved, reliable PostgreSQL. If you want to add even more functionality to PostgreSQL, explore TimescaleDB—it extends PostgreSQL with things like automatic time-based partitioning and indexing, continuous aggregations, columnar compression, and time-series functionality.
And if you’re using a managed service for PostgreSQL, try Timescale—it’s free for 30 days, no credit card required.