Exploring PgConfig comparison tool
PgConfig is a RustProof Labs hosted tool that makes it easy
to compare configuration changes (postgresql.conf) between major PostgreSQL versions.
The initial version of this tool was
the result of a lazy Sunday
at home, and since then has seen multiple enhancements. This post
goes over how I have been using the tool over the past few months.
The current version of PgConfig has three (3) main functions:
- Differences between version X and Y
- Single parameter history
- Compare your configuration to version defaults
Upgrading to Postgres 12 and PostGIS 3
My initial testing
to upgrade our PostGIS servers to Postgres 12 and PostGIS 3 went well.
After that testing I noticed
an extra snippet in the output of SELECT PostGIS_Full_Version(); in all of
our databases, post-upgrade:
procs need upgrade for use with PostgreSQL "120"
The only way I have found to resolve this message is to perform a hard upgrade of PostGIS databases after the main upgrade is complete. If you have managed PostGIS databases for any amount of time you have likely experienced this before.
"By HARD UPGRADE we mean full dump/reload of postgis-enabled databases. You need a HARD UPGRADE when PostGIS objects' internal storage changes or when SOFT UPGRADE is not possible."
At first I thought this was only affecting a lone, older database that maybe I didn't get cleanly upgraded last time. Then I noticed it affecting a PostGIS-enabled database I had created in the past couple of months on Postgres 11 and PostGIS 2.5.
This post outlines how to upgrade to Postgres 12 and PostGIS 3 from the most recent prior versions.
Postgres 12 Generated Columns and PostGIS
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.
PgDD - PostgreSQL Data Dictionary Extension
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.
PostgreSQL 12 and PostGIS 3 Initial Review
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.
Mastodon