#postgres

• Fix Postgres `strchrnul` Compile Error on macOS 15.4

  https://justatheory.com/2025/04/fix-postgres-strchrnul

  A fix for the error: 'strchrnul' is only available on macOS 15.4 or newer Postgres compile error.

• Update Your Control Files

  https://justatheory.com/2025/04/update-control

  Suggestions to PostgreSQL extension maintainers to make some tweaks to your .control files and Makefiles.

• POC: PGXN Binary Distribution Format

  https://justatheory.com/2024/06/trunk-poc

  A proof of concept for “trunk”, the proposed binary distribution format for Postgres extensions.

• Mini Summit Five

  https://justatheory.com/2024/05/mini-summit-five

  Links, notes, and commentary from Yurii Rashkovskii’s presentation, “Universally Buildable Extensions: Dev to Prod,” at the fifth Postgres Extension Ecosystem Mini-Summit on May 1, 2024.

• Dynamic OFFSETs and LIMITs

  https://justatheory.com/2009/01/postgres-dynamic-limit

• pgTAP 0.22: Test Your Results

  https://justatheory.com/2009/07/pgtap-test-your-results

  I’m pleased to announce that, after much thinking, committing, and not an insignificant amount of hair-pulling, pgTAP 0.22 has finally landed. Download it here. Many, many thanks to all who commented on my previous posts, made suggestions, and helped me on IRC to figure out how to get all this stuff to work. The crazy thing is that it does, quite well, all the way back to PostgreSQL 8.0.

• How to Determine Your Transaction ID

  https://justatheory.com/2004/10/what-postgres-transaction

• JSON Path Operator Confusion

  https://justatheory.com/2023/10/sql-jsonpath-operators

  The relationship between the Postgres SQL/JSON Path operators @@ and @? confused me. Here’s how I figured out the difference.

• PGXN v2: Go or Rust?

  https://justatheory.com/2024/03/pgxn-language-choices

  What programming language(s) should we use to build new and revamp existing PGXN services and tools: Rust or Go? Vote your preference!

• 2025 Postgres Extensions Mini Summit Two

  https://justatheory.com/2025/04/mini-summit-two

  A transcript of from the second PostgreSQL Extension Mini Summit, “Implementing an Extension Search Path”, by Peter Eisentraut.

• Rails Migrations with Slony?

  https://justatheory.com/2007/03/rails-and-slony

  The new app I’m developing is written in Ruby on Rails and runs on PostgreSQL. We’re replicating our production database using Slony-I, but we’ve run into a bit of a snag: database schema updates must be run as plain SQL through a Slony script in order to ensure proper replication of the schema changes within a transaction, but Rails migrations run as Ruby code updating the database via the Rails database adapter.

• PGXN Development Project

  https://justatheory.com/2010/06/pgxn-development-project

  I’m pleased to announce the launch of the PGXN development project. I’ve written a detailed specification and pushed it through general approval on pgsql-hackers. I’ve written up a detailed project plan and estimated things at a highly reduced PostgreSQL Experts rate to come up with a fundraising goal: $25,000. And now, thanks to founding contributions from myYearbook.com, and PostgreSQL Experts, we have started the fundraising phase of the project.

• pgTAP 0.12 Released

  https://justatheory.com/2008/10/pgtap-0.12

• RFC: Extension Metadata Typology

  https://justatheory.com/2024/02/extension-metadata-typology

  Thinking through the PostgreSQL extension metadata use cases and recognizing the types of information they need.

• Compiling libreadline on Mac OS X

  https://justatheory.com/2004/10/compile-libreadline

• New in PostgreSQL 9.2: format()

  https://justatheory.com/2012/11/postgres-format-function

• Bricolage 1.8.3 Released

  https://justatheory.com/2004/11/bricolage-1.8.3

  The Bricolage development team is pleased to announce the release of Bricolage 1.8.3. This maintenance release addresses a number of issues in Bricolage 1.8.2. The most important changes eliminate or greatly reduce the number of deadlocks caused during bulk publishes of many documents. Other changes include new contributed scripts for importing contributors and for generating thumbnail images, Russian localization, and various fixes for database transaction, template formatting, and various user interface fixes. Here are the other highlights of this release:

