11 ms·

PostgreSQL Performance on Raspberry Pi

188 points by nreilly 8y ago

FlyingSnake 8y ago
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 8y ago
  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 8y ago
    I really hope the RPi4 has eMMC or M.2
    - MR4D 8y ago
      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.
dijit 8y ago
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.
- Quarrelsome 8y ago
  > that's what PostgreSQL needs more than anything depends what the queries are. If they're all for exactly the same data then I/O is gonna be a one off. Even then dbs are usually relatively smart about minimising disk reads and trying to avoid them where possible.
- jpablo 8y ago
  sd card access doesn't go trougth the USB controller in any of the rpi models. You are confusing it with the Ethernet port which is connected trougth USB.
  - jsight 8y ago
    Does it matter? In practice, it doesn't seem like the sd card is ever faster than the Pi USB bus.
    - jng 8y ago
      I seem to recall the USB bus on the RPi3 is limited to 480Mb/s, the fastest SD cards in the market are around 150MB/s, and the SDcard port on the RPi3 is below 25Mb/s.
      - jsight 8y ago
        Yes, that makes sense. The SDCard port on the pi seems to be slower than the USB bus. I will say that I think your estimate for the USB bus on the pi is probably too high. In practice, I don't think it can do more than 250 or so.
- dsr_ 8y ago
  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.
cat199 8y ago
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 8y ago
  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 8y ago
    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.
Nux 8y ago
Would be interesting to see a pgsql vs mariadb Rpi benchmark.
- bloopernova 8y ago
  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.
contingencies 8y ago
workloads > RAM ≈ 0 ∴ benchmark = premature optimization # ;)
wybiral 8y ago
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.
- vbezhenar 8y ago
  That might be actually a good thing. Any disk will break. If disk breaks sooner, you'll have to have a good recover plan and not just hope that this RAID-1 will save you, because probability is not that high.
  - viraptor 8y ago
    It's more in annoying rather than good territory. Spam the generic cards with enough writes and you'll be able to corrupt many of them within days. If you do write-heavy things or expect reliability from your rpi, you pretty much have to use an external drive. And hopefully some log/journal based filesystem.
- mirceal 8y ago
  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 8y ago
    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 8y ago
      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 8y ago
Is the limit he found CPU bound? IO bound? I suspect he basically tested some random SD card's performance.
- Uberphallus 8y ago
  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 8y ago
    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 8y ago
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 8y ago
  How long have we had SD Cards for them to still be unreliable like this? This is unacceptable.
  - dleslie 8y ago
    Now you have good odds of purchasing a counterfeit. Doesn't seem to matter where you buy from, too. The consumer electronics market is a shit show.
    - sneakernets 8y ago
      This is exactly my fear. There is no quality control anymore.
  - AlotOfReading 8y ago
    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 8y ago
      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 8y ago
        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.
warmwaffles 8y ago
I've always wanted to make an arm based cluster for kicks and giggles. Just can't find a fun project to warrant it.
hobs 8y ago
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 8y ago
  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 8y ago
    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.
  - Quarrelsome 8y ago
    production isn't the issue, multi-user is the issue.
- steve_adams_86 8y ago
  I worked at a company that used sqlite on a fairly data-intensive app until it had around 2500 paying customers, with something like 600 of those customers being active, heavy users. Scaling and availability started to kneecap our customers left and right so we migrated to postgres. It was a world of difference. When customers had specials and sent piles of traffic to their stores, they'd stay online the whole time. Everyone slept better. As much as I disliked that specific use of sqlite, I learned a ton about how awesome it is and came to really love the software behind it. It was also super impressive that it took the company so far, both in terms of the efforts the devs made and sqlite itself.
rb808 8y ago
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 8y ago
  ODroids are a great alternative with much better performance and SATA for not much more money (~$55).
  - nurettin 8y ago
    And 2GB RAM, 2GHz CPU.
  - pheleven 8y ago
    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 8y ago
      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.
- dleslie 8y ago
  It's a shame that ongoing support for other SBCs is almost universally poor. Often you're stuck using older, unpatched kernels and broken video drivers. RPi has binary blobs, but at least they keep supporting boards with software updates
  - nfriedly 8y ago
    There is Armbian, which produces up-to-date Debian-based builds for various SBCs: https://www.armbian.com/ https://www.armbian.com/ But, I still agree with your point. I basically only buy Raspberry PI's now because the software experience has been terrible with everything else I've tried. (I only learned about Armbian recently, haven't actually tried it out yet.)
jxcl 8y ago
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@mark.mielke.cc https://www.postgresql.org/message-id/20070526145214.GA21290...
- vkaku 8y ago
  You'd be surprised to read about bitrot then. Well, storage is the one that causes maximum damage there, especially really old data.
  - jxcl 8y ago
    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 8y ago
      Well, one also needs periodic scrubbing. Without that, it rots!
