Using Query ID in Postgres 14
Postgres 14 was released
on September 30, 2021. With a new major version comes new features
This post takes a look at the unique query id option
This particular backend improvement, included with Postgres 14, is one I am
excited about because it makes investigating and
monitoring query related performance easier.
This post covers how to enable the new feature and explores how it can be used
in real life performance tuning.
Enable query id
For testing I created a new instance with Postgres 14 installed
and edited the
postgresql.conf file to change a few configuration options
related to the query id.
on instead of
and to allow the
pg_stat_statements extension to be loaded.
Additionally, I turn on
log_line_prefix to include
compute_query_id = on shared_preload_libraries = 'pg_stat_statements' log_duration = on log_statement = 'all' log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h,query_id=%Q '
PgDD extension moves to Pgx
Our data dictionary extension, PgDD,
has been re-written using the pgx framework in Rust!
At this time I have tagged
0.4.0.rc3 and just need to do a bit more testing before
While I am excited for the news for PgDD, what is more exciting is the
pgx framework and the ease it brings to developing Postgres extensions!
Getting started with pgx is straightforward and using
cargo pgx run makes it
simple to build your extension against multiple versions of Postgres.
This post outlines how I came to the decision to use pgx for Postgres extension development.
Progression of PgDD
Before now, PgDD was a raw SQL extension, with that version being an evolution from prior iterations.
Shortly after I converted PgDD to a raw SQL extension I wanted it to do more,
specifically related to supporting newer features such as
Supporting new features in new versions of Postgres is a good idea, but I couldn't
drop support for older versions at that time either.
Using generated columns as an example, the feature was added in Postgres 12 and
came along with an update to the
catalog. In Pg12 and newer,
has a column named
attgenerated while earlier versions of Postgres do not have
(Webinar) Routing with PostGIS and OpenStreetMap
I am excited to be presenting Routing with PostGIS and OpenStreetMap at PostgresConf South Africa 2021! The talk is scheduled for Tuesday October 5, 2021, 3:10 PM SAST (7:10 AM MST).
This page has the resources used during this session.
Downloads for session
Scripts used for the demo:
Psycopg3 Initial Review
If you use Postgres and Python together you are almost certainly familiar with psycopg2. Daniele Varrazzo has been the maintainer of the psycopg project for many years. In 2020 Daniele started working full-time on creating psycopg3, the successor to psycopg2. Recently, the Beta 1 release of psycopg3 was made available via PyPI install. This post highlights two pieces of happy news with psycopg3:
- Migration is easy
- The connection pool rocks
As the first section shows, migration from psycopg2 to psycopg3 is quite easy. The majority of this post is dedicated to examining pyscopg3's connection pool and the difference this feature can make to your application's performance.
Easy migration is an important feature to encourage developers to upgrade. It is frustrating when a "simple upgrade" turns into a cascade of error after error throughout your application. Luckily for us, psycopg3 got this part right! In the past week I fully migrated two projects to psycopg3 and started migrating two more projects. So far the friction has been very low and confined to edge case uses.
The following example shows a simplified example of how my projects have
Identify OpenStreetMap changes with Postgres
The data in the main OpenStreetMap database is constantly changing. Folks around the world are almost certainly saving changes via JOSM, iD, and other editors as you read these words. With change constantly occurring in the data, it is often desirable to have an idea of what has actually changed in the data. This post explores one approach to tracking changes to the tags attribute data once it has been loaded to Postgres.
The topic of this post surfaced while I was working on refreshing
a project involving travel times (routing). In the process I noticed a
few instances where the analysis had shifted significantly.
My first hunch was that entire segments of road had been
added or removed, but that was not
the cause. It became apparent that tags in the area had been
improved. It was easy to specifically point to the value associated
highway key but I also knew there were other changes
happening, I just wasn't sure what all was involved and at what scale.
Calculate tag hash
The database I am working in has five (5) Colorado
snapshots loaded spanning back to 2018. The tags data is loaded
to a table named
osmts.tags, read my post
Timescale, Compression and OpenStreetMap Tags
for how this table was created. The
tags table has one row for
every OpenStreetMap feature and stores the full key/value attribute data
in a JSONB column (
A relatively simple way to detecting change in data is to
calculate the hash for each feature's key/value data. Comparing
hashes for any change will identify rows that had changes to their