PostgreSQL's IO subsystem: Problems, Workarounds, Solutions

Date: 2019-09-13
Time: 15:30 - 16:20
Room: Salon 17-18
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


The following slides have been made available for this session:


Andres Freund