PostgreSQL performance on Raspberry Pi, Reporting edition
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
Another difference is my Pi database servers aren't constantly bombarded at these high rates of connection. A lot of databases are idle much of the time (gasp) with occasional use by a handful of users in short bursts. Especially with a Raspberry Pi, I see those as "developer machines." They're cheap enough that each developer can have a rack (seriously, $250 for a rack of 4...) and they perform well enough, along with automation, that makes them seriously useful.
pgbench gives us everything we need to change up our testing scenario
a different workload in mind.
Setting up for Tests
Switch to the
postgres user and setup for the tests.
sudo su - postgres mkdir ~/git cd ~/git git clone https://github.com/rustprooflabs/pgbench-tests.git cd pgbench-tests
bench_test database and initialize it with the reporting tables.
We need to use
psql to seed the database instead of
With custom tests, we have custom tables and data loading too. For the
Pi I use
scale=2 to load the
pgbench_reporting table with 200,000 rows.
createdb bench_test psql -d bench_test -f init/reporting.sql -v scale=2
This creates a
bench_test database with three tables. The main table being
pgbench_reporting. Using a scale of 2 on this custom data set seeds 200,000 rows
taking up a little under 40MB on disk.
psql -d bench_test -c "\dt+" List of relations ┌────────┬───────────────────┬───────┬──────────┬───────┬─────────────┐ │ Schema │ Name │ Type │ Owner │ Size │ Description │ ╞════════╪═══════════════════╪═══════╪══════════╪═══════╪═════════════╡ │ public │ pgbench_lookup1 │ table │ postgres │ 48 kB │ │ │ public │ pgbench_lookup2 │ table │ postgres │ 48 kB │ │ │ public │ pgbench_reporting │ table │ postgres │ 37 MB │ │ └────────┴───────────────────┴───────┴──────────┴───────┴─────────────┘ (3 rows)
Looking closer at the
pgbench_reporting table there are a few types of data, foreign
key constraints and indexes added to the foreign key columns.
bench_test=# \d pgbench_reporting Table "public.pgbench_reporting" ┌────────┬──────────────┬───────────┬──────────┬─────────┐ │ Column │ Type │ Collation │ Nullable │ Default │ ╞════════╪══════════════╪═══════════╪══════════╪═════════╡ │ id │ bigint │ │ not null │ │ │ d1 │ text │ │ │ │ │ fk1 │ bigint │ │ not null │ │ │ fk2 │ bigint │ │ not null │ │ │ v1 │ numeric(8,4) │ │ │ │ │ v2 │ integer │ │ │ │ │ d2 │ text │ │ │ │ └────────┴──────────────┴───────────┴──────────┴─────────┘ Indexes: "pk_pgbench_reporting" PRIMARY KEY, btree (id) "ix_pgbench_reporting_fk1" btree (fk1) "ix_pgbench_reporting_fk2" btree (fk2) Foreign-key constraints: "fk_pgbench_reporting_fk1" FOREIGN KEY (fk1) REFERENCES pgbench_lookup1(id) "fk_pgbench_reporting_fk2" FOREIGN KEY (fk2) REFERENCES pgbench_lookup2(id)
This structure is intended to simulate common types of tables seen often "in the wild" of production databases.
View for test queries
The initialization script above also created a view named
for the tests.
This handles the joins to use the foreign keys defined on the base table,
the added columns are aliased as
\d pgbench_view View "public.pgbench_view" ┌─────────┬──────────────┬───────────┬──────────┬─────────┐ │ Column │ Type │ Collation │ Nullable │ Default │ ╞═════════╪══════════════╪═══════════╪══════════╪═════════╡ │ id │ bigint │ │ │ │ │ d1 │ text │ │ │ │ │ fk1 │ bigint │ │ │ │ │ fk1data │ text │ │ │ │ │ fk2 │ bigint │ │ │ │ │ fk2data │ text │ │ │ │ │ v1 │ numeric(8,4) │ │ │ │ │ v2 │ integer │ │ │ │ │ d2 │ text │ │ │ │ └─────────┴──────────────┴───────────┴──────────┴─────────┘
I used three queries for this testing
pgbench allows you to weight these queries I decided to keep all weights
equal to start with.
The first query (tests/reporting1.sql)
calculates a few aggregates grouped by
SELECT fk1, fk1data, COUNT(*) AS cnt, AVG(v1) AS v1avg, MAX(v2) AS v2avg FROM pgbench_view GROUP BY fk1, fk1data ;
EXPLAIN ANALYZE with
max_parallel_workers_per_gather = 2 (default in Pg11) shows that Postgres does
decide to use Parallel for this query. This indicates our setting here
could help or hurt performance.
... Workers Planned: 2 Workers Launched: 2 ... Sort Method: quicksort Memory: 19kB Worker 0: Sort Method: quicksort Memory: 19kB Worker 1: Sort Method: quicksort Memory: 19kB ...
The TCP-B (sort of) tests do not benefit/hurt from parallel queries as of Pg11. (At least not on the Pi!)
Testing full speed
First I ran
pgbench for 1 hour with 10 clients and my three new queries.
This had the result of 5-6 TPS on nearly any configuration.
This was frankly rather boring.
pgbench -c 10 -j 2 -T 3600 -P 60 -s 2 \ -f tests/reporting1.sql \ -f tests/reporting2.sql \ -f tests/reporting3.sql \ bench_test
Note: These tests used non-app rated SD cards. See my post on SD cards for more details.
I want to pull back on the rate of execution to see what is reasonable to expect
from Postgres on a Pi for a single-user.
I don't expect this user to be hitting
F5 (execute query) as fast as machine-possible
either. We've already tried that, now lets see what happens when things are made
more realistic for this tiny hardware.
To do this, I need to define my quality expectations for performance.
Let's say I want to run one query once per second and I expect full results
in 500ms or less (e.g. latency, or lat).
This definition can now be translated to
pgbench using a couple new options.
-R 1 -r --latency-limit=500 to the
pgbench command should achieve these results.
-R 1 sets the target rate at 1 TPS and
--latency-limit=500 sets the performance threshold at 500ms.
Performance of your database is a quality metric. The trick for planning is to translate between user expectations and our testing methodology.
pgbench -c 1 -j 1 -T 3600 -P 30 -s 2 \ -R 1 -r --latency-limit=500 \ -f tests/reporting1.sql \ -f tests/reporting2.sql \ -f tests/reporting3.sql \ bench_test ... progress: 330.0 s, 0.8 tps, lat 216.944 ms stddev 144.121, lag 15.872 ms, 0 skipped progress: 360.0 s, 1.1 tps, lat 257.683 ms stddev 155.640, lag 45.778 ms, 1 skipped progress: 390.0 s, 1.2 tps, lat 208.859 ms stddev 130.061, lag 21.272 ms, 0 skipped progress: 420.0 s, 1.0 tps, lat 260.450 ms stddev 141.157, lag 45.121 ms, 1 skipped progress: 450.0 s, 1.2 tps, lat 253.322 ms stddev 171.986, lag 56.560 ms, 0 skipped ... number of transactions actually processed: 611 number of transactions skipped: 3 (0.489 %) number of transactions above the 500.0 ms latency limit: 16/611 (2.619 %) latency average = 237.988 ms latency stddev = 144.305 ms rate limit schedule lag: avg 32.037 (max 462.121) ms ...
Note: Setting clients (
-c) higher than than TPS rate limits (
-R) limits all clients to the defined rate. This probably only matters on tiny hardware!
pgbench output now provides information about "slow" and "skipped"
queries in the summary.
In this test case, 0.5% were skipped and 2.6% of the queries took longer than 0.5 seconds to execute.
number of transactions skipped: 3 (0.489 %) number of transactions above the 500.0 ms latency limit: 16/611 (2.619 %)
Skipped transactions are explained in the docs:
"If --latency-limit is used together with --rate, a transaction can lag behind so much that it is already over the latency limit when the previous transaction ends, because the latency is calculated from the scheduled start time. Such transactions are not sent to the server, but are skipped altogether and counted separately.""
Now for the results from the bulk of the testing.
All tests were ran for 1 hour (
-T 3600) and most of them were ran at least twice
on multiple Pis. The "# of Clients" (
N) is controlled by setting both clients (
and rate (
-R N) via
pgbench. For these purposes, this simulates each client attempting to achieve 1 TPS. The "Testing full speed" section above
found that these queries on this hardware will max out at 5-6 TPS so I tested
below and above those limits (1-8) for number of clients.
I didn't test 3 or 7 clients because I didn't expect them to provide meaningful insight.
The only PostgreSQL-specific parameter
controlled for in these tests was
max_parallel_workers_per_gather, all other
Postgres configurations are as documented in
my prior post with nearly all
configurations at their defaults.
The following chart and table show the percentage (%) of late queries. In other words,
how many queries took longer than the target 500ms execution time?
max_parallel_workers_per_gather = 0 these queries didn't perform as hoped more
than 1/3 of the time. The good news is, performance was
consistent up through 5 clients (read: predictable). Performance really starts to drop off with 6 or more clients; by 8 clients about 2/3 queries were slow.
max_parallel_workers_per_gather set at either 2 or 4 (the Pi has 4 cores),
and 1 or 2 clients, well under 10% of queries were slow. Performance was also
noticeably better with this setting at 4 clients than it was with the setting at 0.
Once the number of clients exceeds the number of cores, performance starts to drop off
for these queries.
At 6 clients, edging past the already known TPS limit for the hardware,
performance drops off a cliff with
max_parallel_workers_per_gather > 0. With 8 clients, nearly 90% of queries
The skipped transactions data shows similar results. Starting again
max_parallel_workers_per_gather = 0, roughly 15% of queries were skipped
when the number of clients was 6 or less.
With 8 clients this failure rate spiked up to more than 1/3 being
max_parallel_workers_per_gather set at either 2 or 4, the query skip rate
held at 1% or lower as long as the number of clients was lower than the number of cores.
I had two goals for this post, let's see how they worked out.
First, was to preform more realistic testing of PostgreSQL performance on the Pi for my workloads. I found the Raspberry Pi 3B is perfectly capable of good performance (as defined above) with analytical-ish queries on a database of modest size (200k rows) and a small (<= 4) number of concurrent users. Would I recommend this hardware for heavy-duty mission critical databases? No. (Why would you even consider that?) I don't use VirtualBox virtual machines on my laptop to power client databases either. But I do use them all the time for development, testing, and other non-critical use cases to enable the production systems. The Pi is no different here.
These results confirm my personal experience of using a Pi in this type of scenario.
Second, was to more thoroughly explore the thresholds
and limits of parallel query. Last year I did some initial testing of
parallel query performance on Pg10 and I wanted
closer look at that. The results in this post illustrate that in
some use cases parallel query can significantly help performance. That said, you need to
be careful with the number of simultaneous clients and the setting
Testing and monitoring are your friends. No results I (or anyone) can publish can ever tell you how your data and your workload will perform on Hardware X.
Need help with your PostgreSQL servers or databases? Contact us to start the conversation!
Published April 18, 2019
Last Updated July 24, 2019