~postgresweekly | Issue 571 - Postgres 17 has entered the building (10)

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:

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.

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..

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.

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.

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.

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

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.

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.

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.