~postgresweekly | Bookmarks (156)

pgddl / ddlx: DDL EXtractor Functions — An SQL-only extension that can create SQL DDL (e.g. CREATE TABLE) for system catalogs / table schemas, etc. since using pg_dump doesn’t suit every scenario.

Creating Multi-Tenant Queues — Spinning up a basic queue on Postgres is reasonably easy, but what about if you don’t want one user’s jobs hogging the limelight? Make things more fair with separate queues for each user.

PDF: The Future of MySQL is Postgres — The title of this slidedeck certainly caught my eye! It’s a slidedeck from a talk given at Postgres Conference 2024 last week. One of the speakers explains all here. They’re working on a Postgres extension to enable MySQL apps to run on Postgres without any code changes (so it’ll support the wire protocol, SQL dialect, etc.) An...

The Architecture of PGXN v2 — David Wheeler, and others, are working on a major update to the PGXN approach to providing extension distribution, discovery, and tooling for the Postgres ecosystem. This new wiki page outlines the current state of play and where things could head next.

node-pg-migrate 7.0: Database Migration Management for Postgres — Lots of cleanups and refactoring from the project’s new maintainer, Shinigami, also a core maintainer of Vite.

pgvector-remote: A pgvector Fork to Work with Remote Vectors — Pinecone only, for now. There's a blog post introducing the idea.

ParadeDB: An Elasticsearch Alternative Built on Postgres — Taking the common ‘cloud offering + open source project’ approach, ParadeDB offers an AGPL licensed (repo here) way to speed up Postgres for search and analytics work.

Vasco: MIC & MINE Statistics Extension — Maximal information coefficient (MIC) and maximal information-based nonparametric exploration (MINE) are statistical measures that can be used to explore multi-dimensional datasets and find important relationships within them.

Postgres + SQLite = pglite? — Not to be confused with the PGlite WASM build of Postgres, this is a quick Go-powered proof of concept to put the Postgres wire protocol in front of SQLite.

trdsql: Tool to Execute SQL Queries Upon Text-Based Data — A CLI tool that can execute SQL queries (in Postgres or MySQL syntax) on CSV, LTSV, JSON, YAML and TBLN files. It can also be used as a library within Go apps.

Use Cases for Merging and Splitting Partitions with Minimal Locking in Postgres 17 — Some interesting new capabilities regarding managing partitioned tables are coming to Postgres 17.

The Postgres 17 Features I'm Most Waiting for — It’s still several months away, but Postgres 17 is shaping up to be yet another great release. A lot of items in today's issue are about Postgres 17 – everyone's getting excited!

📊 Ten Years of Improvements in Postgres's Optimizer — “I decided to run the join order benchmark (JOB)1 on PostgreSQL 8 through 16. I recorded the 90th percentile query latency for each database version.”

Improve Postgres Performance and Reduce Costs with OtterTune — Automatically tune and optimize knobs, plus get index and query recommendations, along with table and autovacuum insights. Customers have improved performance over 90% and saved thousands of dollars in database labor costs. Do a free, 30-day trial.

Supabase Declares 'General Availability' — I hadn’t noticed Supabase has been rocking ‘beta’ all this time, but the Postgres, auth, realtime, and edge functions platform has decided – some 1 million databases into their journey – that now they’re ready for anything!

Postgres Roles and Privileges — A follow along, tutorial style, guide to understanding how roles and privileges work.

Improved Query Cancellation in Postgres 17 and Psycopg 3.2 — Postgres 17 will gain some improved query cancellation capabilities and Python users will hopefully be able to take advantage from day one.

Awesome Postgres: A Curated List of 200+ Libraries, Tools, and Resources — A handy, curated resource that’s been around for years but continues to get updates. It covers all areas from backups to GUI clients, managed services to Docker images, blogs, and more. You can also contribute your own suggestions via the repo.

pgmock: An In-Memory Postgres for Unit/E2E Testing — No external dependencies and runs entirely within WebAssembly on both Node.js and the browser. Here’s how it works.

Building the Best Postgres GUI? PopSQL Joins Timescale — The team behind the PopSQL collaborative SQL editor has joined Timescale.

Compiling Postgres on macOS to Test Documentation and Patches — One developer’s experience compiling and installing Postgres from source. (And not just any developer, but the author of Pragmatic’s High Performance PostgreSQL for Rails).

Protect Your Database with pg_tde (Transparent Data Encryption) — A tech preview release of an extension from Percona designed to provide transparent data encryption capabilities to Postgres. GitHub repo.

Save the Date for POSETTE: An Event for Postgres — Wondering who will be speaking at the 3rd annual POSETTE: An Event for Postgres? POSETTE is a free & virtual developer event organized by the Postgres team at Microsoft. Schedule drops May 1, event is Jun 11-13. In the meantime, save the date!

A Simple Query with a Big Problem — “Most of the time, PostgreSQL invisibly optimizes our queries, but sometimes, a query wreaks havoc..” If you like debugging stories, this is a good one.