• Postgres Extensions: Use PG_MODULE_MAGIC_EXT

  https://justatheory.com/2025/05/pg_module_magic_ext

  Details for extension authors for how to use the new PG_MODULE_MAGIC_EXT macro introduced in PostgreSQL 18.

• Committed: pgTAP Result Set Assertion Functions

  https://justatheory.com/2009/07/pgtap-results-eq

  Regular readers will know that I’ve been thinking a lot about testing SQL result sets and how to how to name result testing functions, and various implementation issues. I am very happy to say that I’ve now committed the first three such test functions to the Git repository. They’ve been tested on 8.4 and 8.3. Here’s what I came up with.

• Fixing Foreign Key Deadlocks in PostgreSQL

  https://justatheory.com/2010/11/postgres-fk-locks-project

• Release: pgxn_meta v0.1.0

  https://justatheory.com/2024/08/pgxn_meta_release

  Today I released pgxn_meta v0.1.0, a Rust crate and executable for validating PGXN Meta v1 and v2 META.json files.

• pgTAP Best Practices Slides Available

  https://justatheory.com/2009/11/pgtap-best-practices

  Last month I gave two presentations at the PostgreSQL Conference West. The first was a variation on Unit Test Your Database!, which I’ve now given about six times (and will at least two more times, including tomorrow night for Portland PLUG and in two weeks at the JPUG 10th Anniversary Conference). The second was a new talk, a 90-minute tutorial, called “pgTAP Best Practices.” And here it is (download and Slideshare). Enjoy!

• PGXN V2 Architecture

  https://justatheory.com/2024/04/pgxn-v2-architecture

  I’ve written and published a document outlining the vision and strategy for the next iteration of PGXN. It includes diagrams. Everybody loves diagrams.

• MySQL's REPLACE Considered Harmful

  https://justatheory.com/2005/03/mysql-replace-considered-harmful

• Corrected PostgreSQL EAN Functions

  https://justatheory.com/2006/05/postgres-ean-validation

• Patch: Postgres ABI and API Guidance

  https://justatheory.com/2024/06/abi-api-guidance

  Dear Postgres extension developers: Please review and give feedback on the proposed patch adding ABI and API guidance to the C language documentation.

• Mocking Serialization Failures

  https://justatheory.com/2012/11/mock-postgres-serialization-failures

• Unicode Normalization in SQL

  https://justatheory.com/2009/09/postgres-unicode-normalization

• Mini Summit 4 Transcript: The User POV

  https://justatheory.com/2025/05/2025-mini-summit-four

  Last week Floor Drees moderated a panel on “The User POV” at the fourth Extension Mini-Summit. Read on for the transcript and link to the video.

• pgTAP Set-Testing Update

  https://justatheory.com/2009/06/pgtap-set-testing-update

• Introducing MyTAP

  https://justatheory.com/2010/07/introducing-mytap

  After some prodding from the MySQL Community Manager, some OSCON hacking yields tangible results.

• PGXN Certifications RFC

  https://justatheory.com/2024/10/pgxn-certifications-rfc

  A request for comments on a new PGXN RFC for signing releases, plus a link to an initial implementation.

• pgenv

  https://justatheory.com/2018/08/pgenv

  I wrote a simple PostgreSQL binary manager. You should try it.

• DBIx::Connector and Serializable Snapshot Isolation

  https://justatheory.com/2011/09/dbix-connector-and-ssi

• RFC: A Simple Markdown Table Format

  https://justatheory.com/2009/02/markdown-table-rfc

  I’ve been thinking about markdown tables a bit lately. I’ve had in mind to follow up on my definition list proposal with a second proposal for the creation and editing of simple tables in Markdown. For better or for worse, an aside on the markdown-discuss mail list led to a longish thread about a syntax for continuing lines in tables (not to mention a long aside on the use of monospaced fonts, but I digress), wherein I realized, after an open-minded post from MultiMarkdown’s Fletcher Penney, that I needed to set to working up this request for comments sooner rather than later.

