--- title: "Postgres 17 has entered the building" publication: "Postgres Weekly" issue: 571 published_at: "2024-09-27" canonical_url: "https://postgresweekly.com/issues/571" --- Plus making Postgres faster with BOLT, building an image search engine, and PostGIS 3.5.0. | #​571 — September 27, 2024 [Web Version](https://postgresweekly.com/issues/571) 🎉 Last week, we warned that this week's issue might be a little late in anticipation of the final release of Postgres 17. Well, it's here, so let's get to it :-) __ _Peter Cooper, your editor_ Together with [![PASS 24\](https://copm.s3.amazonaws.com/044be26d.png)](https://passdatacommunitysummit.com/sessions/?category=Keynote&utm_source=postgresweekly&utm_medium=email&utm_campaign=es-202411-pass-summit-pg-weekly-ad-sept25-em-all) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/v1727358522/ebxxozg8vnblpecfdm9u.jpg)](https://www.postgresql.org/about/news/postgresql-17-released-2936/) [PostgreSQL 17 Released](https://www.postgresql.org/about/news/postgresql-17-released-2936/) — The big one is here. It's the newest major version of Postgres, and it takes a bigger step forward than [even v16.](https://www.postgresql.org/about/news/postgresql-16-released-2715/) Some of what’s new: - Overhauled memory management for vacuuming, resulting in significantly lower memory usage and running time. [More on this here.](https://pganalyze.com/blog/5mins-postgres-17-faster-vacuum-adaptive-radix-trees) - [Incremental backup support.](https://pgdash.io/blog/incremental-backup-in-postgresql-17.html) - [Faster B-tree index scans.](https://www.crunchydata.com/blog/real-world-performance-gains-with-postgres-17-btree-bulk-scans) - [`MERGE` enhancements,](https://databaserookies.wordpress.com/2024/08/09/enhanced-merge-command/) including view support. - New functions to [extract elements from UUIDs.](https://www.postgresql.org/docs/current/functions-uuid.html) - WAL improvements – up to 2x write throughput on some workloads. - Improvements to SQL/JSON support, [including `JSON_TABLE`.](https://www.percona.com/blog/json_table-will-be-in-postgresql-17/) - Bulk loading improvements and perf improvements for `COPY` which gains the `ON_ERROR ignore` option to ignore errors. - [Identity columns on partitioned tables.](https://www.depesz.com/2024/02/07/waiting-for-postgresql-17-support-identity-columns-in-partitioned-tables/) 💡 If you want the complete list of everything's that new, [the release notes](https://www.postgresql.org/docs/17/release-17.html) provide an incredibly thorough bulletpoint walkthrough. [![\](https://copm.s3.amazonaws.com/044be26d.png)](https://passdatacommunitysummit.com/sessions/?category=Keynote&utm_source=postgresweekly&utm_medium=email&utm_campaign=es-202411-pass-summit-pg-weekly-ad-sept25-em-all) [Keynote Speakers Announced for PASS Data Community Summit 2024](https://passdatacommunitysummit.com/sessions/?category=Keynote&utm_source=postgresweekly&utm_medium=email&utm_campaign=es-202411-pass-summit-pg-weekly-ad-sept25-em-all) — 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](https://vondra.me/posts/playing-with-bolt-and-postgres/) — [BOLT](https://github.com/llvm/llvm-project/blob/main/bolt/README.md) 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](https://tembo.io/blog/image-search) — `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) **QUICK BITS:** - ▶️ The _Talking Postgres_ podcast featured [Melanie Plageman talking about becoming a Postgres committer.](https://talkingpostgres.com/episodes/becoming-a-postgres-committer-with-melanie-plageman) We've also been tipped off that an episode with Tom Lane is on the way, so if you don't want to miss it, you can [subscribe to the show here.](https://talkingpostgres.com/subscribe) - ▶️ Meanwhile, over at [Postgres.fm](https://postgres.fm/), they've [been speaking to Postgres committer Peter Geoghegan](https://postgres.fm/episodes/skip-scan) about adding skip scan support to Postgres 17. - ⚡ Serverless Postgres platform _Neon_ was quick out of the gate and [already offers Postgres 17.](https://neon.tech/blog/postgres-17) - 💰 [Supabase has raised $80m](https://techcrunch.com/2024/09/25/supabase-a-postgres-centric-developer-platform-raises-80m-series-c/) in a series C round of funding. [Generating a Few Secure, Random Bytes Without `pgcrypto`](https://brandur.org/fragments/secure-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](https://fljd.in/en/2024/09/19/hierarchical-data-types/) — [hierarchyid](https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16) 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](https://www.postgresql.org/docs/current/ltree.html) provides a more structured option. Florent Jardin [When Postgres Indexing Went Wrong](https://blog.bemi.io/indexing/) — _“TLDR: Be careful when creating indexes — a lesson I learned the hard way when concurrent indexing failed silently.”_ Arjun Lall 📺 [18 Months of `pgvector` Learnings in 47 Minutes](https://www.youtube.com/watch?v=Ua6LDIOVN1s) Avthar Sewrathan (Timescale) 📄 [Writing a Postgres Extension with Pgrx for Visual Query Plans](https://davidgomes.com/writing-postgres-extension-with-pgrx-query-plans/) David Gomes 📄 [Transitioning from Oracle to Postgres: Understanding the Concept of 'Schema'](https://stormatics.tech/blogs/transitioning-from-oracle-to-postgresql-understanding-the-concept-of-schema) Umair Shahid 📄 [Taking Backups from a Standby Server with pgBackRest](https://pgstef.github.io/2024/09/23/pgbackrest_backups_from_the_standby_server.html) Stefan Fercot 📰 Classifieds 🐘 [FINAL DAYS of the State of PostgreSQL survey!](https://form.typeform.com/to/wtUFIZwA) Make sure you’ve shared your thoughts and experience before September 30th. --- 💰 [Dragonfly (25k GitHub stars)](https://www.dragonflydb.io?utm_campaign=cooperpress_postgres_sep24&utm_source=cooper_press&utm_medium=email&utm_content=Postgres%20Weekly) is a modern Redis replacement. Organizations that switch to Dragonfly can reduce infrastructure costs by 80%. --- [Blacksmith runs GitHub Actions up to 2x faster](https://blacksmith.sh/?utm_source=cooperpress&utm_medium=classified&utm_campaign=sep-20&utm_term=postgres) at half the cost with a one-line change. Trusted by 100+ companies like Ashby and Slope. 🛠 Code and Tools [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/zsn9cx7qodr8i2sbikjc.jpg)](https://www.pgmustard.com/) [pgMustard v5: Review Postgres Query Plans Quickly](https://www.pgmustard.com/) — 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](https://postgis.net/2024/09/PostGIS-3.5.0/) — 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.](https://git.osgeo.org/gitea/postgis/postgis/raw/tag/3.5.0/NEWS) PostGIS PSC & OSGeo [libpg-query-node: Use Postgres' SQL Parser from Node.js](https://github.com/launchql/libpg-query-node) — 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](https://github.com/launchql/pgsql-parser) which gives the ability to parse and serialize queries into/from ASTs. Dan Lynch - [River 0.12](https://github.com/riverqueue/river/releases/tag/v0.12.0) – Fast and reliable Postgres-powered background jobs for Go. - [Bytebase 2.23](https://github.com/bytebase/bytebase/releases/tag/2.23.0) – Database devops and CI/CD system for teams. - [pgAdmin4 v8.12](https://www.postgresql.org/about/news/pgadmin-4-v812-released-2937/) – Popular admin platform / panel for Postgres. **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. _Thanks!_ --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*).