PostgreSQL Portability Tales

PostgreSQL Portability Tales

https://www.youtube.com/watch?v=17XYIJbkGz4
https://www.meetup.com/pl-PL/Warsaw-PostgreSQL-Users-Group/events/269906534/

This is an informal talk I gave to the Warsaw PostgreSQL User Group via video link. If I sounded a bit spaced out, that might be because it was 4:30am my time and I hadn't had anywhere near enough coffee!

Description: "PostgreSQL runs on many operating systems. I'm going to talk about a selection of interesting differences between them, and things we have done or could to in the future to make things better. Mostly focusing on Linux and FreeBSD, with some discussion of macOS and Windows, and some passing mentions of the other BSDs, illumos and some ancient commercial Unix history. Topics include memory, I/O, IPC and management."

Here are some links to more information about things discussed or other talks/blogs mentioned in the talk or the Q&A session:

* Work-in-progress patch to get posix_fadvise(POSIX_FADV_WILLNEED) to work on OpenZFS (for Linux and FreeBSD at least): https://github.com/openzfs/zfs/pull/9807

* Async and direct I/O have been researched quite a bit by Andres Freund who has spoken about it at a couple of conferences: https://anarazel.de/talks/2020-01-31-fosdem-aio/aio.pdf

* I mentioned the paper "On the Impact of Instruction Address Translation Overhead", about the performance impact of page size for executable code on PostgreSQL performance: https://www.cs.rochester.edu/u/xdong/ispass-19-final.pdf

* I've also heard anecdotes reporting similar benefits for text segments on huge pages on Linux, for PostgreSQL, Oracle and other software. The instructions for getting this working on Linux are at: https://github.com/libhugetlbfs/libhugetlbfs/blob/master/HOWTO

* Some more information about the differences in write-back error handling on different operating systems can be found here: https://wiki.postgresql.org/wiki/Fsync_Errors

* A description of ancient Berkeley POSTGRES's Lisp origins, and also the famous promise to move from processes to threads, as mentioned by Adam in the Q&A: https://dsf.berkeley.edu/papers/ERL-M90-34.pdf

* Some information about PostgreSQL on ZFS from Sean Chittenden, that came up in the Q&A: https://www.slideshare.net/SeanChittenden/postgresql-zfs-best-practices ; Sean also presented on Joyent's pg_prefaulter at pgcon: https://www.pgcon.org/2018/schedule/track/Case%20Studies/1204.en.html

* My attempt to get WAL prefetching into core PostgreSQL (work in progress): https://commitfest.postgresql.org/28/2410/

* A post mortem of an outage published by Joyent, that came up in the Q&A: https://www.joyent.com/blog/manta-postmortem-7-27-2015

* In the Q&A, Adam mentioned multithreading designs that involve event driven programming with one thread per core. I mentioned that one subproject involved in that problem is converting queries from a tree walker format that uses the C stack to represent state, to something more amenable to processing as 'coroutines', 'green threads' or 'continuation capture' (cf. the Scheme programming language). Andres Freund has been exploring that topic and has written about his designs and prototyping here: https://www.postgresql.org/message-id/flat/20180525033538.6ypfwcqcxce6zkjj%40alap3.anarazel.de

* As mentioned in the Q&A, some notes on some small projects to improve PostgreSQL on FreeBSD can be found here: https://wiki.postgresql.org/wiki/FreeBSD

* In the Q&A, Alicja mentioned that she'd seen some evidence that the traditional advice to turn Linux's THP off (which I repeated in this talk) might not be the whole story, and that she'd found cases here it helped a lot and had presented on that topic: https://www.postgresql.eu/events/pgconfeu2018/sessions/session/2125/slides/103/VMandFS_tuning.pdf

7b7e8e5a434cc7986bb95dcc523f59fa?s=128

Thomas Munro

April 16, 2020
Tweet