Movatterモバイル変換

E.50.1. Overview

E.50.2. Migration to Version 11

E.50.3. Changes

• Allow the creation of partitions based on hashing a key column (Amul Sul)

• Support indexes on partitioned tables (Álvaro Herrera, Amit Langote)

  An“index” on a partitioned table is not a physical index across the whole partitioned table, but rather a template for automatically creating similar indexes on each partition of the table.

  If the partition key is part of the index's column set, a partitioned index may be declaredUNIQUE. It will represent a valid uniqueness constraint across the whole partitioned table, even though each physical index only enforces uniqueness within its own partition.

  The new commandALTER INDEX ATTACH PARTITION causes an existing index on a partition to be associated with a matching index template for its partitioned table. This provides flexibility in setting up a new partitioned index for an existing partitioned table.

• Allow foreign keys on partitioned tables (Álvaro Herrera)

• AllowFOR EACH ROW triggers on partitioned tables (Álvaro Herrera)

  Creation of a trigger on a partitioned table automatically creates triggers on all existing and future partitions. This also allows deferred unique constraints on partitioned tables.

• Allow partitioned tables to have a default partition (Jeevan Ladhe, Beena Emerson, Ashutosh Bapat, Rahila Syed, Robert Haas)

  The default partition will store rows that don't match any of the other defined partitions, and is searched accordingly.

• UPDATE statements that change a partition key column now cause affected rows to be moved to the appropriate partitions (Amit Khandekar)

• AllowINSERT,UPDATE, andCOPY on partitioned tables to properly route rows to foreign partitions (Etsuro Fujita, Amit Langote)

  This is supported bypostgres_fdw foreign tables. Since theExecForeignInsert callback function is called for this in a different way than it used to be, foreign data wrappers must be modified to cope with this change.

• Allow faster partition elimination during query processing (Amit Langote, David Rowley, Dilip Kumar)

  This speeds access to partitioned tables with many partitions.

• Allow partition elimination during query execution (David Rowley, Beena Emerson)

  Previously, partition elimination only happened at planning time, meaning many joins and prepared queries could not use partition elimination.

• In an equality join between partitioned tables, allow matching partitions to be joined directly (Ashutosh Bapat)

  This feature is disabled by default but can be enabled by changingenable_partitionwise_join.

• Allow aggregate functions on partitioned tables to be evaluated separately for each partition, subsequently merging the results (Jeevan Chalke, Ashutosh Bapat, Robert Haas)

  This feature is disabled by default but can be enabled by changingenable_partitionwise_aggregate.

• Allowpostgres_fdw to push down aggregates to foreign tables that are partitions (Jeevan Chalke)

• Allow parallel building of a btree index (Peter Geoghegan, Rushabh Lathia, Heikki Linnakangas)

• Allow hash joins to be performed in parallel using a shared hash table (Thomas Munro)

• AllowUNION to run eachSELECT in parallel if the individualSELECTs cannot be parallelized (Amit Khandekar, Robert Haas, Amul Sul)

• Allow partition scans to more efficiently use parallel workers (Amit Khandekar, Robert Haas, Amul Sul)

• AllowLIMIT to be passed to parallel workers (Robert Haas, Tom Lane)

  This allows workers to reduce returned results and use targeted index scans.

• Allow single-evaluation queries, e.g.,WHERE clause aggregate queries, and functions in the target list to be parallelized (Amit Kapila, Robert Haas)

• Add server parameterparallel_leader_participation to control whether the leader also executes subplans (Thomas Munro)

  The default is enabled, meaning the leader will execute subplans.

• Allow parallelization of commandsCREATE TABLE ... AS,SELECT INTO, andCREATE MATERIALIZED VIEW (Haribabu Kommi)

• Improve performance of sequential scans with many parallel workers (David Rowley)

• Add reporting of parallel workers' sort activity inEXPLAIN (Robert Haas, Tom Lane)

• Allow B-tree indexes to include columns that are not part of the search key or unique constraint, but are available to be read by index-only scans (Anastasia Lubennikova, Alexander Korotkov, Teodor Sigaev)

  This is enabled by the newINCLUDE clause ofCREATE INDEX. It facilitates building“covering indexes” that optimize specific types of queries. Columns can be included even if their data types don't have B-tree support.

