Schedule - PGConf.EU 2019
PostgreSQL's IO subsystem: Problems, Workarounds, Solutions
Date: 2019-10-16
Time: 15:40–16:30
Room: Washington
Level: Advanced
PostgreSQL's IO subsystem—while working well for a large fraction of installations—has a few problems:
- hard to predict latency, especially on databases that are significantly larger than the available memory
- IO throughput, especially with fast storage subsystems (e.g. NVMe SSDs), is not high enough
- backends perform too much write IO themselves, as the background writer does not work very well
- unnecessary random write IO is generated (bgwriter, backends)
- double buffering between kernel page cache and postgres' shared buffers
- all read IO is synchronous (from the OS page cache)
- large shared_buffers can cause problems when tables are very frequently dropped or truncated
- ...
After an introduction of how PostgreSQL's IO subsystem works, the above problems (and maybe some workarounds), the talk will go over current work to improve this situation. This work includes:
- new background writer (patch in a decent prototype shape)
- new buffer mapping implementation, enabling new optimizations (patch in early prototype stage, potential improvements include fast DROP/TRUNCATE, write coalescing, better readahead)
- direct IO
Slides
The following slides have been made available for this session: