
PostgreSQL Performance on Raspberry Pi - nreilly
https://blog.rustprooflabs.com/2019/04/postgrseql-pgbench-raspberry-pi
======
FlyingSnake
I use postgres on my raspberry Pi 3B as my development server (along with
PostgREST and Nginx) and it has served me faithfully for more than a year.
Many people are surprised to see the setup and how fast it is to spin up a
backend server. The response time is also pretty good and it's amazing to see
such a tiny machine pull off such a feat.

~~~
atomi
I run Postgres for Miniflux, both on Raspberry Pi3. The Postgres images on
Docker Hub officially support armv7. It could not be any easier. Of course,
make sure to back up often. As others have said, SD cards can fail.

~~~
dleslie
I really hope the RPi4 has eMMC or M.2

~~~
MR4D
YES! And also move entirely to USB-C for all connectors.

Even if they made this another $10-15 more expensive, and treated it like a
hi-end version, I’d bet they could never make enough of them.

------
hobs
I love postrgres, and obviously this is just a test, but for anything where I
would use a pi sqlite is insanely good.

I use sqlite for full fledged websites with millions of rows with no issue,
fast, easy to deploy, easy to move.

~~~
freedomben
Sqlite is amazing performant and scalable, as long as you don't need high
availability or horizontal scaling. While I don't recommend using sqlite in
production, I have done it before and had a similar experience. Millions of
rows with excellent performance. Just hope you never have to scale beyond what
a single machine can handle tho :-)

~~~
hobs
Agreed, I use SQL Server in my day job and always reached for PG when doing
OSS stuff, was just blown away how well SQLite managed to hold up.

Generally sharding/HA/DR are difficult problems with any db tech, so yeah, you
are definitely right there.

------
rb808
Hopefully nextgen raspberry pi will take sata or some other storage interface.
I think SD cards are the main problem with the platform right now.

~~~
driverdan
ODroids are a great alternative with much better performance and SATA for not
much more money (~$55).

~~~
pheleven
And non-unique MAC addresses and lower stability, at least when we tried to
use them a few years ago. Bought 3 and they all had the same MAC.

~~~
eropple
I've run into the MAC problem, but found it easy enough to deal with via my
standard home Chef configs. I can't echo the stability issues, though; my two
have been ticking away happily for a couple years.

------
MuffinFlavored
For comparison, I ran this on a $80/mo OVH dedicated server (SP-32 Server -
E3-1270v6 - 32GB - SoftRaid 2x450GB SSD NVMe)

    
    
      $ sudo su postgres
      $ createdb bench_test
      $ pgbench -i -s 10 bench_test
      $ pgbench -c 10 -j 2 -T 3600 -P 60 bench_test 
      starting vacuum...end.
      progress: 60.0 s, 9192.5 tps, lat 1.088 ms stddev 0.924

~~~
elamje
So the performance was lower on the server than the pi?

~~~
RexM
Not sure how you're seeing that.

From the article it says the incremental reporting (every 60 seconds) were:

> progress: 540.0 s, 171.8 tps, lat 55.105 ms stddev 946.851

> progress: 600.0 s, 24.6 tps, lat 435.693 ms stddev 2945.727

> progress: 660.0 s, 405.8 tps, lat 24.108 ms stddev 134.692

So they were getting between 24.6 and 405.8 transactions per second.

On the server, he's seeing:

> progress: 60.0 s, 9192.5 tps, lat 1.088 ms stddev 0.924

So the server is doing 9192.5 transactions per second. The test wasn't run as
long, but the server is showing much lower latency and standard deviation, as
well.

------
antirez
Redis is quite fast on the Pi3, completely another story compared to the
original Pi. With AOF enabled and fsync policy every second, without using
pipelining it does 28k ops/sec. With pipelining it reaches 80k easily. Not bad
for a hardware that is quite cheap and limited. About the problems with the SD
card, in the specific case of Redis there are mitigations _if_ it is possible
to give up on durability. One could use snapshotting and configure a delay
that will not trash the SD too much. I think PostgreSQL does a lot of random
access in the on-disk btree, but other systems using some log structured
storage in append only should create less problems in theory.

------
moreentropy
Is the limit he found CPU bound? IO bound?

I suspect he basically tested some random SD card's performance.

~~~
Uberphallus
That was my first thought. Especially when the article says

> Starting with Postgres 10, the default is to give 2 cores to parallel
> processing. Switching max_parallel_workers_per_gather between 2 and 0 had
> nearly zero impact on that Pi, either for or against.

With that extra info I'm 99% sure it's I/O limited.

~~~
dleslie
It's almost certainly IO. IIRC, Sdcard write speed on the RPi is known to be
slow. You can improve it with superior cards, but the USB interface generally
remains the path to fastest disk reads and writes.

