~postgresweekly | Issue 540 - Even better robots writing SQL (8)

Towards an Ideal Postgres Extension Ecosystem — Tembo hired David Wheeler, creator of PGXN, to work on the Postgres extension ecosystem, and he’s begun to share his initial ideas on how things could look. While there are several independent attempts to aggregate Postgres extensions (we even do it a bit ourselves), could Postgres benefit from a more official, authoritative approach?

pgvector 0.6.0: Now ~30x Faster with Parallel Index Builds — The pgvector vector similarity search extension is arguably the Postgres extension of the moment, and it keeps getting better. v0.6 introduces parallel index builds for HNSW, a huge win for rebuilding indexes potentially covering millions of vectors.

Instant Realtime GraphQL APIs on PostgreSQL with Hasura — 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.

DuckDB's Multi-Database Support — DuckDB is an SQLite-esque in-process DBMS but focused on OLAP use cases. With the introduction of a pluggable storage and transactional layer, DuckDB can now connect to Postgres (or MySQL or SQLite) databases and work with them. This opens up some interesting use cases like moving data between different databases or formats (including Parquet) all from within DuckDB.

Getting JSON and SVG from PostGIS into Google Sheets — A walk through using pg_featureserv to create JSON and pg_svg to create SVG images directly from your database to feed into a spreadsheet.

How to Load Stack Overflow's StackExchange Data into Postgres — It’s an interesting process in itself, but the dataset offers potential for experimenting with your analytics skills or just seeing how Postgres scales.

Partitioning Postgres Tables by Timestamp Based UUIDs — A story from the Elixir forum about partitioning a table with 28 million rows in order to improve query speeds. (Note: The approach shown is very much targeted at Elixir developers.)

pg_show_plans: Show Query Plans of All Running Statements — Lets you get live access via SELECT * FROM pg_show_plans or you can see it joined against pg_stat_activity via the pg_show_plans_q view.