- Phrodo_00 8y ago
  There's a lot more to worry about in the Raspberry PI, starting with SD storage
- Tepix 8y ago
  Do you have a laptop with ECC RAM then?
MuffinFlavored 8y ago
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 8y ago
  So the performance was lower on the server than the pi?
  - MuffinFlavored 8y ago
    No. The article claims about ~200 TPS. I was able to pull ~9k TPS.
  - RexM 8y ago
    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.
- blyat 8y ago
  More OVH numbers using your same pgbenches. I've got a $99/mo GAM1 - Intel i7-7700K NO OC - 4C/8T - 4.2GHz - 64GB - NO SoftRAID (using ESXi) 2x450GB NVMe running 3 VMs. Two VMs are using < 50 MHz, the other is using 30% of the CPU for an empty Minecraft server. With the Minecraft server on: progress: 60.0 s, 6956.5 tps, lat 1.437 ms stddev 1.485 With the Minecraft server off: progress: 60.0 s, 7275.5 tps, lat 1.374 ms stddev 1.975 Looks like not having SoftRaid is killing me. I wish I could test with it on so I can see the comparison of the E3 vs the 7700K, but I would need to re-image. For fun, a random $5 lowest tier Digital Ocean droplet running a TeamSpeak server (which is using about .07% CPU): progress: 60.0 s, 1507.3 tps, lat 6.631 ms stddev 5.809 And for even more fun, WSL running on my personal/WFH rig - i7-7820X @ 4.8GHz OC, 1TB 950 Pro NVME, 32GB RAM (I let this one run a bit longer as WSL has performance issues, was curious if I would see variance): progress: 60.0 s, 1172.4 tps, lat 8.516 ms stddev 6.017 progress: 120.0 s, 1271.3 tps, lat 7.863 ms stddev 4.817 progress: 180.0 s, 1274.0 tps, lat 7.849 ms stddev 4.943 progress: 240.0 s, 1266.1 tps, lat 7.896 ms stddev 4.875 progress: 300.0 s, 1239.2 tps, lat 8.069 ms stddev 5.211 progress: 360.0 s, 1213.1 tps, lat 8.242 ms stddev 5.447 Ouch! A $3k rig gets outperformed by a $5 DO box.
goombastic 8y ago
The amount of problems I have with the SD card on the raspberry pi is crazy. The thing is never stable.
- maccio92 8y ago
  Use USB
- JudgeWapner 8y ago
  Change out your SD cards with high performance ones (U 10 or whatever). Mine runs just fine for weeks.
  - bityard 8y ago
    Not just high performance ones, high _endurance_ ones.
antirez 8y ago
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.
Abishek_Muthian 8y ago
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.
ggm 8y ago
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.
Tepix 8y ago
I wonder how the Asus Tinkerboard and the Pine64 RockPro 64 (RK3399) compare

188 points by nreilly 8y ago

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 8y ago
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 8y ago
  I really hope the RPi4 has eMMC or M.2
  - MR4D 8y ago
    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.

dijit 8y ago

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.

Quarrelsome 8y ago
> that's what PostgreSQL needs more than anything depends what the queries are. If they're all for exactly the same data then I/O is gonna be a one off. Even then dbs are usually relatively smart about minimising disk reads and trying to avoid them where possible.
jpablo 8y ago
sd card access doesn't go trougth the USB controller in any of the rpi models. You are confusing it with the Ethernet port which is connected trougth USB.
- jsight 8y ago
  Does it matter? In practice, it doesn't seem like the sd card is ever faster than the Pi USB bus.
  - jng 8y ago
    I seem to recall the USB bus on the RPi3 is limited to 480Mb/s, the fastest SD cards in the market are around 150MB/s, and the SDcard port on the RPi3 is below 25Mb/s.
    - jsight 8y ago
      Yes, that makes sense. The SDCard port on the pi seems to be slower than the USB bus. I will say that I think your estimate for the USB bus on the pi is probably too high. In practice, I don't think it can do more than 250 or so.
dsr_ 8y ago
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.

cat199 8y ago

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 8y ago
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 8y ago
  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.

Nux 8y ago

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

bloopernova 8y ago
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.

contingencies 8y ago

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

wybiral 8y ago

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.

vbezhenar 8y ago
That might be actually a good thing. Any disk will break. If disk breaks sooner, you'll have to have a good recover plan and not just hope that this RAID-1 will save you, because probability is not that high.
- viraptor 8y ago
  It's more in annoying rather than good territory. Spam the generic cards with enough writes and you'll be able to corrupt many of them within days. If you do write-heavy things or expect reliability from your rpi, you pretty much have to use an external drive. And hopefully some log/journal based filesystem.
mirceal 8y ago
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 8y ago
  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 8y ago
    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 8y ago

Is the limit he found CPU bound? IO bound? I suspect he basically tested some random SD card's performance.