------
dcbadacd
Please for the love of god, do not use Postgres on a SD card, you'll corrupt
it quickly, even spinning rust over USB2.0 is faster and more reliable. That's
my experience with it at least.

~~~
sneakernets
How long have we had SD Cards for them to still be unreliable like this? This
is unacceptable.

~~~
AlotOfReading
You can have high capacity, low cost, or reliability. Pick 2.

Consumer cards choose capacity and cost, because that's what sells. Industrial
SD cards pick capacity and reliability, but cost at least 4x more than similar
consumer cards due to inherent tradeoffs in the hardware/firmware design.

~~~
dpedu
Are they though? I looked on Amazon; the price difference between a 'regular'
32GB microsd and a 'high endurance' card of the same capacity is $3 (7.99 vs
10.99).

~~~
AlotOfReading
Consumer high endurance cards are still MLC or TLC. They're better than
regular consumer SD cards, but still not what you'd consider ideal
reliability. Industrial cards are better QC'd and use more reliable (but less
dense) SLC, as well as various smaller changes to operate reliably in extreme
conditions. I'm sure some of it is industrial product markup as well, but the
technology is fundamentally more expensive.

------
wybiral
You better have some good redundancy on those SD cards because I've run Pi
clusters before and most of those cards go corrupt pretty quickly with any
frequent number of writes.

~~~
mirceal
after boot, the sd cards for my pis go into readonly :) checkout cattlepi.com
on how this is done. i’ve had good luck with the lifetime of the cards, but
it’s also true I treat the Pis as disposable.

~~~
moreentropy
I started building "real" firmware for my raspi appliances using buildroot.
That makes it super easy to build images that completely run from initramfs
without a single write on the sd card (and are fully booted in a few seconds
including userland).

Only works if I don't need any local persistence but makes the Pi just work
super reliably. Upgrading is as easy as swapping the Linux Kernel image on the
fat32 and rebooting.

~~~
mirceal
cattlepi has the advantage of being able to essentially build and distribute
images over the network + full raspbian.

on boot it self-updates if needed, builds on overlay filesystem w/ the
squashed image as the bottom ro layer + tmpfs as top rw layer.

~~~
moreentropy
Thanks, cattlepi looks super interesting!

------
jxcl
I'm wary of running anything with data I care about on a device without ECC
RAM.

From the docs:

> PostgreSQL does not protect against correctable memory errors and it is
> assumed you will operate using RAM that uses industry standard Error
> Correcting Codes (ECC) or better protection.

[https://www.postgresql.org/docs/11/wal-
reliability.html](https://www.postgresql.org/docs/11/wal-reliability.html)

Re: IS ECC RAM really needed?

[https://www.postgresql.org/message-
id/20070526145214.GA21290...](https://www.postgresql.org/message-
id/20070526145214.GA21290@mark.mielke.cc)

~~~
vkaku
You'd be surprised to read about bitrot then.

Well, storage is the one that causes maximum damage there, especially really
old data.

~~~
jxcl
That may also be a concern, but there are software workarounds. I would also
not run Postgres on a non-checksummed filesystem or without data checksums
enabled in the postgres cluster.

~~~
vkaku
Well, one also needs periodic scrubbing. Without that, it rots!

------
Abishek_Muthian
If the storage is handled properly, RPi can do wonders.

I've used RPi1 as a git sever in our company for 5 years with encrypted USB
flash drive as storage.

We must have committed at-least a million lines of code into it & we were
using it every day.

Except for changing some memory related flags in git, I didn't change much
w.r.t to git.

I had another encrypted USB flash drive to which the files were backed up,
interestingly this flash drive failed but the primary drive never died till
date.

The git server is running 24*7 on RPi1 for past 5 years.

------
cat199
cool article!

It would be great to know the SDCard speed and maybe see some comparisons
based on the SDCard type since the speeds vary quite a bit

see also:
[https://en.wikipedia.org/wiki/SD_card#Speed](https://en.wikipedia.org/wiki/SD_card#Speed)

~~~
jng
I believe Raspberry Pi's SDCard port is limited to a bit below 25 MB/s, so
that's gonna be the actual limit very often.

~~~
megous
There's also IOPS for random 4k block access, which is usually way below
25MB/s. Typically around 2000/500 r/w IOPS for regular A1 cards. With
expensive cards you can get to 2500-3000 range.

Read queries will be cached in RAM and fast (for <1GB database), and write
queries will be limited to 500 IOPS, and heavily dependent on how you use
transactions and write queries in your database.

Also disabling statistics collecting is the first thing I do when putting a
postgresql db on an sd card. Because that causes a lot! of writes.

------
goombastic
The amount of problems I have with the SD card on the raspberry pi is crazy.
The thing is never stable.

~~~
JudgeWapner
Change out your SD cards with high performance ones (U 10 or whatever). Mine
runs just fine for weeks.

~~~
bityard
Not just high performance ones, high _endurance_ ones.

------
ggm
They need to re-test on the grid using different PI for each version. And then
re-test using same pi, different SD card.

Beecause it is clear from the bad SD card comment that the SD card and PI can
influence the speed of the tests.

------
Nux
Would be interesting to see a pgsql vs mariadb Rpi benchmark.

~~~
bloopernova
Definitely! The author of the article mentions how lower power hardware
doesn't let you ignore database anti-patterns quite so much, so I'd like to
see comparisons of tuning the 2 different databases.

It's all too easy to dismiss it as an old school engineer being curmudgeonly
with a desire to make everyone develop on low-powered hardware. I think
there's definite value to telling people to work out the kinks and bottlenecks
of their code on a raspberry pi. Especially since those lessons might
translate to highly-performing k8s/containers/clusters on lower-power
hardware.

------
warmwaffles
I've always wanted to make an arm based cluster for kicks and giggles. Just
can't find a fun project to warrant it.

------
Tepix
I wonder how the Asus Tinkerboard and the Pine64 RockPro 64 (RK3399) compare

------
contingencies
workloads > RAM ≈ 0 ∴ benchmark = premature optimization # ;)

------
dijit
I only read the title; but I'm expecting performance generally to be abysmal.

I/O is quite poor on rPIs and that's what PostgreSQL needs more than anything.
More than memory or CPU for sure.

The best thing you can ever do for your database is (in this order):

1) Have enough ram to fit your entire dataset (mostly due to joins)

2) Have the best raid card money can buy.

