PDF page 18
Describes the improved architecture and features in PostgreSQL 18. #### 3.1.1 Modified System catalogs {#pg18-3-1-1-modified-system-catalogs} The following catalogs and views have been changed. [[12227a1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=12227a1d5f8ecad296c4204cc924d33c6a6bcd34), [32d3ed8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=32d3ed8165f821f6994c95230a9a4b2ff0ce9f12), [f0d1127](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f0d11275954719fd5d0281d4135e5c78de46e099), [559efce](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=559efce1d684069acf234a5cb032acba84e70938), [17cc5f6](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=17cc5f666f6aada21eb3237974c50681ba4814ea), [7054186](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7054186c4ebe24e63254651e2ae9b36efae90d4e), [e7a9496](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e7a9496de90657e2161f68b3a5a9b2d9b0b7bb07), [02a8d0c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=02a8d0c45253eb54e57b1974c8627e5be3e1d852), [a0f1fce](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a0f1fce80c0373c16b22a1bdc9b0b61958fc9b6a), [ca87c41](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ca87c415e2fccf81cec6fd45698dde9fae0ab570), [f92c854](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f92c854cf406a5ad34c9aa92416d578819704aa2), [30a6ed0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=30a6ed0ce4bb18212ec38cdb537ea4b43bc99b83), [75eb976](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=75eb9766ec201b62f264554019757716093e2a2f), [a051e71](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a051e71e28a12342a4fb39a3c149a197159f9c46), [83ea6c5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b), [bb8dff9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bb8dff9995f2cf501376772898bcbcf58aa05cde), [ac0e331](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ac0e33136abc4668c9b08e1ba7db69afe1e0e2c3), [2421e9a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2421e9a51d20bb83154e54a16ce628f9249fa907), [99f8f3f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=99f8f3fbbc8f743290844e8c676d39dad11c5d5d), [60f566b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=60f566b4f24362f54003569d5f6e24137bcd83fc), [89f908a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=89f908a6d0ac1337c868625008c9598487d184e7), [73eba50](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=73eba5004a06a744b6b8570e42432b9e9f75997b), [8cc139b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8cc139bec34a2971b0682a04eb52ce7b3f5bb425), [0d6c477](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0d6c4776647feeee26f3e29fff6a5edb222fa260)] **Table 4.** Add system catalogs and views | View name | Description | | --- | --- | | pg_aios | The pg_aios view lists all asynchronous I/O handles. | | pg_shmem_allocations_numa | Shows how shared memory allocations in the server's main shared memory segment are distributed across NUMA nodes. | **Table 5.** System catalogs\/views with additional columns | View name | Add column | Data type | Description | | --- | --- | --- | --- | | pg_backend_memory_contexts | type | text | Context type | | | path | integer[] | Array showing context hierarchy | | pg_class | relallfrozen | integer | Number of pages marked as all frozen | | pg_constraint | conenforced | boolean | Is the constraint specified as ENFORCED? | | | conperiod | boolean | Specify WITHOUT OVERLAPS or PERIOD | | pg_publication | pubgencols | char | Replicate the values of the generated columns? | | pg_replication_slots | two_phase_at | pg_lsn | Address from which the decoding of prepared transactions is enabled | | pg_stat_{all\|user\|sys}_tables | total_vacuum_ti | double | Total time for VACUUM execution | | | me | precision | | | | total_autovacuu | double | Total time for automatic VACUUM | | | m_time | precision | execution | | | total_analyze_ti | double | Total time of ANALYZE execution | | | me | precision | | | | total_autoanalyze | double | Total time for automatic ANALYZE | | | _time | precision | execution | | pg_stat_checkpointer | num_done | bigint | Number of checkpoints completed | | | slru_written | bigint | Number of SLRU buffers written during checkpoint | | pg_stat_database | parallel_workers_to_launch | bigint | Number of parallel workers attempted to be activated | | | parallel_workerslaunched_ | bigint | Number of parallel workers activated | | pg_stat_io | read_bytes | numeric | Number of bytes read | | | write_bytes | numeric | Number of bytes write | | | extend_bytes | numeric | Number of extended bytes | | pg_stat_progress_analyze | delay_time | double precision | Sleep time due to cost-based delay | | pg_stat_progress_vacuum | delay_time | double precision | Sleep time due to cost-based delay | | pg_stat_subscription_stats | confl_update_origin_differs | bigint | Number of tuples modified by other sources during execution of the UPDATE statement | | | confl_update_exists | bigint | Number of tuples that violated the unique constraint during execution of the UPDATE statement | | | confl_update_missing | bigint | Number of tuples not found to be changed during execution of the UPDATE statement | | | confl_delete_origin_differs | bigint | Number of tuples modified by other sources during execution of the DELETE statement | | | confl_delete_missing | bigint | Number of tuples not found for deletion during execution of the DELETE statement | | | confl_multiple_unique_conflicts | bigint | Number of tuples that violated the unique constraint when executing a multi-tuple INSERT statement | | | confl_insert_exists | bigint | Number of tuples that violated the unique constraint when the INSERT statement was executed |PDF page 19
PDF page 20
**Table 6.** System catalogs\/views with removed columns | View name | Remove column | Description | | --- | --- | --- | | pg_backend_memory_contexts | parent | Due to the addition of the PATH column, it is no longer needed. | | pg_attribute | attcacheoff | No longer needed due to changes in cache structure | | pg_stat_wal | wal_write | For integration into pg_stat_io view | | | wal_sync | For integration into pg_stat_io view | | | wal_write_time | For integration into pg_stat_io view | | | wal_sync_time | For integration into pg_stat_io view | | pg_stat_io | op_bytes | Because it was a fixed value | **Table 7.** Views in the information\_schema schema with modified content | View name | Column name | Description | | --- | --- | --- | | table_constraints | enforced | Outputs YES or NO depending on column setting (previously only YES) | **Table 8.** System catalogs\/views with modified output | View name | Description | | --- | --- | | pg_aggregate | Includes max/min functions for user-defined types | | pg_attribute | Outputs 'v' indicating virtual column in attgenerated column | | pg_class | Outputs information on TOAST table in pg_index catalog | | pg_constraint | Includes information on NOT NULL constraints for columns | | pg_default_acl | L (Large Object) output in defaclobjtype column | | pg_proc | Includes max/min functions for user-defined types | | pg_replication_slots | Outputs idle_timeout in invalidation_reason column | | pg_stat_io | WAL information added |PDF page 21
##### Pg_aios view {#pg18-p21-pg-aios-view} The pg_aios view outputs the status of the currently used asynchronous I/O handle. [[60f566b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=60f566b4f24362f54003569d5f6e24137bcd83fc)] **Table 9.** Pg\_aios view column structure | Column name | Data type | Description | | --- | --- | --- | | pid | integer | Server process ID performing I/O | | io_id | integer | I/O handle ID | | io_generation | bigint | I/O handle generation | | state | text | I/O handle status | | operation | text | I/O handle operations | | off | bigint | I/O operation offset | | length | bigint | I/O operation length | | target | text | I/O execution target | | handle_data_len | smallint | I/O operation data length | | raw_result | integer | Low-level I/O results | | result | text | High-level I/O results | | target_desc | text | I/O target description | | f_sync | boolean | I/O execution flags | | f_localmem | boolean | Local memory flag | | f_buffered | boolean | Flag indicating buffer I/O | ##### Pg_shmem_allocations_numa view {#pg18-p21-pg-shmem-allocations-numa-view} This view shows how shared memory segments are distributed among NUMA nodes. [[8cc139b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8cc139bec34a2971b0682a04eb52ce7b3f5bb425)] **Table 10.** Pg\_shmem\_allocations\_numa view column structure | Column name | Data type | Description | | --- | --- | --- | | name | text | Memory area name | | numa_node | integer | NUMA node number | | size | bigint | Memory size |PDF page 22
**Example 16.** Retrieving the pg\_shmem\_allocations\_numa view ```text postgres=# SELECT * FROM pg_shmem_allocations_numa ; name | numa_node | size ------------------------------------------------+-----------+----------- multixact_offset | 0 | 139264 subtransaction | 0 | 270336 notify | 0 | 139264 Shared Memory Stats | 0 | 319488 serializable | 0 | 270336 PROCLOCK hash | 0 | 4096 … ``` ##### NOT NULL constraint {#pg18-p22-not-null-constraint} NOT NULL constraints on a table columns are now included in the pg_constraint catalog. The constraint name is automatically generated from the table and column names. The value of the contype column of the NOT NULL constraint is specified as 'n'. [[14e87ff](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=14e87ffa5c543b5f30ead7413084c25f7735039f)] **Example 17.** Cataloging NOT NULL Constraints ```text postgres=> CREATE TABLE data1(col1 INT NOT NULL, col2 VARCHAR(10)) ; CREATE TABLE postgres=> \d+ data1 Table "public.data1" Column | Type | Collation | Nullable | Default | Storage … --------+-----------------------+-----------+----------+---------+---------… col1 | integer | | not null | | plain … col2 | character varying(10) | | | | extended… Not-null constraints: "data1_col1_not_null" NOT NULL "col1" Access method: heap postgres=> SELECT conname FROM pg_constraint WHERE contype='n' AND conname LIKE 'data1%' ; conname --------------------- data1_col1_not_null (1 row) ```PDF page 23
##### Pg_stat_{all|user|sys}_tables view {#pg18-p23-pg-stat-all-user-sys-tables-view} VACUUM and ANALYZE execution times have been added to the pg_stat_{all|user|sys}_tables view. The manually executed and automatically executed cases are cumulated, respectively. [[30a6ed0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=30a6ed0ce4bb18212ec38cdb537ea4b43bc99b83)] **Example 18.** Cumulative VACUUM \/ ANALYZE execution time ```text postgres=> ANALYZE data1 ; ANALYZE postgres=> VACUUM data1 ; VACUUM postgres=> SELECT relname, total_vacuum_time, total_autovacuum_time, total_analyze_time, total_autoanalyze_time FROM pg_stat_user_tables ; -[ RECORD 1 ]----------+------ relname | data1 total_vacuum_time | 1826 total_autovacuum_time | 14607 total_analyze_time | 125 total_autoanalyze_time | 1475 ``` #### 3.1.2 Logical Replication Enhancements {#pg18-3-1-2-logical-replication-enhancements} The following new features have been implemented for logical replication ##### Log output when conflicts occur {#pg18-p23-log-output-when-conflicts-occur} Information about conflicts that occur when updating against the standby database are now output to the log. The reason for the conflict is output in the format “conflict={reason}”. The parameter track_commit_timestamp must be set to 'on' on the subscriber side (default value 'off') to detect the conflict reasons update_differ and delete_differ. [[9758174](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9758174e2e5cd278cf37e0980da76b51890e0011), [73eba50](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=73eba5004a06a744b6b8570e42432b9e9f75997b)]PDF page 24
**Table 11.** Reason for conflict output | Output text | Description (DETAIL log) | Replication | | --- | --- | --- | | insert_exists | INSERT statement was executed but a tuple with the same key already exists (Key already exists in unique index) | Stop | | update_differ | UPDATE statement target tuple was updated by another origin (Updating the row that was modified locally) | Continue | | update_exists | UPDATE statement was executed but a tuple with the same key exists (Key already exists in unique index) | Stop | | update_missing | UPDATE statement was executed but the target tuple does not exist (Could not find the row to be updated) | Continue | | delete_differ | DELETE statement was executed but the target tuple was updated by another origin (Updating the row that was modified locally) | Continue | | delete_missing | DELETE statement executed but target tuple does not exist (Could not find the row to be deleted) | Continue | | multiple_unique_conflicts | Multiple tuples violate unique constraints (Key already exists in unique index) | Stop | **Example 19.** Error log when executing INSERT statement ```text ERROR: conflict detected on relation "public.data1": conflict=insert_exists DETAIL: Key already exists in unique index "data1_pkey", modified in transaction 776. Key (col1)=(100); existing local tuple (100, standby1); remote tuple (100, primary1). CONTEXT: processing remote data for replication origin "pg_24577" during message type "INSERT" for replication target relation "public.data1" in transaction 761, finished at 0/3059248 ```PDF page 25
##### Propagation of Generated Columns {#pg18-p25-propagation-of-generated-columns} The option PUBLISH_GENERATED_COLUMNS has been added to the CREATE PUBLICATION statement to determine whether the values of generated columns (GENERATED ALWAYS AS STORED specified columns) are to be replicated. The default value for this option is ‘none’, which means that the values of the generated columns will not be propagated. To propagate generated columns, specify ‘stored’ for this option. The value of the specified option is stored in the pubgencols_type column of the pg_publication catalog. [[7054186](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7054186c4ebe24e63254651e2ae9b36efae90d4e), [745217a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=745217a051a9341e8c577ea59a87665d331d4af0), [7054186](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7054186c4ebe24e63254651e2ae9b36efae90d4e), [e65dbc9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e65dbc9927fb86aa3c8a914ede6a6ae934384f5a), [117f9f3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=117f9f328e0f146a4b6de517501f4d5d994040a9)] **Example 20.** Propagation settings for generated columns \(PUBLICATION side\) ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 INT GENERATED ALWAYS AS (col1*2) STORED, col3 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE PUBLICATION pub1 FOR TABLE data1(col1, col2, col3) WITH (publish_generated_columns = stored) ; CREATE PUBLICATION postgres=> SELECT pubname, pubgencols FROM pg_publication ; -[ RECORD 1 ]---- pubname | pub1 pubgencols | s postgres=> INSERT INTO data1(col1, col3) VALUES (100, 'data1') ; INSERT 0 1 ``` **Example 21.** Confirmation of propagation of generated sequence \(SUBSCRIPTION side\) ```text postgres=> CREATE TABLE data1(col1 INT PRIMARY KEY, col2 INT, col3 VARCHAR(10)) ; CREATE TABLE postgres=> CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres' PUBLICATION pub1 ; NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION postgres=> SELECT * FROM data1 ; col1 | col2 | col3 -------+-------+------- 100 | 200 | data1 (1 row) ```PDF page 26
#### 3.1.3 Optimizer Statistics Management {#pg18-3-1-3-optimizer-statistics-management} Functions to set and clear optimizer statistics for tables and columns are now provided. Options have been added to the pg_dump, pg_dumpall, and pg_upgrade commands to use this function to migrate statistics. See “3.4 Utilities” for additional command options. ##### Pg_restore_relation_stats function {#pg18-p26-pg-restore-relation-stats-function} The pg_restore_relation_stats function sets the optimizer statistics for a table. It returns false in case of minor errors. [[d32d146](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d32d1463995c036853eeb9ec99cc367ffc7794ae)] #### Syntax 1 - Pg_restore_relatoin_stats function {#pg18-syntax-1} ```sql boolean pg_restore_relation_stats(VARIADIC kwargs "any") ``` **Example 22.** Execute pg\_restore\_relation\_stats function ```text postgres=> SELECT * FROM pg_catalog.pg_restore_relation_stats( 'version', '180000'::integer, 'schemaname', 'public', 'relname', 'data1', 'relpages', '5406'::integer, 'reltuples', '1e+06'::real, 'relallvisible', '0'::integer, 'relallfrozen', '0'::integer) ; pg_restore_relation_stats --------------------------- t (1 row) ``` ##### Pg_clear_relation_stats function {#pg18-p26-pg-clear-relation-stats-function} The function pg_clear_relation_stats clears the optimizer statistics for the specified table. Executing this function resets the corresponding column in the pg_class catalog. [[e839c8e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e839c8ecc9352b7754e74f19ace013c0c0d18613), [650ab8a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=650ab8aaf1957863ae14c80265e79f5d903b49fd)] #### Syntax 2 - Pg_clear_relation_stats function {#pg18-syntax-2} ```sql void pg_clear_relation_stats(schemaname text, relname text) ```PDF page 27
**Example 23.** Execute pg\_clear\_relation\_stats function ```text postgres=> SELECT pg_clear_relation_stats('public', 'data1') ; pg_clear_relation_stats ------------------------- (1 row) postgres=> SELECT relname, reltuples FROM pg_class WHERE relname='data1' ; relname | reltuples ---------+----------- data1 | -1 (1 row) ``` ##### Pg_restore_attribute_stats function {#pg18-p27-pg-restore-attribute-stats-function} The function pg_restore_attribute_stats sets the column optimizer statistics. It returns false in case of minor errors. [[d32d146](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d32d1463995c036853eeb9ec99cc367ffc7794ae)] #### Syntax 3 - Pg_restore_attribute_stats function {#pg18-syntax-3} ```sql boolean pg_restore_attribute_stats(VARIADIC kwargs "any") ``` **Example 24.** Execute pg\_restore\_attribute\_stats function ```text postgres=> SELECT * FROM pg_catalog.pg_restore_attribute_stats( 'version', '180000'::integer, 'schemaname', 'public', 'relname', 'data1', 'attname', 'col1', 'inherited', 'f'::boolean, 'null_frac', '0'::real, 'avg_width', '4'::integer, 'n_distinct', '-1'::real) ; pg_restore_attribute_stats ---------------------------- t (1 row) ```PDF page 28
##### Pg_clear_attribute_stats function {#pg18-p28-pg-clear-attribute-stats-function} The function pg_clear_attribute_stats clears the optimizer statistics for the specified column. Executing this function resets the corresponding column in the pg_stats catalog. [[ce207d2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ce207d2a79017713c0293bf70132dc6d41166b03), [650ab8a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=650ab8aaf1957863ae14c80265e79f5d903b49fd)] #### Syntax 4 - Pg_clear_attribute_stats function {#pg18-syntax-4} ```sql void pg_clear_attribute_stats(schemaname text, relname text, attname text, inherited boolean) ``` **Example 25.** Execute pg\_clear\_attribute\_stats function ```text postgres=> SELECT pg_clear_attribute_stats('public', 'data1', 'col1', true) ; -[ RECORD 1 ]------------+- pg_clear_attribute_stats | ``` #### 3.1.4 Roles and Permissions {#pg18-3-1-4-roles-and-permissions} The role pg_signal_autovacuum_worker has been added as a predefined role. This role allows signaling to the Autovacuum worker process. [[ccd3802](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ccd38024bc3c61e62af2097d408a670661713e68)] #### 3.1.5 I/O Processing {#pg18-3-1-5-i-o-processing} PostgreSQL 18 adds large enhancements to the Storage I/O. ##### Streaming I/O coverage enhancement {#pg18-p28-streaming-i-o-coverage-enhancement} Streaming I/O, introduced in PostgreSQL 17, is now more widely applicable. Streaming I/O is used in the following cases - VACUUM statement for a table [[9256822](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9256822608f3b0636cfd88c3436cdceda7003cf3), [c3e775e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c3e775e608f2a6d0bcfba147bf08a506827cc567), [9256822](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9256822608f3b0636cfd88c3436cdceda7003cf3), [c3e775e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c3e775e608f2a6d0bcfba147bf08a506827cc567)] - CREATE DATABASE STRATEGY=WAL_LOG statement [[8720a15](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8720a15e9ab121e49174d889eaeafae8ac89de7b)] - VACUUM for B-Tree index [[c5c239e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c5c239e26e3876c05b3a2c5e8989817fd38ceed1)] - VACUUM for GiST index [[69273b8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=69273b818b1df82c36b2b2acb592db3d0743cc7c)] - VACUUM for SP-GiST index [[e215166](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e215166c9c810950cff101cc098e66c8758538fa)] - Amcheck module [[043799f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=043799fa08c2c71f35816ca067951266d2e9ebe0)] - Autoprewarm module [[d9c7911](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d9c7911e1a5f354b7c34b7f53a52e0a690e7ebc9)]PDF page 29
##### Providing Asynchronous I/O {#pg18-p29-providing-asynchronous-i-o} The infrastructure for using asynchronous I/O is now in place. The parameter io_method has been added to specify the method used for asynchronous I/O. The default value of this parameter is 'worker', which performs asynchronous I/O by an I/O worker process; the number of I/O worker processes started is determined by the parameter io_workers (default value 3). If using Linux's io_uring, specify io_uring for this parameter. [[da72269](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=da7226993fd4b73d8b40abb7167d124eada97f2e), [55b454d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=55b454d0e14084c841a034073abbf1a0ea937a45), [247ce06](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=247ce06b883d7b3a40d08312dc03dfb37fbff212), [c325a76](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c325a7633fcb33dbd73f46ddbbe91e95ddf3b227)] **Example 26.** Confirmation of I\/O worker process ```bash $ ps -ef | grep 'io worker' | grep -v grep postgres 148065 148063 0 10:08 ? 00:00:00 postgres: io worker 0 postgres 148066 148063 0 10:08 ? 00:00:00 postgres: io worker 2 postgres 148067 148063 0 10:08 ? 00:00:00 postgres: io worker 1 ``` **Table 12.** Parameter io\_method setting value | Value | Description | Note | | --- | --- | --- | | worker | Asynchronous I/O is executed by the io worker process. | Default | | sync | Synchronous I/O is executed. This is the same behavior as in the previous version. | | | io_uring | Use io_uring for asynchronous I/O. | | ##### Prefetch support {#pg18-p29-prefetch-support} Prefetching is now possible with PostgreSQL running on macOS. [[6654bb9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6654bb92047b37cee053cedd6fa1829841b2ad8e)] #### 3.1.6 Locale Provider {#pg18-3-1-6-locale-provider} The locale PG_UNICODE_FAST has been added to the builtin locale provider. This locale uses code point sort order and conforms to Unicode's full case mapping. However, conversions may cause changes in the number of characters, such as “ß” being lowercased to “ss”. [[d3d0983](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d3d0983169130a9b81e3fe48d5c2ca4931480956), [286a365](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=286a365b9c25479f8ad82043ed136748733adfa6)]PDF page 30
**Example 27.** Database cluster creation with PG\_UNICODE\_FAST locale specified ```bash $ initdb --locale-provider=builtin --builtin-locale=PG_UNICODE_FAST --encoding=utf8 data … The database cluster will be initialized with this locale configuration: locale provider: builtin default collation: PG_UNICODE_FAST … ``` **Example 28.** Database creation with PG\_UNICODE\_FAST locale specified ```text postgres=# CREATE DATABASE fastuni1 LOCALE_PROVIDER builtin LOCALE 'PG_UNICODE_FAST' lc_collate 'C' LC_CTYPE 'C' ENCODING 'UTF8' TEMPLATE template0 ; CREATE DATABASE ``` #### 3.1.7 OAUTH SASL {#pg18-3-1-7-oauth-sasl} OAUTHBearer/SASL can now be used for database user authentication. To use OAUTHBearer/SASL authentication, specify 'oauth' in the pg_hba.conf authentication method. To use this authentication, the client module uses the libcurl library, so a binary with --with-libcurl to the 'configure' command is required. The parameter oauth_validator_libraries specifies the validator libraries. If this parameter is not specified, authentication by the oauth authentication method will be failed. [[b3f0be7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b3f0be788afc17d2206e1ae1c731d8aeda1f2f59)] #### 3.1.8 Faster hash joins {#pg18-3-1-8-faster-hash-joins} Deform processing during hash join execution has been optimized. [[adf97c1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=adf97c1562380e02acd60dc859c289ed3a8352ee)]PDF page 31
**Example 29.** Deforms during hash join ```text postgres=> EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM data1, data2 WHERE data1.col1 = data2.col1 ; QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=32789.00..65469.01 rows=1000000 width=20) (actual time=99.8… Hash Cond: (data1.col1 = data2.col1) -> Seq Scan on data1 (cost=0.00..15406.00 rows=1000000 width=10) (act … -> Hash (cost=15406.00..15406.00 rows=1000000 width=10) (actual time=99… Buckets: 262144 Batches: 8 Memory Usage: 7179kB -> Seq Scan on data2 (cost=0.00..15406.00 rows=1000000 width=10) (… Planning Time: 0.165 ms JIT: Functions: 10 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 0.228 ms (Deform 0.112 ms), Inlining 0.000 ms, Optimization 0.153 ms, Emission 3.116 ms, Total 3.497 ms Execution Time: 389.868 ms (12 rows) ``` #### 3.1.9 Libpq {#pg18-3-1-9-libpq} The following APIs have been added and enhanced. ##### Connection string {#pg18-p31-connection-string} The minimum / maximum protocol version and SSL connection log file name have been added to the connection string. Corresponding environment variables have also been added. [[285613c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=285613c60a7aff5daaf281c67002483b0d26e715), [2da74d8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2da74d8d6400975bf73fb0df97c3943ad3ed9a36)] **Table 13.** Added connect string | Connect string | Environment variables | Description | | --- | --- | --- | | max_protocol_version | PGMAXPROTOCOLVERSION | Maximum version | | min_protocol_version | PGMINPROTOCOLVERSION | Minimum version | | sslkeylogfile | - | SSL key log file |PDF page 32
##### Other API {#pg18-p32-other-api} ```text The following APIs have been added. [4b99fed, cdb6b0f, fef6da9] ``` **Table 14.** Added API functions | API Name | Description | | --- | --- | | PQfullProtocolVersion | Return protocol version including minor version | #### 3.1.10 Access Method {#pg18-3-1-10-access-method} The following enhancements have been added to the index access methods. [[56fead4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=56fead44dcc70df9f9188fee08e5aefe3da43ccc), [af4002b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=af4002b381d86df6479962953d82f03ecb4e2e06;hp=6da469badaffec32f8a804181cca279561467378), [ce62f2f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ce62f2f2a0a), [af4002b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=af4002b381d86df6479962953d82f03ecb4e2e06;hp=6da469badaffec32f8a804181cca279561467378)] **Table 15.** Added Access Method Element | Element name | Description | | --- | --- | | amgettreeheight | Calculates the height of the index tree | | amconsistentequality | Consistent equivalence semantics. Split old version of amcancrosscompare. Has consistent ordering semantics. Split out old version of amcancrosscompare. | | amcanhash | Support hashing | #### 3.1.11 NUMA {#pg18-3-1-11-numa} The basic NUMA environment-aware API, SQL functions and pg_shmem_allocations_numa view have been added. The --with-libnuma option to the 'configure' command is required to enable this feature. [[65c298f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=65c298f61fc70f2f960437c05649f71b862e2c48), [8cc139b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8cc139bec34a2971b0682a04eb52ce7b3f5bb425)] **Table 16.** Added APIs | API names | Description | | --- | --- | | pg_numa_init | Initialize NUMA environment | | pg_numa_get_max_node | Get maximum number of nodes | | pg_numa_query_pages | Identify NUMA nodes in individual memory pages |PDF page 33
**Example 30.** Execute pg\_numa\_available function ```text postgres=> SELECT pg_numa_available() ; pg_numa_available ------------------- t (1 row) ``` #### 3.1.12 Accelerated numerical calculations {#pg18-3-1-12-accelerated-numerical-calculations} Acceleration of the following basic calculations and CRC32C functions are implemented. - Faster numeric multiplication [[c4e4422](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c4e44224cf617c8cd33a734f888c045ac9575226), [c4e4422](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c4e44224cf617c8cd33a734f888c045ac9575226), [ca481d3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ca481d3c9ab7bf69ff0c8d71ad3951d407f6a33c)] - Faster division of numeric types [[9428c00](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9428c001f67540e6206b7a76adfc5377d9207777)] - Intel AVX-512 use of CRC32C functions [[3c6e8c1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3c6e8c123896584f1be1fe69aaf68dcb5eb094d5)] #### 3.1.13 Scope of Use of Unique Key Constraints {#pg18-3-1-13-scope-of-use-of-unique-key-constraints} Previously, only B-Tree indexes could be used for foreign keys, materialized views, and partition keys, but now other index access methods can be used to create indexes as long as they are unique. [[bfe21b7](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bfe21b760e039163b91279f58fd09a0298572775), [9d6db8b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9d6db8bec19413cd0167f1e59d1af005a997bd3e), [f278e1f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f278e1fe300ab1b7d43c3efb55a29aa17e5f5dda)] #### 3.1.14 Hook {#pg18-3-1-14-hook} The following hooks for the EXPLAIN statement have been added. [fd02bf7, [50ba65e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=50ba65e73325cf55fedb3e1f14673d816726923b)] - Hook explore_per_plan_hook for EXPLAIN statement - Hook explain_per_node_hook for per-node execution - Hook for option validation, explore_validate_options_hook