Top 5 PostgreSQL Extensions

At Timescale, we’re big fans of PostgreSQL. We asked our team and developer community to recommend their favorite PostgreSQL extensions, and here’s what they said...

PostgreSQL is designed to be easily extensible, and PostgreSQL extensions add extra functionality to your database by modifying and enhancing how it does certain processes.  Moreover, Postgres extensions can help with some of the limitations you may find with vanilla Postgres (such as working efficiently with time-series data) – without the hassle of switching to a whole new database.

Extensions loaded into the database can function just like features that are built-in, and a rich ecosystem of extensions for all manner of use cases has sprung up over the years. For example, extensions can enable you to handle time-series and geospatial data, track the performance of your queries, and more.

It can be tricky to know which extensions are the best or most relevant ones to use, so we did the research for you.  

To compile our list, we asked our team and TimescaleDB Slack community members to recommend their favorite PostgreSQL extensions. The below includes most the frequently recommended extensions, as well as some less widely known - but useful - ones too.

We’ve also included sample queries to help you see how each extension works, what it allows you to do with your data, and determine which ones might be right for you.

1. TimescaleDB

We're biased, but TimescaleDB tops our list (developers agree with us: there are 500K+ active TimescaleDB deployments around the world 🤗).

What is it and why use it?

TimescaleDB is “Postgres for time-series.” It’s packaged as a Postgres extension and is purpose-built for time-series use cases. The most common uses for TimescaleDB involve storing massive amounts of data for cloud infrastructure metrics, product analytics, web analytics, and IoT devices, but there are many more.  These use cases are time-centric, almost solely append-only (lots of INSERTs), and require rapid ingestion of large amounts of data at high ingestion rates during small time windows. Check out our What the heck is time-series data (and why do I need a time-series database)? blog post to learn more.

TimescaleDB also supports full SQL, enables fast, complex queries on your data, and has a variety of other features for simplifying monitoring and analysis, such as real-time aggregation and automated data retention policies. With TimescaleDB, you can also join your time-series data with your relational business data to gain additional insight, something that’s hard to do with pure metrics databases like CrateDB or InfluxDB.

Installation

The best way to get TimescaleDB is via Managed Service for TimescaleDB, our fully-managed database-as-a-service product. You can also download TimescaleDB and run it on your own infrastructure.

👉 Managed Service for TimescaleDB or download TimescaleDB.

TimescaleDB sample query

We’ll use a sample query that analyzes taxi rides during the month of January 2016.

This query has both time-series and relational components, and is a good example of how TimescaleDB allows you to combine time-series data with relational metadata for more insightful analysis.

We want to know: how many rides take place over a day (time-series data) for each rate type (metadata about the rate the customer paid).

SELECT time_bucket('5 minute', pickup_datetime) AS five_min, count(*), rates.description
FROM rides
JOIN rates ON rides.rate_code = rates.rate_code
WHERE rides.rate_code IN (2,3) AND pickup_datetime < '2016-01-02'
GROUP BY five_min, rates.description 
ORDER BY five_min
LIMIT 4;

We get the following results, showing the number of rides that took place in each 5 minute time bucket for each rate type:

-[ RECORD 1 ]--------------------
five_min    | 2016-01-01 00:00:00
count       | 1
description | Newark
-[ RECORD 2 ]--------------------
five_min    | 2016-01-01 00:00:00
count       | 3
description | JFK
-[ RECORD 3 ]--------------------
five_min    | 2016-01-01 00:05:00
count       | 1
description | Newark
-[ RECORD 4 ]--------------------
five_min    | 2016-01-01 00:05:00
count       | 6
description | JFK

2. PostGIS

What is it and why use it?

PostGIS extends Postgres to handle spatial data and data types. It adds support for geographic objects, allowing location queries to be run in SQL. PostGIS adds extra types (like geometry, geography, raster, and others) to the Postgre database, as well as  functions, operators, and index enhancements that apply to these spatial types. These additional functions, operators, index bindings, and types augment the power of Postgres, making it a fast, feature-plenty, and robust spatial database management system.

A core benefit of PostGIS is that geospatial queries can take place at the database level rather than the application level, making them more efficient. PostGIS is useful for many IoT use cases that involve tracking, routing, addressing, and other location-based attributes.