• Mini Summit Four

  https://justatheory.com/2024/04/mini-summit-four

  Links, notes, and commentary from Jonathan Katz’s presentation at the fourth Postgres Extension Ecosystem Mini-Summit, “Trusted Language Extensions for PostgreSQL”.

• Managing Key/Value Pairs in PostgreSQL

  https://justatheory.com/2010/08/postgres-key-value-pairs

  Let’s say that you’ve been following the latest research in key/value data storage and are interested in managing such data in a PostgreSQL database. You want to have functions to store and retrieve pairs, but there is no natural way to represent pairs in SQL. Many languages have hashes or or data dictionaries to fulfill this role, and you can pass them to functional interfaces. SQL’s got nothin’. In PostgreSQL, have two options: use nested arrays (simple, fast) or use a custom composite data type (sugary, legible).

• More on Database Testing

  https://justatheory.com/2009/05/more-on-database-testing

  More thoughts on they whys and hows of database testing, and wondering why one might think that it’s pointless or useless.

• PGXN Language Poll Result

  https://justatheory.com/2024/05/pgxn-language-poll-result

  Results of the Postgres community poll for building PGXN v2 in Go, Rust, or both.

• Higher-Order PL/pgSQL

  https://justatheory.com/2006/05/higher-order-plpgsql

  Well, it’s not really higher-order PL/pgSQL, since the language doesn’t support closures, as far as I know. But I have been working on a series of articles for O’Reilly Databases site, drawing inspiration from Mark Jason Dominus’s Higher-Order Perl, and specifically using the Fibonacci sequence to create example PL/pgSQL functions. It turns out that, while the Fibonacci sequence may not be of much use in day-to-day database work, it makes for great pedagogy. And I learned a fair bit along the way, as well.

• Neither NULL nor NOT NULL: An SQL WTF

  https://justatheory.com/2009/07/sql-neither-null-nor-not-null

• SQL Hack: The Something-est From Each Entity

  https://justatheory.com/2010/01/somethingest-from-each-entity

• Thoughts on Testing SQL Result Sets

  https://justatheory.com/2009/06/pgtap-comparing-relations

  pgTAP: The Critique I’ve been continuing hacking on pgTAP in order to add a lot more schema-testing functionality and a few other niceties. But back when I started the project, I using it to write tests for CITEXT, which was great for my sanity as I developed it, but proved a bit controversial. In a pgsql-hackers post, Tom Lane wrote:

• 🏔 Extension Ecosystem Summit 2024

  https://justatheory.com/2024/06/extension-ecosystem-summit

  A brief report on the PostgreSQL Extension Ecosystem Summit at PGConf.dev 2024 in Vancouver, Canada.

• Auto-Release PostgreSQL Extensions on PGXN

  https://justatheory.com/2025/05/release-on-pgxn

  Step-by-step instructions to publish PostgreSQL extensions and utilities on the PostgreSQL Extension Network (PGXN).

• PostgreSQL + OSSP UUID on Mac OS X

  https://justatheory.com/2009/04/postgres-ossp-uuid

• PL/pgSQL Talk Slides Posted

  https://justatheory.com/2006/07/plpgsql-talk-slides

• Always use the C Locale with PostgreSQL

  https://justatheory.com/2004/08/postgres-always-use-c-locale

• Agile Database Development Tutorial

  https://justatheory.com/2013/06/agile-db-dev

• Bricolage Now has PHP 5 Templating

  https://justatheory.com/2005/08/bricolage-1.9.0

• pgTAP 0.11 Released

  https://justatheory.com/2008/09/pgtap-0.11

• 2025 GSOC: Mankirat Singh — ABI Compliance Reporting

  https://justatheory.com/2025/05/2025-gsoc

  Please welcome 2025 Google Summer of Code contributor Mankirat Singh, who will be developing an ABI compliance checker for the PostgreSQL maintenance branches.

