3 Architecture

PDF page 14

3.1.1 Modified System catalogs

The following catalogs and views have been changed. [1e68e43, 007693f, 46ebdfe, 78806a9, 13aeaf0, 3ee2f25, b0e96f3, e64c733 ,e83d1b0, 96f0526, bc3c8db, 12915a5, 4f62250, 46a0cd4, c393308, 776621a, ddd5f4f, 030e10f, f696c0c, 012460e, 6ae701b, a11f330, 6d49c8d, 7294396, 667e65a, 74604a3, 74604a3, f1affb6]

PDF page 15

PDF page 16

Pg_wait_events view

The following is the detailed description of the pg_wait_events view added to PostgreSQL 17. The pg_wait_events view provides the names and descriptions of registered wait events. [1e68e43]

postgres=> SELECT name, description FROM pg_wait_events ;
      name             |      description
-----------------------+------------------------------------------------------
 ArchiverMain          | Waiting in main loop of archiver process
 AutoVacuumMain        | Waiting in main loop of autovacuum launcher process
 BgWriterHibernate     | Waiting in background writer process, hibernating
 BgWriterMain          | Waiting in main loop of background writer process
…

PDF page 17

Pg_stat_checkpointer view

The following is the detail of the pg_stat_checkpointer view, which is new to PostgreSQL 17. [96f0526, 12915a5]

postgres=> SELECT * FROM pg_stat_checkpointer ;
-[ RECORD 1 ]-------+------------------------------
num_timed           | 84
num_requested       | 1
restartpoints_timed | 0
restartpoints_req   | 0
restartpoints_done  | 0
write_time          | 93982
sync_time           | 5
buffers_written     | 935
stats_reset         | 2024-09-27 23:44:02.608669+09

PDF page 18

3.1.2 Logical Replication Enhancements

The following features have been added to Logical Replication.

Integration with Streaming Replication

The WAL Sender (walsender) process of logical replication can be configured to send any update data to the plugin after confirming that the replication slot in streaming replication has received the WAL. The primary server parameter synchronized_standby_slots specifies the name of the streaming replication slot that should confirm the transfer. To use this feature, the ‘failover’ attribute of the logical replication slot must be set as described below. If the replication slot specified in synchronized_standby_slots becomes inactive Logical replication transfers are also stopped and the following message is periodically output to the primary server log. [bf279dd]

WARNING:
replication
slot
"slot1"
specified
in
parameter
synchronized_standby_slots does not have active_pid
DETAIL:  Logical replication is waiting on the standby associated with "slot1".
HINT:  Consider starting standby associated with "slot1" or amend parameter
synchronized_standby_slots.

Replication slot new attribute

The parameter ‘failover’ has been added to the pg_create_logical_replication_slot function. The default value of this parameter is ‘false’. Replication slots with this parameter set to ’true’ can synchronize LSN information to the standby instance of streaming replication. The ‘failover’ column has been added to the pg_replication_slots catalog to store the added attribute values. [c393308]

PDF page 19

postgres=# \df pg_create_logical_replication_slot
List of functions
-[ RECORD 1 ]-------+---------------------------------------------------------
Schema              | pg_catalog
Name                | pg_create_logical_replication_slot
Result data type    | record
Argument data types | slot_name name, plugin name, temporary boolean DEFAULT
false, twophase boolean DEFAULT false, failover boolean DEFAULT false, OUT
slot_name name, OUT lsn pg_lsn
Type                | func
postgres=# SELECT pg_create_logical_replication_slot
('logislot1', 'test_decoding', false, false, true) ;
-[ RECORD 1 ]----------------------+----------------------
pg_create_logical_replication_slot | (logislot1,0/70004C0)

Subscription new attribute

The new attribute FAILOVER can now be specified for subscriptions. To store this attribute, a subfailover column has been added to the pg_subscription catalog. If this attribute is set to TRUE for the subscription, replication slots created on the primary server will also have the FAILOVER attribute at the same time.

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=dbsvr1 port=5432
dbname=postgres' PUBLICATION pub1 WITH (FAILOVER = TRUE) ;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT subname, subfailover FROM pg_subscription ;
 subname | subfailover
---------+-------------
 sub1    | t
(1 row)

PDF page 20

Logical Replication Slot Synchronization

The information of logical replication slots created on the primary server can be periodically synchronized to the standby server in streaming replication. [93db6cb] The following conditions are required on the streaming replication standby server to use this feature.

• Set the parameter sync_replication_slots to ‘on’ (default ‘off’)

