PDF page 8
#### 3.1.1 Added System Catalogs {#pg9-6-3-1-1-added-system-catalogs} Along with the addition of new features, the following system catalogs have been added. **Table 4.** Added system Catalogs | Catalog Name | Description | | --- | --- | | pg_config | PostgreSQL binary’s install information | | pg_stat_wal_receiver | Activity of wal receiver process | | pg_stat_progress_vacuum | Activity of vacuum process progress | | pg_init_privs | Initial privilege settings for object | ##### pg_config Catalog {#pg9-6-p8-pg-config-catalog} This catalog exposes the information of various macros that are specified at compile time of PostgreSQL binary. Previously, pg_config command provides the information. Entity of this catalog is pg_config function. This catalog is only readable by a superuser. **Table 5.** pg\_config Catalog | Column Name | Data Type | Description | | --- | --- | --- | | namesetting | text | Macro name Setting value | **Example 1.** Query for pg\_config Catalog ```text postgres=# SELECT * FROM pg_config ; name | setting -------------------+--------------------------------------------------- BINDIR | /usr/local/pgsql/bin DOCDIR | /usr/local/pgsql/share/doc HTMLDIR | /usr/local/pgsql/share/doc INCLUDEDIR | /usr/local/pgsql/include PKGINCLUDEDIR | /usr/local/pgsql/include … ```PDF page 9
##### pg_stat_wal_receiver Catalog {#pg9-6-p9-pg-stat-wal-receiver-catalog} This catalog provides information about the state of a slave instance's WAL receiver process in replication environment. This catalog is readable by the general user. **Table 6.** pg\_stat\_wal\_receiver Catalog | Column Name | Data Type | Description | | --- | --- | --- | | pid | integer | Process ID of wal receiver | | status | text | Activity Status | | receive_start_lsn | pg_lsn | First transaction log position | | receive_start_tli | integer | First timeline number | | received_lsn | pg_lsn | Last transaction log position already received and flushed to disk | | received_tli | integer | Timeline number of last transaction log position received and flushed to disk | | last_msg_send_time | timestamp with time zone | Send time of last message received | | last_msg_receipt_time | timestamp with time zone | Receipt time of last message received | | latest_end_lsn | pg_lsn | Last transaction log position reported | | latest_end_time | timestamp with time zone | Time of last transaction log position reported | | slot_name | text | Replication slot name | This catalog is made up by the result of pg_stat_get_wal_receiver function. **Example 2.** Query for pg\_stat\_wal\_receiver Catalog ```text postgres=# SELECT * FROM pg_stat_wal_receiver ; -[ RECORD 1 ]---------+------------------------------ pid | 2782 status | streaming receive_start_lsn | 0/36000000 receive_start_tli | 1 received_lsn | 0/36000000 received_tli | 1 last_msg_send_time | 2016-05-15 11:49:09.753784+09 last_msg_receipt_time | 2016-05-15 11:49:09.753879+09 latest_end_lsn | 0/360000D0 … ```PDF page 10
##### pg_stat_progress_vacuum Catalog {#pg9-6-p10-pg-stat-progress-vacuum-catalog} This catalog provides information about the state of the progress of vacuum processing. This catalog is readable by the general superuser. **Table 7.** pg\_stat\_progress\_vacuum Catalog | Column Name | Data Type | Description | | --- | --- | --- | | pid | integer | Process ID of backend | | datid | oid | OID of database | | datname | name | Connected database name | | relid | oid | OID of the table being vacuumed | | phase | text | Current processing phase of vacuum | | heap_blks_total | bigint | Total number of heap blocks in the table | | heap_blks_scanned | bigint | Number of heap blocks scanned | | heap_blks_vacuumed | bigint | Number of heap blocks vacuumed | | index_vacuum_count | bigint | Number of completed index vacuum cycles | | max_dead_tuples | bigint | Number of dead tuples that we can store before needing to perform an index vacuum cycle | | num_dead_tuples | bigint | Number of dead tuples collected since the last index vacuum cycle | **Example 3.** Query for pg\_stat\_progress\_vacuum Catalog ```text postgres=# SELECT * FROM pg_stat_progress_vacuum ; -[ RECORD 1 ]------+-------------- pid | 3184 datid | 16385 datname | demodb relid | 16398 phase | scanning heap heap_blks_total | 10052 heap_blks_scanned | 2670 heap_blks_vacuumed | 2669 index_vacuum_count | 0 max_dead_tuples | 291 num_dead_tuples | 185 ```PDF page 11
##### pg_init_privs Catalog {#pg9-6-p11-pg-init-privs-catalog} The pg_init_privs catalog stores the information about the initial privileges of objects which have non-default value in the database. This catalog is readable by the general superuser. **Table 8.** pg\_init\_privs Catalog | Column Name | Data Type | Description | | --- | --- | --- | | objoid | oid | The OID of the specific object | | classoid | oid | The OID of the system catalog the object is in | | objsubid | integer | For a table column, this is the column number | | privtype | char | The type of initial privilege of this object | | initprivs | aclitem[] | The initial access privileges | #### 3.1.2 Modified Catalogs {#pg9-6-3-1-2-modified-catalogs} The following system catalogs have been changed. **Table 9.** Modified catalogs | Catalog Name | Changed | | --- | --- | | pg_replication_slots | Add confirmed_flush_lsn column | | pg_stat_activity | Remove waiting column Add wait_event_type column and wait_event column | | pg_proc | Add proparallel column | | pg_aggregate | Stores information about aggregate functions | | pg_am | Stores information about index access methods | ##### pg_replication_slots Catalog {#pg9-6-p11-pg-replication-slots-catalog} The confirmed_flush_lsn column has been added. **Table 10.** Added column to pg\_replication\_slots Catalog | Column Name | Data Type | Description | | --- | --- | --- | | confirmed_flush_lsn | pg_lsn | Receiving LSN information of the logical slot | ##### pg_stat_activity Catalog {#pg9-6-p11-pg-stat-activity-catalog} The "waiting" column that shows only the waiting state is replaced with the wait_event column and the wait_event_type column that show the wait events.PDF page 12
**Table 11.** Added columns to pg\_stat\_activity Catalog | Column Name | Data Type | Description | | --- | --- | --- | | wait_event_typewait_event | text | The type of event for which the backend is waiting Wait event name if backend is currently waiting | Please refer to the following URL information about wait events. [https://www.postgresql.org/docs/9.6/static/monitoring-stats.html](https://www.postgresql.org/docs/9.6/static/monitoring-stats.html) ##### pg_proc Catalog {#pg9-6-p12-pg-proc-catalog} Proparallel column that shows whether procedure is PARALLEL SAFE or PARALLEL UNSAFE has been added. **Table 12.** Added column to pg\_proc Catalog | Column Name | Data Type | Description | | --- | --- | --- | | proparallel | char | Parallel Safe = 's' function, Restricted Parallel Safe function = 'r' (Only leader can execute), Parallel Unsafe function = 'u' | ##### pg_aggregate Catalog {#pg9-6-p12-pg-aggregate-catalog} The following column has been added. **Table 13.** Added columns to pg\_aggregate Catalog | Column Name | Data Type | Description | | --- | --- | --- | | aggcombinefn | regproc | Combine function (zero if none) | | aggserialfn | regproc | Serialization function (zero if none) | | aggdeserialfn | regproc | Deserialization function (zero if none) | | aggserialtype | oid | Return data type of the aggregate function's serialization function | ##### pg_am Catalog {#pg9-6-p12-pg-am-catalog} pg_am catalog change completely, became simple. **Table 14.** Columns of pg\_am Catalog | Column Name | Data Type | Description | | --- | --- | --- | | amname | name | Access method name | | amhandler | oid | OID of handler function | | amtype | char | Type of access method |PDF page 13
```text At the time of writing ( June 6, 2016), there is no description about amtype column on the manual ``` ([http://www.postgresql.org/docs/9.6/static/catalog-pg-am.html](http://www.postgresql.org/docs/9.6/static/catalog-pg-am.html)). #### 3.1.3 Modified Contrib modules {#pg9-6-3-1-3-modified-contrib-modules} In PostgreSQL 9.6, some Contrib modules have been changed. **Table 15.** Changed in Contrib modules | postgres_fdw | Add options | Fetch_size option has been added | | --- | --- | --- | | | | Extensions option has been added | | pg_visibility | Add module | Provide Visibility Map information | | bloom | Add module | Index that uses a Bloom filter | | sslinfo | Add function | Add ssl_extension_info function | | tsearch2 | Enhancement | Add operators for phrase search | | pg_trgm | Enhancement | Add support for "word similarity". Add configuration parameter pg_trgm.similarity_threshold | | pgcrypto | Add function | Add an optional S2K iteration count parameter | | pgpageinspect | Add output | Output data added to the heap_page_items function | | hstore | Add function | Add functions for json type | Module Changed Note auto_explain Add parameter Sample_rate parameter has been added ##### Enhancement of auto_explain module {#pg9-6-p13-enhancement-of-auto-explain-module} Parameter auto_explain.sample_ratio has been added to the auto_explain module. This parameter specifies the percentage of SQL statements to log the execution plan. For example, the execution plan to log by auto_explain.log_min_duration parameter will be reduced to the percentage specified by this parameter. The default value is 1 (= 100%). ##### Enhancement of postgres_fdw module {#pg9-6-p13-enhancement-of-postgres-fdw-module} The fetch size to get tuples by SELECT statement can be specified now. This option can be specified for each SERVER or FOREIGN TABLE. The fetch size in the previous postgres_fdw module was fixed to 100.PDF page 14
**Example 4.** fetch\_size option ```text postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remhost1', port '5432', dbname 'demodb', fetch_size '300') ; CREATE SERVER postgres=# CREATE FOREIGN TABLE table1(c1 NUMERIC, c2 VARCHAR(10)) SERVER remsvr OPTIONS(fetch_size '300') ; CREATE FOREIGN TABLE ``` ```text The following example shows the actually executed SQL statement at the remote instance when "SELECT * FROM table1" statement were executed. ``` **Example 5.** Executed SQL statement \(from log file\) ```sql LOG: duration: 0.072 ms statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ LOG: duration: 156.616 ms parsePDF page 15
##### Add pg_visibility module {#pg9-6-p15-add-pg-visibility-module} Pg_visibility modules that can get the information of the Visibility Map has been added. The following functions are provided. The execution requires superuser privileges. **Table 16.** Functions in the pg\_visibility module | Function name | Description | | --- | --- | | pg_visibility | View the status of each block of the specified table | | pg_visibility_map | View the status of each block of the specified table | | pg_visibility_map_summary | Display the status of the specified table | In the following example, the number of VISIBLE blocks and FREEZEed blocks is gotten by the pg_visibility_map_summary function . **Example 7.** pg\_visibility module ```text postgres=# CREATE EXTENSION pg_visibility ; CREATE EXTENSION postgres=# SELECT pg_visibility_map_summary('data1') ; pg_visibility_map_summary --------------------------- (5406,5406) (1 row) ``` ##### Add bloom module {#pg9-6-p15-add-bloom-module} Bloom module has been added to the Contrib modules. Indexes that use the Bloom Filter can be created by loading the bloom module. Using this module, it is possible to create an index for a large number of column at the same time that uses only a relatively small capacity of the storage. By simple verification, if the BTree index is created on the column described in the WHERE clause, the BTree index is used. To use the bloom module, it is necessary to specify the USING bloom clause in the CREATE INDEX statement. There are "size", "col1", "col2", … options, but use of them has not yet been verified.PDF page 16
**Example 8.** Creating a bloom module and the execution plan ```text postgres=# CREATE EXTENSION bloom ; CREATE EXTENSION postgres=> CREATE TABLE bloom1(c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER, c5 INTEGER); CREATE TABLE postgres=> CREATE INDEX bl1_bloom1 ON bloom1 USING bloom (c1, c2, c3, c4, c5) ; CREATE INDEX postgres=> EXPLAIN ANALYZE SELECT * FROM bloom1 WHERE c1 = 10000 AND c5 = 10000 ; QUERY PLAN ----------------------------------------------------------------------- Bitmap Heap Scan on bloom1 (cost=17848.00..17852.02 rows=1 width=20) (actual time=8 .376..8.538 rows=1 loops=1) Recheck Cond: ((c1 = 10000) AND (c5 = 10000)) Rows Removed by Index Recheck: 76 Heap Blocks: exact=76 -> Bitmap Index Scan on bl1_bloom1 (cost=0.00..17848.00 rows=1 width=0) (actual time=8.337..8.337 rows=77 loops=1) Index Cond: ((c1 = 10000) AND (c5 = 10000)) Planning time: 0.123 ms Execution time: 8.579 ms (8 rows) ``` ##### Enhancement of pageinspect module {#pg9-6-p16-enhancement-of-pageinspect-module} The real data (t_data) column has been added to the output of the heap_page_items function.PDF page 17
**Example 9.** Execution of heap\_page\_items function ```text postgres=# CREATE EXTENSION pageinspect ; CREATE EXTENSION postgres=# SELECT lp,t_data FROM heap_page_items( get_raw_page('insp1', 0)) ; lp | t_data ----+------------------------ 1 | \x0b008064000b696e6974 2 | \x0b0080c8000b696e6974 (2 rows) ``` #### 3.1.4 Avoid Full-Table Vacuum {#pg9-6-3-1-4-avoid-full-table-vacuum} PostgreSQL manages the age of transaction (XID) by the unsigned 32-bit integer. When huge number of transactions are executed, it may be run out of 32-bit integer. For this reason, before running out of the transaction ID, PostgreSQL updates old XIDs in the database to the special XID (FrozenXID = 2). This process is called FREEZE. In FREEZE process of previous version, when it exceeds the specified age with the parameter autovacuum_freeze_max_age, full scan for the table was executed regardless of whether the table was updated or not. In PostgreSQL 9.6, by extending the Visibility Map to identify the block to be FREEZEed, now it is possible to prevent the large-scale I/O due to the full scan. #### 3.1.5 Improve CHECKPOINT {#pg9-6-3-1-5-improve-checkpoint} Checkpoint of the previous PostgreSQL searched dirty pages in the shared buffer randomly and wrote them to file. In PostgreSQL 9.6, it divides the dirty pages for each table space, sort them by file and block number and writes them. This will allow checkpoint process to write more sequentially.