Relational and Non-relational Data: PGSQL Phriday #005
Welcome to the 5th installment of the #PGSQLPhriday blogging series. I am thrilled to be this month's host! The topic posts should be published by Friday February 3rd.
When Ryan Booz proposed the idea for #PGSQLPhriday I was immediately excited about it. Other than our first names, Ryan and I have a other few things in common. One of these common points is we both started our database careers in the world of MS SQL Server and later found our way to Postgres. My move to Postgres, and why I discovered Postgres, is at the heart of this month's topic for PGSQL Phriday 005.
Is your data relational?
The entire reason I discovered and started using Postgres was PostGIS. I needed PostGIS because I had a project in 2011 that could benefit from the OpenStreetMap data. The project still needed rock solid support for relational data and the SQL Standard, which Postgres also provides. However, it was the spatial support of PostGIS that pulled me into the world of Postgres.
Postgres and Software Development: PGSQL Phriday #004
This blog post is part of PGSQL Phriday #004. Hettie Dombrovskaya is this month's host! I was very excited to see the topic chosen as Postgres and Software Development. My contribution to this month's #pgsqlphriday topic covers how I manage code through our development processes. Check out Hettie's post for more about this month's topic.
Types of code
Before continuing on to the specific questions for the challenge, I want to define the broad groupings of code I work.
- Mission critical
- Not trivial
Mission critical code is where most of the "special sauce" is at. Mission
critical SQL code includes DDL commands to create the database structure
CREATE TABLE foo and
CREATE VIEW baz. This level of code
represents the data structure that enables everything else to function.
Stubbing toes with
Postgres has a handy module called
lives up to its name: it runs
EXPLAIN automatically for you.
The intent for this module is to automatically provide information useful
for troubleshooting about your slow queries as they happen.
This post outlines a pitfall I recently discovered with
auto_explain. Luckily for us, it's an easy thing to avoid.
I discovered this by running
CREATE EXTENSION postgis;
and watching it run for quite a while before failing with an out of disk space error.
That is not my typical experience with a simple
CREATE EXTENSION command!
Standard use of
A common way
auto_explain is used is to target "slow queries" through the setting
auto_explain.log_min_duration. This setting defines the number of milliseconds to use
as the threshold of when to log the
EXPLAIN output. If your queries are typically 10-50 ms,
you might decide to set
auto_explain.log_min_duration = 100 to log queries taking twice as
long as your goal. An instance serving big analytic queries might want to set that much higher,
say 2 or 5 seconds.
I say my testing was innocent because I wasn't trying to break something.
That makes it innocent, right? I was playing around with
auto_explain trying out
PgMustard's scoring API.
At the time I didn't want to think about where to set that threshold... I just wanted to capture
explain output for testing.
explains that setting
auto_explain.log_min_duration = 0
will capture "all plans." Sounds good, let's do that!
What is the PostgreSQL community to you? - PGSQL Phriday #003
This blog post is for PGSQL Phriday #003. Read Ryan Booz' introduction from September for more details on PGSQL Phriday. Pat Wright (SQL Asylum) is this month's host and chose the topic: What is the PostgreSQL community to you?
The Postgres community is helpful.
One big community with many layers
The remainder of this post explores why I say the Postgres community is helpful. Postgres is an open source project with multiple layers and locations of community. Membership is open, free, and no invite is needed.
Prefer having an invite? You're invited!
Route the Interesting things (Not just roads) with OpenStreetMap
PostGIS Day 2022
is tomorrow, was November 17th.
I am excited to be giving a talk at this year's PostGIS Day virtual event hosted by
The recording to this talk is on the Crunchy Data's YouTube page! Downloads are below.
My talk, Route the Interesting things (not just roads!) with OpenStreetMap, is scheduled for 2:00 PM Mountain Time. The full schedule is available on Crunchy Data's website. The original purpose of this post is to provide resources I will reference in my talk today. If you're here for those resources, feel free to skip to the Downloads and Links sections below.
The other purpose for this post is to provide a last minute announcement for this year's PostGIS Day talks and speakers. There are a lot of great talks scheduled! Register now, there's still time, it's virtual, and free!
What I'll be watching
PostGIS Day is an all-day, 12 hour, 24 session marathon of PostGIS related talks. I plan to attend PostGIS sessions from start to finish this year, with small breaks in there for a bit of real life. As I read through the schedule I find myself saying "That'll be interesting" and "I wonder how..." It's a seriously great lineup!
It was tough to choose the top-5 list of talks I don't want to miss. I had to firmly apply a "How will I use this today?" filter to narrow it down.
- Vicky Vergara: How to Prepare a Graph for pgRouting
- Krishna Lodha: Creating APIs from PostGIS with Django and GeoAlchemy
- Aliaksandr Kalenik: GiST Index Building in PostgreSQL 15
- Bonny Mcclain: Once upon a database... Telling stories with PostGIS and QGIS
- Greg Smith: Speedrunning the Open Street Map osm2pgsql Loader