• JPUG Talk Posted

  https://justatheory.com/2009/11/jpug-talk

  No Perl content today, I’m afraid. I’m just back from my trip to Japan and wanted to post this very nice video of my talk [Update 2018: Sadly gone now]. Unlike the versions from other conferences, this one focuses primarily on the slides, with me appearing in audio only. This makes it really easy to follow. Enjoy.

• PGAN Bikeshedding

  https://justatheory.com/2010/05/pgan-bikeshedding

  Help me pick a good name for the PostgreSQL extension distribution network and site.

• 🛠️ PGXN Tools v1.7

  https://justatheory.com/2026/01/pgxn-tools-v1.7

  Just released the PGXN test and build OCI image upgraded to Trixie and improving PGXS build parallelization.

• POSETTE 2024

  https://justatheory.com/2024/06/posette

  I attended and gave a presentation at POSETTE, an event for Postgres. This post highlights some talks and the slides for my own.

• My First C: A GTIN Data Type for PostgreSQL

  https://justatheory.com/2006/09/postgres-gtin-0.01

• No UTF-8 Support on Windows?

  https://justatheory.com/2005/08/windows-perl-no-utf8

  This just blows. It will be a while before Bricolage runs on Windows, then. The PostgreSQL team is understandably reluctant to simply include the whole ICU library in PostgreSQL. Maybe it could be compiled into the binaries, though?

• Mini Summit Three

  https://justatheory.com/2024/04/mini-summit-three

  A rough transcript of my Devrim Gündüz’s talk, “Overview of {yum,zypp}.postgresql.org, and the challenges around RPMifying extensions”, along with interspersed chat comments.

• RFC: PGXN Meta Spec v2

  https://justatheory.com/2024/07/rfc-pgxn-meta-v2

  PGXN Meta Spec v2 represents a significant reworking of the original spec for PGXN distributions. It would very much benefit from feedback from Postgres extension and developers.

• Introducing Go SQL/JSON Path and Playground

  https://justatheory.com/2024/07/go-sqljson-path

  Introducing the SQL/JSON Path package, a PostgresSQL-compatible jsonpath parser and executor in Go. Also: a Wasm-based playground!

• Validating UPCs with PL/pgSQL

  https://justatheory.com/2006/05/plpgsql-upc-validation

• PostgreSQL Development: Lessons for Perl?

  https://justatheory.com/2009/07/pg-vs-perl-dev

  Pondering Conservatism I’ve been following chromatic’s new blog since it launched, and have read with particular interest his posts on the Perl 5 development and release process. The very long time between releases of stable versions of Perl has concerned me for a while, though I hadn’t paid much attention until recently. There has been a fair amount of discussion about what it means for a release to be “stable,” from, among others, now-resigned Perl Pumpking Rafael Garcia-Suarez and Perl 5 contributor chromatic. Reading this commentary, I started to ponder what other major open-source projects might consider “stable,” and how they manage stability in their development and release processes. And it occurred to me that the Perl 5 code base is simultaneously treated too conservatively and – more importantly – not conservatively enough. What open-source projects treat their code highly conservatively?

• My Adventures with Mac OS X

  https://justatheory.com/2002/11/my-osx-adventures

  I recently decided to make the leap from Yellow Dog Linux to Mac OS X on my Titanium PowerBook. Getting everything to work the way I wanted proved to be a challenge, but well worth it. This document outlines all that I learned, so that neither you nor I will have to experience such pain again. The overall goal was to get Bricolage up and running, figuring that if it worked, then just about any mod_perl based solution would run. I’m happy to say that I was ultimately successful. You can be, too.

• 2025 Postgres Extensions Mini Summit One

  https://justatheory.com/2025/03/mini-summit-one

  A rough transcript of my talk “State of the Extension Ecosystem”.

