Bookmarks (130)

5. Hacking on PostgreSQL is Really Hard — Robert has consistently been in the top ten committers to Postgres for many years, but this year he reflected on just how intimidating the process of contributing to Postgres can be, especially for newcomers.

4. pgPedia: A Postgres Encyclopedia of Sorts — An interesting, cleanly formatted wiki-esque reference guide to Postgres’ features and settings that complements the official documentation well. Well worth poking around a bit.

3. Can Postgres Replace Redis as a Cache? — Redis is a popular, high performance in-memory data storage server commonly used for caching, but if we can just use Postgres for everything, could Postgres take on the job instead?

1. SQL Query Optimization: A Comprehensive Developer's Guide — A bumper packed post digging into the optimization of SELECT, INSERT, and DELETE queries, covering areas like using indexes, paginating results, avoiding joins, and how window functions can help (or not). Not Postgres-only but written with Postgres in mind.

pg_incremental: Incremental Data Processing in Postgres — Marco introduces a new Postgres extension offering automated, incremental batch processing for creating reliable processing pipelines. GitHub repo.

Some of My Favorite Things – Postgres Queries — There’s not really any big Postgres news this week, so why not enjoy this festive gift of some (reasonably complex) SQL queries Shane uses to track database usage in various ways, to see unused indexes, to generate a vacuuming report, and more.

Electric: A Postgres Sync Engine, Now in Beta — It’s always nice to see a new project we casually mentioned a year ago come to fruition, and this real-time engine for syncing data between Postgres and numerous other services (over HTTP) has come a long way since then. GitHub repo.

Running Durable Workflows in Postgres using DBOS — A look at DBOS, a ‘durable workflow’ engine built by a team including none other than one of the founders of the Postgres project.

pg_worker_pool: Extension to Create a Pool of Background Workers — A simple extension that leans on Postgres’s background worker process mechanism to make it easy to distribute queries across a number of workers.

pgBadger 13.0: A Postgres Log Analysis Tool — A performance-focused log analyzer that produces detailed usage reports. Here’s an example report to get a feel for it. GitHub repo.

Smarter Postgres LLM with Retrieval Augmented Generation — A quick, easy to digest introduction to the concept of ‘RAG’ where the results from large language models are improved by providing added context sourced from extra data sources.

🤯 Explaining the ABI Breakage in Postgres 17.1 — A few weeks ago, Postgres 17.1 was released and some changes caused problems for a few popular extensions. Postgres 17.2 was rapidly released to resolve the problem, but here we get a bit more of the story and technical detail behind what happened. Certainly stuff to be aware of if you’re an extension developer.

▶  7+ Million Postgres Tables — Firmly in the space of “you might not wanna do this, but it’s certainly interesting”, an engineer shares an interesting approach to large scale analytics that involves creating a colossal amount of tables (which Postgres has no problem with).

Full-Stack Observability from Honeybadger — Honeybadger's new logging platform is a bit like Splunk, DataDog, or CloudWatch Logs, but with just the good parts and a reasonable price tag. Plus, it's easy to integrate with Crunchy Bridge, Journald, AWS, and more!

Supabase Cron: Job Scheduling in Postgres — A module that uses the pg_cron extension to manage recurring jobs on the Supabase platform (both via the dashboard or SQL).

Optimizing Postgres Performance and Compression: pglz vs. LZ4 — Did you know you can change the algorithm used to compress your TOAST data? It might be worth it if LZ4 is as fast as we see here.

The substring Function, Regex Style — While you can use Postgres’ substring function to extract parts of strings using numeric indices, did you know it supports using regular expressions too?

Transactional Schema Migration Across Tenant Databases with pg_karnak — If you run a setup where each tenant/user of your system has its own database, maintaining the schemas across them can rapidly become tricky. pg_karnak is Nile’s distributed DDL layer for coordinating schemas across tenants. It hasn’t been open sourced yet, but this post digs deep into how it works.

