One of the main new features in Postgres 12 I was excited for was generated columns. I mentioned this feature breifly in my intial reivew of Postgres 12 where I gave one example of how I have already used generated columns. The most likely and common use cases for generated columns work as expected and improve performance for SELECT queries.

This post dives further into the feature including a peek at performance as usage goes beyond "convenience" columns (concatenate strings, basic formulas, etc.) to "heavy compute" columns.

Continue Reading

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