• 🐏 Taming PostgreSQL GUC “extra” Data

  https://justatheory.com/2025/12/taming-guc-extra

  For the ClickHouse blog I wrote up learning how to work with C data structures and memory allocation within the tight constraints of the Postgres “GUC” API.

• No more USE_PGXS=1?

  https://justatheory.com/2010/03/no-more-use-pgxs

  I’m tired of having to remember to set USE_PGXS=1 when building third-party PostgreSQL extensions like pgTAP. Aren’t you?

• https://justatheory.com/2024/05/extension-summit-topics

  Final Postgres Extension Mini-Summit! On Wednesday May 15 at noon Eastern / 16:00 UTC, we’ll review some of the topics that have come up in previous Mini-Summits with the goal to winnow down and select issues to address at PGConf.dev.

• Use Variables in PL/pgSQL DECLARE Blocks

  https://justatheory.com/2006/04/plpgsql-declare

• I'm a Postgres Extensions Tembonaut

  https://justatheory.com/2024/01/tembonaut

  Near year, new job. I accepted a new position at Tembo to work on improving the PostgreSQL extension ecosystem full time.

• Understanding Window Functions

  https://justatheory.com/2013/08/understanding-window-functions

• To Preload, or Not to Preload

  https://justatheory.com/2024/08/extension-preloading

  When should a Postgres extension be pre-loaded and when should it not? Should it be loaded in user sessions or at server startup? For the Tembo blog, I dug into this question and wrote up my findings.

• https://justatheory.com/2020/10/release-postgres-extensions-with-github-actions

  Go beyond testing and fully automate the release of Postgres extensions on both GitHub and PGXN using GitHub actions.

• Test and Release pgrx Extensions with pgxn-tools

  https://justatheory.com/2024/04/pgxn-tools-pgrx

  The v1.6.0 release of the pgxn-tools Docker image adds a new command to efficiently build and test pgrx extensions on a wide variety of Postgres versions.

• Contemplating Decentralized Extension Publishing

  https://justatheory.com/2024/02/decentralized-extension-publishing

  The Go package ecosystem uses distributed publishing to release modules without authentication or uploads. Could we do something similar for Postgres extensions?

• How to Generate Recurring Events in the Database

  https://justatheory.com/2008/01/postgres-recurring-events

  Going deep on using Postgres functions to create efficient recurring events.

• CITEXT Patch Submitted to PostgreSQL Contrib

  https://justatheory.com/2008/06/citext-patch-submitted

  On Friday, I submitted a patch to add a locale-aware case-insensitive text type as a PostgreSQL contrib module. This has been among my top requests as a feature for PostgreSQL ever since I started using it. And as I started work on yet another application recently, I decided to look into what it would take to just make it happen myself. I’m hopeful that everyone will be able to benefit from this bit of yak shaving.

• Mini Summit 5 Transcript: Improving the PostgreSQL Extensions Experience in Kubernetes with CloudNativePG

  https://justatheory.com/2025/05/mini-summit-cnpg

  At the final Mini-Summit of 2025, Gabriele Bartolini gave an overview of PostgreSQL extension management in CloudNativePG.

• Mini Summit 3 Transcript: Apt Extension Packaging

  https://justatheory.com/2025/04/mini-summit-three

  Last week Christoph Berg, who maintains PostgreSQL’s APT packaging system, gave a very nice talk on that system. Herein lie the transcript and links to the slides and video.

• Enable CSV Logging in PostgreSQL

  https://justatheory.com/2009/08/enable-postgres-csvlogging

  One of the cooler features of recent versions of PostgreSQL is support for CSV-formatted logging. I’ve never had a chance to use it, but after reading Josh’s cool hack for determining sums of concurrent queries using windowing functions in PostgreSQL 8.4 to query a table generated from a CSV log, I just had to give it a try. But while there is decent documentation for loading up the contents of a CSV-formatted log file, there I couldn’t find simple information on how to set it up. So I figured it out and record it here for posterity.

