PostgreSQL 16 improves infinity: PgSQLPhriday #012
This month's #pgsqlphriday challenge is the 12th PgSQL Phriday, marking the end of the first year of the event! Before getting into this month's topic I want to give a shout out to Ryan Booz for starting #pgsqlphriday. More importantly though, a huge thank you to the hosts and contributors from the past year! I looked forward to seeing the topic each month followed by waiting to see who all would contribute and how they would approach the topic.
Check out pgsqlphriday.com for the full list of topics, including recaps from each topic to link to contributing posts. This month is the 7th topic I've been able to contribute to the event. I even had the honor of hosting #005 with the topic Is your data relational? I'm really looking forward to another year ahead!
Now returning to your regularly scheduled PgSQL Phriday content.
This month, Ryan Booz chose the topic: What Excites You About PostgreSQL 16? With the release of Postgres 16 expected in the near(ish) future, it's starting to get real. It won't be long until casual users are upgrading their Postgres instances. To decide what to write about I headed to the Postgres 16 release notes to scan through the documents. Through all of the items, I picked this item attributed to Vik Fearing.
- Accept the spelling "+infinity" in datetime input
The rest of this post looks at what this means, and why I think this matters.
Load the Right Amount of OpenStreetMap Data
Populating a PostGIS database with OpenStreetMap data is favorite way to start a new geospatial project. Loading a region of OpenStreetMap data enables you with data ranging from roads, buildings, water features, amenities, and so much more! The breadth and bulk of data is great, but it can turn into a hinderance especially for projects focused on smaller regions. This post explores how to use PgOSM Flex with custom layersets, multiple schemas, and osmium. The goal is load limited data for a larger region, while loading detailed data for a smaller, target region.
The larger region for this post will be the Colorado extract from Geofabrik. The smaller region will be the Fort Collins area, extracted from the Colorado file. The following image shows the data loaded in this post with two maps side-by-side. The minimal data loaded for all of Colorado is shown on the left and the full details of Fort Collins is on the right.
Getting started with MobilityDB
The MobilityDB is an exciting project I have been watching for a while. The project's README explains that MobilityDB "adds support for temporal and spatio-temporal objects." The spatio-temporal part translates as PostGIS plus time which is very interesting stuff to me. I had briefly experimented with the project in its early days and found a lot of potential. Mobility DB 1.0 was released in April 2022, at the time of writing the 1.1.0 alpha release is available.
This post explains how I got started with MobilityDB using PostGIS and pgRouting. I'm using OpenStreetMap roads data with pgRouting to generate trajectories. If you have gpx traces or other time-aware PostGIS data handy, those could be used in place of the routes I create with pgRouting.
When I started working on this post a few weeks ago I had an unexpectedly
difficult time trying to get MobilityDB working.
I was trying to install from the production branch with Postgres 15 and Ubuntu 22.04
and ran into a series of errors. It turned out the fixes
to allow MobilityDB to work with these latest versions had been in the
branch for more than a year.
After realizing what the problem was I asked a question and got an answer. The fixes are now in the
tagged as 1.1.0 alpha.
Thank you to everyone involved with making that happen!
To install MobilityDB I'm following the instructions to install from source. These steps involve
sudo make install.
Esteban Zimanyi explained
they are working on getting packaging worked out for providing deb and yum installers.
It looks like work is progressing on those!
After installing the extension, the
postgresql.conf needs to be updated to include
PostGIS in the
shared_preload_libraries and increase the
to double the default value.
shared_preload_libraries = 'postgis-3' max_locks_per_transaction = 128
Postgres 15: Explain Buffer now with Temp Timings
This post explores a helpful addition to Postgres 15's
EXPLAIN output when
The Postgres 15 release notes
mention lists this item:
EXPLAIN (BUFFERS)output for temporary file block I/O (Masahiko Sawada)
This improvement adds new detail to the output provided from Postgres 15 when running
EXPLAIN (ANALYZE, BUFFERS) <query>. This post explores this feature along with
a couple different ways the reported I/O timing interacts with performance tuning.
Getting the feature
The first thing you need is to be using at least Postgres 15.
Your instance also needs to have
track_io_timing=on in your Postgres configuration
Check the value of this setting with
SHOW track_io_timing; ┌─────────────────┐ │ track_io_timing │ ╞═════════════════╡ │ on │ └─────────────────┘
Test Data and Server
This post used a Postgres 15 instance on a
Digital Ocean droplet
with 2 AMD CPU and 2 GB RAM. I loaded Colorado OpenStreetMap data via
The data loaded to the
weighs in at 2.5 GB, the
public schema, 3.3 GB, has the raw data needed to enable
--replication feature via osm2pgsql-replication. The advantage
to having more data than RAM is it's pretty easy to show I/O timings, which I need
for this post!
Using v4 of the Postgres H3 extension
I wrote about using the H3 extension last year in Using Uber's H3 hex grid in PostGIS and H3 indexes for performance with PostGIS data. Naturally, things have changed over the past 12 months, specifically version 4 of the H3 Postgres extension was released. The H3 Postgres extension (h3-pg) closely follows the upstream H3 project, including naming conventions. The changes made in H3 version 4 unfortunately changed every function name used in my original blog posts. It seems this mass renaming was a one-time alignment in the H3 project, hopefully they don't all get renamed again.
This post covers the changes required to migrate the code in my prior two posts work with version 4.x h3-pg.
Create the h3 extension
Creating the extension for PostGIS usage now involves installing two (2)
extensions. Some components have been split out into the
CASCADE when installing the
h3_postgis portion since that also
postgis_raster which I do not have installed by default.
CREATE EXTENSION h3; CREATE EXTENSION h3_postgis CASCADE;