What I Wish Someone Told Me About Postgres — A diverse grab-bag of practical tips covering areas from normalization and the use of different types to SQL ‘eccentricities’, locks, and the use of JSONB.

Amazon Aurora DSQL: A New Postgres-Compatible Database — In ‘preview’ for now, DSQL’s promise is ‘virtually unlimited scalability’ with high availability and strong consistency, while being Postgres compatible (kinda..? No views, triggers, JSON, or foreign key constraints..). AWS Hero AJ Stuyvenberg calls it “the single most exciting launch I’ve seen from AWS in a while”, so watch this space. (Official press release.)

Why Postgres Major Version Upgrades Are Hard — Update the binaries and restart? No, no, no. Tweaks and updates to the underlying data format required a more structured approach – Peter explains why.

pgspot: Spot Vulnerabilities in Postgres Extension Scripts — A security analysis tool for SQL scripts that checks for vulnerabilities and best practices. Created by a Timescale engineer to prevent the reoccurance of vulnerabilities found in TimescaleDB, it’s already resulted in finding an extension-related vulnerability which Postgres core has fixed.

Could GROUP BY Clause Reordering Improve Performance? — Could reordering the columns in a GROUP BY significantly enhance query performance? Small changes in query structure can indeed result in serious gains.

Benchmarking Batch Ingest with INSERT vs COPY — Two of the simplest approaches to loading data into Postgres en masse are by using INSERT and COPY. James explains each of their benefits, runs a benchmark of numerous configurations of both, and gives guidance on which approach to use in certain situations.

▶  The PGConf.EU 2024 Lightning Talks — A handy roundup of twelve brief talks given at the recent PGConf EU event, along with links to slides and recordings. Topics covered include BM25, an update on the Postgres Performance Farm, pg_duckdb, and WAL-G.

DELETEs Are Difficult — Conceptually, deleting data from a table is easy to understand. In practice, it involves a lot of work under the hood and performance issues can quickly rear their head.

Pigsty v3.1: A 'Batteries Included' Postgres Distribution — Pigsty is a packaging up of Postgres along with a variety of extensions and tools to provide a sort of RDS-like experience on your own machines. v3.1 introduces Postgres 17 as the default version and includes a way to quickly spin up a self-hosted Supabase instance.

💎 pg_query 6.0: Ruby Library to Parse, Deparse and Normalize SQL Queries with Postgres' Own Parser — Uses the actual Postgres server source to parse SQL queries and return their internal Postgres parse tree. v6.0 upgrades to using the Postgres 17 parser and the new features it supports (e.g. new JSON functions).

Postgres 17.1, 16.5, 15.9, 14.14, 13.17, and 12.21 Released — A roundup of releases for every maintained line of Postgres including bug fixes and fixes for four security issues. 12.21 also marks the final release of Postgres 12. Be careful with upgrading to Postgres 17.1, though, as there are some potential compatibility issues.. see the next item!

DBngin: Simple Postgres, MySQL and Redis on Mac and Windows — You could use Docker, you could use Postgres.app.. but if you’d like to spin up different versions of several common databases using a simple UI, this could fit the bill.

pglite-fusion: Embed SQLite Databases in Postgres Tables — An extension that gives you the ability to embed SQLite databases into Postgres tables by way of a special new SQLITE column type (so, yes, every row in a table could itself have an embedded SQLite database..)

The Doom That Came To Postgres: When Collations Change — What can happen when the library that provides locales changes out from under an existing Postgres database? It’s not pretty..

Running Postgres on Spot VMs? 🤯 — Building on a talk from PGConf Europe, Kaarel looks at how viable it is to run Postgres on instances that could, well, disappear at almost any time. pg-spot-operator is a utility he’s built to make it work.

