RustProof Labs: blogging for education (logo)

PostgreSQL at RustProof Labs: 2019 in Review

By Ryan Lambert -- Published December 28, 2019

As I look back on 2019, I can say with great joy: I have spent a lot of time working with PostgreSQL and PostGIS this year! Last year I decided to write an end-of-year post, this is the 2019 version.

This post is a quick snapshot of my Postgres/PostGIS activities in 2019 with a quick look into 2020.

PostgreSQL popularity

Starting with popularity again, PostgreSQL has continued it's trend in popularity growth through 2019, a long running trend according to DB-Engine's ranking. Postgres is still 4th on the list, ahead of MongoDB and behind Oracle, MySQL and MS SQL. Through 2019 Postgres and MongoDB continued their growth in popularity, while all of the top 3 databases have gained back popularity they lost in 2018. The gap between Postgres and the top 3 on the list continues to shrink.

Chart from db-engines.com showing trend of top-5 most popular databases.  PostgreSQL (in 4th place) continues to grow at a steady rate through 2019 while the top three (Oracle, MySQL, MS SQL) worked to gain back losses in popularity they saw in 2018.

Yay, PostgreSQL!!

DB-Rankings explains their ranking methodology if you're curious.

Blog Posts

The most obvious way I contribute to PostgreSQL is by writing blog posts about topics I think are interesting, helpful, and not already covered in depth elsewhere. Hopefully you find them interesting too!

Through 2019, I published 23 posts (before this one); 22 of those specifically cover PostgreSQL and/or PostGIS (96%). The lone post not specific to Postgres was dedicated to why I love low power computing with the Raspberry Pi, and of course Postgres running like a champ, even with low power, is one of the reasons!

The goal I set last year was to publish two (2) Postgres-related posts per month, or 24 in a year. With 22 Postgres-related posts published I am two shy of that goal. I am okay with that for a few reasons:

PostGIS presentation and webinar

In March 2019 I presented Right Size your Spatial Data at Postgres Conference NYC.

I am also currently working with PostgresConf.org to provide a 6-part webinar series on using PostGIS and OpenStreetMap together. The first of the series, a non-technical overview ran on 12/11/2019, I should have the recording from that session posted soon.

The other five (5) sessions will be posted in the near future and run through March or April 2020. The goal is to record and post each session as they are completed. The full series will provide a complete overview of the technology (PostGIS) and the data (OpenStreetMap) and how to use them effectively together.

Book: Mastering PostGIS and OpenStreetMap

I am officially writing a book! The title is expected to be Mastering PostGIS and OpenStreetMap. I began drafting the structure in earnest during March 2019 and writing truly started in April. At this point the majority of the content is in place (or outlined), though I realize I have created a disconnect between the chapters where I did not keep my example data set consistent.

I have decided on the example data set, and now need to rework the example code, screenshots and descriptions to be coherent. I hope to complete that work in the next few weeks so I can get back to filling out the content and writing the last couple advanced chapters.

Postgres Acknowledgments

I am listed on the Postgres 12 release notes under Acknowledgments! As I mentioned in last year's review I had submitted a bug. Since then, that bug has been fixed. Getting involved in the development process of Postgres has been fun, I hope to continue to increase my involvement at that level. I now regularly review commitfest items and comment on patches as I feel I have relevant insight. Though, most of my time is spent simply following along with the discussions and learning about the internals!

PostgreSQL projects

I mentioned Postgres' popularity at the beginning because an active community is critical for most systems, and this is especially true with complex software such as a relational database. The more projects/people/etc that uses a system, the more likely it is that it will live on and receive maintenance. At RustProof Labs we use PostgreSQL in both open- and closed-source projects.

Open source projects

RustProof Labs has three open source projects based around PostgreSQL. The newest and most active this year is the PgOSM project. This project started in 2015 as a closed-source projected called OSM Reduce. The goal back then, and still is today, to make it easier to prepare OpenStreetMap data for use in PostGIS. It has been an open source project for almost a year now!

The PgDD project saw a major upgrade in 2019 too... It is now an extension! PgDD is a Data Dictionary extension to help document your PostgreSQL databases and the data contained within.

The PiWS (GitHub) is still alive and in use by myself, but did not see any real progress in 2019. There is plenty I want to do but simply have not had the time.

PgConfig Compare

In September I started a weekend project that turned into the pgconfig comparison tool. This tool was created to help make it easier to track configuration changes as Postgres evolves over time. I won't spend much time writing about this here since I just published a dedicated post on it yesterday!

Raspberry Pi and OpenStreetMap

There are two projects outside of the Postgres ecosystem itself that I regularly use together with Postgres. These deserve mention due to how much I have written about them:

In the past year I wrote seven (7) posts about the Raspberry Pi and included my most popular post from 2019: PostgreSQL performance on Raspberry Pi. There were five (5) posts that highlight using OpenStreetMap with PostGIS, most recently a look at the time and costs of loading data via osm2pgsql for regions of various sizes.

More specifically to OpenStreetMap are the contributions I have made to the OSM data itself, shown in the following screenshot. I hope to continue contributing a bit more consistently throughout next summer!

Chart from https://hdyc.neis-one.org showing my OpenStreetMap contributions for the past 12 months.  160 mapping days with 683 changesets.

Data Driven Art

Being an artist was never on my list of possible careers. Until about a month ago when I was playing around with travel time data! Turns out, I can make pretty pictures using data and software! The following image is something I worked up for Pangea Coffee Roasters, they hosted our 2016 OSM mapping party so I thought this was appropriate.

Reduced resolution image of data-driven artwork.  Artwork created by Ryan Lambert by using OpenStreetMap roads data, PgOSM travel time processing, and QGIS for styling.

The above image was generated from OpenStreetMap data by using PostgreSQL, PostGIS, osm2pgsql, PgOSM, and QGIS.

Planning for 2020

My main goal for 2020 is to complete and publish v1.0 of Mastering PostGIS and OpenStreetMap. Ideally that will happen by the end of June!

Another goal is to establish at least one buildfarm animal on a Raspberry Pi. Time will tell where that ambition will lead to!

I am not setting a specific goal for how many blog posts I will write, but I will commit to writing as much quality content as I have time for. Between the book, finishing the PostGIS and OpenStreetMap webinar series, and other commitments already made, 2020 is shaping up to be a busy year!

Summary

Well, 2019 has been a lot of fun. Here's to another fun year in 2020!

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!

By Ryan Lambert
Published December 28, 2019
Last Updated December 28, 2019