--- title: "Speeding up index creation" publication: "Postgres Weekly" issue: 564 published_at: "2024-07-24" canonical_url: "https://postgresweekly.com/issues/564" --- Plus we look at savepoints, row pattern recognition, and make a correction on the Postgres trademark. | #​564 — July 24, 2024 [Web Version](https://postgresweekly.com/issues/564) Together with [![Render logo\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_500,h_100/e_make_transparent/co_white,e_outline:7/bil3olyaqqkao0fkbhis.png)](https://render.com?utm_source=cooperpress&utm_medium=cooperpress) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/a9erkdqglxbpiucfetlk.jpg)](https://www.crunchydata.com/blog/magic-tricks-for-postgres-psql-settings-presets-cho-and-saved-queries) [Magic Tricks for `psql`: Settings, Presets, Echo, and Saved Queries](https://www.crunchydata.com/blog/magic-tricks-for-postgres-psql-settings-presets-cho-and-saved-queries) — _“I wanted to share a couple of my favorite things I’ve found that make getting around Postgres better. If you’re just getting started with psql, or haven’t ventured too far out of the defaults, this is the post for you.”_ Elizabeth Christensen ▶[All the Talks from PGConf.dev 2024](https://www.youtube.com/playlist?list=PLTw6f6dqzO1tTW6Ka_bou9rs5YTNxD8Xr#pgconfdev2024) — If you didn’t get to attend May’s [PGConf.dev](https://2024.pgconf.dev/) conference in Canada, you can now catch up with everything that happened on stage, at least, including talks on [autovacuuming going awry](https://www.youtube.com/watch?v=RfTD-Twpvac&list=PLTw6f6dqzO1tTW6Ka_bou9rs5YTNxD8Xr&index=37), [petabyte-scale deployments](https://www.youtube.com/watch?v=Dotlq50ZReQ&list=PLTw6f6dqzO1tTW6Ka_bou9rs5YTNxD8Xr&index=24), [how Amazon RDS scales](https://www.youtube.com/watch?v=98AC_fXkfAA&list=PLTw6f6dqzO1tTW6Ka_bou9rs5YTNxD8Xr&index=5), and [how to make a bigger impact with your Postgres blog posts.](https://www.youtube.com/watch?v=UhDkARpmBGU&list=PLTw6f6dqzO1tTW6Ka_bou9rs5YTNxD8Xr&index=3) PostgreSQL Development Conference [![\](https://copm.s3.amazonaws.com/ecbbf111.jpg)](https://render.com?utm_source=cooperpress&utm_medium=cooperpress) [Render Is Your Fastest Path to Production](https://render.com?utm_source=cooperpress&utm_medium=cooperpress) — Whatever your stack you can build, deploy quickly, update and confidently scale your apps with ease, from the first to the billionth user. Start building with Render for free — the modern cloud for devs. Render sponsor [Speeding Up Index Creation in Postgres](https://www.cybertec-postgresql.com/en/speeding-up-index-creation-in-postgresql/) — Indexes are fundamental to getting the most performance out of your queries, but it can take a long time to create new ones on large datasets. Hans-Jürgen looks at a way to significantly speed up the process. Hans-Jürgen Schönig [_You Make A Good Point!_ — Postgres Savepoints](https://andyatkinson.com/blog/2024/07/22/postgresql-savepoints) — Did you know that within a transaction it’s possible to define points where the remainder of the transaction can be rolled back to? They’re called [savepoints.](https://www.postgresql.org/docs/current/sql-savepoint.html) Andrew Atkinson **QUICK BITS:** - **CORRECTION:** Last week, we inaccurately said that the _PostgreSQL Global Development Group_ holds the _PostgreSQL_ trademark, but Jonathan Katz kindly reminded us that it's actually held by the _PostgreSQL Community Association of Canada._ A silly mistake, indeed, given we include this information in the footer of each issue! 🫢 - On the topic of trademarks, however, if you want to learn more about Postgres's trademark, [there's an official page all about that.](https://www.postgresql.org/about/policies/trademarks/) - 🤖 Over on X/Twitter, a quick look at [how Facebook's new Llama 3.1 model fares on text-to-SQL tasks](https://x.com/rishdotblog/status/1815811715030011967) targeting Postgres. [Bringing Row Pattern Recognition to Postgres](https://pgsqlpgpool.blogspot.com/2024/07/row-pattern-recognition-feature-for.html) — The SQL standard includes an ability to search for a sequence of rows in a table based upon a defined pattern. Tatsuo is looking to bring it to Postgres too. He shows us how it works and the progress of his work. Tatsuo Ishii [Postgres vs. Pinecone for Vector Storage and Queries](https://lantern.dev/blog/postgres-vs-pinecone) — A response to a recent post from Pinecone that [compared Pinecone and Postgres](https://www.pinecone.io/blog/pinecone-vs-pgvector/) for vector search tasks. Narek Galstyan 📰 Classifieds Boost your PostgreSQL skills with [Redgate’s 101 webinar series](https://www.red-gate.com/hub/events/series/postgresql-101) of easy-to-follow, expert hosted sessions. It’s PostgreSQL, simplified. --- 🐿️ Go developer? We have a newsletter for that! Check out [Golang Weekly](https://golangweekly.com/), one of our sister newsletters. [Looking for Hidden Hurdles When Postgres Faces Partitions](https://danolivo.substack.com/p/looking-for-hidden-hurdles-when-postgres) — Andrei ran into a curious situation where partitioning a table introduced some tricky performance issues. Andrei Lepikhov [Which Cloud Providers Support `auto_explain`?](https://www.pgmustard.com/blog/which-cloud-providers-support-auto-explain) — [auto_explain](https://www.postgresql.org/docs/current/auto-explain.html) is a Postgres module for logging execution plans of slow queries automatically, but not every cloud platform supports it out of the box.. Michael Christofides 📄 [Differences Between Running Postgres for Yourself and for Others](https://www.ubicloud.com/blog/difference-between-running-postgres-for-yourself-and-for-others) – Unsurprisingly, building a platform is a whole different game to spinning up a database server for yourself. Burak Yucesoy 📄 [Advanced Postgres Performance Tuning and Monitoring Methods](https://stepchange.work/blog/advanced-postgresql-performance-tuning-and-monitoring) – A list of tools and techniques you might adopt. Niall OHiggins 📄 [Working with Row Level Security in Node.js](https://www.newline.co/@kristiandupont/row-level-security-in-nodejs--7691de55) Kristian Dupont 🛠 Code and Tools [PLV8ify: Turn JavaScript Files into PLV8 Functions](https://github.com/divyenduz/plv8ify) — Bundles TS/JS input and writes SQL containing Postgres functions using the [PLV8](https://plv8.github.io/) extension (which enables using JavaScript as a procedure language in Postgres). Divyendu Singh - [ActiveRecordExtended 3.3](https://github.com/GeorgeKaraszi/ActiveRecordExtended) – Adds additional Postgres functionality to an ActiveRecord / Ruby on Rails app. - [Stateless Postgres Query Router (SPQR) 1.5](https://github.com/pg-sharding/spqr/discussions/724) – An approach to horizontal sharding, originally built at Yandex Cloud. ([GitHub repo](https://github.com/pg-sharding/spqr)) - [River 0.10](https://github.com/riverqueue/river) – Fast and reliable Postgres-powered background jobs for Go. - [Piccolo 1.14](https://github.com/piccolo-orm/piccolo) – User-friendly Python ORM and query builder. - [PGSync 3.2](https://github.com/toluaina/pgsync) – Postgres to Elasticsearch/OpenSearch sync. - [Mikro ORM 6.3](https://github.com/mikro-orm/mikro-orm) – TypeScript ORM for Node.js. **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|*).