Installation

👉 Get PostGIS

PostGIS sample query

For this one, we'll use a sample query that asks a question about geospatial and time-series elements. It's a great example of how you might combine different Postgres extensions to meet your requirements (in this case, combining PostGIS and TimescaleDB).

We want to know: "How many taxis picked up passengers within 400m of Times Square on New Years Day 2016?"

-- How many taxis pick up rides within 400m of Times Square on New Years Day, grouped by 30 minute buckets.
-- Note: Times Square is at (lat, long) (40.7589,-73.9851)

SELECT time_bucket('30 minutes', pickup_datetime) AS thirty_min, COUNT(*) AS near_times_sq
FROM rides
WHERE ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)) < 400
AND pickup_datetime < '2016-01-01 14:00'
GROUP BY thirty_min ORDER BY thirty_min
LIMIT 5;

The above query produces the following results:

-[ RECORD 1 ]-+--------------------
thirty_min    | 2016-01-01 00:00:00
near_times_sq | 74
-[ RECORD 2 ]-+--------------------
thirty_min    | 2016-01-01 00:30:00
near_times_sq | 102
-[ RECORD 3 ]-+--------------------
thirty_min    | 2016-01-01 01:00:00
near_times_sq | 120
-[ RECORD 4 ]-+--------------------
thirty_min    | 2016-01-01 01:30:00
near_times_sq | 98
-[ RECORD 5 ]-+--------------------
thirty_min    | 2016-01-01 02:00:00
near_times_sq | 112

👉 See how one company, Blue Sky Analytics, combines geospatial and time-series data to power their environmental monitoring platform.

3. pg_stat_statements

What is it and why use it?

pg_stat_statements tracks statistics on the queries executed by a Postgres database. It will help you debug queries, identify slow queries, and generally give you deeper information about how your queries are running. The statistics gathered by the module are made available via a system view named pg_stat_statements.

Install

👉 pg_stat_statements ships with most versions of Postgres.

To enable the extension, run the below on your target database, using psql or your database administration tool of choice:

CREATE EXTENSION pg_stat_statements;

pg_stat_statements sample query

pg_stat_statements allows us to uncover lots of useful information, but we’ll share just one example.

In the query below, we calculate the total and average execution time for all of our queries and show the 100 queries that take the longest:

SELECT (total_time / 1000 / 60) as total, 
(total_time/calls) as avg, 
query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 100;

👉 Learn more about pg_stat_statements

4. ZomboDB

What is it and why use it?

ZomboDB enhances your ability to search text data in Postgres, making indexing and maintaining search data simpler and easier. It uses Elasticsearch as an index type to bring powerful text-search and analytics features to Postgres. Its comprehensive query language and SQL functions enable new and creative ways to query your relational data.

ZomboDB abstracts away Elasticsearch such that it appears as a regular Postgres index. As a native Postgres index type, ZomboDB allows you to CREATE INDEX ... USING ZomboDB on your existing Postgres tables. At that point, ZomboDB takes over and fully manages the remote Elasticsearch index and guarantees transactionally-correct text-search query results.

ZomboDB is worth exploring if Postgres' tsearch features aren’t adequate for your requirements for searching large text content.

Install

👉 Get ZomboDB

ZomboDB sample query

Let’s use the scenario of eCommerce to delve into the power of ZomboDB. Take this sample table called products, representing a catalog of products:

postgres=# SELECT * from products;