• Improve performance of monotonically increasing index additions (Pavan Deolasee, Peter Geoghegan)

• Improve performance of hash index scans (Ashutosh Sharma)

• Add predicate locking for hash, GiST and GIN indexes (Shubham Barai)

  This reduces the likelihood of serialization conflicts in serializable-mode transactions.

• Improve selection of the most common values for statistics (Jeff Janes, Dean Rasheed)

  Previously, the most common values (MCVs) were identified based on their frequency compared to all column values. Now,MCVs are chosen based on their frequency compared to the non-MCV values. This improves the robustness of the algorithm for both uniform and non-uniform distributions.

• Improve selectivity estimates for>= and<= (Tom Lane)

  Previously, such cases used the same selectivity estimates as> and<, respectively, unless the comparison constants areMCVs. This change is particularly helpful for queries involvingBETWEEN with small ranges.

• Reducevar=var tovarIS NOT NULL where equivalent (Tom Lane)

  This leads to better selectivity estimates.

• Improve optimizer's row count estimates forEXISTS andNOT EXISTS queries (Tom Lane)

• Make the optimizer account for evaluation costs and selectivity ofHAVING clauses (Tom Lane)

• AddJust-in-Time (JIT) compilation of some parts of query plans to improve execution speed (Andres Freund)

  This feature requiresLLVM to be available. It is not currently enabled by default, even in builds that support it.

• Allow bitmap scans to perform index-only scans when possible (Alexander Kuzmenkov)

• Update the free space map duringVACUUM (Claudio Freire)

  This allows free space to be reused more quickly.

• AllowVACUUM to avoid unnecessary index scans (Masahiko Sawada, Alexander Korotkov)

• Improve performance of committing multiple concurrent transactions (Amit Kapila)

• Reduce memory usage for queries using set-returning functions in their target lists (Andres Freund)

• Improve the speed of aggregate computations (Andres Freund)

• Allowpostgres_fdw to pushUPDATEs andDELETEs using joins to foreign servers (Etsuro Fujita)

  Previously, only non-joinUPDATEs andDELETEs were pushed.

• Add support forlarge pages on Windows (Takayuki Tsunakawa, Thomas Munro)

  This is controlled by thehuge_pages configuration parameter.

• Show memory usage in output fromlog_statement_stats,log_parser_stats,log_planner_stats, andlog_executor_stats (Justin Pryzby, Peter Eisentraut)

• Add columnpg_stat_activity.backend_type to show the type of a background worker (Peter Eisentraut)

  The type is also visible inps output.

• Makelog_autovacuum_min_duration log skipped tables that are concurrently being dropped (Nathan Bossart)

• Allow the server to specify more complexLDAP specifications in search+bind mode (Thomas Munro)

  Specifically,ldapsearchfilter allows pattern matching using combinations ofLDAP attributes.

• AllowLDAP authentication to use encryptedLDAP (Thomas Munro)

  We already supportedLDAP overTLS by usingldaptls=1. This newTLSLDAP method for encryptedLDAP is enabled withldapscheme=ldaps orldapurl=ldaps://.

• Improve logging ofLDAP errors (Thomas Munro)

• Adddefault roles that enable file system access (Stephen Frost)

  Specifically, the new roles are:pg_read_server_files,pg_write_server_files, andpg_execute_server_program. These roles now also control who can use server-sideCOPY and thefile_fdw extension. Previously, only superusers could use these functions, and that is still the default behavior.

• Allow access to file system functions to be controlled byGRANT/REVOKE permissions, rather than superuser checks (Stephen Frost)

  Specifically, these functions were modified:pg_ls_dir(),pg_read_file(),pg_read_binary_file(),pg_stat_file().

• UseGRANT/REVOKE to control access tolo_import() andlo_export() (Michael Paquier, Tom Lane)

  Previously, only superusers were granted access to these functions.

  The compile-time optionALLOW_DANGEROUS_LO_FUNCTIONS has been removed.

• Use view owner not session owner when preventing non-password access topostgres_fdw tables (Robert Haas)

  PostgreSQL only allows superusers to accesspostgres_fdw tables without passwords, e.g., viapeer. Previously, the session owner had to be a superuser to allow such access; now the view owner is checked instead.

