All PostgreSQL Extensions Cheat Sheet

This article is a practical copy-paste guide for Database Engineers, System Administrators, and SREs, aggregating all PostgreSQL extensions…

All PostgreSQL Extensions Cheat Sheet

This article is a practical copy-paste guide for Database Engineers, System Administrators, and SREs, aggregating all PostgreSQL extensions for RPM-based systems. Bookmark it, copy the commands, and build something awesome.

Foreign Data Wrappers (FDW): Connect to external data sources (MySQL, Oracle, Hadoop, etc.) directly.
DBA Tools: Utilities for automation, scheduling, and maintenance.
Application Developers: Tools for HTTP requests, job queues, and caching within SQL.
Data Types & Extra Functions: Support for UUIDs, IP ranges, and SI units.
Replication: Logical replication enhancements and failover slots.
Procedural Languages: Write functions in Lua, R, or Shell scripts.
Monitoring: Advanced query tracking, resource usage, and audit stats.
Analytics: HyperLogLog, top-N sorting, and statistical algorithms.
Oracle Compatibility: Emulate Oracle functions, packages, and behavior.
Indexes: Specialized indexing like RUM and Bigram for search.
Security: Encryption, detailed auditing, and password checks.
Geospatial: PostGIS, routing, and H3 bindings for map data.
Major Features: Massive extensions like Citus, TimescaleDB, and pgvector.
Other Features: Miscellaneous tools for versioning, unit testing, and fact tables.

1. Foreign Data Wrappers (FDW)

PostgreSQL is a data integration hub. FDWs allow you to connect to external data sources (like Oracle, MySQL, Hadoop, or simple files) and query them using standard SQL, just as if they were local tables.

oracle_fdw

The #1 most requested FDW. It is the industry standard for efficient read/write access from PostgreSQL to Oracle databases. Highly optimized for performance (supports push-down) and critical for migration tasks.

Installation (RPM):

dnf install oracle-fdw_18

Activation (SQL):

CREATE EXTENSION oracle_fdw;

mysql_fdw

The standard wrapper for MySQL, MariaDB, and Percona. It supports both reading and writing (INSERT/UPDATE/DELETE) data to the remote MySQL server, making it perfect for hybrid open-source environments.

Installation (RPM):

dnf install mysql-fdw_18

Activation (SQL):

CREATE EXTENSION mysql_fdw;

tds_fdw

The bridge to the Microsoft world. It connects to databases that use the Tabular Data Stream (TDS) protocol, specifically Microsoft SQL Server (MSSQL) and Sybase.

Installation (RPM):

dnf install tds-fdw_18

Activation (SQL):

CREATE EXTENSION tds_fdw;

pgbouncer_fdw

Essential for DBAs. It provides a direct SQL interface to query the internal SHOW commands of a PgBouncer connection pooler (e.g., viewing active clients, pools, or stats) without needing a separate admin console.

Installation (RPM):

dnf install pgbouncer-fdw_18

Activation (SQL):

CREATE EXTENSION pgbouncer_fdw;

sqlite_fdw

Allows you to query SQLite database files residing on the server’s filesystem. Extremely useful for analyzing mobile application logs, embedded device data, or temporary datasets.

Installation (RPM):

dnf install sqlite-fdw_18

Activation (SQL):

CREATE EXTENSION sqlite_fdw;

ogr_fdw

A powerhouse for Geospatial (GIS) data. It uses the OGR library to read dozens of vector formats (Shapefiles, KML, GeoJSON, Excel, etc.) as PostgreSQL tables.

Installation (RPM):

dnf install ogr-fdw_18

Activation (SQL):

CREATE EXTENSION ogr_fdw;

hdfs_fdw

Connects PostgreSQL to the Hadoop File System (HDFS). It allows you to run SQL queries against Hive or HDFS data for Big Data analytics without moving the petabytes of data.

Installation (RPM):

dnf install hdfs-fdw_18

Activation (SQL):

CREATE EXTENSION hdfs_fdw;

multicorn2

A developer favorite. It allows you to write your own Foreign Data Wrappers using Python instead of C. Great for fetching data from REST APIs, CSVs, or custom proprietary formats.

Installation (RPM):

dnf install multicorn2_18

Activation (SQL):

CREATE EXTENSION multicorn;

firebird_fdw

A specific wrapper to connect PostgreSQL to legacy Firebird databases.

Installation (RPM):

dnf install firebird-fdw_18

Activation (SQL):