• Set the parameter wal_level to ’logical’ (default ‘replica’)

• Use the replication slot with the ‘failover’ option specified

• Setting of the parameter primary_slot_name

• Parameter hot_standby_feedback set to ‘on’ (default ‘off’)

• Include ‘dbname’ setting in parameter primary_conninfo If the above conditions are met, the “slotsync worker” process is launched on the streaming replication instance. If the conditions are not met, the following log is output.

LOG:  slot synchronization requires hot_standby_feedback to be enabled
LOG:  slot synchronization requires primary_slot_name to be defined
ERROR:  slot synchronization requires dbname to be specified in primary_conninfo

The replication slot synchronization status is output to the log of the standby server. Below is a log
of the slot synchronization worker being started and the new replication slot being automatically
created.

LOG:  slot sync worker started
LOG:  newly created replication slot "sub1" is sync-ready now

Pg_sync_replication_slots function

PostgreSQL 17 adds the function pg_sync_replication_slots to synchronize logical replication slots on streaming replication standby servers. This function forces synchronization of replication slot information even in environments where the parameter sync_replication_slots is set to ‘off’. [ddd5f4f]

PDF page 21

postgres=# SELECT pg_sync_replication_slots() ;
 pg_sync_replication_slots
---------------------------
(1 row)

Use hash the index on subscribers

The subscribers can now use not only Btree indexes but also Hash indexes for update processing. [edca342]

Pg_logical_emit_message function

Added parameter ‘flush’ to control WAL flush. The default value is ‘false’, which means no messages will be flushed. [173b56f]

Syntax 1 - Pg_logical_emit_message function

pg_lsn pg_logical_emit_message(transactional boolean, prefix text, message
bytea|text, flush boolean DEFAULT false)

3.1.3 Streaming Replication Enhancements

Data to be transferred to the standby instance is now retrieved from the WAL buffer if possible. Previously, the data was reloaded from the WAL file after the writing was completed. [91f2cae]

3.1.4 Incremental Backup

The incremental backups are now available as a standard feature of PostgreSQL. [174c480, dc21234, ee1bfd1, d9ef650, f8ce4ed]

WAL summary

To use incremental backup, the WAL summarize feature must be enabled. The WAL summarize feature can be enabled by setting the parameter wal_level to ‘replica’ or ’logical’ and changing the parameter summarize_wal (default value ‘off’) to ‘on’. When this feature is enabled, a “walsummarizer” process is invoked in the instance and summary data of the WAL file is stored in the ${PGDATA}/pg_wal/summaries directory.

PDF page 22

$ ls data/pg_wal/summaries/
00000001000000000100002800000000010B2D50.summary
0000000100000000010B2D5000000000014E8508.summary
0000000100000000014E850800000000014E8608.summary
0000000100000000014E860800000000014EEF98.summary
0000000100000000014EEF9800000000014EF098.summary
0000000100000000014EF09800000000014EF3D0.summary
0000000100000000014EF3D00000000002000028.summary
0000000100000000020000280000000008000028.summary
…

The WAL summary file is automatically removed when the parameter wal_summary_keep_time
(default value '10d') is exceeded. The following functions are provided to retrieve WAL summary
information.

Syntax 2 - WAL summary retrieval functions

record pg_available_wal_summaries()
record pg_wal_summary_contents(tli bigint, start_lsn pg_lsn, end_lsn pg_lsn)
record pg_get_wal_summarizer_state()

PDF page 23

postgres=> SELECT * FROM pg_available_wal_summaries() ;
-[ RECORD 1 ]--------
tli       | 1
start_lsn | 0/5A77BB0
end_lsn   | 0/BF29980
…
postgres=> SELECT * FROM pg_wal_summary_contents(1, '0/5A77BB0', '0/BF29980') ;
-[ RECORD 1 ]--+-----
relfilenode    | 1259
reltablespace  | 1663
reldatabase    | 1
relforknumber  | 0
relblocknumber | 3
is_limit_block | f
…
postgres=> SELECT * FROM pg_get_wal_summarizer_state() ;
-[ RECORD 1 ]--+----------
summarized_tli | 1
summarized_lsn | 0/BF11AE8
pending_lsn    | 0/BF11BF0
summarizer_pid | 9876

Incremental Backup

The incremental backup is a feature that retrieves the difference the previous backup. The manifest file output during the previous backup is used to obtain an incremental backup. To execute the incremental backup, specify the manifest file of the previous backup in the –incremental option (abbreviated form -i) of the pg_basebackup command.

