~postgresweekly | Bookmarks (155)

Slonik v38: The Sophisticated Postgres Client for Node.js — A ‘battle tested’ framework offering strict types, detailed logging, abstracts repeating code patterns, protects against unsafe behavior, as well as a rich debugging experience.

Will Postgres Ever Change Its License? — The recent announcement that Redis will be dropping the BSD license in favor of a dual ‘source available’ and SSPL license has got people worried. Is Postgres vulnerable to a similar move? Certainly not.

Figma's Nine Month Journey to Shard Its Postgres Stack — In a quest to unlock '(nearly) infinite scalability', Figma’s database team had a lot of work to do in moving to a horizontally sharded architecture ("End to end, it took our team roughly nine months to shard our first table"). This post goes into the depths of how they planned out and executed the project.

OtterTune Optimizes Your RDS and Aurora Postgres Databases — No mock-ups or fake screenshots — use our product tour to click through all of OtterTune and learn how you can use AI to optimize your Postgres databases. And then put it to the test: do a free, 30-day trial on an unlimited number of databases.

'Look Ma, I Wrote a New JIT Compiler for Postgres' — Before you get too excited, the author’s pg-copyjit project is experimental (and x86-64 only), but they do seek feedback on how it works out for you — so if you’re feeling brave and are seeking some potential performance gains...

transqlate: Transpile SQL into the PostgreSQL Dialect — Focusing on Oracle-flavored SQL, this Go-powered tool (which is admittedly still in the early stages) aims to let you transpile SQL into the ‘PostgreSQL dialect’ in terms of style, syntax, and semantics.

Postgres Performance Boost: HOT Updates and Fill Factor — There’s a way for Postgres to only update the heap (the table) without having to update all the indexes – a so-called HOT update, where HOT stands for ‘heap only tuple.’

When Postgres is Not Enough? Performance Evaluation of Postgres vs. Distributed DBMSs — An empirical study of Postgres, CockroachDB, and YDB by way of the TPC-C benchmark, as well as a consideration of reliability/performance trade-offs in Postgres.

WAL-G 3.0: Database Archive Restoration Tool — A tool for database archival and restoration for Postgres, MySQL/MariaDB, MongoDB, and several other DBMSs – v3.0 introduces failover storage, plus a WAL archiving daemon for Postgres in particular.

Distributed Queries for pgvector — Distributing a workload across multiple databases provides another way to scale once a single instance has maxed out. Jonathan takes a look into how pgvector and Postgres can be used to scale vector workloads horizontally too.

pgzx: Create Postgres Extensions using Zig — If you’re familiar with pgrx as a way to build Postgres extensions with Rust, this is a similar idea with Zig, another modern systems language that’s a little more C-like than Rust.

pgCluu 4.0: Postgres Performance Monitoring and Auditing Tool — A two part tool that collects statistics from a running Postgres cluster and then presents performance information for you to inspect. Version 4.0 is a major release with new features and report types.

Speeding Up pgvector Queries with Indexes — If you’re using Postgres and 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.

Postgres Creator Introduces His New Database Startup — 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 (DBOS) putting the database closer to the center of the software stack.

▶  The Path to Citus Con: A Podcast for Postgres — The Citus Con event itself is now called POSETTE but the podcast continues under the old name (for now) with monthly episodes digging deep into Postgres topics. The latest episode 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.

Creating a Postgres Logical Replication System in Go — The folks behind the (Go-powered) Dolt database are working on a feature to replicate data from Postgres, and share what they’ve learnt so far in this particularly detailed post.

🌐 Using Postgres 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).

Enforcing Join Orders in Postgres — 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..

How to Do Performance Testing with pgbench — A straightforward introduction to using pgbench, a benchmarking tool included with Postgres.

Building a Q&A Bot Natively with Postgres Extensions — pgvector and embedding models have made it easier to create advanced natural language search systems in the past year or two. Here, Tembo shows off how their stack and extensions make it an even easier, direct process. pg_vectorize is a particular highlight as it’s an extension that automates a lot of the process of transforming...

pg_ivm 1.8: Incremental View Maintenance Extension — IVM can update materialized views incrementally, which is more efficient than a full recomputation as REFRESH MATERIALIZED VIEW does.

pg_jsonschema 0.3: JSON Schema Validation Extension — Postgres’s support for JSON / JSONB columns is second to none, but what if you want to validate the structure of JSON data? JSON Schema provides a way to define JSON-based formats and this extension lets you verify JSON documents using such schemas. v0.3 completes the extension’s Postgres 16 support.

▶  Database Disasters and How to Find Them — You’re part of an engineering team and you get pinged because something’s gone down.. what happens next?

pganalyze Index Advisor 3.0: Workload-Aware Index Suggestions — Launched last week, the new advisor finds missing indexes for each table, detects indexes that would block HOT Updates, lets you drill-down to see all considered indexes, and more. Powered by the constraint programming model introduced at PGCon 2023.