CREATE EXTENSION firebird_fdw;

odbc_fdw

The fallback option. It implements a wrapper for any remote database that supports the Open Database Connectivity (ODBC) standard. Useful for connecting to very old or niche systems not listed above.

Installation (RPM):

dnf install odbc-fdw_18

Activation (SQL):

CREATE EXTENSION odbc_fdw;

2. DBA Toolset: Management & Maintenance

A Database Administrator’s best friends. These extensions automate maintenance, optimize storage, and provide deep insights into database performance that standard logs cannot.

pg_repack

The “Lifesaver.” It removes bloat from tables and indexes and restores physical order without holding an exclusive lock. Unlike VACUUM FULL, it allows the application to stay online during the process.

Installation (RPM):

dnf install pg_repack_18

Activation (SQL):

CREATE EXTENSION pg_repack; -- Usage is via command line: /usr/pgsql-16/bin/pg_repack -t table -d db

pg_cron

Brings the Linux cron scheduler inside the database. You can schedule SQL commands (like VACUUM or CALL my_procedure()) directly within PostgreSQL.

Installation (RPM):

dnf install pg_cron_18

Activation (SQL): (Requires shared_preload_libraries = 'pg_cron' in postgresql.conf)

CREATE EXTENSION pg_cron;

pg_partman

The standard for partition management. It automates creating new partitions (time-based or ID-based) and detaching old ones. Essential for time-series data.

Installation (RPM):

dnf install pg_partman_18

Activation (SQL):

CREATE EXTENSION pg_partman;

pg_stat_kcache

Goes beyond query timing. It gathers statistics about real filesystem reads/writes and CPU usage per query. When combined with pg_stat_statements, it identifies resource-heavy queries instantly.

Installation (RPM):
Bash

dnf install pg_stat_kcache_18

Activation (SQL): (Requires shared_preload_libraries = 'pg_stat_kcache')

CREATE EXTENSION pg_stat_kcache;

pg_wait_sampling

Tells you why a query is slow. It samples the wait events of processes, allowing you to see if a query is waiting on Disk I/O, Locks, or CPU.

Installation (RPM):

dnf install pg_wait_sampling_18

Activation (SQL): (Requires shared_preload_libraries = 'pg_wait_sampling')

CREATE EXTENSION pg_wait_sampling;

pg_hint_plan

Brings Oracle-style “Hints” to PostgreSQL. You can force the query planner to use a specific index or join method using SQL comments (e.g., /*+ SeqScan(t) */).

Installation (RPM):

dnf install pg_hint_plan_18

Activation (SQL):

CREATE EXTENSION pg_hint_plan;

hypopg

Allows you to create “hypothetical” (virtual) indexes. You can test if an index would improve performance using EXPLAIN without actually spending disk space or time building it.

Installation (RPM):

dnf install hypopg_18

Activation (SQL):

CREATE EXTENSION hypopg;

pg_failover_slots

Solves a major High Availability problem. It makes logical replication slots usable across physical failovers, preventing broken replication streams after a standby promotion.

Installation (RPM):

dnf install pg_failover_slots_18

Activation (SQL):

CREATE EXTENSION pg_failover_slots;

pg_dirtyread

A forensic tool that allows you to read “dead” (deleted or updated) rows that haven’t been vacuumed yet. Useful for recovering accidentally deleted data.

Installation (RPM):

dnf install pg_dirtyread_18

Activation (SQL):

CREATE EXTENSION pg_dirtyread;

pg_squeeze

A modern alternative to pg_repack. It removes unused space from tables and sorts tuples according to an index, using a different logical replication approach.

Installation (RPM):

dnf install pg_squeeze_18

Activation (SQL):

CREATE EXTENSION pg_squeeze;

pgagent

A job scheduler that can be managed via the pgAdmin GUI. It is older than pg_cron but preferred by users who want a visual interface for complex batch jobs.

Installation (RPM):

dnf install pgagent_18

Activation (SQL):

CREATE EXTENSION pgagent;

system_stats

Provides functions to access system-level statistics (CPU load, memory usage, disk info) directly from SQL, allowing you to monitor the OS without leaving the DB.

Installation (RPM):

dnf install system_stats_18

Activation (SQL):

CREATE EXTENSION system_stats;

ddlx

A handy tool that generates SQL DDL scripts (Create Table, Create Index, etc.) for existing objects in the database. Useful for reverse engineering schemas.

Installation (RPM):

dnf install ddlx_18

