PostgreSQL Extensions in Action

PostgreSQL already provides many features required for production database environments, including query planning, native partitioning, logging, and autovacuum. As workloads grow, however, some operational requirements need additional capabilities.

Common examples include identifying queries that consume the most resources, maintaining an audit trail of database activity, implementing AI-powered search without introducing another database, reclaiming disk space from bloated tables, and automating partition maintenance.

PostgreSQL extensions provide these additional capabilities while keeping the functionality within the PostgreSQL ecosystem. This article covers five extensions that address these production requirements pg_stat_statements, pgAudit, pgvector, pg_repack, and pg_partman.

pg_stat_statements: Query Performance Visibility

pg_stat_statements is a PostgreSQL core module that collects statistics for normalized SQL statements across the database workload. It provides information about query execution, including calls, execution time, rows, WAL generation, I/O activity, JIT usage, and parallel-worker activity.

The extension must be loaded through shared_preload_libraries, which requires a PostgreSQL restart.

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = auto

After restarting PostgreSQL, create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The main configuration parameters are:

Query Performance Analysis

For query workload analysis, pg_stat_statements provides several execution and resource-related metrics. The most commonly used metrics are calls, total_exec_time, and mean_exec_time, which show execution frequency, cumulative execution time, and average execution time per call.

Additional resource-related metrics include wal_bytes, shared_blks_hit, and shared_blks_read. The dealloc and stats_reset columns provide information about statement tracking capacity and the current statistics state.

The following query can be used to identify the statements with the highest cumulative execution time:

SELECT
    calls,
    total_exec_time,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

The output shows the number of executions, total execution time, average execution time, rows, and the normalized query text.

In this example, the first query was executed 15,201 times with a total execution time of 7,248.567 ms and an average execution time of 0.48 ms. The high execution count makes its cumulative execution time the highest among the queries shown.

The third query has an average execution time of 178.40 ms but was executed only 12 times. This shows why both execution frequency and execution time should be considered when analyzing query workload.

total_exec_time is useful for identifying queries that contribute the most cumulative execution time, while mean_exec_time helps identify queries with high execution cost per call. Analyzing both metrics together provides a better view of the actual workload.

PostgreSQL 18 also provides additional information through pg_stat_statements. CREATE TABLE AS and DECLARE statements are tracked with query IDs. The parallel_workers_to_launch and parallel_workers_launched columns provide visibility into parallel execution, while wal_buffers_full provides information about WAL-buffer pressure. SET-value parameterization also helps reduce duplicate entries in the statistics.

pgAudit: Compliance-Grade Audit Trail

PostgreSQL logging can capture SQL activity using log_statement = ‘all’, but detailed auditing often requires additional classification and object-level information.

pgAudit extends PostgreSQL logging with structured session-level and object-level audit records. It can capture database operations such as SELECT, INSERT, UPDATE, DELETE, DDL statements, role changes, and other supported operations.

pgAudit must be loaded through shared_preload_libraries, which requires a PostgreSQL restart.

-- postgresql.conf
shared_preload_libraries = 'pgaudit'

After restarting PostgreSQL, create the extension:

CREATE EXTENSION pgaudit;

The audit behavior can then be configured using the following parameters:

pgaudit.log = 'ddl,write'
pgaudit.log_catalog = off
pgaudit.log_param = on
pgaudit.role = 'audit_scope'

The pgaudit.log parameter controls which audit classes are captured. The available classes include READ, WRITE, FUNCTION, ROLE, DDL, MISC, MISC_SET, and ALL.

For example:

pgaudit.log = 'read,write,ddl'

This configuration enables auditing for read, write, and DDL operations.

Object-level auditing can be configured using pgaudit.role when detailed auditing is required only for specific database objects.

Audit Log Example

A typical pgAudit log contains connection information, the audit class, command type, object type, object name, and the executed SQL statement. The following example shows READ, WRITE, and DDL operations captured during a database session.

The log demonstrates how pgAudit records different database activities within the same session.

• The first entry shows a successful database connection.
• The READ operation records a SELECT statement executed on public.customers.
• The WRITE operation captures an INSERT statement executed on public.orders.
• The DDL operation records a CREATE TABLE statement executed on public.audit_test.
• The final entry shows the session disconnection information.

This structured format makes it easier to trace database activity, identify affected objects, and review executed SQL statements during auditing and compliance investigations.

Audit configuration should be based on the required level of visibility. Enabling extensive auditing increases logging volume and therefore increases I/O, storage, and processing requirements.

The following points should be considered when deploying pgAudit:

• Ship audit logs to an external logging system when long-term retention is required.
• Protect audit logs from unauthorized modification or deletion.
• Avoid enabling unnecessary audit classes.
• Evaluate the security impact before enabling pgaudit.log_param.
• Do not use pgAudit as a replacement for WAL or forensic analysis.
• Restrict superuser access because superuser activity is not reliably covered by normal auditing.
• Use a pgAudit version compatible with the PostgreSQL version.
  
  

pgvector: Vector Similarity Search

AI applications commonly require semantic search, recommendation systems, and Retrieval-Augmented Generation workloads. These applications use embeddings to represent data as numerical vectors and then search for vectors that are similar to a query vector.

pgvector adds a vector data type and distance operators directly to PostgreSQL. This allows embeddings to be stored alongside relational application data without requiring a separate vector database for workloads that can be handled within PostgreSQL.

A vector is a list of numerical values such as:

[0.21, -0.43, 0.87, ...]

An embedding is a vector generated by an AI model that represents the meaning or characteristics of data. pgvector stores and searches these vectors; it does not generate the embeddings.

The extension can be enabled using:

CREATE EXTENSION vector;

A table can then contain a vector column:

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding vector(3)
);

Vector values can be inserted as follows:

INSERT INTO items (embedding)
VALUES
    ('[1,2,3]'),
    ('[4,5,6]'),
    ('[2,3,4]');

For similarity searches on larger datasets, an HNSW index can be created:

CREATE INDEX items_embedding_hnsw
ON items USING hnsw (embedding vector_l2_ops);

A similarity search can then be performed using the distance operator:

SELECT
    id,
    embedding,
    embedding <-> '[3,1,2]' AS distance
FROM items
ORDER BY embedding <-> '[3,1,2]'
LIMIT 3;

The following example shows the complete execution of the extension creation, table creation, vector insertion, HNSW index creation, and similarity search.

The query returns the rows ordered by their distance from the query vector. A lower distance indicates greater similarity for the L2 distance operator used in this example.

pgvector supports two commonly used approximate nearest-neighbor indexing methods: IVFFlat and HNSW.

IVFFlat generally provides faster index builds and lower memory usage. Its search behavior can be adjusted using lists and probes.

HNSW generally provides higher recall but requires more memory. It also supports parallel index builds.

Filtered vector searches require additional consideration. A typical query can combine vector similarity with a relational filter:

SELECT
    id,
    embedding
FROM items
WHERE tenant_id = 100
ORDER BY embedding <-> '[3,1,2]'
LIMIT 10;

When an approximate vector index is combined with a WHERE condition, the initial index scan may not return enough rows that satisfy the filter. Iterative scans, available from pgvector 0.8.0, allow the search to continue until enough qualifying rows are found.

pg_repack: Online Bloat Removal

Tables that experience frequent UPDATE and DELETE operations can accumulate dead tuples and become significantly larger than their live data.

VACUUM makes dead space available for reuse within the table, but it normally does not return that space to the operating system. VACUUM FULL can reclaim physical space, but it requires a strong table lock for the duration of the operation.

pg_repack uses a different approach to rebuild the table and its indexes while keeping the blocking period small. It creates a new copy of the table, rebuilds the indexes, applies changes that occurred during the operation, and then swaps the rebuilt structure with the original table.