• RFC: Extension Packaging & Lookup

  https://justatheory.com/2024/11/rfc-extension-packaging-lookup

  A proposal to modify the PostgreSQL core so that all files required for an extension live in a directory named for the extension, along with a search path to find extension directories.

• Need Help Naming Result Set Testing Functions

  https://justatheory.com/2009/06/pgtap-result-testing-function-names

• Bootstrapping Bucardo Master/Master Replication

  https://justatheory.com/2013/02/bootstrap-bucardo-mulitmaster

  Let’s say you have a production database up and running and you want to set up a second database with Bucardo-powered replication between them. Getting a new master up and running without downtime for an existing master, and without losing any data, is a bit fiddly and under-documented. Having just figured out one way to do it with the forthcoming Bucardo 5 code base, I wanted to blog it as much for my own reference as for yours.

• Quest for PostgreSQL Project Hosting

  https://justatheory.com/2009/12/pg-project-hosting

• Catalyst with DBIx::Connector and Template::Declare

  https://justatheory.com/2009/11/catalyst-tutorial-continued

  Following up on my post yesterday introducing Catalyst::View::TD, today I’d like to continue with the next step in chapter 3 of the Catalyst tutorial. The twist here is that I’m going to use PostgreSQL for the database back-end and start introducing some database best practices. I’m also going to make use of my DBIx::Connector module to interact with the database.

• Extension Ecosystem Summit 2025

  https://justatheory.com/2025/03/extension-ecosystem-summit

  We’re doing it again! The PostgreSQL Extension Ecosystem Summit returns to PGConf.dev. We’ll again be looking at indexing, discovery, packaging, and core support. And we’ll once again be hosting a series of “Mini Summits” leading up to the event. Join us!

• Talk: State of the Extension Ecosystem

  https://justatheory.com/2024/03/state-of-the-extension-ecosystem

  A quick reminder that I’ll be giving a brief talk on the “State of the Extension Ecosystem” on Wednesday at noon US Eastern / 17:00 UTC.

• Need Help Reducing View Calculations

  https://justatheory.com/2007/11/reducing-postgres-view-calculations

• Validating Time Zones in PostgreSQL

  https://justatheory.com/2007/11/postgres-timezone-validation

• pgTAP 0.16 in the Wild

  https://justatheory.com/2009/02/pgtap-0.16

  I’ve been writing a lot tests for a client in pgTAP lately. It’s given me a lot to think about in terms of features I need and best practices in writing tests. I’m pleased to say that, overall, it has been absolutely invaluable. I’m doing a lot of database refactoring, and having the safety of solid test coverage has been an absolute godsend. pgTAP has done a lot to free me from worry about the effects of my changes, as it ensures that everything about the databases continue to just work.

• Compiling Postgres with LLVM

  https://justatheory.com/2024/06/compile-postgres-llvm

  I decided to compile Postgres with LLVM this week but ran into failing tests due to server crashes. Here’s how to avoid the issue.

• Mini Summit 3: APT Extension Packaging

  https://justatheory.com/2025/04/mini-summit-apt-packaging

  Join us at PostgresSQL Extension Mini Summit #3 this week, where PostgreSQL Debian packaging maintainer Christoph Berg will takes on a tour of APT extension packaging.

• Mini Summit One

  https://justatheory.com/2024/03/mini-summit-one

  A rough transcript of my talk “State of the Extension Ecosystem”, along with interspersed chat comments and appearances by Bagel.

• Always Use TIMESTAMP WITH TIME ZONE

  https://justatheory.com/2012/04/postgres-use-timestamptz

• PGXN Challenges

  https://justatheory.com/2024/01/pgxn-challenges

  Some thoughts on the challenges for PGXN’s role in the ideal PostgreSQL extension ecosystem of the future.

• Mini Summit 4: The User POV

  https://justatheory.com/2025/04/mini-summit-user-pov

  Join our fine panel on Wednesday at Extension Mini Summit #4 to hear all about “The User POV” — for better and for worse!

• Why Test Databases?

  https://justatheory.com/2009/03/why-test-databases

