Postgres and Pi: Use the right SD card

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.

TLDR;

Swapping SD cards to A1 rated cards increased TPS (Transactions per Second) by more than 140% over my prior pgbench testing with exactly the same hardware! These results are from a clean PostgreSQL 9.6 install and shared_buffers = 250MB on one of the Raspberry Pis previously tested.

To amend my prior performance claims:

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

This increase in performance is a direct result of having SD cards designed for random read/write operations, like the A1 class SD cards are. A1 is required to achieve 1500/500 random read/write IOPS, while A2 is required to achieve 4000/2000.

Source

SD Cards tested

The pgbench results I previously published (TCP-B and reporting) used 4 of these SD non-application rated cards. I purchased four of those 16GB cards from Amazon on 4/27/2018 for $9.83 each. Three of the cards are still in service (14+ months now), one was retired after my TCP-B testing showed it had performance issues (just under 12 months).

The new A1 rated cards I am using now were purchased on 4/11/2019 for $7.39 (32 GB). I just opened the first one this week.

Tests and Results

I used the same basic testing documented previously.

Results from PostgreSQL 9.6, Raspbian Stretch:

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
duration: 3600 s
number of transactions actually processed: 1919796
latency average = 18.742 ms
latency stddev = 25.295 ms
tps = 533.270810 (including connections establishing)
tps = 533.272645 (excluding connections establishing)

Results from PostgreSQL 11, Raspbian Buster:

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
duration: 3600 s
number of transactions actually processed: 1685367
latency average = 21.361 ms
latency stddev = 63.041 ms
tps = 468.138654 (including connections establishing)
tps = 468.140050 (excluding connections establishing)

I wasn't suprised to see Pg11 perform slower than 9.6 on the Pi with their respective default configurations. A number of changes have been made to Postgres defaults between versions 9.6 and 11, many of these changes are better suited for more powerful hardware. My prior post describes those differences in the "Postgres Configuration" section. I'm certain with a little more fiddling with the configuration for Pg 11 things will speed up nicely.

Summary

If you have Raspberry Pis with non-app rated SD cards, it's worth upgrading! The price per GB of storage continues to decline while simultaneously providing major performance improvements. I haven't even tested the Raspberry Pi 4 yet, that just arrived as I was finishing up this post. Stay tuned for a detailed writeup of performance on the Pi 4!

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