Before running pg_repack, check the number of rows and the current table size:

SELECT count(*)
FROM public.invoices;
SELECT
    pg_size_pretty(
        pg_total_relation_size('public.invoices')
    ) AS total_size;

In this example, the table contains 4.8 million rows and its total size is 6,198 MB.

Create the extension:

CREATE EXTENSION pg_repack;

The table can then be repacked using:

pg_repack --table=public.invoices billing_db

The following output shows the complete pg_repack operation, including index rebuilding and the final table swap.

After the operation, check the table size again:

SELECT
    pg_size_pretty(
        pg_total_relation_size('public.invoices')
    ) AS total_size;

The table size was reduced from 6,198 MB to 3,412 MB, representing approximately a 45% reduction in physical size.

The main operational consideration is disk space. During the rebuild, the original table and indexes remain available while new table and index structures are created. The required temporary space depends on the table and index sizes and the method used during the operation.

Before running pg_repack, check the following:

• Available disk space.
• Current table and index sizes.
• Temporary storage requirements.
• Database connectivity.
• Required privileges.
• Expected maintenance duration.
• Locking requirements during the final table swap.

PostgreSQL 19 introduces the native REPACK command for reclaiming table storage. The command supports the CONCURRENTLY option, which allows normal reads and writes to continue while the table is being repacked, with the stronger lock generally limited to the final file-swap stage. PostgreSQL 19 also provides the pg_stat_progress_repack view for monitoring repack progress.

pg_partman: Automated Partition Lifecycle

PostgreSQL provides native declarative partitioning, but manually creating future partitions and removing old partitions can become difficult as the amount of data increases.

pg_partman works on top of PostgreSQL declarative partitioning and automates the partition lifecycle. It supports RANGE and LIST partitioning and can create future partitions and manage retention using its background worker or an external scheduler.

pg_partman does not replace PostgreSQL’s partitioning engine. PostgreSQL continues to handle the actual partitioning, while pg_partman provides automation for partition maintenance.

The extension can be enabled using:

CREATE EXTENSION pg_partman;

A time-based partitioned table can be created using:

CREATE TABLE public.events (
    id bigint,
    event_time timestamptz NOT NULL,
    event_name text
) PARTITION BY RANGE (event_time);

The table can then be registered with pg_partman:

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'event_time',
    p_interval     => '1 day',
    p_premake      => 7
);

This configuration uses a one-day partition interval and creates seven future partitions.

The following output shows the partition creation process and the partitions generated by pg_partman.

The output shows seven daily partitions created for the events table, from events_p2026_08_30 through events_p2026_09_05. This demonstrates how pg_partman prepares future partitions based on the configured one-day interval and premake value.

Maintenance can be executed using:

CALL partman.run_maintenance_proc();

Once configured, pg_partman can create future partitions automatically and apply retention rules to older partitions. This removes the need to manually create partitions as new data arrives.

The main operational capabilities include:

• Automatic creation of future partitions.
• Retention-based drop or detach of old partitions.
• Template-based propagation of indexes and constraints.
• Background-worker-based maintenance.
• External scheduler support.
• Automated partition lifecycle management.

A template table can be used to propagate common indexes, constraints, and other properties to newly created partitions.

For time-based partitioning, using UTC for the control column helps avoid retention-boundary differences caused by timezone conversions. The role used for automated maintenance should also follow the principle of least privilege.

Conclusion

PostgreSQL extensions provide additional capabilities for production workloads. pg_stat_statements improves query performance visibility, pgAudit provides database auditing, pgvector supports AI and vector search, pg_repack helps remove table bloat, and pg_partman automates partition management.

Before deploying an extension, its configuration, resource requirements, security, version compatibility, and operational impact should be evaluated.

References

• PostgreSQL Documentation – pg_stat_statements
• pgvector Documentation