PDF page 24

$ pg_basebackup -D back.1
$ psql postgres demo
psql (17.0)
Type "help" for help.
postgres=> INSERT INTO data1 VALUES (generate_series(1, 1000000), 'data1') ;
INSERT 0 1000000
postgres=> \q
$ pg_basebackup -D back.inc1 --incremental=back.1/backup_manifest

The pg_combinebackup command has been added to merge base and incremental backups. To merge
incremental backups, the pg_combinebackup command specifies the directories where base and
incremental backup are stored, and the --output option (shortened -o) specifies the directory to be
merged. The directory to be merged must not exist or must be an empty directory.

$ pg_combinebackup back.1 back.inc1 --output=data.2
$ ls data.2
backup_label      pg_dynshmem    pg_serial     PG_VERSION
backup_manifest   pg_hba.conf    pg_snapshots  pg_wal
base              pg_ident.conf  pg_stat       pg_xact
current_logfiles  pg_logical     pg_stat_tmp   postgresql.auto.conf
global            pg_multixact   pg_subtrans   postgresql.conf
log               pg_notify      pg_tblspc
pg_commit_ts      pg_replslot    pg_twophase

Manifest file enhancements

The System Identifier is now output in the manifest file. The version of the manifest file has been changed to version 2. [2041bc4]

PDF page 25

