PostgreSQL 19 Extensions: pg_hint_plan 1.9.0, pg_ivm 1.17, and PostGIS RC3Thematic roundup · digest for October 22
Infographic patent blueprint of PostgreSQL extensions: pg_hint_plan, pg_ivm, and PostGIS modules plugging into the query planner core.
Thematic roundup3 sections≈ 3 min

PostgreSQL 19 Extensions: pg_hint_plan 1.9.0, pg_ivm 1.17, and PostGIS RC3

PostgreSQL’s superpower has always been extensibility: open planner hooks and index access methods let you tune the core engine for almost any specialized workload.

There’s a catch, though. Every major Postgres release reshapes internal core structs, which means extensions have to update in lockstep. Right after Postgres 19 RC1 landed, three cornerstone extensions shipped compatible releases: query-hint optimizer pg_hint_plan 1.9.0, incremental materialized view engine pg_ivm 1.17, and geospatial heavyweight PostGIS 3.7.0 RC3.

Let’s see what operational headaches these updates solve.

pg_hint_plan 1.9.0

99% of the time, PostgreSQL’s query planner picks a great execution plan using fresh ANALYZE stats. In that remaining 1%—usually gnarly analytical queries with ten JOINs—cost estimation can trip over cardinality and pick a sluggish Nested Loop instead of a Hash Join, stalling production.

pg_hint_plan lets you nudge the planner explicitly via special SQL comments:

/*+
    HashJoin(orders customers)
    IndexScan(orders idx_orders_created_at)
    Leading((orders customers))
*/
SELECT c.name, count(o.id)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-10-01'
GROUP BY c.name;

Version 1.9.0 adapts to Postgres 19’s updated planner node structures and adds hint support for parallel scans on partitioned tables.

pg_ivm 1.17

Standard PostgreSQL materialized views have one brutal flaw: updating them requires REFRESH MATERIALIZED VIEW, which recomputes the entire query from scratch. If the underlying tables hold billions of rows, a refresh can drag on for hours.

pg_ivm (Incremental View Maintenance) fixes this with trigger-based delta updates—insert or update a single row, and only the affected slice of the view updates immediately. We previously covered incremental materialized views in pg_ivm and data security, and version 1.17 finally solves a major DBA pain point: preserving IVM views across major upgrades:

-- Verify IVM metadata integrity before running pg_upgrade
SELECT * FROM pg_ivm_dump_metadata();

-- Create an incremental materialized view in pg_ivm 1.17
CREATE INCREMENTAL MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', order_date) AS day, sum(amount) AS revenue
FROM orders
GROUP BY 1;

Previously, pg_upgrade dropped incremental metadata, forcing you to rebuild every analytical view from scratch. Version 1.17 adds metadata export so upgrading to Postgres 19 is painless.

PostGIS 3.7.0 RC3

Geospatial extension PostGIS 3.7.0 RC3 is wrapping up stabilization ahead of the Postgres 19 GA release.

Key improvements in this release:

  • Faster spatial joins: ST_Intersects and ST_DWithin are now optimized with AVX-512 vector instructions, delivering a 2x speedup on complex geometries.
  • Parallel cluster aggregation: spatial clustering functions like ST_ClusterKMeans now scale across CPU cores alongside the Postgres 19 planner.

Extension Update Takeaways

Having pg_hint_plan, pg_ivm, and PostGIS ready on day one means teams running heavy analytics and strict performance SLAs can plan their move to PostgreSQL 19 without nasty surprises.

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