Bookmarks (148)

Advanced psql Scripting with coproc — coproc is a shell command that runs a process asynchronously in a subshell. If you invoke psql with it, it’s still possible to interact with it from afar and here’s how.

What’s New with Postgres at Microsoft, 2024 Edition — Partly thanks to buying Citus Data and partly through Azure, Microsoft is a significant contributor to the Postgres ecosystem these days and this is a surprisingly thorough summary of everything they’ve got going on lately.

Postgres 16.3, 15.7, 14.12, 13.15, and 12.19 Released — Right on schedule, the Postgres team has unveiled new minor versions of each maintained release line. Bug fixes are the focus, but Postgres 14-16 have also received a fix for a security issue with pg_stats_ext and pg_stats_ext_exprs. (Note that Postgres 12 will stop receiving fixes from November this year.)

Making a Postgres Query 1,000 Times Faster — If you want to get attention for a blog post, this is the sort of title you need to write! But you also need to back it up with a story, a solution, some useful insights, and perhaps a little humor – thankfully all those elements are present here.

The Draft Postgres 17 Release Notes — Bruce just announced he’s completed the first draft of the release notes for Postgres 17 (due later this year). It’s mostly a bullet-point list of low level improvements for now, but it makes the release feel a lot closer! The first beta is due imminently.

pg_lakehouse: Query Any Data Lake from Postgres — An extension that lets you query object stores like S3 and table formats like Delta Lake. Queries are pushed down to Apache DataFusion. Google Cloud Storage and Azure Blob Storage support is promised soon.

Understanding Postgres Schemas and search_path — The developers of the Dolt database have a lot of experience with MySQL but have moved into building Doltgres as a version controlled database that offers Postgres compatibility. This has meant learning about about Postgres’ schemas. This is a good primer, and also shows off search_path, somewhat akin to the OS’s PATH variable when resolving unqualified names in queries.

Hacking on PostgreSQL is Really Hard — Robert has consistently been in the top ten committers to Postgres for many years, but reflects on just how intimidating the process of contributing to Postgres can be, even for experienced developers. This post has a lot of fascinating insights.

What's New in pgvector v0.7 — We mentioned pgvector’s rather exciting v0.7 release last week, but here’s a proper roundup of its new features, including extra vector types and new distance functions.

pgdsat: A Postgres Database Security Assessment Tool — Checks around 70 security controls on your clusters including all recommendations from the CIS compliance benchmark.

Saturation Arithmetic with a Postgres Extension — Saturation arithmetic is the concept of bounding the results of arithmetic functions to within a certain range, thus preventing overflows. The folks at Adjust have built pg-saturated_int, an extension that provides an integer type adhering to this concept.

cursor_tuple_fraction and PostgreSQL JDBC — Laurenz had me at the first sentence: “This article is a kind of failure story.”

The Notifier Pattern for Apps That Use Postgres — Brandur is a big fan of Postgres’s built-in pub/sub features.

How Not to Change Postgres Column Type — ALTER TABLE .. ALTER COLUMN is fine in certain situations but has some limitations to be aware of, particularly in production. Luckily, there are some alternative routes.

The 150x pgvector Speedup: A Year in Review — pgvector provides Postgres with extra data types, indexing, and querying facilities to work with high dimensional vectors (such as are common with LLMs and embeddings). The past year has seen a huge leap in its capabilities and performance.

Squawk: A Linter for Postgres Migrations — Prevent unexpected downtime caused by database migrations and encourage best practices around schemas and SQL.

pg_tier: Tier Data to External Amazon S3 Storage — Once data becomes older or much less frequently accessed, it might be more cost effective to store it elsewhere. pg_tier makes it easier to push a table over to S3 while still maintaining the ability to query it (at the price of some added latency, naturally).

PeerDB: A Simple, Fast Way to Replicate Postgres Data Elsewhere — A data replication system built by former Microsoft and Citus Data employees that targets use cases where you need to stream data from Postgres to data warehouses, queues, and other storage engines. This week’s v0.13.0 release introduces an Elasticsearch connector. GitHub repo.

Papa's Got a Brand New RAG — RAG (Retrieval-Augmented Generation) is a technique used alongside large language models where you fetch data to include in prompts to improve the responses LLMs give. Shaun demonstrates bringing all the parts together to build a simple RAG-based query system in Python with pg_vectorize doing the heavy lifting on the Postgres side.

Postgres Bloat Minimization — Tables occupying more space than expected is a common cause of complaint, but there are a variety of strategies you can employ so that space is used more efficiently.

Crunchy Bridge for Analytics: Your Data Lake in Postgres? — Marco ponders: “What if you could use PostgreSQL to easily query your data lake with state-of-the-art analytics performance?” Crunchy Bridge for Analytics is a new part of Crunchy Data’s Postgres platform offering S3 integration, CSV/Parquet querying support, and bidirectional data lake ↔ Postgres import/export.

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.

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.

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.

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.

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

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.

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!

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.

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.

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.

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.

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.

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

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!

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.

📊 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.”

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.

Announcing PL/.NET 0.99: A Public Beta Release — pl/dotnet adds full support for C# and F# to Postgres (like this). If you're in the .NET space, they want you to kick the tires a bit ahead of a final 1.0 release. GitHub 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!

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.

Building a Managed Postgres Service in Rust: Part 1 — A little peek behind the scenes at Tembo.

journald and Syslog Logging in Postgres — Using journald and syslog logging with Postgres isn’t a new idea, but Laurenz thinks more people should be giving it a go and so has put together this basic tutorial: “I switched my PostgreSQL instance to syslog logging, and I don’t think I will switch back.”