Activation (SQL):

CREATE EXTENSION ddlx;

pg_profile

A historical workload analyzer. It captures performance statistics over time, allowing you to compare “today’s performance” vs “yesterday’s performance.”

Installation (RPM):

dnf install pg_profile_18

Activation (SQL):

CREATE EXTENSION pg_profile;

pg_qualstats

Keeps statistics on predicates (conditions) found in WHERE and JOIN clauses. Helps you understand which columns are frequently filtered and might need indexing.

Installation (RPM):

dnf install pg_qualstats_18

Activation (SQL):

CREATE EXTENSION pg_qualstats;

pgfincore

Advanced memory management. It provides functions to inspect and manage which file blocks are currently in the OS page cache (RAM).

Installation (RPM):

dnf install pgfincore_18

Activation (SQL):

CREATE EXTENSION pgfincore;

pg_jobmon

A logging and monitoring extension specifically designed to track the status of functions and jobs (often used with pg_partman).

Installation (RPM):

dnf install pg_jobmon_18

Activation (SQL):

CREATE EXTENSION pg_jobmon;

pg_store_plans

Similar to pg_stat_statements, but instead of just text, it stores the actual Execution Plan statistics for queries.

Installation (RPM):

dnf install pg_store_plans_18

Activation (SQL):

CREATE EXTENSION pg_store_plans;

login_hook

Allows you to execute specific code or checks immediately upon user login. Comparable to Oracle’s “After Logon Trigger.”

Installation (RPM):

dnf install login_hook_18

Activation (SQL):

CREATE EXTENSION login_hook;

pg_ddl_deploy

Provides transparent DDL (Data Definition Language) replication. Useful for replicating schema changes automatically in logical replication setups.

Installation (RPM):

dnf install pg_ddl_deploy_18

Activation (SQL):

CREATE EXTENSION pg_ddl_deploy;

pg_prioritize

Allows you to get and set the nice priority (CPU priority) of PostgreSQL backend processes directly from SQL. Useful for ensuring critical queries get CPU preference over background tasks.

Installation (RPM):

dnf install pg_prioritize_18

Activation (SQL):

CREATE EXTENSION pg_prioritize;

pgmeminfo

A lightweight extension that provides access to PostgreSQL memory usage statistics. It helps you debug memory consumption issues by exposing internal memory context details.

Installation (RPM):

dnf install pgmeminfo_18

Activation (SQL):

CREATE EXTENSION pgmeminfo;

pg_readme

A unique documentation tool. It allows you to generate a README.md file (Markdown format) dynamically based on the comments and structure of your database schemas and extensions.

Installation (RPM):

dnf install pg_readme_18

Activation (SQL):

CREATE EXTENSION pg_readme;

pg_readonly

A simple safety switch. It allows you to set all databases in a cluster to “read-only” mode without restarting the server or messing with complex pg_hba.conf rules. Great for maintenance windows.

Installation (RPM):

dnf install pg_readonly_18

Activation (SQL):

CREATE EXTENSION pg_readonly;

pgsql_tweaks

A collection of SQL functions and views designed to assist with daily PostgreSQL development and administration tasks. It acts like a “utility belt” for developers.

Installation (RPM):

dnf install pgsql_tweaks_18

Activation (SQL):

CREATE EXTENSION pgsql_tweaks;

logerrors

Instead of grepping text files, this extension collects statistics about error messages appearing in the PostgreSQL log file, allowing you to query error trends via SQL.

Installation (RPM):

dnf install logerrors_18

Activation (SQL):

CREATE EXTENSION logerrors;

sslutils

Provides functions to generate SSL certificates and keys directly within the database. It is often used by management tools (like pgAdmin) to handle secure connections.

Installation (RPM):

dnf install sslutils_18

Activation (SQL):

CREATE EXTENSION sslutils;

3. Application Developers

Modern databases are more than just storage; they are active parts of the application stack. These extensions allow developers to interact with web services, manage caching, and handle complex logic without leaving the database.

pg_net

The modern choice for HTTP. It enables PostgreSQL to make asynchronous HTTP/HTTPS requests in SQL. Unlike other HTTP extensions, it is non-blocking, making it perfect for triggering webhooks or calling serverless functions without slowing down transactions.

Installation (RPM):

dnf install pg_net_18

Activation (SQL):

