From d125d25790901683d5ad16bfc96e9de4ccc9a581 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 16 Aug 2016 23:04:50 -0400 Subject: [PATCH] docs: my third pass over the 9.6 release notes Backpatch-through: 9.6 --- doc/src/sgml/release-9.6.sgml | 628 ++++++++++++++++++---------------- 1 file changed, 332 insertions(+), 296 deletions(-) diff --git a/doc/src/sgml/release-9.6.sgml b/doc/src/sgml/release-9.6.sgml index 9003b1f6e4..8d7356e27f 100644 --- a/doc/src/sgml/release-9.6.sgml +++ b/doc/src/sgml/release-9.6.sgml @@ -401,7 +401,7 @@ This commit is also listed under libpq and psql Allow GIN index builds to make effective use of - settings larger than 1GB (Robert Abraham, Teodor Sigaev) + settings larger than 1 GB (Robert Abraham, Teodor Sigaev) @@ -467,55 +467,130 @@ This commit is also listed under libpq and psql - General Performance + Sorting - Avoid re-vacuuming pages containing only frozen tuples (Masahiko - Sawada, Robert Haas, Andres Freund) + Improve sorting performance by using quicksort, not replacement + selection sort, when performing external sort steps (Peter + Geoghegan) - Formerly, anti-wraparound vacuum had to visit every page of - a table, even pages where there was nothing to do. Now, pages - containing only already-frozen tuples are identified in the table's - visibility map, and can be skipped by vacuum even when doing - transaction wraparound prevention. This should greatly reduce the - cost of maintaining large tables containing mostly-unchanged data. + The new approach makes better use of the CPU cache + for typical cache sizes and data volumes. Where necessary, + the behavior can be adjusted via the new configuration parameter + . + + + - If necessary, vacuum can be forced to process all-frozen - pages using the new DISABLE_PAGE_SKIPPING option. - Normally, this should never be needed but it might help in - recovering from visibility-map corruption. + Speed up text sorts where the same string occurs multiple times + (Peter Geoghegan) - Avoid useless heap-truncation attempts during VACUUM - (Jeff Janes, Tom Lane) + Speed up sorting of uuid, bytea, and + char(n) fields by using abbreviated keys + (Peter Geoghegan) - This change avoids taking an exclusive table lock in some cases - where no truncation is possible. The main benefit comes from - avoiding unnecessary query cancellations on standby servers. + Support for abbreviated keys has also been + added to the non-default operator classes text_pattern_ops, + varchar_pattern_ops, and + bpchar_pattern_ops. Processing of ordered-set + aggregates can also now exploit abbreviated keys. + + + + + + + Speed up CREATE INDEX CONCURRENTLY by treating + TIDs as 64-bit integers during sorting (Peter + Geoghegan) + + + + + + + + + Locking + + + + + + + Reduce contention for the ProcArrayLock (Amit Kapila, + Robert Haas) + + + + + + + Improve performance by moving buffer content locks into the buffer + descriptors (Andres Freund, Simon Riggs) + + + + + + + Replace shared-buffer header spinlocks with atomic operations to + improve scalability (Alexander Korotkov, Andres Freund) + + + + + + + Use atomic operations, rather than a spinlock, to protect an + LWLock's wait queue (Andres Freund) + + + + + + + Partition the shared hash table freelist to reduce contention on + multi-CPU-socket servers (Aleksander Alekseev) @@ -531,111 +606,138 @@ This commit is also listed under libpq and psql This change avoids substantial replication delays that sometimes - occurre while replaying such operations. + occurred while replaying such operations. + + + + + + Optimizer Statistics + + + - Avoid computing GROUP BY columns if they are - functionally dependent on other columns (David Rowley) + Improve ANALYZE's estimates for columns with many nulls + (Tomas Vondra, Alex Shulgin) - If a GROUP BY clause includes all columns of a - non-deferred primary key, as well as other columns of the same - table, those other columns are redundant and can be dropped - from the grouping. This saves computation in many common cases. + Previously ANALYZE tended to underestimate the number + of non-NULL distinct values in a column with many + NULLs, and was also inaccurate in computing the + most-common values. - When appropriate, postpone evaluation of SELECT - output expressions until after an ORDER BY sort - (Konstantin Knizhnik) - - - - This change ensures that volatile or expensive functions in the - output list are executed in the order suggested by ORDER - BY, and that they are not evaluated more times than required - when there is a LIMIT clause. Previously, these - properties held if the ordering was performed by an index scan or - pre-merge-join sort, but not if it was performed by a top-level - sort. + Improve planner's estimate of the number of distinct values in + a query result (Tomas Vondra) - Where feasible, trigger kernel writeback after a configurable - number of writes, to prevent accumulation of dirty data in kernel - disk buffers (Fabien Coelho, Andres Freund) + Use foreign key relationships to infer selectivity for join + predicates (Tomas Vondra, David Rowley) - PostgreSQL writes data to the kernel's disk cache, - from where it will be flushed to physical storage in due time. - Many operating systems are not smart about managing this and allow - large amounts of dirty data to accumulate before deciding to flush - it all at once, leading to long delays for new I/O requests. - This change attempts to alleviate this problem by explicitly - requesting data flushes after a configurable interval. + If a table t has a foreign key restriction, say + (a,b) REFERENCES r (x,y), then a WHERE + condition such as t.a = r.x AND t.b = r.y cannot + select more than one r row per t row. + The planner formerly considered AND conditions + to be independent and would often drastically misestimate + selectivity as a result. Now it compares the WHERE + conditions to applicable foreign key constraints and produces + better estimates. + + + + + + + + <command>VACUUM</> + + + + - On Linux, sync_file_range() is used for this purpose, - and the feature is on by default on Linux because that function has few - downsides. This sync capability is also available on other platforms - that have msync() or posix_fadvise(), - but those interfaces have some undesirable side-effects so the - feature is disabled by default on non-Linux platforms. + Avoid re-vacuuming pages containing only frozen tuples (Masahiko + Sawada, Robert Haas, Andres Freund) - The new configuration parameters , , , and control this behavior. + Formerly, anti-wraparound vacuum had to visit every page of + a table, even pages where there was nothing to do. Now, pages + containing only already-frozen tuples are identified in the table's + visibility map, and can be skipped by vacuum even when doing + transaction wraparound prevention. This should greatly reduce the + cost of maintaining large tables containing mostly-unchanged data. + + + + If necessary, vacuum can be forced to process all-frozen + pages using the new DISABLE_PAGE_SKIPPING option. + Normally, this should never be needed but it might help in + recovering from visibility-map corruption. - Perform checkpoint writes in sorted order (Fabien Coelho, - Andres Freund) + Avoid useless heap-truncation attempts during VACUUM + (Jeff Janes, Tom Lane) - Previously, checkpoints wrote out dirty pages in whatever order - they happen to appear in shared buffers, which usually is nearly - random. That performs poorly, especially on rotating media. - This change causes checkpoint-driven writes to be done in order - by file and block number, and to be balanced across tablespaces. + This change avoids taking an exclusive table lock in some cases + where no truncation is possible. The main benefit comes from + avoiding unnecessary query cancellations on standby servers. + + + + + + General Performance + + + + + Avoid computing GROUP BY columns if they are + functionally dependent on other columns (David Rowley) + + + + If a GROUP BY clause includes all columns of a + non-deferred primary key, as well as other columns of the same + table, those other columns are redundant and can be dropped + from the grouping. This saves computation in many common cases. + + + + + + + When appropriate, postpone evaluation of SELECT + output expressions until after an ORDER BY sort + (Konstantin Knizhnik) + + + + This change ensures that volatile or expensive functions in the + output list are executed in the order suggested by ORDER + BY, and that they are not evaluated more times than required + when there is a LIMIT clause. Previously, these + properties held if the ordering was performed by an index scan or + pre-merge-join sort, but not if it was performed by a top-level + sort. + + + + + @@ -687,25 +828,63 @@ This commit is also listed under libpq and psql - Use foreign key relationships to infer selectivity for join - predicates (Tomas Vondra, David Rowley) + Perform checkpoint writes in sorted order (Fabien Coelho, + Andres Freund) - If a table t has a foreign key restriction, say - (a,b) REFERENCES r (x,y), then a WHERE - condition such as t.a = r.x AND t.b = r.y cannot - select more than one r row per t row. - The planner formerly considered AND conditions - to be independent and would often drastically misestimate - selectivity as a result. Now it compares the WHERE - conditions to applicable foreign key constraints and produces - better estimates. + Previously, checkpoints wrote out dirty pages in whatever order + they happen to appear in shared buffers, which usually is nearly + random. That performs poorly, especially on rotating media. + This change causes checkpoint-driven writes to be done in order + by file and block number, and to be balanced across tablespaces. + + + + + + + Where feasible, trigger kernel writeback after a configurable + number of writes, to prevent accumulation of dirty data in kernel + disk buffers (Fabien Coelho, Andres Freund) + + + + PostgreSQL writes data to the kernel's disk cache, + from where it will be flushed to physical storage in due time. + Many operating systems are not smart about managing this and allow + large amounts of dirty data to accumulate before deciding to flush + it all at once, leading to long delays for new I/O requests. + This change attempts to alleviate this problem by explicitly + requesting data flushes after a configurable interval. + + + + On Linux, sync_file_range() is used for this purpose, + and the feature is on by default on Linux because that function has few + downsides. This sync capability is also available on other platforms + that have msync() or posix_fadvise(), + but those interfaces have some undesirable side-effects so the + feature is disabled by default on non-Linux platforms. + + + + The new configuration parameters , , , and control this behavior. @@ -808,35 +987,6 @@ This commit is also listed under libpq and psql - - Improve ANALYZE's estimates for columns with many nulls - (Tomas Vondra, Alex Shulgin) - - - - Previously ANALYZE tended to underestimate the number - of non-NULL distinct values in a column with many - NULLs, and was also inaccurate in computing the - most-common values. - - - - - - - Improve planner's estimate of the number of distinct values in - a query result (Tomas Vondra) - - - - - @@ -851,69 +1001,6 @@ This commit is also listed under libpq and psql - - Improve sorting performance by using quicksort, not replacement - selection sort, when performing external sort steps (Peter - Geoghegan) - - - - The new approach makes better use of the CPU cache - for typical cache sizes and data volumes. Where necessary, - the behavior can be adjusted via the new configuration parameter - . - - - - - - - Speed up text sorts where the same string occurs multiple times - (Peter Geoghegan) - - - - - - - Speed up sorting of uuid, bytea, and - char(n) fields by using abbreviated keys - (Peter Geoghegan) - - - - Support for abbreviated keys has also been - added to the non-default operator classes text_pattern_ops, - varchar_pattern_ops, and - bpchar_pattern_ops. Processing of ordered-set - aggregates can also now exploit abbreviated keys. - - - - - - - Speed up CREATE INDEX CONCURRENTLY by treating - TIDs as 64-bit integers during sorting (Peter - Geoghegan) - - - - - @@ -924,57 +1011,6 @@ This commit is also listed under libpq and psql - - Reduce contention for the ProcArrayLock (Amit Kapila, - Robert Haas) - - - - - - - Improve performance by moving buffer content locks into the buffer - descriptors (Andres Freund, Simon Riggs) - - - - - - - Replace shared-buffer header spinlocks with atomic operations to - improve scalability (Alexander Korotkov, Andres Freund) - - - - - - - Use atomic operations, rather than a spinlock, to protect an - LWLock's wait queue (Andres Freund) - - - - - - - Partition the shared hash table freelist to reduce contention on - multi-CPU-socket servers (Aleksander Alekseev) - - - - - @@ -1017,6 +1053,21 @@ This commit is also listed under libpq and psql + + Add pg_control_system(), + pg_control_checkpoint(), + pg_control_recovery(), and + pg_control_init() functions to expose fields of + pg_control to SQL (Joe Conway, Michael + Paquier) + + + + + @@ -1082,21 +1133,6 @@ This commit is also listed under libpq and psql - - Add pg_control_system(), - pg_control_checkpoint(), - pg_control_recovery(), and - pg_control_init() functions to expose fields of - pg_control to SQL (Joe Conway, Michael - Paquier) - - - - - @@ -1219,18 +1255,18 @@ This commit is also listed under libpq and psql - Add configure option - This allows the use of systemd service units of - type notify, which greatly simplifies the management - of PostgreSQL under systemd. + This behavior is controlled by the new configuration parameter + . It can + be useful to prevent forgotten transactions from holding locks + or preventing vacuum cleanup for too long. @@ -1247,18 +1283,18 @@ This commit is also listed under libpq and psql - Allow sessions to be terminated automatically if they are in - idle-in-transaction state for too long (Vik Fearing) + Add configure option - This behavior is controlled by the new configuration parameter - . It can - be useful to prevent forgotten transactions from holding locks - or preventing vacuum cleanup for too long. + This allows the use of systemd service units of + type notify, which greatly simplifies the management + of PostgreSQL under systemd. @@ -1857,6 +1893,30 @@ XXX this is pending backpatch, may need to remove + + Improve full-text search to support searching for phrases, that + is, lexemes appearing adjacent to each other in a specific order, + or with a specified distance between them (Teodor Sigaev, Oleg + Bartunov, Dmitry Ivanov) + + + + A phrase-search query can be specified in tsquery + input using the new operators <-> and + <N>. The former means + that the lexemes before and after it must appear adjacent to + each other in that order. The latter means they must be exactly + N lexemes apart. + + + + + @@ -1917,30 +1977,6 @@ XXX this is pending backpatch, may need to remove - - Improve full-text search to support searching for phrases, that - is, lexemes appearing adjacent to each other in a specific order, - or with a specified distance between them (Teodor Sigaev, Oleg - Bartunov, Dmitry Ivanov) - - - - A phrase-search query can be specified in tsquery - input using the new operators <-> and - <N>. The former means - that the lexemes before and after it must appear adjacent to - each other in that order. The latter means they must be exactly - N lexemes apart. - - - - -