\--

An SD card is shocking enough but on the rPI it's going over a USB bridge
which takes CPU cycles too!

Still probably performs better than postgresql on WSL; since the I/O is the
major bottleneck there too.

~~~
dsr_
For comparison's sake, I ran exactly the test described in the blog post on my
house server, which is an AMD FX4130 with 32 GB RAM. The database was stored
on SATA SSDs managed by ZFS. Lots of other things were going on at the same
time.

pgbench -c 10 -j 2 -T 600 -P 60 bench_test

starting vacuum...end. progress: 60.0 s, 5373.1 tps, lat 1.860 ms stddev 0.709
progress: 120.0 s, 5277.3 tps, lat 1.894 ms stddev 1.383 progress: 180.0 s,
5323.4 tps, lat 1.878 ms stddev 0.733 progress: 240.0 s, 4606.6 tps, lat 2.170
ms stddev 1.508 progress: 300.0 s, 4122.0 tps, lat 2.425 ms stddev 1.517
progress: 360.0 s, 4062.0 tps, lat 2.461 ms stddev 1.532 progress: 420.0 s,
3957.3 tps, lat 2.526 ms stddev 1.598 progress: 480.0 s, 3939.6 tps, lat 2.537
ms stddev 1.599 progress: 540.0 s, 3906.5 tps, lat 2.559 ms stddev 1.627
progress: 600.0 s, 3808.6 tps, lat 2.624 ms stddev 1.747

transaction type: <builtin: TPC-B (sort of)>

scaling factor: 10

query mode: simple

number of clients: 10

number of threads: 2

duration: 600 s

number of transactions actually processed: 2662601

latency average = 2.252 ms

latency stddev = 1.435 ms

tps = 4437.570722 (including connections establishing)

tps = 4437.599312 (excluding connections establishing)

So you get roughly an 11x performance improvement by running on a 7 year old
desktop processor and rather cheap SSDs. On the other hand, if we say that the
Pi setup cost $50 and we estimate the current value of my server at $500,
performance/price is basically linear.

~~~
bloopernova
Do you know roughly how much power your server draws? Like you said, the
cost/performance may be linear but I wonder what happens when you add in power
to that equation. Idle speculation, to be sure, but it would be fascinating to
see what amount of solar panels plus wind power plus batteries would be needed
to power each system.

~~~
dsr_
Oh, loads of power. The CPU is spec'd for 125W and would be completely
outclassed by a 65W Ryzen 5 1600 (150% cores, 300% threads, each thread
probably 2x performance) and the rest of the system likely draws another 100W
or so. It's irritatingly reliable and my house server needs really don't
encompass a lot of CPU power, so replacing it hasn't been a priority.

~~~
bloopernova
> irritatingly reliable

This made me laugh. I have an AMD Sempron 3850 cpu+gpu sitting in a small case
under the TV that has been chugging along for 5 years now. The somewhat recent
addition of a cheap SSD has increased its usability even more. As much as I'd
like to replace it with something more up to date, I can't justify it while
it's still working fine.

It's funny to think of what is "good enough" for a lot of needs.

