Latest posts from Greg Smith

The OpenStreetMap (OSM) database builds almost 750GB of location data from a single file download. OSM notoriously takes a full day to run. A fresh open street map load involves both a massive write process and large index builds. It is a great performance stress-test bulk load for any Postgres system. I use it to stress the latest PostgreSQL versions and state-of-the-art hardware. The stress test validates new tuning tricks and identifies performance regressions.

Two years ago, I presented (video

By default Linux uses a controversial (for databases) memory extension feature called overcommit. How that interacts with PostgreSQL is covered in the Managing Kernel Resources

Some people are obsessed with sports or cars. I follow computer hardware. The PC industry has overclocking instead of nitrous, plexi cases instead of chrome, and RGB lighting as its spinning wheels.

The core challenge I enjoy is cascading small improvements to see if I can move a bottleneck. The individual improvements are often just a few percent. Percentage gains can compound as you chain them together.

Today I'm changing the memory speed on my main test system, going from 2133MHz to 3200MHz, and measuring how that impacts PostgreSQL SELECT results. I'm seeing a 3% gain on this server, but as always with databases that's only on a narrow set of in-memory use cases. Preview:

Why more benchmarks?

This week Apple started delivering Macs using their own Apple Silicon chips, starting with a Mac SOC named the M1. M1 uses the ARM instruction set and claims some amazing acceleration for media workloads. I wanted to know how it would do running PostgreSQL, an app that's been running on various ARM systems for years. The results are great!

The OSS community around the homebrew project already qualified their PostgreSQL package as working on M1, and with some recompiling work that all worked as expected:

$ /opt/homebrew/bin/psql -c "select version()"
PostgreSQL 13.0 on arm-apple-darwin20.1.0, compiled by
Apple clang version 12.0.0 (clang-1200.0.32.28), 64-bit

Apple's Intel-based laptops are very popular among developers, and that's as true of people who work on PostgreSQL as other groups. Tomorrow, the first shipping Apple laptops running on ARM CPUs instead of Intel are expected. That is likely to include at least a 13" MacBook Pro. I decided to prepare for that with a survey of PostgreSQL performance on my small herd of Apple laptops. Mine are all the 15" or newer 16" models.

Crunchy Data has already started digging into PostgreSQL on ARM performance as part of Crunchy Bridge

PostgreSQL 13 was released last week. I'm excited about this one, as the more mature partitioning plus logical replication features allow some long-requested deployment architectures. I ran 13 through my usual 144 test quick spin to see if everything was working as expected. Mainly boring stuff, but I was pleased to see that with the simple 128 client/4X RAM benchmark workload, Postgres 13 is driving 1.6GB/s of random read traffic requests to my PCI-e 4.0 NVM-e SSD. It keeps up with a whole RAID volume of the old SATA SSDs! (I checked)

Hardware is standard enthusiast stuff, AMD Ryzen 9 3950X with 32 cores running clients on the server itself, delivering 140K reads/s. Motherboard is a Asus Pro WS X570-ACE. The SSD is Microcenter's Inland brand, the "Performance 2TB", using the popular Phison PCIe 4.0 controller that legit delivers 5GB/s sequential reads. (I checked)

Upgrading to PG13 on Ubuntu/Debian