RustProof Labs: blogging for education (logo)

PostgreSQL at RustProof Labs: 2018 in Review

By Ryan Lambert -- Published December 20, 2018

As I look back on 2018, I can say with great joy: I have spent a lot of time working with PostgreSQL this year! Postgres is my favorite open-source database, and I'm really excited to start digging into some of the new features and enhancements in PostgreSQL 11 and PostGIS 2.5.

This post is a quick snapshot of my Postgres activities in 2018 and some goals for 2019. For me, these activities fall into a three broad groups.

PostgreSQL popularity

PostgreSQL has continued to grow in popularity through 2018, a long running trend according to DB-Engine's ranking. Postgres is 4th on the list ahead of MongoDB (even before the news of The Guardian switching!) and behind Oracle, MySQL and MS SQL. Postgres and MongoDB are growing in popularity while all of the top 3 databases are apparently declining.

Chart from db-engines.com showing trend of top-5 most popular databases.  PostgreSQL (in 4th place) continues to grow at a faster rate while the top three (Oracle, MySQL, MS SQL) continue to decline in popularity.

Yay, PostgreSQL!!

They explain 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 2018, I published 18 posts (before this one); 12 of those specifically cover PostgreSQL (66%) with four on PostGIS (22%). Another three (3) posts wouldn't have been possible without PostgreSQL and PostGIS, so I should really say that 15/18 posts in 2018 were on PostgreSQL (83%)!

Projects using PostgreSQL

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

Open source projects

RustProof Labs has three open source projects based around PostgreSQL. Two are available now, the third is weeks away from being released.

Early in 2018, I released the first versions of The PiWS. The PiWS is an open source weather station built around PostgreSQL running on the low power Raspberry Pi. I wrote an introduction to the system in May, the code is on GitHub. Just last month (November), I published our pgdd project (post, GitHub). pgdd is an open source data dictionary enhancement to help document your PostgreSQL databases and the data contained within.

The third project, pgosm, will hopefully be publicly available mid-January 2019. I started the project in 2015 (called OSM Reduce originally) to make it easier to load data to PostGIS. Every map I have produced in the last four years that included OpenStreetMap data has used the data produced via pgosm. I'm really excited to get this code cleaned up a bit more and out there for others.

PostgreSQL @ RustProof Labs

Up until recently, most of our databases were running PostgreSQL 9.5 and PostGIS 2.2. I had upgraded to 9.5 shortly before the 9.6 release came out so decided to skip the 9.6 upgrade. When PostgreSQL 10 came out I started testing new enhancements (like parallel queries, my most popular post of 2018), but by the time I got serious about upgrading production servers it was only a couple months before PostgreSQL 11 was scheduled to come out, so I held out until Pg11.

PostgreSQL 11

The good news is: all PostgreSQL servers* at RustProof Labs have been upgraded to PostgreSQL 11 and PostGIS 2.5!

  • This excludes PostgreSQL running on Raspberry Pis! Those are running PostgreSQL 9.6 for reasons beyond the scope of this post.

Track Your Garden

I have continued to develop and enhance Track Your Garden. Track Your Garden also started in 2015, on Pg 9.3 originally, and continues to improve and grow. Remember The PiWS I mentioned earlier? Yup, each PiWS can send its data from the sensors through an API to Track Your Garden's PostgreSQL database.

Other stuff

I am officially (finally) a member of the PgUS Association!

I reported my first bug, and did my best to document the problem and possible workarounds for the few users it may affect.

After upgrading Postgres clusters, I noticed a possible place for improvement in pgbackrest, so I made a suggestion to the fantastic folks maintaining pgbackrest. They have already implemented the change (12 days after opening the issue), and it should be included in the next release (v2.08).

The PostgreSQL community rocks!

Planning for 2019

I have a bit more cleanup on the pgosm repo before I can release it as an open source project. I hope to make progress on that during my time off around the holidays and get it on GitHub in January 2019. Both the pgdd and PiWS projects are young, and have a lot of room for features and enhancements. Some bugs too, I would suspect!

In 2018, I averaged roughly one PostgreSQL blog post per month. I'd like to get that up to two posts per month, but we'll see if that is feasible. I plan to focus more on PostGIS and that means more time experimenting, learning, and trying to break stuff! I have the ST_AsMVT() function on my list for testing since that was released in PostGIS 2.4 and with 2.5 also includes parallel support. (We were on 2.3 and 2.2 until a couple weeks ago)

The upgrade from Postgres 9.5 to 11 was almost too simple. I intend to upgrade our servers to the latest major version within 1-2 months of release. While November and December are busy months, so are the other 10 months of the year...

Last, I intend to push my herd of Raspberry Pis to their limits. Here are the four Pis recieving the most regular abuse right now.

View this post on Instagram

The #raspberrypi looks good when rack mounted! They also do quite a bit of work too! #piws #postgresql #openstreetmap #trackyourgarden

A post shared by Track Your Garden (@trackyourgarden) on

Summary

Well, PostgreSQL... 2018 has been a lot of fun. Here's to another one in 2019!

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

By Ryan Lambert
Published December 20, 2018
Last Updated December 20, 2018