In a previous post I documented PostgreSQL's performance on the Raspberry Pi 3B over multiple versions of Postgres. There I used the standard TPC-B (sort of) tests included with pgbench that try to run as fast as possible. That's reasonable from a certain perspective, but falls short from helping to decide how much hardware you really need.

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

This post puts Postgres on the Pi under a more realistic workload (for me) and explains how I approached the task. For this post I'm using Postgres v11.2 for all tests and using three Raspberry Pi's for the testing. The configuration for Postgres is the same as the prior post and the only Postgres-specific tuning I do in this post is on max_parallel_workers_per_gather. That brings me to by secondary goal with this post, do a better job finding where PostgreSQL's parallel query feature works well, and where it can hurt performance. Parallel query was introduced in Pg 9.6 and has improved with each subsequent version.

My example workload

My Postgres-Pis are all disposable and easy to rebuild. I have a standard image created that I can flash to an SD card, run an Ansible deployment or two, and the Pi is setup for whatever I want it to do. Much of my querying is from a reporting/analytical perspective. This means I'm using wider tables with multiple data types, indexes, joins, and aggregations. Not tiny SELECT, UPDATE and INSERT queries.

Continue Reading

Have you ever wondered how well PostgreSQL performs on a Raspberry Pi 3B? Oh. You haven't? Oh well, I asked myself that question and put it to the test. This post covers how I used pgbench to answer this question and the results of what I found.

I love the Raspberry Pi, my post Low Power Computing with Raspberry Pi explains more.

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

TLDR;

Modern PostgreSQL (>=9.6), out-of-the-box, achieves around 200 TPS (TPC-B (sort of)) on a Raspberry Pi 3B. That's 17+ million transactions per day!

Note: These tests used non-app rated SD cards. See my post on SD cards for more details.

What was tested

I tested four PostgreSQL versions, 9.6.12, 10.7, 11.2, and 12-dev. Tests were ran using four Raspberry Pi 3Bs mounted as a Rack-o-Pi. The first round of testing used one version of Postgres installed on each Pi. For the rest of the testing I focused on Pg11 and Pg12.

Continue Reading

Are you trying to decide which version of PostgreSQL to install? Maybe you need to decide if it's worth upgrading to the latest version. This post is here to help.

I started thinking about this after reading Brent Ozar's post on this topic from a MS SQL Server perspective. One thing I realized is that PostgreSQL major versions are maintained for roughly 5 years compared to MS SQL's 10 year support. The more I have pondered this difference, the more I agree with only keeping versions in support for 5 years.

New Installations

If you are starting from scratch with Postgres, start with v11. There aren't going to be many good reasons to start a new project on anything but the latest version.

Continue Reading

It's no secret that I love the Raspberry Pi. Earlier this year I wrote about using osm2pgsql on a Raspberry Pi to load OpenStreetMap data to PostgreSQL. Less than a year ago I released the PiWS, an open source weather station based around the Raspberry Pi. In fact, my first test PiWS was setup almost exactly one year ago on a single-core, 512 MB RAM Raspberry Pi Zero W and it is still running today.

Since publishing this post, I have also tested PostgreSQL performance on the Pi. Parts One and Two.

Anyway, the point of this post is to explain why I think the Pi is so awesome, why I continue to chose it over other competing hardware available today, and why I think the challenge of developing for low-power devices is so valuable.

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 Dec 20, 2018 at 6:35pm PST

Continue Reading

I wrote about the PgOSM project last month (GitHub) showing how to transform OpenStreetMap data into a more relational format. The goal of this post is to show how you can easily customize the transformations and reductions to suit your needs. The prior post used a command to load a layer_definitions.sql file (view on GitHub) under the section Deploy schema and define layers.

psql -d pgosm -f ~/git/pgosm/db/data/layer_definitions.sql

Customizing the data loaded in that step is the key to changing how PgOSM transforms, reduces and organizes your OpenStreetMap data. This post shows you how to do that!

This post is outdated. The PgOSM project was replaced by PgOSM Flex. See Better OpenStreetMap data using PgOSM Flex 0.6.0 for a more recent approach.

History of PgOSM

I came up with the core of this project in 2015 over the course of a weekend or two. Since then it has continued to serve my needs reliably, mostly without updates, ready to load fresh OpenStreetMap data any time I asked. Recently I decided to clean up the project and release it under the MIT license. I've always felt like it's an ugly solution, it's far from perfect... but being reliable and customizable counts for a lot!

Continue Reading