--- title: "pgvector Indexes and Optimizations" publication: "Postgres Weekly" issue: 546 published_at: "2024-03-13" canonical_url: "https://postgresweekly.com/issues/546" --- Plus plotting strategic geographical data, a Postgres podcast, and Michael Stonebraker's next big thing. | #​546 — March 13, 2024 [Web Version](https://postgresweekly.com/issues/546) Together with [![Hasura\](https://res.cloudinary.com/cpress/image/upload/c_fill,g_auto,e_trim,w_285,h_84/e_make_transparent/co_white,e_outline:5/rqccs6gvtpeifpf64bb4.png)](https://hasura.io/graphql/database/postgresql?utm_source=postgres-weekly&utm_medium=newsletter) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/tmrvfpkzogaohqrwchxc.jpg)](https://www.eversql.com/speed-up-postgresql-pgvector-queries-with-indexes/) [Speeding Up `pgvector` Queries with Indexes](https://www.eversql.com/speed-up-postgresql-pgvector-queries-with-indexes/) — If you’re using Postgres and [pgvector](https://github.com/pgvector/pgvector) to work with large language embeddings, _as is the style at the time_, this is a must read. Francesco goes into more detail than you might expect, and you’ll certainly pick up some techniques here. Francesco Tisiot [Postgres Creator Introduces His New Database Startup](https://www.dbos.dev/blog/announcing-dbos) — MIT professor Mike Stonebraker was a principal architect of the Postgres project and, aged 80, refuses to rest on his laurels, with [a fresh project](https://www.dbos.dev/) (DBOS) putting the database closer to the center of the software stack. Mike Stonebraker 💡 [TechCrunch also has a writeup](https://techcrunch.com/2024/03/12/new-startup-from-postgres-creator-puts-the-database-at-heart-of-software-stack/) taking a broader, more _newsy_take. [![\](https://copm.s3.amazonaws.com/60d9c346.png)](https://hasura.io/graphql/database/postgresql?utm_source=postgres-weekly&utm_medium=newsletter) [Instant Realtime GraphQL APIs on PostgreSQL with Hasura](https://hasura.io/graphql/database/postgresql?utm_source=postgres-weekly&utm_medium=newsletter) — Connect Hasura to your PostgreSQL database and get a secure and highly performant GraphQL API. Query tables and views with filtering, sorting, pagination, aggregations and more. Instantly pull any real-time changes to your data with subscriptions. Hasura sponsor ▶[The Path to Citus Con: A Podcast for Postgres](https://pathtocituscon.transistor.fm/) — The _Citus Con_ event itself is now called [POSETTE](https://www.citusdata.com/posette/2024/) but the podcast continues under the old name (for now) with monthly episodes digging deep into Postgres topics. [The latest episode](https://pathtocituscon.transistor.fm/episodes/spinning-up-on-postgres-ai) focuses on the latest around using Postgres in AI and ML use cases, starring Microsoft’s Arda Aytekin along with Claire Giordano and Pino de Candia. Microsoft podcast _IN BRIEF:_ - _Adjust_'s Artur Zakirov is [the latest PostgreSQL _Person of the Week_](https://postgresql.life/post/artur_zakirov/). - 📊 Claire Giordano [shares her fantastically designed slidedeck](https://speakerdeck.com/clairegiordano/beginners-guide-to-partitioning-vs-sharding-in-postgres-claire-giordano-nordic-pgday-2024) from her Nordic PGDay talk _"Beginner's Guide to Partitioning vs Sharding in Postgres."_ - Phil Eaton recently joined the EDB team and [writes about his first month there](https://notes.eatonphil.com/2024-03-11-first-month-on-a-database-team.html). [Creating a Postgres Logical Replication System in Go](https://www.dolthub.com/blog/2024-03-08-postgres-logical-replication/) — The folks behind the (Go-powered) [Dolt database](https://github.com/dolthub/dolt) are working on a feature to replicate data from Postgres, and share what they’ve learnt so far in this particularly detailed post. Zach Musgrave (Dolthub) 🌐 [Using Postgres for (Military) Geoanalytics Tasks](https://klioba.com/how-to-use-postgresql-for-military-geoanalytics-tasks) — If you dislike military scenarios, skip this, but otherwise it’s a well walked through example of using publicly available data and PostGIS to analyze and plot information of strategic importance (though the powers that be almost certainly do this at 1000x the scale). Taras Kloba [Enforcing Join Orders in Postgres](https://www.cybertec-postgresql.com/en/enforcing-join-orders-in-postgresql/) — With SQL being a declarative language, you'd reasonably expect Postgres to handle the complexities of joining tables in the way you require. It usually does a good job, but there are sometimes reasons to force Postgres to take a different approach.. Hans-Jürgen Schönig 📰 Classifieds 📢 6 critical considerations for evaluating distributed PostgreSQL: [New Guide details the implications of Postgres-based vs Postgres compatible](https://www.pgedge.com/landing-pages/distributed-postgresql-buyers-guide?utm_campaign=Distributed%20PostgreSQL%20Buyers%20Guide&utm_source=Postgres%20Weekly&utm_medium=email&utm_content=newsletter). --- ⚠️ Want to restore lost data? Learn how [Neon Point-In-Time Restore](https://neon.tech/blog/point-in-time-recovery-in-postgres?utm_source=cooperpress&utm_medium=newsletter&utm_campaign=postgresweekly&utm_content=classified) helps you recover seconds so you're always prepared when disaster strikes. --- Looking to elevate your database skills? Get up to speed with [Redgate’s PostgreSQL learning resources hub](https://www.red-gate.com/hub/entrypage/postgresql/?utm_source=postgresweekly&utm_medium=email&utm_campaign=mc-202403-t2-sodl-xdb-ubi-gc-all). [How to Do Performance Testing with `pgbench`](https://ftisiot.net/posts/pgbench-test-postgresql/) — A straightforward introduction to using [pgbench](https://www.postgresql.org/docs/current/pgbench.html), a benchmarking tool included with Postgres. Francesco Tisiot [A Postgres Aurora DB Major Version Upgrade with Minimal Downtime](https://eng.lyft.com/postgres-aurora-db-major-version-upgrade-with-minimal-downtime-4e26178f07a0) Jay Patel (Lyft) 🛠 Code and Tools [Procrastinate 2.0: Postgres-Based Task Queue for Python](https://procrastinate.readthedocs.io/en/stable/) — A distributed task processing library for Python that uses Postgres to store task definitions, manage locks and dispatch tasks. Use with Django is a core focus with [version 2.0](https://github.com/procrastinate-org/procrastinate/releases/tag/2.0.0) reworking the Django integration entirely. Jablon and Lemoine [pgCluu 4.0: Postgres Performance Monitoring and Auditing Tool](https://pgcluu.darold.net/) — A two part tool that collects statistics from a running Postgres cluster and then presents performance information for you to inspect. [Version 4.0](https://www.postgresql.org/about/news/pgcluu-v40-released-2820/) is a major release with new features and report types. Gilles Darold [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60,q_auto/pggti3u3hnvemroymafr.jpg)](https://www.pgadmin.org/docs/pgadmin4/8.4/release_notes_8_4.html) - [pgAdmin 4 v8.4](https://www.pgadmin.org/docs/pgadmin4/8.4/release_notes_8_4.html) – Popular admin platform for Postgres_(above)._ - [PeerDB 0.11](https://github.com/PeerDB-io/peerdb/releases/tag/v0.11.0) – Stream data from Postgres to data warehouses, queues and storage engines. - [PostgresNIO 1.21.0](https://github.com/vapor/postgres-nio) – Non-blocking, event-driven Postgres client for Swift. - [Ora2Pg 24.2](https://www.postgresql.org/about/news/ora2pg-242-have-been-released-2823/) – Tooling to migrate from Oracle to Postgres. - [Prisma 5.11](https://github.com/prisma/prisma/releases/tag/5.11.0) – Next-gen ORM for Node.js & TypeScript. - [Slonik 37.3](https://github.com/gajus/slonik) – Node.js Postgres client with type safety. **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|*).