Fresh PostgreSQL Extensions: Hypothetical Indexes, Exact Percentiles, and Incremental ViewsThematic roundup · digest for October 27
Axonometric exploded diagram of PostgreSQL's modular core architecture featuring three new extensions: HypoPG, pg_hdr_histogram, and pg_ivm.
Thematic roundup3 sections≈ 4 min

Fresh PostgreSQL Extensions: Hypothetical Indexes, Exact Percentiles, and Incremental Views

The PostgreSQL ecosystem is famous for its remarkable extensibility: while other databases make you wait years for a major release just to get one feature, the Postgres community solves tough engineering problems with specialized C extensions. Over the past few days, three notable releases landed that tackle classic DBA and backend headaches: testing indexes without touching disk, measuring true latency percentiles, and keeping materialized views in sync without full rebuilds. Let's look at each one.

HypoPG 1.4.4

Creating an index on a table hundreds of gigabytes wide is always nerve-wracking. Even with CONCURRENTLY, building the tree hammers disk I/O and burns CPU. Worse yet, after waiting all that time, EXPLAIN might show the query planner ignoring your shiny new index in favor of a Sequential Scan—meaning all that disk space went to waste.

That's the exact problem solved by the open-source extension HypoPG 1.4.4 (details in the official announcement). It introduces virtual indexes that exist strictly in the memory of your current session and never write a single file to disk.

-- Load the extension in your session
CREATE EXTENSION IF NOT EXISTS hypopg;

-- Create a hypothetical index in fractions of a millisecond
SELECT * FROM hypopg_create_index(
    'CREATE INDEX ON orders (user_id, created_at)'
);

-- Check the plan: the optimizer sees the virtual index!
EXPLAIN SELECT * FROM orders 
WHERE user_id = 42 AND created_at >= NOW() - INTERVAL '7 days';

-- Clear out virtual indexes
SELECT hypopg_reset();

In version 1.4.4, the authors updated internal planner hooks for PostgreSQL 16–18, refined selectivity estimates, and fixed memory leaks in long-running transactions.

pg_hdr_histogram 1.0.0

When we inspect database performance with pg_stat_statements, we usually glance at average execution time (mean_exec_time). In distributed systems, averages lie: ninety-nine queries finish in a millisecond while the hundredth stalls for ten seconds behind a lock. On average everything looks great, while a real user stares at an endless spinner.

Honest monitoring demands percentiles (p95, p99), yet storing every single query latency quickly eats up memory. The pg_hdr_histogram 1.0.0 extension by Alexey Lesovsky (see the release announcement) brings the battle-tested HdrHistogram algorithm into Postgres. It compresses latency histograms into a compact fixed-size buffer accurate to 3–5 significant digits:

-- Enable the extension and query percentiles
CREATE EXTENSION IF NOT EXISTS pg_hdr_histogram;

SELECT 
    hdr_percentile(hist, 0.50) AS p50_ms,
    hdr_percentile(hist, 0.95) AS p95_ms,
    hdr_percentile(hist, 0.99) AS p99_ms
FROM query_performance_log;

The stable 1.0.0 release locks down the binary storage format, confirms PostgreSQL 17 compatibility, and speeds up histogram aggregation across parallel workers.

pg_ivm 1.17

Materialized views (MATERIALIZED VIEW) are great for precomputing heavy analytical reports. The catch in stock PostgreSQL is that REFRESH MATERIALIZED VIEW recomputes the entire view from scratch, blocking reads or hammering disk I/O.

The pg_ivm 1.17 extension (Incremental View Maintenance—see the project announcement) implements incremental updates: when base tables change, only the changed data delta gets recomputed!

-- Create an incrementally maintained materialized view
CREATE EXTENSION IF NOT EXISTS pg_ivm;

SELECT create_immv(
    'daily_sales_summary',
    'SELECT department_id, COUNT(*) AS total_orders, SUM(amount) AS total_revenue 
     FROM orders GROUP BY department_id'
);

Now every INSERT into orders transparently updates the counters in daily_sales_summary in fractions of a millisecond. Update 1.17 improves compatibility with PostgreSQL 18 devel and optimizes delta-tracking triggers. If you're following this space, check out our earlier piece on PostgreSQL 19-ready extensions and pg_ivm.

Together, all three extensions show how mature the Postgres ecosystem is: you can safely test indexes, collect honest telemetry, and speed up reporting tables right inside the database without external workarounds.

Want a deep diveVotes help us choose what to break down in detail. This material has 0 votes.
Sources · 6