RustProof Labs: blogging for education (logo)

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!

Pi 4 differences

The Raspberry Pi 4B is still a Raspberry Pi, but when you look at what has changed with this version of the hardware it seems like an entirely different beast:

That's right, I ended the feature list with the "heat profile" because as this testing showed, the Pi 4 is the first Pi where heat is an issue you have to deal with. I recommend Gareth Halfacree's review that includes tons of information about the power increases, thermal profile, and other raw benchmarks of the Pi 4 compared to previous models.

Setup for Test

The three Pis I test in this post are the new Pi 4B with 4GB RAM, the Pi 3B with 1GB RAM, and the Pi Zero W with 512 MB RAM. All three are loaded with 32GB A1 rated SD cards and configured as described in my initial pgbench testing with parallel query disabled (max_parallel_workers_per_gather = 0) since the Zero only has a single core. Also, shared_buffers was scaled to be 25% of each device's RAM. Rasbian Buster Lite is the base OS image.

Picture showing the three Raspberry Pis being tested in their cases.  The Raspberry Pi 4B is in the black case, it is identifiable by its two blue USB 3.0 ports.

Test Run 1

Staying in line with my prior testing the pgbench database (bench_test) was initialized with scale = 10 and tests were ran against a fresh database for 1 hour. The following chart shows the Pi 4B started out with a slight lead in TPS over the Pi 3B, but after the first 5 minutes the Pi 4's performance dropped significantly with the decline continuing for the first 20 minutes where it stabilized.

Initial pgbench TPS results on Raspberry Pi 4, 3B, and Zero W

The following table shows the summary results for this first test run, the Pi 3B is the clear winner here. Obviously the Pi Zero is way down in last place, but 54 TPS for a single core, 512MB RAM device... not too shabby.

Pi Model TPS Latency Std Dev
Pi 4 426 23 57
Pi 3B 453 22 61
Pi Zero W 54 186 80

Baked Pi (aka thermal issues)

I went into this test knowing heat would be an issue, so before starting I setup each Pi with a simple script to track its internal temperature. This code goes into a script named temp_check.sh on each Pi:

while true
do
    printf "%s, %s, %s\n" $(hostname) $(date +%Y%m%d-%H:%M:%S) $(cat /sys/class/thermal/thermal_zone0/temp) >> temp_tracking.csv
    sleep 15
done

Note: Divide the temperature by 1,000 to get the value in C.

Before starting the testing I ran the script in the background:

bash ./temp_check.sh &

The following chart shows the temperature of each Pi during the previous pgbench test, readings were aggregated to once per minute. The chart includes about 15 minutes before and after the test.

Each Pi had been powered on idling and in its case for a few hours preceding the tests. The Pi 4 stabilized at 75C! The Pi 3B sits around 50C and the low power Pi Zero around 44C. The first small spike in temperature (time 14:00:00) was the result of running the pgbench initialize command (pgbench -i -s 10 bench_test). The pgbench test started at 14:02:00 and ran until 15:02:00. By 14:07 (5 minutes elapsed), the Pi 4 had reached 86C with the hottest single recorded temp at 88.3C! At the same time (5 minutes elapsed), the Pi 3 had risen to 76C, still below its thermal limiting. The Pi Zero seems to peak around 59C.

Initial pgbench on-board temperature results from Raspberry Pi 4, 3B, and Zero W

Improve cooling

After the first test, I took the Pis out of their cases and let them cool down a bit. After 10 minutes, even the Pi 4's heat sinks were cool to the touch so I powered them back on open to the air and started watching the temps again. The Pi Zero and Pi 3 had a nice, cool, steady temperature. The Pi 4... did not. It quickly was at 50C and continued climbing at a steady rate. (See 15:30 to 15:37 in the following chart)

Up until now, I haven't had issues with heat and my Pis, so I hadn't bothered with getting any 5V fans for them. For an immediate solution I grabbed a 8" fan we had handy and placed that on the desk angled to blow over the Pis. The following picture was taking from the point of view of the fan.

Picture showing the three Raspberry Pis out of their cases under the wind provided by a desk fan.

With this fancy cooling system in place, I ran another test watching the temperature closely. The init happened at 15:45 and the full test started at 15:46. Within the first couple of minutes I was astonished that the Pi 3B was running 1 - 2C warmer than the Pi 4 while under load! I rearranged the original layout on the desk (above picture) so the Pi 3B and Pi 4B would receive more equal treatment from the fan, but the Pi 4 continued to run cooler. Having the extra chip with a heat sink really seems to help distribute the extra heat! The Pi 3B ran the warmest around 57C, the Pi 4B around 55C, and the Pi Zero down at 42C. Cool. 😄

Final pgbench on-board temperature results from Raspberry Pi 4, 3B, and Zero W

Note: Ambient temperature was 27.8C (82F) according to the PiWS in my office.

Performance with Air Flow

That's great that the temps were brought down safely below the thermal limiting levels, but did it help? Yup, big time.

The results were impressive with all three Pis running faster with the cooler temps. The Raspberry Pi 4 was where it really helped though, this run showed a 76% improvement, reaching an overall 750 TPS!

Final pgbench TPS results on Raspberry Pi 4, 3B, and Zero W

The following table shows the summary results for this test run. With cooling, the Pi 4B is the obvious winner!

Pi Model TPS % Diff (TPS) Latency Std Dev
Pi 4 750 76% 13 61
Pi 3B 481 6% 21 60
Pi Zero W 61 13% 165 79

Pi 4: Baked vs Cooled

The following chart shows the TPS trends from the two runs on the Raspberry Pi 4B.

pgbench TPS comparison for the Raspberry Pi 4

One thing that sticks out on run2 (no case with fan) is the increased amount of variance in TPS from minute to minute. It hit 818 TPS in minute 19 vs 619 TPS in minute 22. I think what this shows is that the SD card bus is finally not the only bottleneck to contend with on the Raspberry Pi. The Pi 4B provides nearly double the previous SD read speed, and increased the write speed by a good margin too.

Next steps

This test run just scratches the surface of the possibilities with the new Raspberry Pi 4. I have ordered 5V fans that I'll install and test in my existing case. Other than that I'm excited to try out using an external USB drive for additional I/O performance since the USB 3.0 bus (> 300 MB/s) greatly outperforms the SD performance.

Of course, I also want to do some real world testing with how the Pi 4B improves timing with real ETL workloads. I'm particularly interested to see how much faster osm2pgsql will load OpenStreetMap data. Colorado's data (167MB) took 2.75 hours to load on a Pi 3B. My prediction is the Pi 4B will be able to do the same load in about 1.5 hours, cutting out about 45% of the time.

Summary

The Raspberry Pi 4B and Postgres are going to be great partners on my desk! With a 56% increase in TPS over the Raspberry Pi 3B, this enables more workloads while keeping the small, low power form we love. The additional capabilities for high performance off-board storage (USB 3 or NAS) opens up so many possibilities I can't wait to test and write about.

As I wrote this summary (I do write my summary last!) I realized there was hardly a mention that my Pi 4B has 4GB RAM. I was so excited about that detail the day of the announcement, to finally have more than 1GB RAM, but other than setting shared_buffers = 1GB I hardly gave it a second thought during this entire testing and writing process.

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

By Ryan Lambert
Published August 23, 2019
Last Updated August 23, 2019