Bookmarks (156)

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.

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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!

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.

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).

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

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.

R.I.P. Postgres Maintainer Simon Riggs — It’s with much sadness we share the news that Postgres contributor (and author of several Postgres books) Simon Riggs has died in a crash at Duxford airfield in the UK. The core team shared its own tribute noting his contributions, including point-in-time recovery, hot standby, and synchronous replication. His final talk was at PGConf EU in December about...

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

Why Has Figma Reinveted the Wheel with Postgres? — A closer look at Figma’s custom sharding solution with motivations and future predictions.

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

Taming WAL File Growth — A quick introduction to the Write Ahead Log subsystem in Postgres, including a look at the key things to monitor for detecting WAL file build up.

Optimize Database Performance with Crunchy Bridge — Boost your Postgres performance with Crunchy Bridge! YNAB experienced 30% more IOPS and a 10% cost reduction. Scale seamlessly, stay on vanilla Postgres, and optimize spend. Read more in the YNAB case study.

Contributing to Postgres 101: A Beginner's Experience — I’ve recently seen some people lament how difficult it can be to figure out how to contribute to certain open source projects, so if that’s you with Postgres, Elizabeth’s experiences in submitting a patch and getting it approved will be super useful.

MobilityDB 1.1: Geospatial Trajectory Data Management and Analysis Platform — Built upon Postgres and PostGIS, MobilityDB is designed for working with things that are on the move and therefore are providing geospatial trajectories that can be stored and queried. GitHub repo.

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.

Recovering Deleted Data from Postgres Tables — Transactions and backups cover most situations, but can you do something similar to an ‘undelete’ on data otherwise considered deleted from a table? With pg_dirtyread, you can do quite a bit and, failing that, you could even go digging into the WAL..

How to Identify Semantic Issues with plpgsql_check — plpgsql_check is a linter for statically analyzing Postgres PL/pgSQL procedure code.

Checking Your Privileges — “If you are counting on the PostgreSQL privilege system to prevent roles from running functions (and accessing other objects), be sure you know what the default permissions are, and adjust them accordingly.”

Introducing pgzx: Create Postgres Extensions using Zig — Yes, we linked to pgzx last week, but it seems we jumped the gun as there’s now a proper launch post for it, complete with a C vs Zig example for creating a barebones extension and a run down of the advantage of using Zig to build extensions.

How to Automate Postgres Index Selection Using the CP-SAT Solver — Learn about the Constraint Programming model powering pganalyze Index Advisor 3.0. This technical deep dive (56min video) with Lukas Fittl & Philippe Olivier is a behind-the-scenes look at using the CP-SAT solver for recommending Postgres indexes.

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.

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.

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.

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.

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.

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.

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.