PostgreSQL 17 Released — The big one is here. It's the newest major version of Postgres, and it takes a bigger step forward than even v16. Some of what’s new:

• Overhauled memory management for vacuuming, resulting in significantly lower memory usage and running time. More on this here.
• Incremental backup support.
• Faster B-tree index scans.
• MERGE enhancements, including view support.
• New functions to extract elements from UUIDs.
• WAL improvements – up to 2x write throughput on some workloads.
• Improvements to SQL/JSON support, including JSON_TABLE.
• Bulk loading improvements and perf improvements for COPY which gains the ON_ERROR ignore option to ignore errors.
• Identity columns on partitioned tables.

Keynote Speakers Announced for PASS Data Community Summit 2024 — PASS Summit have just unveiled their keynote speakers for this year’s event (November, 4-8, 2024). Featuring keynotes from Microsoft, Redgate and a community expert, buy your 3-day ticket now before the next price bump.

PASS Data Community Summit sponsor

Playing with BOLT to Get a Faster Postgres — BOLT is a tool from the LLVM project that optimizes binaries after they’re compiled by way of analyzing profiler data. Could it be used to speed up Postgres? Yes! But it’s complicated..

Tomas Vondra

Building an Image Search Engine on Postgres — pgvector strikes again. The technique is simple: take images, create embeddings of them, then use the embeddings of other supplied images to find similar images based via a vector similarity search.

Adam Hendel (Tembo)

Generating a Few Secure, Random Bytes Without pgcrypto — random() gives you pseudo random numbers, but pgcrypto makes it possible to get much better randomness. If your server (perhaps a local one) isn’t built against OpenSSL, though, it won’t be available. Luckily, Brandur found an interesting alternative option.

Brandur Leach

Hierarchical Data Types in Postgres — hierarchyid is a special type in SQL Server for representing positions within a hierarchy (such as a tree). You could do it with any integer column in Postgres, but if you want something more structured, ltree provides a more structured option.

Florent Jardin

When Postgres Indexing Went Wrong — “TLDR: Be careful when creating indexes — a lesson I learned the hard way when concurrent indexing failed silently.”

Arjun Lall

pgMustard v5: Review Postgres Query Plans Quickly — A tool for helping review Postgres query plans, now with enhanced Postgres 17 support and new tip types for things like suboptimal JIT compilation and excessive trigger times. You only get five uses for free, though, as it’s ultimately a paid service.

Michael Christofides

PostGIS 3.5.0 Released — The latest release of the popular geospatial data indexing and querying extension landed hours before Postgres 17 final, so it still recommends RC1, but it should be good. Breaking changes are listed here.

PostGIS PSC & OSGeo

libpg-query-node: Use Postgres' SQL Parser from Node.js — Use Postgres’s SQL parser at a low level from Node via libpg_query. If you want something higher level, this library is used by pgsql-parser which gives the ability to parse and serialize queries into/from ASTs.

Dan Lynch