$ pg_basebackup -D back.1
$ grep System-Identifier back.1/backup_manifest
"System-Identifier": 7345983777657046843,
$ grep Manifest-Version back.1/backup_manifest
{ "PostgreSQL-Backup-Manifest-Version": 2,

The pg_combinebackup command must specify a backup with the same System Identifier. In the
following example, an error occurs when specifying a difference retrieved from a different database.

$ pg_combinebackup back.a1 inc.b2 --output=data.1
pg_combinebackup: error: back.a1/global/pg_control: expected system identifier
7347556981648665444, but found 7347557495025884081

3.1.5 Partition Table Enhancements

The following new features have been implemented for partition tables.

Enhancements to Partition Pruning

The IS UNKNOWN, IS NOT UNKNOWN clause for boolean columns now supports partition pruning. [07c36c1]

Allow exclude constraints

Previously, only unique constraints using Btree were allowed for partitions. PostgreSQL 17 now also allows EXCLUDE CONSTRAINT with the same restrictions. However, column comparisons must be performed for equality. In the second example below, an error occurs because the partition key does not include the constraint target column. [8c852ba]

PDF page 26

postgres=> CREATE TABLE partc1 (col1 INT4RANGE,
EXCLUDE USING GIST (col1 WITH = )) PARTITION BY RANGE (col1) ;
CREATE TABLE
postgres=> CREATE TABLE partc2 (col1 INT4RANGE, col2 INT4RANGE,
EXCLUDE USING GIST (col2 WITH =)) PARTITION BY RANGE (col1) ;
ERROR:  unique constraint on partitioned table must include all partitioning
columns
DETAIL:  EXCLUDE constraint on table "partc3" lacks column "col1" which is part
of the partition key.

Support for IDENTITY column

The INSERT statements are now supported on partitions of partitioned tables with IDENTITY columns. The second INSERT statement in the example below will cause an error in PostgreSQL 16. [6995863]

postgres=> CREATE TABLE part1(c1 INT, c2 INT GENERATED ALWAYS AS IDENTITY)
PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 PARTITION OF part1
FOR VALUES FROM (0) TO (1000000) ;
CREATE TABLE
postgres=> INSERT INTO part1(c1) VALUES (100) ;
INSERT 0 1
postgres=> INSERT INTO part1v1(c1) VALUES (200) ; ← PostgreSQL 16 でエラー
INSERT 0 1
postgres=> SELECT * FROM part1 ;
 c1  | c2
-----+----
 100 |  1
 200 |  2
(2 rows)

PDF page 27

postgres=> INSERT INTO part1v1(c1) VALUES (200) ;
ERROR:  null value in column "c2" of relation "part1v1" violates not-null
constraint

3.1.6 Roles and Privileges

Predefined roles and privileges that can be granted have been added.

MAINTAIN privilege

The MAINTAIN privilege can now be granted on tables and materialized views. A role granted this privilege can execute VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and LOCK TABLE statements on the target relation. The result of the \dp meta-command in the psql command will output ’m’. [ecb0fd3]

postgres=# CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=# GRANT MAINTAIN ON data1 TO demo ;
GRANT
postgres=# \dp data1
                                 Access privileges
 Schema | Name  | Type  |     Access privileges      | Column privileges | …
--------+-------+-------+----------------------------+-------------------+-…
 public | data1 | table | postgres=arwdDxtm/postgres+|                   |
        |       |       | demo=m/postgres            |                   |
(1 row)
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> VACUUM data1 ;
VACUUM

PDF page 28

Addition of pg_maintain role

The pg_maintain predefined role has been added. This role is able to grant MAINTAIN privilege on all relations.

Modification of pg_monitor role

The predefined role pg_monitor has been granted permission to execute the pg_current_logfile function. [8d8afd4]

postgres=# CREATE USER demo PASSWORD '<<password>>' ;
CREATE ROLE
postgres=# GRANT pg_monitor TO demo ;
GRANT ROLE
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> SELECT pg_current_logfile() ;
          pg_current_logfile
--------------------------------------
 log/postgresql-2024-09-27_085557.log
(1 row)
postgres=> SELECT pg_current_logfile('csvlog') ;
 pg_current_logfile
--------------------
(1 row)

3.1.7 Built-in Locale Provider

PostgreSQL 17 has added a ‘built-in’ locale provider that is independent of external environments such as Libc or icu. The BUILTIN can now be specified in commands and SQL statements that specify a locale provider. [2d819a0, f69319f]

PDF page 29

postgres=# CREATE DATABASE builtdb LOCALE_PROVIDER BUILTIN LOCALE 'C.UTF-8'
TEMPLATE template0 ;
CREATE DATABASE
postgres=# SELECT datcollate, datctype, datlocale FROM pg_database
WHERE datname='builtdb' ;
-[ RECORD 1 ]-------
datcollate | C.UTF-8
datctype   | C.UTF-8
datlocale  | C.UTF-8

Currently, the only locales offered by this locale provider are C and C.UTF-8. The built-in locale
C.UTF-8 provides advantages versus the libc locale, such as faster sorting, faster case conversion, and
is platform-independent.

3.1.8 Event Trigger

The following features have been added to event triggers.

Disabling Event Triggers

  The parameter event_triggers has been added to disable the operation of event triggers. The

default value is ‘on’, which enables event triggers.

Login event trigger

The login event trigger, which is executed on successful authentication from the client, has been added. The following is an example of the event trigger that appends the connection time to the table. [e83d1b0]

PDF page 30

postgres=# CREATE OR REPLACE FUNCTION save_login()
   RETURNS event_trigger SECURITY DEFINER
   LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO public.login_history VALUES(current_timestamp);
END; $$ ;
CREATE FUNCTION
postgres=# CREATE EVENT TRIGGER login_trigger ON login
EXECUTE FUNCTION save_login() ;
CREATE EVENT TRIGGER
postgres=# ALTER EVENT TRIGGER login_trigger ENABLE ALWAYS ;
ALTER EVENT TRIGGER
postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=> SELECT * FROM login_history ;
           login
----------------------------
 2024-09-27 15:44:21.889142
(1 row)

Reindex DDL trigger

The DDL event triggers can now be generated at the start and end of the execution of REINDEX statements. [f21848d]

PDF page 31

postgres=# CREATE OR REPLACE FUNCTION reindex_start_func()
RETURNS event_trigger AS $$
BEGIN
   RAISE NOTICE 'START REINDEX: % %', tg_event, tg_tag ;
END; $$ LANGUAGE plpgsql ;
CREATE FUNCTION
postgres=# CREATE EVENT TRIGGER trg_reindex_start ON ddl_command_start
    WHEN TAG IN ('REINDEX')
     EXECUTE PROCEDURE reindex_start_func() ;
CREATE EVENT TRIGGER
postgres=# REINDEX INDEX idx1_data1 ;
NOTICE:  START REINDEX: ddl_command_start REINDEX
REINDEX

PDF page 32

postgres=# CREATE FUNCTION reindex_end_func()
RETURNS event_trigger AS $$
DECLARE
obj record ;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
   RAISE NOTICE 'REINDEX END: command_tag=% type=% identity=%',
   obj.command_tag, obj.object_type, obj.object_identity ;
END LOOP ;
END; $$ LANGUAGE plpgsql ;
CREATE FUNCTION
postgres=# CREATE EVENT TRIGGER trg_reindex_end ON ddl_command_end
     WHEN TAG IN ('REINDEX')
     EXECUTE PROCEDURE reindex_end_func() ;
CREATE EVENT TRIGGER
postgres=# REINDEX TABLE data1 ;
NOTICE:  START REINDEX: ddl_command_start REINDEX
NOTICE:  REINDEX END: command_tag=REINDEX type=index identity=public.idx1_data1
NOTICE:  REINDEX END: command_tag=REINDEX type=index identity=public.idx2_data1
REINDEX

3.1.9 Configuration File

The maximum length of tokens in the pg_hba.conf and pg_ident.conf files has been extended from 256 bytes to unlimited. In complex environments configured with LDAP, there were problems with exceeding the previous maximum length. [38df84c]

3.1.10 Log files

The following new changes have been implemented in the output of the server logs.

Logging of trust connections

When the parameter log_connections is set to “on”, logs are output for trust connections as follows. [38df84c]

PDF page 33

LOG:  connection received: host=[local]
LOG:  connection authorized: user=postgres database=postgres
 application_name=psql

LOG:  connection received: host=[local]
LOG:  connection authenticated: user="postgres" method=trust
 (/postgres/data/pg_hba.conf:117)
LOG:  connection authorized: user=postgres database=postgres
 application_name=psql

Replication Slot logging

The logs about replication slots are output at the LOG level (usually at the DEBUG1 level) when the parameter log_replication_commands is set to ‘on’. The following example shows the logs at the beginning and end of replication. [7c3fb50]

LOG:  received replication command: START_REPLICATION SLOT "slot1" 0/3000000
TIMELINE 1
LOG:  acquired physical replication slot "slot1"
STATEMENT:  START_REPLICATION SLOT "slot1" 0/3000000 TIMELINE 1
LOG:  released physical replication slot "slot1"

Recovery related logging

The logs at the start and end of the recovery process have been added. The following example shows the log at instance startup for streaming replication. [1d35f70]

LOG:  starting backup recovery with redo LSN 0/2000028, checkpoint LSN
0/2000080, on timeline ID 1
LOG:  entering standby mode
LOG:  redo starts at 0/2000028
LOG:  completed backup recovery with redo LSN 0/2000028 and end LSN 0/2000120

PDF page 34

3.1.11 Hook

The following Object Access Type (OAT) hooks have been added to the ALTER TABLE statement. [352ea3a]

• { ENABLE | DISABLE | [NO] FORCE } ROW LEVEL SECURITY

• { ENABLE | DISABLE } TRIGGER

• { ENABLE | DISABLE } RULE

3.1.12 Libpq

The following APIs have been added and enhanced.

Connect string sslnegotiation

The connection option sslnegotiation has been added. This option determines how the encryption method is negotiated during the client connection. This option can also be specified by the environment variable PGSSLNEGOTIATION. [d39a49c, fb5718f]

Close Portals and Statements

Functions to close portals and statements have been added. Functions with “send” in the name are non-blocking versions. [28b5726]

PDF page 35

Password change

The function PQchangePassword has been added to change the password of a connected user. [a7be2a6]

Changes to chunk mode

The PGresults now retrieves multiple tuples at once. The function PQsetChunkedRowsMode has been added to change the mode accordingly. [4643a2b]

Pipeline Synchronization

The PQsendPipelineSync function has been added. This function is similar to the PQpipelineSync function, but it does not flush a synchronization message to the server unless the output buffer reaches a threshold value. [4794c2]

Sequence operation

The functions have been added to manipulate sequences, such as the relation_open function. [449e798]

Injection Points

The following functions have been added to support injection points. [d86d20f]

Non-Blocking cancel functions

Previously, the PQcancel API used blocking requests. It is now possible to send a cancel request without blocking. [61461a3]

PDF page 36

Background Worker

The flag BGWORKER_BYPASS_ROLELOGINCHECK can now be specified in the functions BackgroundWorkerInitializeConnection and BackgroundWorkerInitializeConnectionBy-Oid. This flag allows bypassing the role login check. [e768919]

DSM Registry

The function GetNamedDSMSegment has been added to allow shared libraries to easily use shared memory. Previously, shared libraries specified in the parameter shared_preload_libraries had to request shared memory via the shmem_request_hook hook. [8b2bcf3]

DSA Initial Size

Previously, DSAs were created at 1 MB and the maximum is still extended to DSA_MAX_SEGMENT_SIZE. The dsa_create_ext and dsa_create_in_place_ext functions have been added to allow the initial and maximum sizes to be specified. [bb952c8]

Streaming I/O

Abstracts access to relations and allows relation data to be accessed as a stream of buffers. This modification will make it easier to make changes in the future when implementations such as asynchronous I/O are added. It is used to execute the ANALYZE statement and perform a sequential scans on the table. [b7b0f3f, 041b968, b5a9b18]

PDF page 37

Multi-block read

The multi-block version of the ReadBuffer API is now provided. The maximum number of blocks is determined by the new parameter io_combine_limit. In the future, asynchronous I/O may be used internally. [210622c]

Bump Memory Allocation

The memory allocator BumpContext has been added, which is suitable for memory allocations with short life times. BumpContext memory is now used for sorting tuples and for VACUUM processing. [29f6a95, 6ed83d5, 8a1b31e]

PDF page 38

Polling file descriptor

The PQsocketPoll function has been provided to monitor file descriptors. [f5e4ded]

3.1.13 Access Method

The following enhancements have been added to the access methods.

Parallel build of BRIN index

The multiple worker processes can now be used to build BRIN indexes. The amcanbuildparallel flag has been added to the index access method structure IndexAmRoutine. [b437571]

heap_page_prune function

The parameters of the HEAP_PAGE_PRUNE function have been changed to allow multiple options to be specified in a bitmap. Currently, only HEAP_PAGE_PRUNE_MARK_UNUSED_NOW and HEAP_PAGE_PRUNE_FREEZE options are available. [3d0f730, 6dbb490]

3.1.14 Wait Events

The following features have been added for wait events.

PDF page 39

Modified Wait Events

The following wait events have been added or changed. [fa88928, c9af054, c8e318b, 0013ba2, 5c430f9, d86d20f]

PDF page 40

List of Wait Events

The list of wait events is now written in the file src/backend/utils/activity/wait_event_names.txt and the code and documentation are generated automatically. [59cbf60]

PDF page 41

…
Section: ClassName - WaitEventClient
CLIENT_READ   "Waiting to read data from the client."
CLIENT_WRITE    "Waiting to write data to the client."
…

Custom Type

The method of registering custom wait events has been changed. New API “uint32 WaitEventExtensionNew(const char *wait_event_name)” is provided. With this, the shared memory hook is no longer required. [af720b4]

3.1.15 FILLFACTOR

The FILLFACTOR is now considered in the estimated number of tuples when table statistics are not gathered. Previously, FILLFACTOR was not considered, which could result in an overestimation of the number of tuples. [29cf61a]

postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) WITH (FILLFACTOR=50) ;
CREATE TABLE
postgres=> INSERT INTO data1 VALUES (generate_series(1, 100000), 'data1') ;
INSERT 0 100000
postgres=> EXPLAIN SELECT COUNT(*) FROM data1 ;
                            QUERY PLAN