• PGConf & Extension Ecosystem Summit EU 2024

  https://justatheory.com/2024/10/pgconf-extension-summit-eu

  Notes and links from the Extension Ecosystem Summit EU 2024 and my first time at PGConf EU. Plus thoughts and photos from ancient sites and archeological museums I visited.

• New Gig: PostgreSQL Experts, Inc.

  https://justatheory.com/2009/05/pgexperts

  A bit of good news: In addition to my ongoing Kineticode work doing Bricolage consulting services, training, and support, I have a new gig! I, along with Josh Berkus, David Fetter, Andrew Dunstan, and a team of other PostgreSQL experts, have started a new company: PostgreSQL Experts, Inc. I’m really excited about PGX, a cooperative of solid and experienced–dare I say expert?–people dedicated to providing exceptional PostgreSQL professional services, including consulting, training, and support.

• Custom SQLite Aggregates in Perl

  https://justatheory.com/2005/10/sqlite-perl-aggregates

  About a year ago, Josh Berkus was reviewing some Bricolage SQL code, looking to optimize it for PostgreSQL. One of the things he noticed was that we were fetching a lot more rows for an object than we needed to. The reason for this is that an object might be associated with one or more groups, and to get back a list of all of the group IDs, we were getting multiple rows. For example, if I wanted to fetch a single story with the ID 10, I might get back rows like this:

• Execute SQL Code on Connect

  https://justatheory.com/2010/04/execute-sql-on-connect

  I’ve been writing a fair bit of PL/Perl for a client, and one of the things I’ve been doing is eliminating a ton of duplicate code by creating utility functions in the %_SHARED hash. This is great, as long as the code that creates those functions gets executed at the beginning of every database connection. So I put the utility generation code into a single function, called prepare_perl_utils(). It looks something like this:

• Enforcing a Set of Values

  https://justatheory.com/2010/01/enforce-set-of-postgres-values

• My Catalyst Tutorial: Add Authors to the View

  https://justatheory.com/2009/11/sql-view-aggregate-magic

• Mini Summit 5: Extension Management in CNPG

  https://justatheory.com/2025/05/mini-summit-cnpg-extensions

  The last Mini-Summit of the year features Gabriele Bartolini talking about extension management in CloudNativePG.

• The History and Future of Extension Versioning

  https://justatheory.com/2024/02/extension-versioning

  What versioning standard should be used for Postgres extension distribution? Some context from PostgreSQL and PGXN, a survey of the version standard landscape today, and a recommendation.

• Introducing pgTAP

  https://justatheory.com/2008/06/introducing-pgtap

  So I started working on a new PostgreSQL data type this week. More on that soon; in the meantime, I wanted to create a test suite for it, and wasn’t sure where to go. The only PostgreSQL tests I’ve seen are those distributed with Elein Mustain’s tests for the email data type she created in a PostgreSQL General Bits posting from a couple of years ago. I used the same approach myself for my GTIN data type, but it was rather hard to use: I had to pay very close attention to what was output in order to tell the description output from the test output. It was quite a PITA, actually.

• pgTAP 0.21 Drops

  https://justatheory.com/2009/05/pgtap-0.21

  I just dropped a new version of pgTAP following a few marathon hack sessions since my talk at PGCon (movie here, BTW). Actually, the new performs_ok() function came about as I wrote the presentation, but all the rest came on the flight home and in the few days since. Not sure when I’ll hack on it this much again (it’s getting a bit big at 5,758 lines of PL/pgSQL and SQL).

• PGConf.dev 2024

  https://justatheory.com/2024/06/pgconf-future-ecosystem

  At PGConf.dev, I attended some great talks, made one of my own, and enjoyed the commearderie of fellow PostgreSQL extension authors and core developers. A brief report.

• ⛰️ Postgres Ecosystem Summit EU

  https://justatheory.com/2024/10/postgres-ecosystem-summit-eu

  The sequel to the successful PGConf.dev event, the Extension Ecosystem Summit EU will showcases some exemplary extension use cases.