• Fix invalid locking permission check inSELECT FOR UPDATE on views (Tom Lane)

• Add server settingssl_passphrase_command to allow supplying of the passphrase forSSL key files (Peter Eisentraut)

  Also addssl_passphrase_command_supports_reload to specify whether theSSL configuration should be reloaded andssl_passphrase_command called during a server configuration reload.

• Add storage parametertoast_tuple_target to control the minimum tuple length beforeTOAST storage will be considered (Simon Riggs)

  The defaultTOAST threshold has not been changed.

• Allow server options related to memory and file sizes to be specified in units of bytes (Beena Emerson)

  The new unit suffix is“B”. This is in addition to the existing units“kB”,“MB”,“GB” and“TB”.

• Allow theWAL file size to be set duringinitdb (Beena Emerson)

  Previously, the 16MB default could only be changed at compile time.

• RetainWAL data for only a single checkpoint (Simon Riggs)

  Previously,WAL was retained for two checkpoints.

• Fill the unused portion of force-switchedWAL segment files with zeros for improved compressibility (Chapman Flack)

• Addpsql command\gdesc to display the names and types of the columns in a query result (Pavel Stehule)

• Addpsql variables to report query activity and errors (Fabien Coelho)

  Specifically, the new variables areERROR,SQLSTATE,ROW_COUNT,LAST_ERROR_MESSAGE, andLAST_ERROR_SQLSTATE.

• Allowpsql to test for the existence of a variable (Fabien Coelho)

  Specifically, the syntax:{?variable_name} allows a variable's existence to be tested in an\if statement.

• Allow environment variablePSQL_PAGER to controlpsql's pager (Pavel Stehule)

  This allowspsql's default pager to be specified as a separate environment variable from the pager for other applications.PAGER is still honored ifPSQL_PAGER is not set.

• Make psql's\d+ command always show the table's partitioning information (Amit Langote, Ashutosh Bapat)

  Previously, partition information would not be displayed for a partitioned table if it had no partitions. Also indicate which partitions are themselves partitioned.

• Ensure thatpsql reports the proper user name when prompting for a password (Tom Lane)

  Previously, combinations of-U and a user name embedded in aURI caused incorrect reporting. Also suppress the user name before the password prompt when--password is specified.

• Allowquit andexit to exitpsql when given with no prior input (Bruce Momjian)

  Also print hints about how to exit whenquit andexit are used alone on a line while the input buffer is not empty. Add a similar hint forhelp.

• Makepsql hint at using control-D when\q is entered alone on a line but ignored (Bruce Momjian)

  For example,\q does not exit when supplied in character strings.

• Improve tab completion forALTER INDEX RESET/SET (Masahiko Sawada)

• Add infrastructure to allowpsql to adapt its tab completion queries based on the server version (Tom Lane)

  Previously, tab completion queries could fail against older servers.

• Addpgbench expression support for NULLs, booleans, and some functions and operators (Fabien Coelho)

• Add\if conditional support topgbench (Fabien Coelho)

• Allow the use of non-ASCII characters inpgbench variable names (Fabien Coelho)

• Addpgbench option--init-steps to control the initialization steps performed (Masahiko Sawada)

• Add an approximately Zipfian-distributed random generator topgbench (Alik Khilazhev)

• Allow the random seed to be set inpgbench (Fabien Coelho)

• Allowpgbench to do exponentiation withpow() andpower() (Raúl Marín Rodríguez)

• Add hashing functions topgbench (Ildar Musin)

• Makepgbench statistics more accurate when using--latency-limit and--rate (Fabien Coelho)

• Addpg_dumpall option--encoding to control output encoding (Michael Paquier)

  pg_dump already had this option.

• Addpg_dump option--load-via-partition-root to force loading of data into the partition's root table, rather than the original partition (Rushabh Lathia)

  This is useful if the system to be loaded to has different collation definitions or endianness, possibly requiring rows to be stored in different partitions than previously.

• Add an option to suppress dumping and restoring database object comments (Robins Tharakan)

  The newpg_dump,pg_dumpall, andpg_restore option is--no-comments.

E.50.4. Acknowledgments