A data dictionary is an important tool for anyone that stores and consumes data. The PgDD extension makes it easy to inspect and explore your data structures in Postgres. This post shows how PgDD provides access to current and accurate information about your databases for a variety of users:

• Analysts
• DBAs and Developers
• The Business

This data dictionary information from PgDD is made available using standard SQL by querying a small set of views.

Background

Relational databases, including Postgres, track the majority of the information needed for a data dictionary. This is done in the underlying system catalogs; Postgres' system catalogs are in the pg_catalog schema. The challenge with using the system catalogs is they are not very user friendly to query for the type of details commonly needed. PgDD does not do anything magical, it is simply a wrapper around the Postgres system catalogs!

Continue Reading

PostGIS is a widely popular spatial database extension for Postgres. It's also one of my favorite tools! A recent discussion on the People, Postgres, Data Discord server highlighted that the permissions required for various PostGIS operations were not clearly explained in the PostGIS documentation. As it turned out, I didn't know exactly what was required either. The basic PostGIS install page provides resources for installing the binary on the server and the basic CREATE EXTENSION commands, but does not explain permissions required.

This post explores the permissions required for three types of PostGIS interactions:

• Install/Create PostGIS
• Use PostGIS
• Load data from pg_dump

Database and Users

I am using Postgres installed on my laptop for these tests, Postgres 13.5 and PostGIS 3.1. I created an empty database named postgis_perms and check the \du slash command in psql to see the current roles. This instance has my my ryanlambert role, a superuser, and the default postgres role. The postgres role is not used in this post outside of this example.

([local] 🐘) ryanlambert@postgis_perms=# \du
                                     List of roles
┌─────────────┬────────────────────────────────────────────────────────────┬───────────┐
│  Role name  │                         Attributes                         │ Member of │
╞═════════════╪════════════════════════════════════════════════════════════╪═══════════╡
│ postgres    │ Superuser, Create role, Create DB, Replication, Bypass RLS │ {}        │
│ ryanlambert │ Superuser, Create role, Create DB                          │ {}        │
└─────────────┴────────────────────────────────────────────────────────────┴───────────┘

Continue Reading

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