3 Architecture

Source chapter 3.1 for PostgreSQL 9.6.

Beta 1Source pages: PDF 8-17Original PDF on GitHub

PDF page 8

3.1.1 Added System Catalogs

Along with the addition of new features, the following system catalogs have been added.

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

Table 4 Added system Catalogs

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.

Column Name	Data Type	Description
namesetting	text	Macro name Setting value

Table 5 pg_config Catalog

Example 1 Query for pg_config Catalog

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

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.

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

Table 6 pg_stat_wal_receiver Catalog

This catalog is made up by the result of pg_stat_get_wal_receiver function.

Example 2 Query for pg_stat_wal_receiver Catalog

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

This catalog provides information about the state of the progress of vacuum processing. This catalog is readable by the general superuser.

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

Table 7 pg_stat_progress_vacuum Catalog

Example 3 Query for pg_stat_progress_vacuum Catalog

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

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.

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

Table 8 pg_init_privs Catalog

3.1.2 Modified Catalogs

The following system catalogs have been changed.

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

Table 9 Modified catalogs

pg_replication_slots Catalog

The confirmed_flush_lsn column has been added.

Column Name	Data Type	Description
confirmed_flush_lsn	pg_lsn	Receiving LSN information of the logical slot

Table 10 Added column to pg_replication_slots Catalog

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

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

Table 11 Added columns to pg_stat_activity Catalog

Please refer to the following URL information about wait events. https://www.postgresql.org/docs/9.6/static/monitoring-stats.html

pg_proc Catalog

Proparallel column that shows whether procedure is PARALLEL SAFE or PARALLEL UNSAFE has been added.

Column Name	Data Type	Description
proparallel	char	Parallel Safe = ’s’ function, Restricted Parallel Safe function = ‘r’ (Only leader can execute), Parallel Unsafe function = ‘u’

Table 12 Added column to pg_proc Catalog

pg_aggregate Catalog

The following column has been added.

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

Table 13 Added columns to pg_aggregate Catalog

pg_am Catalog

pg_am catalog change completely, became simple.

Column Name	Data Type	Description
amname	name	Access method name
amhandler	oid	OID of handler function
amtype	char	Type of access method

Table 14 Columns of pg_am Catalog

PDF page 13

  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).

3.1.3 Modified Contrib modules

In PostgreSQL 9.6, some Contrib modules have been changed.

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

Table 15 Changed in Contrib modules

Module Changed Note auto_explain Add parameter Sample_rate parameter has been added

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

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

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

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)

LOG:  duration: 0.072 ms  statement: START TRANSACTION ISOLATION LEVEL
REPEATABLE READ
LOG:  duration: 156.616 ms  parse <unnamed>: DECLARE c1 CURSOR FOR
        SELECT c1, c2 FROM public.table1
LOG:  duration: 0.102 ms  bind <unnamed>: DECLARE c1 CURSOR FOR
        SELECT c1, c2 FROM public.table1
LOG:  duration: 0.039 ms  execute <unnamed>: DECLARE c1 CURSOR FOR
        SELECT c1, c2 FROM public.table1
LOG:  duration: 0.272 ms  statement: FETCH 300 FROM c1
LOG:  duration: 0.202 ms  statement: FETCH 300 FROM c1
LOG:  duration: 0.028 ms  statement: CLOSE c1
LOG:  duration: 0.038 ms  statement: COMMIT TRANSACTION

EXTENSIONS option for the remote instance can be specified now.

Example 6 Specify extensions option

postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remsvr1', port '5433', dbname 'postgres',
extensions 'hstore') ;
CREATE SERVER

PDF page 15

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.

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

Table 16 Functions in the pg_visibility module

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

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

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

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

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

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

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

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.