Pi 4 Performance: PostgreSQL and PostGIS
Happy Pi (π) Day! I decided to celebrate with another post
looking at the Raspberry Pi 4's
performance running Postgres and PostGIS.
A few months ago I added another Raspberry Pi to my collection,
the new Model 4 with 4 GB RAM. My
pgbench results comparing the Pi 4 against the previous
3B models as well as the even lower-powered Raspberry Pi Zero W.
This post continues testing the Rasperry Pi 4's performance with
PostgreSQL and PostGIS, this time with a look at a more suitable
setup for production use cases.
The main difference are the use of an external SSD drive and
Hardware and Configuration
The Raspberry Pi 4 is mounted in an enclosed Cana Kit case with a small fan in the top running on the 3.3 V rail (lower power), powered by a dedicated 3.5A power supply. The internal SD card is a 32 GB A1 rated SD card. With the Pi 4 you need the fan (see Baked Pi) and the application-class SD card to reach and maintain the best performance. This core setup from CanaKit costs around $100 at the time of writing. The SSD drive used is a SanDisk 500GB Extreme Portable SSD ($90) that can take advantage of the Pi's two (2) newly available USB 3 ports. That SSD claims 550 MB/s so so should be faster than what the Pi can handle, this benchmark showed 363 MB/s read, 323 Mb/s write was possible by the Pi.
The latest versions of Postgres (12.2) and PostGIS (3.0.1)
were installed from source on Rasbian Buster Lite,
read this post
for an overview of that process. The data directory for our
Postgres instance is created on the SSD instead of the Pi's
internal SD card. This can be defined during the
startup steps, or moved later via
initdb -D /mnt/ssd/pgsql/data pg_ctl -D /mnt/ssd/pgsql/data -l logfile start
My end goal for this Pi 4 is to use it as one of my local development
PostGIS servers. The configuration changes I make in
postgresql.conf are with that goal in mind. I'm leaving
(per results here),
(see Postgres: JIT),
and bumping up memory values for
I always use TLS to encrypt connections and
am using the
to limit protocols.
shared_buffers = 1GB work_mem = 50MB jit = off checkpoint_timeout = 120min checkpoint_completion_target = 0.9 ssl = on ssl_cert_file = '/etc/ssl/certs/my-cert.pem' ssl_key_file = '/etc/ssl/private/my-cert.key' ssl_prefer_server_ciphers = on ssl_ecdh_curve = 'prime256v1' ssl_min_protocol_version = 'TLSv1.2'
Full Disk Encryption
Now that it is realistic to setup a Pi with external storage, said external storage can (and should!) be encrypted. The SSD I am using is setup with full disk encryption (FDE) using LUKS. This can be accomplished quite easily. Encryption is great for security though it does come at a cost. This benchmark showed FDE has a 20% hit on disk performance. I figured this was worth mentioning since this is a post about performance, after all.
Performance Test: Bulk data load
The first place I test performance is typically with bulk data loads via osm2pgsql. I wrote about this with the Raspberry Pi 3B last year, and found that Colorado loaded in about 2 hours and 45 minutes. The Raspberry Pi 4 can do the same on with the SSD in 7.9 minutes, 95% faster! An additional detail to note is the file size of the Geofabrik export for Colorado has grown by 14%, from 167 MB to 191 MB over the past 14 months.
PGBench TPC-B (sort of)
I previously found the Pi 3B could reliably hit 200 TPS. Running the same TPC-B (sort of) benchmark on this Pi 4 setup gets 570 TPS (+185%)
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: 2053621 latency average = 17.514 ms latency stddev = 7.212 ms tps = 570.443363 (including connections establishing) tps = 570.445788 (excluding connections establishing)
PGBench Custom Reporting
The built-in pgbench tests show one perspective, though it isn't all that similar to many common workloads. A custom pgbench test can help show a more realistic perspective for your workload. Read this post for more details, I use the same process outlined in that post.
The initial wide open reporting test was ran for 10 minutes to get a baseline for these queries. This showed an all-out rate of nearly 16 TPS for the three reporting queries. On the Pi 3B this test topped out at roughly 6 TPS; the Pi 4 with full disk encryption on an external SSD provides a 167% improvement. Another win!
pgbench -c 10 -j 2 -T 600 -P 60 -s 2 \ -f tests/reporting1.sql \ -f tests/reporting2.sql \ -f tests/reporting3.sql \ bench_test
Snippet from results:
... duration: 600 s number of transactions actually processed: 9477 latency average = 633.449 ms latency stddev = 665.079 ms tps = 15.779909 (including connections establishing) tps = 15.780174 (excluding connections establishing) ...
I will also revisit the throttled rate test I did in the prior post. The goal with this approach is to determine the threshold of where performance, a measurable quality metric, breaks down. With the Pi 3B, the following command was enough to cause noticable numbers of late (2.6%) and skipped (0.5%) queries.
The Pi 4, however, handles this with ease with zero skipped queries and only 1 of 612 coming in too slow (>500ms). The timing results show a latency average of 159ms (vs. 238ms) and a standard deviation of 89ms (vs 144ms). We want both of these numbers to be lower and the Pi 4 delivers.
pgbench -c 1 -j 1 -T 600 -P 30 -s 2 \ -R 1 -r --latency-limit=500 \ -f tests/reporting1.sql \ -f tests/reporting2.sql \ -f tests/reporting3.sql \ bench_test ... number of transactions actually processed: 612 number of transactions skipped: 0 (0.000 %) number of transactions above the 500.0 ms latency limit: 1/612 (0.163 %) latency average = 159.270 ms latency stddev = 89.459 ms rate limit schedule lag: avg 17.318 (max 315.268) ms tps = 1.020003 (including connections establishing) tps = 1.020036 (excluding connections establishing)
I pushed this test with
--latency-limit fixed at 500ms up through
The Pi 4 handles this increased level of activity
far better than the Pi 3B handled 1 TPS. Even at 12 TPS there
were zero skipped queries and 17% of queries were "slow."
number of transactions actually processed: 7137 number of transactions skipped: 0 (0.000 %) number of transactions above the 500.0 ms latency limit: 1231/7137 (17.248 %) latency average = 306.024 ms latency stddev = 379.891 ms rate limit schedule lag: avg 0.842 (max 363.135) ms tps = 11.889196 (including connections establishing) tps = 11.889424 (excluding connections establishing)
Increase Performance Threshold
The above tests outperformed the benchmark set by the Pi 3B. With
the improved query performance on the Pi 4, I decided to
increase quality expectations (
--latency-limit) from 500 ms to 250 ms.
The following table shows the results for latency, standard deviation,
% late and % skipped. Results are included for a target Client/TPS count
of 3 through 16.
|Clients / TPS||Latency||StdDev||Late %||Skipped %|
A while back I added a couple scripts under an OpenStreetMap folder in my tests repo. The first uses the data from the table structure created by osm2pgsql. In this original form, the 250ms quality is too high of a bar. The average query latency comes in at 578ms and 15% of the transactions were skipped.
pgbench -c 4 -j 2 -T 600 -P 30 -s 2 \ -R 4 -r --latency-limit=250 \ -f tests/openstreetmap/osm--co-jeffco-trees-benches.sql \ pgosm transaction type: tests/openstreetmap/osm--co-jeffco-trees-benches.sql scaling factor: 2 query mode: simple number of clients: 4 number of threads: 2 duration: 600 s number of transactions actually processed: 2100 number of transactions skipped: 378 (15.254 %) number of transactions above the 250.0 ms latency limit: 2100/2100 (100.000 %) latency average = 578.368 ms latency stddev = 179.850 ms rate limit schedule lag: avg 22.379 (max 248.092) ms tps = 3.498242 (including connections establishing) tps = 3.498356 (excluding connections establishing)
Data structure is important
In my talk PostGIS Queries and Performance I cover
the importance of the data structure and its impact on performance.
The second query in the
tests/openstreetmap folder runs an
equivalent query that returns the same results,
but uses the structure created by
The results using the restructured data are more impressive and well
within the quality expectations.
pgbench -c 4 -j 2 -T 600 -P 30 -s 2 \ -R 4 -r --latency-limit=250 \ -f tests/openstreetmap/pgosm--co-jeffco-trees-benches.sql \ pgosm transaction type: tests/openstreetmap/pgosm--co-jeffco-trees-benches.sql scaling factor: 2 query mode: simple number of clients: 4 number of threads: 2 duration: 600 s number of transactions actually processed: 2423 number of transactions skipped: 0 (0.000 %) number of transactions above the 250.0 ms latency limit: 0/2423 (0.000 %) latency average = 67.341 ms latency stddev = 24.611 ms rate limit schedule lag: avg 0.856 (max 104.765) ms tps = 4.038397 (including connections establishing) tps = 4.038486 (excluding connections establishing)
This post has reviewed the Raspberry Pi 4's performance and ability to handle my Postgres and PostGIS workloads. I'm impressed at the improvements in numbers that I am seeing from pgbench. I'll give it a try to see how it handles my regular local use cases including exploring with QGIS. Stay tuned!
Need help with your PostgreSQL servers or databases? Contact us to start the conversation!
Published March 14, 2020
Last Updated March 14, 2020