Loading the World: An OpenStreetMap Import in Under 4 Hours — Did you know that OpenStreetMap’s database is a whopping 750GB in size, and you can grab it and set it up for yourself as a way to stress-test Postgres? Greg looks at how long it takes to ingest, how to make it faster, and performance improvements over the years. (Minor spoiler: Postgres 17...

Easy Totals and Subtotals in Postgres with Rollup and Cube — Handy additions for performing more analytical queries. ROLLUP can create hierarchical subtotals along with a grand total, while CUBE generates subtotals for every possible combination of grouping columns.

Boosting INSERT Performance by 50% with UNNEST — You probably know that using COPY is usually the fastest way to ingest data into Postgres at scale, but INSERT retains some advantages due to its extra features. Can INSERT be sped up? UNNEST arrives to shake things up..

Postgres Reimagined for Data Warehousing — Crunchy Data Warehouse combines managed storage, a powerful analytics engine, and unified data pipeline. Seamlessly query data using familiar Postgres tools with the added benefit of columnar storage and performance.

A Change to ResultRelInfo: A Near Miss with Postgres 17.1 — A seemingly minor change in the Postgres 17.1 release led to compatibility issues with some extensions (such as TimescaleDB and Apache AGE – see what TimeScale’s CTO had to say here). Craig explains what the issue is and why it arose here, reminding us that developing extensions robust to such changes is a...

pgvector 0.8.0 Released — There’s a post about this release of the popular vector indexing and querying extension on the Postgres site, but I like this writeup as it digs into a change in index scans. With 0.8, we get improved performance, support for iterative index scans, and a way to cast arrays to sparsevec.

Schemalint: A Linter for Postgres Schemas — A linter that lets you raise errors on a variety of common matters like name casing, enforcing RLS, or the presence of mandatory columns (so think more like ESLint than a basic formatter). You can also write your own custom rules.

PostgreSQL Workload Analyzer (PoWA) 5.0 Released — A performance tool for collecting and aggregating metrics from multiple Postgres instances which can then be viewed through real-time charts and used to help optimize your setup. v5 has a much improved Web UI and offers more data sources than before, including WAL stat views, an IO stats view, and info on background writers and replication slots.

From Backup to Integrity: Leveraging WAL-G — Making backups is one thing, ensuring their integrity in the face of hardware or network issues is another, but it’s possible to use checksums to detect integrity problems.

Text Identifiers in Postgres Database Design — A variety of things to consider if, rather than numbers or UUIDs, you’re working with textual identifiers for your records.

8 Steps in Writing Analytical SQL Queries — If you’ve got a complex analytical query to write, start small and build from the ground up to avoid issues creeping in, says Christopher, who has an eight step rubric to doing just that.

Learn PostgreSQL with Redgate — New to PostgreSQL? Check out Redgate’s PostgreSQL learning resources hub for easy-to-follow webinars, how-to articles, and a supportive community forum. Boost your skills and save time by learning tips, tricks and best practices from experts.

▶  The PostgreSQL Conference Europe 2024 Talks — Europe’s annual Postgres conference went down well in Athens last month and there are now a whopping sixty videos to enjoy. We haven’t had time to go through them yet, but will be featuring some in forthcoming issues.

Understanding Volatility in PL/pgSQL Functions — Deepak shares a real-world example of the importance of understanding the different levels of function volatility you can specify for PL/pgSQL functions.

pg_flo: Stream, Transform, and Route Postgres Data in Real-time — Offers a set of filters and transformations that can make moving data between, say, production and staging databases simple using one of two modes: copy-and-stream or stream-only. Uses NATS and Postgres’s replication.

pg-dump-parser: Parses Postgres Dump Files into Arrays of Schema Objects — Takes a Postgres database dump, splits it up, and turns the table and view structures into a format you can more easily work on from code (or just use as a reference or check into version control).

Vector Databases are the Wrong Abstraction — When it comes to semantic search, Timescale thinks that externally producing embedding vectors and then putting them into a database is the wrong approach, and that embeddings should be treated more like database indexes and handled by the database. Pgai Vectorizer is their attempt at making this a reality.