CREATE EXTENSION pg_net;

 -- Example: SELECT net.http_get(&amp;amp;#x27;https://api.example.com/data&amp;amp;#x27;);

pgsql_http

The classic HTTP client for PostgreSQL. It allows you to retrieve a web page or call a REST API directly from inside a database function. It is synchronous, meaning the query waits for the response.

Installation (RPM):

dnf install pgsql_http_18

Activation (SQL):

CREATE EXTENSION http;

postgresql_faker

A must-have for testing and staging environments. It generates random-but-meaningful datasets (names, addresses, emails) for functional testing, anonymization, and training data.

Installation (RPM):

dnf install postgresql_faker_18

Activation (SQL):

CREATE EXTENSION faker;

PgQ

Provides a generic, high-performance, lockless queue with a simple API based on SQL functions. Originally developed by Skype, it is a battle-tested solution for batch processing and event handling.

Installation (RPM):

dnf install pgq_18

Activation (SQL):

CREATE EXTENSION pgq;

pgmemcache

Connects PostgreSQL to Memcached. It provides user-defined functions to set, get, and delete keys in a Memcached server, allowing you to invalidate caches directly from database triggers.

Installation (RPM):

dnf install pgmemcache_18

Activation (SQL):

CREATE EXTENSION pgmemcache;

pgsql_gzip

Sometimes you need to save space or handle compressed blobs. This extension brings Gzip compression and decompression functions directly into SQL.

Installation (RPM):

dnf install pgsql_gzip_18

Activation (SQL):

CREATE EXTENSION gzip;

pg_curl

Another tool for transferring data using URL syntax, leveraging the libcurl library. It offers more low-level control over the connection compared to standard HTTP extensions.

Installation (RPM):

dnf install pg_curl_18

Activation (SQL):

CREATE EXTENSION pg_curl;

pg_statement_rollback

Adds server-side transaction control with rollback at the statement level, similar to the behavior found in Oracle or DB2. Useful for porting legacy applications that rely on this specific error-handling behavior.

Installation (RPM):

dnf install pg_statement_rollback_18

Activation (SQL): (Requires shared_preload_libraries = 'pg_statement_rollback')

CREATE EXTENSION pg_statement_rollback;

4. Data Types and Extra Functions

Standard types like int or text are often not enough. These extensions add specialized data types to PostgreSQL, handling complex validation and logic natively so your application code doesn't have to.

ip4r

If you store IP addresses, use this. It is infinitely better than storing IPs as text or inet. It provides efficient indexing support for IPv4/IPv6 ranges, making lookups (e.g., "Is this IP in this subnet?") lightning fast.

Installation (RPM): dnf install ip4r_18
Activation: CREATE EXTENSION ip4r;

pg_uuidv7

Standard UUID v4 is random and causes index fragmentation. UUID v7 is time-ordered, meaning it sorts correctly and is efficient for B-Tree indexes. This extension generates them natively.

Installation (RPM): dnf install postgresql16-pg-uuidv7_18
Activation: CREATE EXTENSION pg_uuidv7;

semver

Implementation of the Semantic Versioning 2.0.0 standard. It allows you to store version numbers (e.g., 1.2.3-beta) and compare them correctly (e.g., 1.10.0 > 1.2.0), which fails if you just use text.

Installation (RPM): dnf install postgresql16-semver_18
Activation: CREATE EXTENSION semver;

postgresql_unit

Implements a datatype for SI units (meters, bytes, kilograms). It handles conversion automatically (e.g., adding 1 km + 500 m results in 1.5 km). Essential for scientific or engineering apps.

Installation (RPM): dnf install postgresql16-unit_18
Activation: CREATE EXTENSION unit;

pguint

PostgreSQL natively supports only signed integers. This extension adds unsigned integer types (uint1, uint2, uint4, uint8), similar to MySQL's unsigned types, doubling the positive range.

Installation (RPM): dnf install postgresql16-pguint_18
Activation: CREATE EXTENSION pguint;

pguri

Provides a dedicated uri data type. It validates URLs strictly (unlike text) and provides functions to extract parts like host, path, or query params directly in SQL.

Installation (RPM): dnf install postgresql16-pguri_18
Activation: CREATE EXTENSION pguri;

timestamp9

Provides nanosecond-precision timestamps (timestamp9). Standard Postgres timestamps only support microsecond precision. Critical for high-frequency trading or scientific logging.

Installation (RPM): dnf install postgresql16-timestamp9_18
Activation: CREATE EXTENSION timestamp9;

sequential_uuids

Implements UUID generators with sequential patterns. It is an alternative to UUID v7 for generating sortable UUIDs to prevent index fragmentation.

Installation (RPM): dnf install postgresql16-sequential-uuids_18
Activation: CREATE EXTENSION sequential_uuids;

pgmp

Wraps the GMP library to provide high-performance arbitrary-precision integers and rational numbers. Use this if standard numeric types are too slow for your complex math.

Installation (RPM): dnf install postgresql16-pgmp_18
Activation: CREATE EXTENSION pgmp;

pgpdf

A niche type specifically for PDF analysis. It allows you to store and validate PDF file structures directly within a database column.

Installation (RPM): dnf install postgresql16-pgpdf_18
Activation: CREATE EXTENSION pgpdf;

pg_extra_time

Adds extra date/time functions and operators not found in the standard library, making date arithmetic easier.

Installation (RPM): dnf install postgresql16-pg-extra-time_18
Activation: CREATE EXTENSION pg_extra_time;

postgresql-numeral

A fun but useful extension that allows input/output of numbers using words (e.g., converts “one hundred twenty” to 120).

Installation (RPM): dnf install postgresql16-numeral_18
Activation: CREATE EXTENSION numeral;

5. Replication

pglogical

The “Grandfather” of Logical Replication. Before Postgres had native logical replication, there was pglogical. It is still widely used because it offers features native replication lacks, such as conflict resolution and multi-master capabilities in specific setups.

Installation (RPM): dnf install pglogical_18
Activation: CREATE EXTENSION pglogical; (Requires shared_preload_libraries = 'pglogical')

6. Procedural Languages & Dev Tools

PostgreSQL allows you to write stored procedures in languages other than PL/pgSQL (like R, Lua, or Shell). It also has powerful tools to debug and optimize your code.

plpgsql_check

The “Linter” for your code. It statically analyzes your PL/pgSQL functions and warns you about errors (like variable typos, SQL injection risks, or unreachable code) before you run them.

Installation (RPM): dnf install postgresql16-plpgsql-check
Activation: CREATE EXTENSION plpgsql_check;

plprofiler

Performance Profiler. It visualizes the execution time of your PL/pgSQL code, line by line. It generates flame graphs so you can see exactly which line of code is slowing down your function.

Installation (RPM): dnf install postgresql16-plprofiler_18
Activation: CREATE EXTENSION plprofiler;

pldebugger

Allows you to “Step Over,” “Step Into,” and set breakpoints in your PL/pgSQL functions using pgAdmin. Essential for fixing complex logic errors.

Installation (RPM): dnf install postgresql16-pldebugger_18
Activation: CREATE EXTENSION pldbgapi; (Requires shared_preload_libraries = 'plugin_debugger')

pg_tle

Trusted Language Extensions. Originally from AWS, now open source. It allows developers to create and install extensions written in SQL or PL/pgSQL without needing file system access to the server.

Installation (RPM): dnf install postgresql16-pg_tle_18
Activation: CREATE EXTENSION pg_tle;

PL/R

A procedural language handler for R. It allows you to write statistical and graphical analysis functions using R packages directly inside the database.

Installation (RPM): dnf install postgresql16-plr_18
Activation: CREATE EXTENSION plr;

PL/Lua

Embeds Lua into PostgreSQL. Lua is famous for being extremely lightweight and fast, making it a great alternative for complex logic where PL/pgSQL feels too slow or verbose.

Installation (RPM): dnf install postgresql16-pllua_18
Activation: CREATE EXTENSION pllua;

luapgsql

A Lua binding for PostgreSQL. While PL/Lua is for writing functions inside the DB, bindings like this are often used for Lua scripts connecting to the DB.

Installation (RPM): dnf install postgresql16-lua_18 (Note: Package name varies by repo)
Activation: CREATE EXTENSION luapgsql;

PL/sh

Allows you to write stored procedures in Shell scripts (bash, sh). Warning: Use with extreme caution as it executes shell commands on the database server.

Installation (RPM): dnf install postgresql16-plsh_18
Activation: CREATE EXTENSION plsh;

7. Monitoring

pg_stat_monitor

Think of this as pg_stat_statements on steroids. It groups queries by time buckets, allowing you to see query performance over time (e.g., "Why was the DB slow at 2:00 PM?") rather than just cumulative stats.

Installation (RPM): dnf install postgresql16-pg-stat-monitor_18
Activation: CREATE EXTENSION pg_stat_monitor; (Requires shared_preload_libraries = 'pg_stat_monitor')

pg_auth_mon

Tracks login attempts (success and failure). Crucial for security monitoring to detect brute-force attacks or unauthorized access attempts.

Installation (RPM): dnf install postgresql16-pg-auth-mon_18
Activation: CREATE EXTENSION pg_auth_mon;

bgw_replstatus

A tiny background worker that cheaply reports the replication status of a node. Useful for external monitoring tools (like Zabbix or Nagios) to check “Is this replica healthy?” without running heavy queries.

Installation (RPM): dnf install postgresql16-bgw-replstatus_18
Activation: CREATE EXTENSION bgw_replstatus;

pgexporter_ext

Designed to work with the Prometheus pg_exporter. It provides additional views and functions to expose custom metrics that the standard exporter doesn’t catch by default.

Installation (RPM): dnf install postgresql16-pgexporter_ext_18
Activation: CREATE EXTENSION pgexporter_ext;

8. Analytics

PostgreSQL is great for OLTP, but with these extensions, it becomes a powerful Analytics (OLAP) engine.

hll (HyperLogLog)

If you need to count distinct users (DAU/MAU) in a massive dataset, standard COUNT(DISTINCT user_id) is slow and memory-heavy. hll does it probabilistically with tiny storage and blazing speed (0.2% error rate).

Installation (RPM): dnf install postgresql16-hll_18
Activation: CREATE EXTENSION hll;

TopN

Returns the top N values in a database according to some criteria instantly. It uses a specialized data structure to avoid sorting the entire table when you only need the "Top 10".

Installation (RPM): dnf install postgresql16-topn_18
Activation: CREATE EXTENSION topn;

tdigest

A data structure for on-line accumulation of rank-based statistics. Perfect for calculating percentiles (e.g., “99th percentile latency”) on live data streams without storing every single data point.

Installation (RPM): dnf install postgresql16-tdigest_18
Activation: CREATE EXTENSION tdigest;

count_distinct

Provides an alternative to COUNT(DISTINCT ...) which often sorts data and kills performance on large sets. This extension uses a hash table approach to speed it up.

Installation (RPM): dnf install postgresql16-count-distinct
Activation: CREATE EXTENSION count_distinct;

pg_incremental

Enables fast, reliable, incremental batch processing. It helps you run batch jobs that pick up exactly where the last one left off.

Installation (RPM): dnf install postgresql16-pg-incremental_18
Activation: CREATE EXTENSION pg_incremental;

extra_window_functions

Adds window functions that are missing from the SQL standard implementation in PostgreSQL, giving data analysts more power in their queries.

Installation (RPM): dnf install postgresql16-extra_window_functions
Activation: CREATE EXTENSION extra_window_functions;

9. Oracle Compatibility

Migrating from Oracle? These extensions bridge the gap, saving you from rewriting thousands of lines of code.

orafce

The “Must-Have” for Migrations. It implements standard Oracle functions (NVL, to_date, ADD_MONTHS) and packages (DBMS_OUTPUT, UTL_FILE) so your PL/SQL code runs on Postgres with minimal changes.

Installation (RPM): dnf install postgresql16-orafce_18
Activation: CREATE EXTENSION orafce;

pg_dbms_job

Provides full compatibility with the Oracle DBMS_JOB package. If your application logic relies on Oracle's job scheduler syntax, this is the drop-in replacement.

Installation (RPM): dnf install postgresql16-pg-dbms-job_18
Activation: CREATE EXTENSION pg_dbms_job;

pg_dbms_lock

Manages advisory locks in a way compatible with the Oracle DBMS_LOCK package. Essential for applications that use user-defined locks for concurrency control.

Installation (RPM): dnf install postgresql16-pg-dbms-lock_18
Activation: CREATE EXTENSION pg_dbms_lock;

pg_dbms_metadata

Extracts DDL (Data Definition Language) of database objects in a format compatible with Oracle’s DBMS_METADATA. Useful for reverse-engineering schemas.

Installation (RPM): dnf install postgresql16-pg-dbms-metadata_18
Activation: CREATE EXTENSION pg_dbms_metadata;

10. Advanced Indexing

pg_bigm

Standard B-Tree indexes cannot efficiently handle LIKE '%text%' (leading wildcard) queries. pg_bigm creates 2-gram indexes that make full-text substring searches incredibly fast.

Installation (RPM): dnf install postgresql16-pg_bigm_18
Activation: CREATE EXTENSION pg_bigm;

rum

An evolution of the GIN index. It is designed for full-text search where you need to return results sorted by ranking (relevance) or include additional information (like timestamps) in the index itself.

Installation (RPM): dnf install postgresql16-rum_18
Activation: CREATE EXTENSION rum;

11. Security

pgaudit

The Compliance Standard. It provides detailed session and object audit logging (who touched what data and when) via the standard logging facility. Essential for GDPR, HIPAA, and SOC2 compliance.

Installation (RPM): dnf install postgresql16-pgaudit_18
Activation: CREATE EXTENSION pgaudit; (Requires configuration in postgresql.conf)

pgsodium

Brings the libsodium library to Postgres. It provides high-level cryptographic algorithms (modern encryption, decryption, hashing, signatures) directly accessible via SQL functions.

Installation (RPM): dnf install postgresql16-pgsodium_18
Activation: CREATE EXTENSION pgsodium;

credcheck

Enforces strict password policies. It checks credentials during user creation, password changes, or user renaming to ensure they meet complexity requirements.

Installation (RPM): dnf install postgresql16-credcheck_18
Activation: CREATE EXTENSION credcheck;

set_user

Allows an administrator to switch to another user (impersonate) with enhanced logging and control, similar to su in Linux. Safer than resetting a user's password to log in as them.

Installation (RPM): dnf install postgresql16-set-user_18
Activation: CREATE EXTENSION set_user;

pg_track_settings

Helps you keep track of PostgreSQL configuration changes. It logs when a setting (GUC) was changed, creating an audit trail for your server configuration.

Installation (RPM): dnf install postgresql16-pg-track-settings_18
Activation: CREATE EXTENSION pg_track_settings;

pgauditlogtofile

An addon to pgaudit that redirects audit logs to a separate, independent file instead of mixing them with the standard PostgreSQL server logs.

Installation (RPM): dnf install postgresql16-pgauditlogtofile_18
Activation: CREATE EXTENSION pgauditlogtofile;

passwordcheck_cracklib

An alternative to credcheck. It uses the standard Linux cracklib library to verify password strength.

Installation (RPM): dnf install postgresql16-passwordcheck_cracklib_18
Activation: CREATE EXTENSION passwordcheck_cracklib;

12. Geospatial (GIS)

PostGIS

The Giant. It transforms PostgreSQL into the world’s most advanced open-source spatial database. It adds support for geographic objects (points, lines, polygons) and hundreds of spatial functions.

Installation (RPM): dnf install postgis34_18 (Note: Package name usually includes PostGIS version)
Activation: CREATE EXTENSION postgis;

pgrouting

Extends PostGIS to provide geospatial routing functionality. It calculates the shortest path (Dijkstra, A*, etc.) on a road network map.

Installation (RPM): dnf install pgrouting_18
Activation: CREATE EXTENSION pgrouting;

h3-pg

PostgreSQL bindings for Uber’s H3 Core Library. It creates a hexagonal hierarchical spatial index, which is superior for analyzing large-scale spatial datasets (like ride-sharing density).

Installation (RPM): dnf install postgresql16-h3_18
Activation: CREATE EXTENSION h3;

pointcloud

Optimized storage for LIDAR data. If you are storing billions of 3D points from laser scanners, standard geometry types will fail. Pointcloud compresses and manages this data efficiently.

Installation (RPM): dnf install pointcloud_18
Activation: CREATE EXTENSION pointcloud;

pgsphere

Contains methods for working with spherical coordinates. Useful for astronomy or global-scale applications where the curvature of the Earth matters significantly.

Installation (RPM): dnf install postgresql16-pgsphere_18
Activation: CREATE EXTENSION pgsphere;

13. Major Features

These are not just simple plugins; they are game-changers that fundamentally alter how PostgreSQL works, turning it into a distributed cluster, a time-series engine, or an AI vector database.

Citus

Turns PostgreSQL into a distributed database. It horizontally scales your database across multiple servers using sharding and replication. Perfect for multi-tenant applications or massive real-time analytics.

Installation (RPM): dnf install citus_18
Activation: CREATE EXTENSION citus; (Requires configuration in postgresql.conf)

TimescaleDB

Optimizes PostgreSQL for time-series and event data (IoT, metrics, crypto). It provides automatic partitioning (hypertables) and compression that is 10x more efficient than standard Postgres.

Installation (RPM): dnf install timescaledb_18
Activation: CREATE EXTENSION timescaledb; (Requires running timescaledb-tune tool first)

pgvector

The engine of the AI Boom. It adds vector similarity search to Postgres. It allows you to store embeddings (from OpenAI, HuggingFace) and perform nearest neighbor searches for RAG (Retrieval-Augmented Generation) applications.

Installation (RPM): dnf install postgresql16-pgvector_18
Activation: CREATE EXTENSION vector;

pg_ivm

Incremental View Maintenance. Unlike a standard REFRESH MATERIALIZED VIEW which re-calculates everything, pg_ivm only computes and applies the incremental changes. It keeps your materialized views up-to-date instantly with minimal cost.

Installation (RPM): dnf install postgresql16-pg_ivm_18
Activation: CREATE EXTENSION pg_ivm;

pg_strom

Accelerates analytics workloads using GPU (CUDA) and NVMe-SSDs. It bypasses the CPU for heavy aggregations, delivering massive speedups for big data queries.

Installation (RPM): dnf install pg_strom_18
Activation: CREATE EXTENSION pg_strom;

jsquery

A specialized language to query jsonb data types. It offers more flexibility and search capabilities for JSON documents than the standard Postgres JSON operators.

Installation (RPM): dnf install postgresql16-jsquery_18
Activation: CREATE EXTENSION jsquery;

temporal_tables

Implements system-versioned tables (SQL:2011 standard). It automatically records the period of time when a row was valid, allowing you to query “what did this row look like last week?”

Installation (RPM): dnf install postgresql16-temporal_tables_18
Activation: CREATE EXTENSION temporal_tables;

pg_background

Allows you to execute arbitrary SQL commands in background worker processes. Useful for offloading heavy tasks (like cleaning up old data) without blocking the main transaction.

Installation (RPM): dnf install postgresql16-pg_background_18
Activation: CREATE EXTENSION pg_background;

periods

Recreates the SYSTEM VERSIONING behavior defined in SQL:2016. It helps manage valid-time and transaction-time history for tables without complex manual triggers.

Installation (RPM): dnf install postgresql16-periods_18
Activation: CREATE EXTENSION periods;

pgspider_ext

An extension to construct a High-Performance SQL Cluster Engine for distributed big data, often used in specialized data warehousing setups.

Installation (RPM): dnf install postgresql16-pgspider_18
Activation: CREATE EXTENSION pgspider;

plproxy

A database partitioning system implemented as a PL language. It acts as a proxy that routes queries to different database shards based on a function logic.

Installation (RPM): dnf install postgresql16-plproxy_18
Activation: CREATE EXTENSION plproxy;

14. Other Features

pg_auto_failover

Monitors and manages automated failover for a Postgres cluster. It is easier to set up than Patroni but powerful enough to handle High Availability (HA) with a “Keeper” node.

Installation (RPM): dnf install pg_auto_failover_18
Activation: CREATE EXTENSION pg_auto_failover;

pgTap

The standard Unit Testing framework for PostgreSQL. It allows you to write tests for your schema, functions, and views using PL/pgSQL.

Installation (RPM): dnf install postgresql16-pgtap_18
Activation: CREATE EXTENSION pgtap;

emaj

A comprehensive tool for recording updates on a set of tables with the capability to rollback these updates to a predefined point in time. Great for complex application versioning.

Installation (RPM): dnf install postgresql16-emaj_18
Activation: CREATE EXTENSION emaj;

pgcryptokey

Allows the creation, rotation, and deletion of cryptographic data keys. It separates key management from data encryption, improving security posture.

Installation (RPM): dnf install postgresql16-pgcryptokey_18
Activation: CREATE EXTENSION pgcryptokey;

table_version

A simple extension for recording row modifications and maintaining a history of table versions.

Installation (RPM): dnf install postgresql16-table_version_18
Activation: CREATE EXTENSION table_version;

pg_fact_tables

Builds fact tables using replicated tables and a queue system. Useful for data warehousing ETL processes inside Postgres.

Installation (RPM): dnf install pg_fact_tables_18
Activation: CREATE EXTENSION pg_fact_tables;

Conclusion

This list proves one thing: PostgreSQL is whatever you want it to be.

Whether you need a GIS engine (PostGIS), an AI vector store (pgvector), or an Oracle replacement (orafce), there is an RPM package waiting for you. This guide is your cheat sheet; bookmark it, copy the commands, and build something awesome.

Which extension is your favorite lifesaver? Let me know in the comments!

Reference

Official RPM Repo: https://yum.postgresql.org/extensions/