Gregory Smith

#PostgreSQL engineer for @Snowflake @crunchydata, database and audio author. Family advocate for #MyalgicEncephalomyelitis #POTS #PwME #MEcfs patients.

[Gregory Smith @postgresperf]

Last PG17 CPU study! 32 core AMD 9950X joins Intel's 14900K in the 1M SELECT TPS club. 16 core MacBook M4 Max hits 317K, thermal limited. Well ventilated Mac Studio M4 Max does 456K. Maybe 32 core M3 Ultra could do 1M? TYVM @crunchydata for the MB M4; 9950X, Studio are mine.

[Gregory Smith @postgresperf]

@craigkerstiens @willking Been doing this too long, I read the code and can see the picture in my head. The (-2.2,-1.5) is a dead giveaway. I first did this in BASIC to a dot matrix printer in 1985, still have that here.

[Gregory Smith @postgresperf]

@mdonnino @sunsopeningband Severe PEM patients can't, that's the criteria problem. Their best day is a paced hour to eat and use the bathroom. 2 hour burst session? That means starving then 14 days of PEM 8+ pain. Their energy problems are serious, obvious. The easy remissions you're seeing don't happen.

[Gregory Smith @postgresperf]

@AbyssPearl It's tough, even on this post you only got a few responses before the ignorant "challenge yourself!" trolls showed up.

[Gregory Smith @postgresperf]

Nov 20 is our annual PostGIS day, check it out, and my state of PG18 report is over on LinkedIn. tl;dr Postgres 18 loads the whole OSM Planet set 3% faster than PG17 due to adding parallel GIN index builds, which are 3X as fast.
https://t.co/dG5y2m41dd

[Gregory Smith @postgresperf]

Ran into this on my benchmark data set! Canceled a query that had already run for two days, found this plan:

Nested Loop (cost=78,613,867,046.43 rows=20,718,100 width=1144)

Oopsie! Add missing index, 6 minutes:

Nested Loop (cost= 1,881,853,963.95 rows=495,950 width=1144)

[Crunchy Data @crunchydata]

Postgres performance best practice: index all foreign keys.

Primary keys are automatically indexed but if you reference a primary key in another table using a foreign key, that is not automatically indexed - and an index needs to be added manually.

[Gregory Smith @postgresperf]

NVIDIA's DGX Spark is based on Ubuntu 24.04, and the available package set include PostgreSQL 16.10. Just tried it out and the database worked fine on a quick spot check. pgbench tests rated initial performance as similar to M1 or M2 cores. Turing...err, Tuning time!

[Gregory Smith @postgresperf]

Gone Encyclopedia Brown and the mystery of why the Postgres server benched at 5Gbps over the 10G network. The physical x16 slot the NIC was plugged into, ha ha! Only runs at x1, so exactly 5Gbps. Only working 10G config is NIC in the GPU slot, against recommendation! Madness.

[Gregory Smith @postgresperf]

@ubuntu Great software engineers take on the hard jobs nobody wants, instead of just doing the easy, shiny work everyone else is chasing.

[Gregory Smith @postgresperf]

@BradenLockwood1 Core would need to merge a major new storage engine with features like columnar. We built an Iceberg storage engine to get good TPC-H/DS results. The gap is financial. Analytics code is valuable; releasing for free then funding an expensive core merge costs money.

[Gregory Smith @postgresperf]

@useless_priest I think of ME and OI a pair of bad feedback loops that are coupled together. In the example you're using, you might label that as a root cause the OI triggered first. Once you're out of the stable zone and into feedback squeal, you can't cleanly separate them anymore.

[Gregory Smith @postgresperf]

@sunsopeningband The thing about cherry-picking data is that people really love that home made cherry taste. Comprehensive data sets are all watered down and taste of p-values, yuck!

[Gregory Smith @postgresperf]

@DafoeWhitney Love how this picture lets me see your large tube headphone amps! I have a giant for the time amp from the Headroom brand I still use. This is me reviewing it then, and one of these days I am going to try out some of your headphones with it: https://t.co/jPx9zvpJWr

[Gregory Smith @postgresperf]

@sunsopeningband Now there's an ad campaign: "Naloxone: we could have saved Layne Staley"

[Gregory Smith @postgresperf]

Enjoyed this Crunchy history piece.

Database engineering feels contrarian, still Moving Slow & Fixing Things. I enjoy how PostgreSQL grinds feature and extension at a time until it's right. Crunchy embraced that and danced past all the fork pitfalls.
https://t.co/19zIBux2MB

[Gregory Smith @postgresperf]

@DafoeWhitney My early 90's audio included a lot of speaker testing with a Proton D1200, used to see that push 500W into 4 ohm Magnepan panels or big 8 ohm Klipsch designs. That VU user interface remains a marvel, and I wish we had something like it on everything.

[Gregory Smith @postgresperf]

Auto analogy time! Perl and Python are Swiss Army knife software tools, but those are just small embedded components to PG. PostgreSQL is more like a giant automotive shop toolbox where you just add more sections as you target additional car lines, like this Snap-On product:

[Craig Kerstiens @craigkerstiens]

I always thought I appreciated the unix philosophy of small sharp tools. Just now realized Postgres is not a small sharp tool it's a swiss army knife, now I'm contemplating all the rest of things I thought I believed in life.

[Gregory Smith @postgresperf]

Made my own version of the Godzilla Easter picture that's gone viral this year.