One of PostgreSQL’s greatest superpowers is its extension system. With a single command, you can enable new features that feel like they were built into the core database all along.

In this guide, we’ll explore five PostgreSQL extensions that are absolutely worth installing — whether you’re tuning performance, generating UUIDs, hashing data, or working with geospatial information.

All of these can be enabled with a simple:

CREATE EXTENSION extension_name;

Let’s dive in.

1. pg_stat_statements — The Performance Tuning Superpower

If you install only one extension, make it pg_stat_statements. It logs aggregate statistics about every SQL query: how often it runs, how long it takes, and how much time it spent on I/O.

Unlike most extensions, pg_stat_statements must be loaded when PostgreSQL starts. If you try to use it without preloading it, you’ll see this error:

ERROR: pg_stat_statements must be loaded via "shared_preload_libraries"

Step 1 — Enable it in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Then restart PostgreSQL so it takes effect.

Step 2 — Install the extension:

CREATE EXTENSION pg_stat_statements;

After that, you're ready to view real query statistics. For example, here are your top slowest or most expensive queries:

SELECT
  query,
  calls,
  total_exec_time AS total_time,
  mean_exec_time AS mean_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This is one of the most valuable tools in all of PostgreSQL — especially in production — because it tells you exactly where to focus your tuning efforts!

2. pgcrypto — Useful Hashing and Random Data Utilities

The pgcrypto extension gives PostgreSQL a set of cryptographic functions, but most people use it for safe hashing and generating secure random values. It’s not meant to replace your application's security layer — but it is great for tasks that naturally belong inside the database.

Enable it:

CREATE EXTENSION pgcrypto;

Generate Secure Random UUIDs
If your tables use UUID primary keys, pgcrypto includes a secure random generator:

SELECT gen_random_uuid();

This is extremely common in distributed systems or anywhere sequential IDs shouldn’t be exposed.

Hash Values for Deduplication or Comparison
Hashing is one-way — you cannot decrypt it — which makes it useful for:

• Detecting duplicate records
• Comparing datasets without exposing raw values
• Obscuring identifiers in logs or exports

Example: computing a SHA-256 hash

SELECT encode(digest('customer email here', 'sha256'), 'hex');

This allows two systems to compare hashed values (like emails) without sharing the original data.

Generate Random Tokens
You can create random values for API tokens, session IDs, or test data:

SELECT encode(gen_random_bytes(16), 'hex') AS api_token;

This is perfect for scripts or migrations that need unique random strings without extra application code.

While pgcrypto also supports full encryption and decryption functions, those require careful key management and are rarely appropriate inside SQL. For most applications, it's safer to handle real encryption at the application layer.

Think of pgcrypto as a utility toolbox: excellent for hashing, UUID generation, and random data — not a full security solution on its own.

3. intarray — Fast Operations on Integer Arrays

PostgreSQL supports arrays natively, and the intarray extension makes operations on integer arrays dramatically faster and easier. If your application stores lists of IDs — like tag IDs, permission IDs, feature flags, or category filters — intarray gives you high-performance tools for querying and manipulating them.

Enable it:

CREATE EXTENSION intarray;

Check Whether an Array Contains Specific Values

SELECT '{1,2,3,4}'::int[] @> '{2,3}'::int[];  -- true

Perfect for “does this user have all required permissions?” or “does this product include these selected tags?”.

Check Whether Two Arrays Overlap

SELECT '{1,2,3}'::int[] && '{3,9,12}'::int[];  -- true

Useful for tag matching, recommendations, and searching for related items.

Sort an Integer Array

SELECT sort('{5,2,9,1}'::int[]);  
-- {1,2,5,9}

Sorting arrays is helpful when you want stable, predictable ordering for comparisons, caching, logs, or storing normalized values.

Remove Duplicate Elements

SELECT uniq('{3,3,2,1,1}'::int[]);  
-- {1,2,3}

Great when user-generated input, imports, or ETL processes produce arrays with repeated values.

Find the Index Position of a Value

SELECT idx('{10,20,30,40}', 30);  
-- 3 (1-based index)

This can be surprisingly useful when parsing list-like metadata or debugging problematic array values. PostgreSQL arrays are a 1-based index, so positions start at 1, not 0

Fast Indexing for Array Queries

The intarray extension includes special operator classes that allow PostgreSQL to index integer arrays efficiently using GIN or GiST. This is essential for production workloads where filtering by tags, category IDs, or permission sets is common.

CREATE INDEX idx_products_tags 
ON products USING GIN (tag_ids intarray_ops);

In this example, products is a hypothetical table with a column tag_ids INT[], similar to how many real applications store lists of tags or category identifiers.

With this index in place, queries like “find products that match at least one of these tags” become fast:

SELECT *
FROM products
WHERE tag_ids && '{10,11}';

And searches like “find products that contain all of these tags” become instant:

SELECT *
FROM products
WHERE tag_ids @> '{4,9}';

These operators mirror real-world filtering systems used in e-commerce, content tagging, search engines, and permission models. intarray makes these kinds of searches extremely efficient with minimal setup.

intarray doesn’t replace a normalized schema — but for analytics, metadata attributes, event logs, or search-heavy applications, it adds powerful querying and indexing capabilities with almost no overhead.

4. uuid-ossp — Generate UUIDs Natively in PostgreSQL

UUIDs are extremely common for distributed systems, event logs, and modern application architectures. The uuid-ossp extension lets PostgreSQL generate UUIDs directly.

Enable it:

CREATE EXTENSION "uuid-ossp";

Example: generate a UUIDv4

SELECT uuid_generate_v4();

UUIDv4 is the most commonly used version for primary keys because it’s random, globally unique, and avoids sequence bottlenecks.

If you're already using pgcrypto, you can also use gen_random_uuid() — but uuid-ossp remains widely used across production systems.

5. PostGIS — A Gentle Introduction to Geospatial Power

PostGIS turns PostgreSQL into a full-featured spatial database. If you’ve ever worked with locations, coordinates, distances, boundaries, or maps, this extension is a game changer.

Important: PostGIS does not come bundled with PostgreSQL. If you see “extension postgis is not available”, you must install the PostGIS package on the server first. On Debian with PostgreSQL 18, that looks like:

sudo apt update
sudo apt-get install postgresql-18-postgis

After installing the system package, you can enable PostGIS in your database:

CREATE EXTENSION postgis;

Example: store a geographic point

SELECT ST_Point(-71.0589, 42.3601) AS boston_location;

Example: compute distance between two points

SELECT ST_Distance(
  ST_MakePoint(-71.0589, 42.3601)::geography,
  ST_MakePoint(-74.0060, 40.7128)::geography
) AS distance_meters;

Even if you never become a full GIS expert, knowing the basics of PostGIS opens the door to solving complex location-based problems with just a few SQL queries.

Conclusion

PostgreSQL’s extension ecosystem is one of the reasons it has become the most loved relational database in the world. Extensions like pg_stat_statements, pgcrypto, intarray, uuid-ossp, and PostGIS provide enormous power with almost zero friction.

This article only scratches the surface — PostgreSQL includes dozens of extensions by default, and many more are available through your OS package manager or the official pgxn (PostgreSQL Extension Network).

You can explore more by running:

SELECT * FROM pg_available_extensions ORDER BY name;

Take some time to browse what’s available. You may find an extension that solves a problem you didn’t even realize PostgreSQL could handle!