The #30DayMapChallenge is going on again this November. Each day of the month has a different theme for that day's map challenge. These challenges do not have a requirement for technology, so naturally I am using OpenStreetMap data stored in PostGIS with QGIS for the visualization component.

The challenge for Day 5 was an OpenStreetMap data challenge. I decided to find and visualize missing crossing tags. Crossing tags are added to the node (point) where a pedestrian highway (e.g. highway=footway) intersects a motorized highway (e.g. highway=tertiary). This post explains how I used PostGIS and OpenStreetMap data to find intersections missing a dedicated crossing tag.

Without further ado, here was my submission for Day 5.

Continue Reading

Postgres 14 was released on September 30, 2021. With a new major version comes new features to explore! This post takes a look at the unique query id option enabled with compute_query_id in postgresql.conf. 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. I set compute_query_id to on instead of auto and to allow the pg_stat_statements extension to be loaded. Additionally, I turn on log_duration, set log_statement to all and update log_line_prefix to include query_id=%Q,

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 '

Continue Reading

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 the official 0.4.0 release. 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 generated columns and native partitioning. 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 pg_catalog.pg_attribute system catalog. In Pg12 and newer, pg_attribute has a column named attgenerated while earlier versions of Postgres do not have that column.

Continue Reading

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:

• PgOSM Flex routing queries (.sql)

Continue Reading

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.

Migration

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

Continue Reading