The PgDD project is now an extension for Postgres! This post is about the PgDD (PostgreSQL Data Dictionary) project, why it exists, and how to install the extension. I have found the ability to query the most important database structures a huge benefit, hopefully others will find this project useful too.

This post is outdated, PgDD has been rewritten using the pgx framework. Read PgDD extension moves to Pgx and Postgres Data Dictionary for everyone for more details!

Why PgDD is helpful

PgDD makes it possible to easily explore your database structure with standard SQL syntax. You do not need to use a specific tool or learn a set of new commands, simply install the extension and start querying. To find the 5 largest tables by size on disk, simply query the dd.tables view.

Continue Reading

Postgres 12 and PostGIS 3 have been out and production ready for about a month now. I have been testing the upgrade from Pg 11 to 12 and have evaluated various workloads, mainly looking for red flags that would hold up an upgrade. At this point I am happy with what I see and am planning the upgrade for all our Postgres databases! This post covers a few points I found interesting during my initial testing. See the Postgres 12 release notes and PostGIS 3 release notes for more details about what has changed in these versions.

Reasons to upgrade

The main feature making me want to upgrade to Postgres 12 is stored generated columns. This allows columns to store a calculated value to disk, much like materialized views store the results of queries.

Continue Reading

I love working with OpenStreetMap (OSM) data in PostGIS. In fact, that combo is the main reason I made the leap to Postgres a few years ago! One downside? Getting data through osm2pgsql takes a bit of patience and can require quite a bit of Oomph from the system doing the load. Earlier this year I wrote about this process for smaller regions using a local virtual machine and the Raspberry Pi 3B. While small area extracts, such as Colorado, are great for some projects, other projects require much larger data sets. Larger data sets can benefit from (or even require!) more powerful hardware.

As OSM's PBF file approaches and exceeds 1GB the process starts requiring some serious RAM in order to complete the process. Testing this process in depth got me curious about cost effectiveness and time involved to load OpenStreetMap data as the source data size increases. I know that I can spool up a larger instance and the process will run faster, but does it end up being cheaper? Or just faster? This post is an attempt to document what I have found.

Continue Reading

PostgreSQL 12 will be released very soon, the current ETA for release is October 3, 2019! I have been looking forward to Pg12 pretty much since we upgraded to Pg11. The full list of amazing improvements in Postgres 12 is too long to get into here, but here are three great reasons:

• Improvements to enable PostGIS to take advantage of parallel query
• Covering GIST indexes
• Generated columns

See the release notes for the full list!

This post is part of the series PostgreSQL: From Idea to Database.

Updates change configurations

With a major upgrade around the corner it is time to review the changes between configuration files, namely postgresql.conf, and ensure our deployment process is updated accordingly. I expected the upgrade to Pg12 to have a decent number of changes as a result of consolidating the recovery.conf options alone.

In the past, I have grabbed a clean copy of postgresql.conf with its defaults and used git diff to get the results I needed. While that works, it does leave quite a bit to be desired from a user-friendly perspective.

Continue Reading

The Raspberry Pi 4B is out, and it's pretty much awesome. With how much I love the Raspberry Pi, it's no surprise that I pre-ordered one of the new Raspberry Pi 4B models with 4GB RAM the same day they were announced. I had to wait about a month for it to arrive, and I have had my Pi 4 for about a month now. I had some free time to put the new Pi 4 hardware to the test with PostgreSQL and pgbench. The goal of this post is to set an initial Postgres benchmark for the Raspberry Pi 4 and PostgreSQL 11, compared to older Raspberry Pi hardware.

This post is part of the series PostgreSQL: From Idea to Database.

Revisit prior testing

Before we get into the new results, let's recap the results from prior testing with the Pi and Postgres. My first serious test (April 2019) used four of the Pi 3B models and tested Postgres 9.6 through Postgres 12 (devel). Those TCP-B (like) tests showed that 200 TPS was a reliable goal for that hardware. At that time I was pretty happy with the results, until a few months later I tested the Pi 3B again using the newer A1 rated SD cards. That change boosted TPS performance by 140%, upping the target to nearly 500 TPS!

Continue Reading