RustProof Labs: blogging for education (logo)

Scaling osm2pgsql: Process and costs

By Ryan Lambert -- Published October 15, 2019

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

Prepare for Postgres 12: Configuration Changes

By Ryan Lambert -- Published September 24, 2019

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:

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

PostgreSQL on Raspberry Pi 4: Initial Testing

By Ryan Lambert -- Published August 23, 2019

The Raspberry Pi 4B is out, and it's pretty much awesome. With how much I love the Raspberry Pi, it's no surprise that I pre-ordered one of the new Raspberry Pi 4B models with 4GB RAM the same day they were announced. I had to wait about a month for it to arrive, and I have had my Pi 4 for about a month now. I had some free time to put the new Pi 4 hardware to the test with PostgreSQL and pgbench. The goal of this post is to set an initial Postgres benchmark for the Raspberry Pi 4 and PostgreSQL 11, compared to older Raspberry Pi hardware.

This post is part of the series PostgreSQL: From Idea to Database.

Revisit prior testing

Before we get into the new results, let's recap the results from prior testing with the Pi and Postgres. My first serious test (April 2019) used four of the Pi 3B models and tested Postgres 9.6 through Postgres 12 (devel). Those TCP-B (like) tests showed that 200 TPS was a reliable goal for that hardware. At that time I was pretty happy with the results, until a few months later I tested the Pi 3B again using the newer A1 rated SD cards. That change boosted TPS performance by 140%, upping the target to nearly 500 TPS!

Continue Reading

Create vector layers using PostGIS and QGIS

By Ryan Lambert -- Published August 04, 2019

The goal of this post is to show how PostGIS and QGIS can be used to create spatial data not bound to Earth. Why bother? As I explained in a recent bug report, there are few valid examples of non-Earth use cases for PostGIS:

For this post I focus on creating a fictional world for use in a novel. Most fictional worlds mimic familiar attributes and properties we know on Earth, such as roads, cities, government boundaries, buildings, rivers, lakes and so on. The difference in a fiction world are all the names, locations, routes and details!

Continue Reading

Postgres and Pi: Use the right SD card

By Ryan Lambert -- Published July 24, 2019

I have written a lot about running PostgreSQL on the Raspberry Pi. In the course of this endeavor, the most frequent comment I hear is:

"... But the SD card!"

One of the common complaints about the SD card is it's poor random read/write performance characteristics. This is a well-known side effect of the way SD cards were originally designed for digital cameras and high definition video recording. The SD 5.1 specification included new "application class" designations, more on the details later. This week I finally had the chance to put an A1 card to the test in a Raspberry Pi 3B.

Spoiler alert: You want SD cards with A1 or A2 designations in your Raspberry Pi.

This post is part of the series PostgreSQL: From Idea to Database.

Continue Reading