------------------------------------------------------------------
 Aggregate  (cost=1875.08..1875.09 rows=1 width=8)
   ->  Seq Scan on data1  (cost=0.00..1717.46 rows=63046 width=0)
(2 rows)

PDF page 42

postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) WITH (FILLFACTOR=50) ;
CREATE TABLE
postgres=> INSERT INTO data1 VALUES (generate_series(1, 100000), 'data1') ;
INSERT 0 100000
postgres=> EXPLAIN SELECT COUNT(*) FROM data1 ;
                            QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=2663.15..2663.16 rows=1 width=8)
   ->  Seq Scan on data1  (cost=0.00..2347.92 rows=126092 width=0)
(2 rows)

3.1.16 VACUUM

The TidStore is now used to store tuple IDs for VACUUM instead of a simple array. This change reduces the amount of memory used, which used to be larger, and eliminates the 1 GB limit. In addition, some column names in the pg_stat_progress_vacuum view have been changed. [667e65a]

3.1.17 LLVM

PostgreSQL 17 now supports LLVM 17, 18. Backported to supported older versions of PostgreSQL. [76200e5, d282e88]

3.1.18 Acceleration

The CRC calculation function COMP_CRC32C can now be executed natively in environments that support LoongArch. [4d14ccd] In environments where the Intel AVX-512 instruction set can be used, visibilitymap_count and pg_popcount functions can now be executed faster. [41c51f0, 792752a]

3.1.19 UNICODE

PostgreSQL 17 has the following changes in Unicode support. [9d17e5f, 9e2e4f0, ad49994]

• Unicode version changed from 15.0.0 to 15.1.0 (https://unicode.org/versions/Unicode15.1.0/)

PDF page 43

• Unicode CLDR version changed from 43 to 45 (https://cldr.unicode.org/index/downloads/cldr-45)

• Add update-unicode build target to update UNICODE table