postgresperf
Joined 2 years ago
Karma
16
Submissions
5
Member since
Apr 16
PostgreSQL greybeard Greg Smith
View on Hacker NewsRecent submissions
Comment · 2 months ago
When I got early access to pgrust my first question was whether it had been tested for building read-only standby analytics questions. The problem has goals beyond that, but having a real analytics node available just by playing WAL data from your primary makes pgrust a low risk eval for your data. Just have to build confidence the results are connection, which is being applied by all the PG infrastructure regression tests the code has been trained on,
Comment · 2 months ago
Optimizing a multi-layer cache system is a Complex System task with no subset you can reduce to a simpler version of the problem. PG has a 3 layer cache--database, OS, storage--and when you combine that with concurrency and locality, you have a giant 5 dimensional space to map out. Building the right set of benchmarks for PG with guidance for the most important parts to navigate fro that space has kept me busy for 20 years now.
Comment · 2 months ago
The pgrust team asked me to look at their results on a review system, and I confirmed the ClickBench speedup there. Regular PostgreSQL is really terrible at some of these queries. Unfortunately fixing that is hard to do in core itself because columnar storage lives outside of the main tree, and some optimization problems only show up when layered on columnar.
Comment · 2 years ago
There's a whole talk about it we had in our conference: https://www.youtube.com/watch?v=TG28lRoailE
Short version is GIS indexes are notably smaller and build faster in PG15 than earlier versions. It's a major version to version PG improvement for these workloads.
Comment · 2 years ago
Contributor to the PG bulk loading docs you referenced here. Good survey of the techniques here. I've done a good bit of this trying to speed up loading the Open Street Map database. Presentation at https://www.youtube.com/watch?v=BCMnu7xay2Y for my last public update. Since then the advance of hardware, GIS improvements in PG15, and osm2pgsql adopting their middle-way-node-index-id-shift technique (makes the largest but rarely used index 1/32 the size) have gotten my times to load the planet set below 4 hours.
One suggestion aimed at the author here: some of your experiments are taking out WAL writing in a sort of indirect way, using pg_bulkload and COPY. There's one thing you could try that wasn't documented yet when my buddy Craig Ringer wrote the SO post you linked to: you can just turn off the WAL in the configuration. Yes, you will lose the tables in progress if there's a crash, and when things run for weeks those happen. With time scale data, it's not hard to structure the loading so you'll only lose the last chunk of work when that happens. WAL data isn't really necessary for bulk loading. Crash, clean up the right edge of the loaded data, start back up.
Here's the full set of postgresql.conf settings I run to disable the WAL and other overhead:
wal_level = minimal max_wal_senders = 0 synchronous_commit = off fsync = off full_page_writes = off autovacuum = off checkpoint_timeout = 60min
Finally, when loading in big chunks, to keep the vacuum work down I'd normally turn off autovac as above then issue periodic VACUUM FREEZE commands running behind the currently loading date partition. (Talking normal PG here) That skips some work of the intermediate step the database normally frets about where new transactions are written but not visible to everyone yet.