• RFC: PGXN Metadata Sketch

  https://justatheory.com/2024/03/rfc-pgxn-metadata-sketch

  Request for comments on a sketch of a new metadata standard for Postgres extension packaging, distribution, and delivery, building on the PGXN Meta Spec to address its shortcomings and emerging use cases 12 years on.

• Introducing pg_clickhouse

  https://justatheory.com/2025/12/pg_clickhouse

  Introducing pg_clickhouse, a PostgreSQL extension that runs your analytics queries on ClickHouse right from PostgreSQL without rewriting any SQL.

• How Do I Avoid Tiger's readline When Compiling PostgreSQL?

  https://justatheory.com/2005/11/postgres-avoid-tigers-readline

  I was delighted to find that Mac OS X 10.4 “Tiger” includes the readline library. So I was able to just compile PostgreSQL and have psql just work. Only it kinda doesn’t. For reasons that Tom Lane has explained, Tiger’s readline implementation is somewhat buggy. I’ve reported the issue to Apple (Radar # 4356545), but in the meantime, I’ve compiled and installed GNU readline 5.0 and wan to use it, instead.

• SQL/JSON Path Playground Update

  https://justatheory.com/2024/12/playground-update

  The Go SQL/JSON Playground has been updated with a fresh design and PostgreSQL 17 compatibility.

• Extension Ecosystem Summit 2024

  https://justatheory.com/2024/02/extension-ecosystem-summit

  Some pals and I organized a summit at PGConf.dev on May 28 to work together as a community toward comprehensive indexing, discovery, and binary distribution, as well as a speaker series “mini summit” leading up to the event.

• Mini Summit Two

  https://justatheory.com/2024/03/mini-summit-two

  A rough transcript my Ian Stanton’s talk, “Building Trunk: A Postgres Extension Registry and CLI”, along with interspersed chat comments.

• CipherDoc: A Searchable, Encrypted JSON Document Service on Postgres

  https://justatheory.com/2023/10/cipherdoc

  I gave a talk at PGCon this year on a privacy-first data storage service I designed and implemented. Perhaps the encryption and searching patterns will inspire others.

• Benchmarking PostgreSQL Functions

  https://justatheory.com/2006/05/benchmarking-functions

  Update 2006-05-19: I realized that there was a nasty error in my algorithm for determining the runtime of a function: It was only fetching the milliseconds part of the runtime, without adding in seconds and minutes! This led to getting negative runtimes then the milliseconds part of the end time was less than the milliseconds part of the start time. Ugh. But with the help of yain on IRC, I’ve switched to calculating the number of seconds by converting the start and end times to epoch seconds (which have subsecond precision in PostgreSQL, and now things are just dandy. While I was at it, I reorganized the function so that it was a bit easier to read, by constructing the created function in the order it would be executed, and fixed the caching problem, as suggested by Aidan in a comment below.

• PostgreSQL Warm Standby Using Ruby

  https://justatheory.com/2007/03/ruby-warm-standby

• Extension Registry Namespacing RFC

  https://justatheory.com/2024/03/extension-namespace-rfc

  A proposal for an additional level of name uniqueness for Postgres extension packaging and distribution, based on URIs.

• POC: Distributing Trunk Binaries via OCI

  https://justatheory.com/2024/06/trunk-oci-poc

  Would it be possible to distribute Postgres extension binaries via Open Container Registries? Tune in to find out!

• Mini Summit Six

  https://justatheory.com/2024/05/mini-summit-six

  A rough transcript of the sixth and final Extension Ecosystem Mini-Summit, in which we review potential topics for the in-person summit at PGConf.dev and discuss how to organize it.

• Adventures in Extension Packaging

  https://justatheory.com/2025/05/extension-packaging-adventures

  Narrative version of a PGConf.dev talk covering the many issues I stumbled upon while designing a universal packaging format for PostgreSQL extensions, maintaining pgt.dev packages, and experimenting with CloudNativePG immutability.