PostgreSQL at RustProof Labs: 2018 in Review
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.
- Blog posts
- Projects built around PostgreSQL (open-source and internal)
- Other stuff
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.
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!
All production servers upgraded to #PostgreSQL 11! pic.twitter.com/PJyEwSvbtg
— RustProof Labs (@RustProofLabs) December 8, 2018
- 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.
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!
Mastodon