Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our User Agreement and Privacy Policy.

Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our Privacy Policy and User Agreement for details.

3.
PostgreSQL 9.5, 9.6, ...
● many improvements
– many of them related to performance
– many quite large
● release notes are good overview, but ...
– many changes not mentioned explicitly
– often difficult to get an idea of the impact
● many talks about new features in general
– this talk is about changes affecting performance

29.
Aggregate functions
● some aggregates use the same state
– AVG, SUM, …
– we’re keeping it separate and updating it twice
– but only the final function is actually different
● so …
Share transition state between different
aggregates when possible.

30.
Aggregate functions
­­ table with 50M rows
CREATE TABLE test_aggregates AS
SELECT i AS a
FROM generate_series(1, 50.000.000) s(i);
­­ compute both SUM and AVG on a column
SELECT SUM(a), AVG(a) FROM test_aggregates;

32.
Checkpoints
● we need to write dirty buffers to disk regularly
– data written to page cache (no O_DIRECT)
– kernel responsible for actual write out
● until now, we simply walked shared buffers
– random order of buffers, causing random I/O
– 9.6 sorts the buffers first, to get sequential order
● until now, we only only did fsync at the end
– a lot of dirty data in page cache, latency spikes
– 9.6 allows continuous flushing (disabled by default)

38.
Freezing
● XIDs are 64-bit, but we only store the low 32 bits
– need to do “freeze” every ~2 billion transactions
– that means reading all the data (even unmodified parts)
– problem on large databases (time consuming)
– users often postpone until it’s too late (outage)
● PostgreSQL 9.6 introduces “freeze map”
– similar to “visibility map” (and stored in the same file)
– “all rows on page are frozen” - we can skip this 8kB page