Uberphallus 8y ago
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 8y ago
  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 8y ago

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 8y ago
How long have we had SD Cards for them to still be unreliable like this? This is unacceptable.
- dleslie 8y ago
  Now you have good odds of purchasing a counterfeit. Doesn't seem to matter where you buy from, too. The consumer electronics market is a shit show.
  - sneakernets 8y ago
    This is exactly my fear. There is no quality control anymore.
- AlotOfReading 8y ago
  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 8y ago
    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 8y ago
      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.

warmwaffles 8y ago

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

hobs 8y ago

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 8y ago
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 8y ago
  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.
- Quarrelsome 8y ago
  production isn't the issue, multi-user is the issue.
steve_adams_86 8y ago
I worked at a company that used sqlite on a fairly data-intensive app until it had around 2500 paying customers, with something like 600 of those customers being active, heavy users. Scaling and availability started to kneecap our customers left and right so we migrated to postgres. It was a world of difference. When customers had specials and sent piles of traffic to their stores, they'd stay online the whole time. Everyone slept better. As much as I disliked that specific use of sqlite, I learned a ton about how awesome it is and came to really love the software behind it. It was also super impressive that it took the company so far, both in terms of the efforts the devs made and sqlite itself.

rb808 8y ago

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 8y ago
ODroids are a great alternative with much better performance and SATA for not much more money (~$55).
- nurettin 8y ago
  And 2GB RAM, 2GHz CPU.
- pheleven 8y ago
  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 8y ago
    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.
dleslie 8y ago
It's a shame that ongoing support for other SBCs is almost universally poor. Often you're stuck using older, unpatched kernels and broken video drivers. RPi has binary blobs, but at least they keep supporting boards with software updates
- nfriedly 8y ago
  There is Armbian, which produces up-to-date Debian-based builds for various SBCs: https://www.armbian.com/ https://www.armbian.com/ But, I still agree with your point. I basically only buy Raspberry PI's now because the software experience has been terrible with everything else I've tried. (I only learned about Armbian recently, haven't actually tried it out yet.)

jxcl 8y ago

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@mark.mielke.cc https://www.postgresql.org/message-id/20070526145214.GA21290...

vkaku 8y ago
You'd be surprised to read about bitrot then. Well, storage is the one that causes maximum damage there, especially really old data.
- jxcl 8y ago
  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 8y ago
    Well, one also needs periodic scrubbing. Without that, it rots!
Phrodo_00 8y ago
There's a lot more to worry about in the Raspberry PI, starting with SD storage
Tepix 8y ago
Do you have a laptop with ECC RAM then?

MuffinFlavored 8y ago

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 8y ago
So the performance was lower on the server than the pi?
- MuffinFlavored 8y ago
  No. The article claims about ~200 TPS. I was able to pull ~9k TPS.
- RexM 8y ago
  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.
blyat 8y ago
More OVH numbers using your same pgbenches. I've got a $99/mo GAM1 - Intel i7-7700K NO OC - 4C/8T - 4.2GHz - 64GB - NO SoftRAID (using ESXi) 2x450GB NVMe running 3 VMs. Two VMs are using < 50 MHz, the other is using 30% of the CPU for an empty Minecraft server. With the Minecraft server on: progress: 60.0 s, 6956.5 tps, lat 1.437 ms stddev 1.485 With the Minecraft server off: progress: 60.0 s, 7275.5 tps, lat 1.374 ms stddev 1.975 Looks like not having SoftRaid is killing me. I wish I could test with it on so I can see the comparison of the E3 vs the 7700K, but I would need to re-image. For fun, a random $5 lowest tier Digital Ocean droplet running a TeamSpeak server (which is using about .07% CPU): progress: 60.0 s, 1507.3 tps, lat 6.631 ms stddev 5.809 And for even more fun, WSL running on my personal/WFH rig - i7-7820X @ 4.8GHz OC, 1TB 950 Pro NVME, 32GB RAM (I let this one run a bit longer as WSL has performance issues, was curious if I would see variance): progress: 60.0 s, 1172.4 tps, lat 8.516 ms stddev 6.017 progress: 120.0 s, 1271.3 tps, lat 7.863 ms stddev 4.817 progress: 180.0 s, 1274.0 tps, lat 7.849 ms stddev 4.943 progress: 240.0 s, 1266.1 tps, lat 7.896 ms stddev 4.875 progress: 300.0 s, 1239.2 tps, lat 8.069 ms stddev 5.211 progress: 360.0 s, 1213.1 tps, lat 8.242 ms stddev 5.447 Ouch! A $3k rig gets outperformed by a $5 DO box.

goombastic 8y ago

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

maccio92 8y ago
Use USB
JudgeWapner 8y ago
Change out your SD cards with high performance ones (U 10 or whatever). Mine runs just fine for weeks.
- bityard 8y ago
  Not just high performance ones, high _endurance_ ones.

antirez 8y ago

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.

Abishek_Muthian 8y ago

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.

ggm 8y ago

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.

Tepix 8y ago

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