-[ RECORD 1 ]-----+-----------------------------------------------------------------------------------------------------------
id                | 1
name              | Magical Widget
keywords          | {magical,widget,round}
short_summary     | A widget that is quite magical
long_description  | Magical Widgets come from the land of Magicville and are capable of things you can't imagine
price             | 9900
inventory_count   | 42
discontinued      | f
availability_date | 2015-08-31
-[ RECORD 2 ]-----+-----------------------------------------------------------------------------------------------------------
id                | 2
name              | Baseball
keywords          | {baseball,sports,round}
short_summary     | It's a baseball
long_description  | Throw it at a person with a big wooden stick and hope they don't hit it
price             | 1249
inventory_count   | 2
discontinued      | f
availability_date | 2015-08-21
-[ RECORD 3 ]-----+-----------------------------------------------------------------------------------------------------------
id                | 3
name              | Telephone
keywords          | {communication,primitive,"alexander graham bell"}
short_summary     | A device to enable long-distance communications
long_description  | Use this to call your friends and family and be annoyed by telemarketers.  Long-distance charges may apply
price             | 1899
inventory_count   | 200
discontinued      | f
availability_date | 2015-08-11
-[ RECORD 4 ]-----+-----------------------------------------------------------------------------------------------------------
id                | 4
name              | Box
keywords          | {wooden,box,"negative space",square}
short_summary     | Just an empty box made of wood
long_description  | A wooden container that will eventually rot away.  Put stuff it in (but not a cat).
price             | 17000
inventory_count   | 0
discontinued      | t
availability_date | 2015-07-01

ZomboDB allows you to run queries using Elasticsearch syntax, like the one below, where we ask “Show me all products with the words ‘sports’ or ‘box’ in any fields”

SELECT * FROM products WHERE products ==> 'sports box';

On our table above, this query would return the following:

-[ RECORD 1 ]-----+------------------------------------------------------------------------------------
id                | 2
name              | Baseball
keywords          | {baseball,sports,round}
short_summary     | It's a baseball
long_description  | Throw it at a person with a big wooden stick and hope they don't hit it
price             | 1249
inventory_count   | 2
discontinued      | f
availability_date | 2015-08-21
-[ RECORD 2 ]-----+------------------------------------------------------------------------------------
id                | 4
name              | Box
keywords          | {wooden,box,"negative space",square}
short_summary     | Just an empty box made of wood
long_description  | A wooden container that will eventually rot away.  Put stuff it in (but not a cat).
price             | 17000
inventory_count   | 0
discontinued      | t
availability_date | 2015-07-01

👉 To learn more about ZomboDB, see their getting started tutorial.

5. Postgres_fdw

What is it and why use it?

The postgres_fdw module enables you to use a Foreign Data Wrapper to access tables on remote Postgres servers (hence the name "fdw"). A Foreign Data Wrapper lets you create proxies for data stored in other Postgres databases, so that they may be queried as if they were coming from a table in the current database.

Postgres_fdw allows you to combine data between two Postgres instances.

Here’s a sample use case:

• You have a Postgres instance (A), and you use postgres_fdw to access data on Postgres instance (B), which is a remote instance.
• You then run queries that combine data from the instance A and B at the database level, rather than at the application level.

Install

👉  Get postgres_fdw by running the below from your psql command line:

CREATE EXTENSION postgres_fdw IF NOT EXISTS;

postgres_fdw sample query

Create a connection to your foreign server

CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '123.45.67.8', dbname ‘postgres’, port '5432');

Creates a connection to a database hosted on ip address 123.45.67.8, with the name postgres at port 5432.

Create user mapping so that users on your database can access the foreign server:

CREATE USER MAPPING FOR postgres 
SERVER myserver 
OPTIONS (user 'postgres', password 'password');

Once that’s done, you can import a schema from your foreign database and access any table.

CREATE SCHEMA schema1;

IMPORT FOREIGN SCHEMA public
FROM SERVER myserver
INTO schema1;

Once imported, you can now access tables of the foreign database on your ‘local’ database, like the example below where we access the metrics table:

SELECT * FROM schema1.metrics
WHERE time < now() - ‘2 days’ :: interval;

👉 Follow this tutorial to learn more about postgres_fdw

⭐ Bonus

Other community suggestions that deserve honorable mentions:

• PLV8, a shared library that provides a Postgres procedural language, powered by V8 Javascript Engine.
• Pg_repack, which helps you maintain tables at peak performance and perform online reindexing on your database.

Learn More

Looking to learn more about extending Postgres for scale and times-series scenarios? Check out this tutorial that covers how to use PostgreSQL, TimescaleDB, and PostGIS to analyze a real-world transportation dataset.

Looking to join a community of TimescaleDB users and time-series enthusiasts? Join the TimescaleDB slack community to meet like-minded developers, ask questions,and get help from the Timescale team (you’ll find me, our co-founders, and engineers active on all channels).