# Architecture

> Source chapter 3.4 for PostgreSQL 11.

---

LLMS index: [llms.txt](/en/llms.txt)

---

<div class="feature-source-note"><span class="feature-edition-badge">GA</span><span><strong>Source pages:</strong> PDF 36-43</span><span><a href="https://github.com/nori-shinoda/documents/blob/1773200c5362476c4a549222b7d17fa91f546cae/postgresql11_ga_new_features_en_20181022-1.pdf">Original PDF on GitHub</a></span></div>

<p class="feature-page-marker" id="pgfeat-11-architecture-pdf-page-36">PDF page 36</p>

#### 3.4.1 Modified catalogs {#pg11-3-4-1-modified-catalogs}

The following catalogs have been changed.

**Table 11.** System catalogs with columns added

| Catalog name | Added column | Data Type Description |  |
| --- | --- | --- | --- |
| pg_aggregate | aggfinalmodify | char | Whether the aggfinalfn function changes the value |
|  | aggmfinalmodify | char | Whether the aggmfinalfn function changes the value |
| pg_attribute | atthasmissing | bool | Have a default value that has not updated the page |
|  | attmissingval | anyarray | Default value not updating page |
| pg_class | relrewrite | oid | OID when the new relation is created during DDL execution |
| pg_constraint | conparentid | oid | Parent partition constraint OID |
| pg_index | indnkeyatts | smallint | Number of key columns |
| pg_partitioned_table | partdefid | oid | OID of the default partition |
| pg_proc | prokind | char | Object kind f: function p: procedure a: aggregate function w: window function |
| pg_publication | pubtruncate | boolean | TRUNCATE propagation |
| pg_stat_wal_receiver | sender_host | text | Connection destination hostname |
|  | sender_port | integer | Connection destination port number |
| information_schema.table_constraints | enforced | yes_or_no | Reserved for future use |

<p class="feature-page-marker" id="pgfeat-11-architecture-pdf-page-37">PDF page 37</p>

**Table 12.** Catalogs with columns deleted

| Catalog name | Deleted column | Description |
| --- | --- | --- |
| pg_class | relhaspkey | Have primary key |
| pg_proc | proisagg | Is aggregate function |
|  | proiswindow | Is Window function |

**Table 13.** Value is now stored information\_schema catalog of the schema

| Catalog name | Column name | Description |
| --- | --- | --- |
| triggers | action_order | Trigger execution order |
|  | action reference new table___ | "NEW" transition table name |
|  | action reference old table___ | "OLD" transition table name |
| tables | table_type | FOREIGN stored for the foreign table (previously FOREIGN TABLE stored) |

##### pg_stat_activity catalog {#pg11-p37-pg-stat-activity-catalog}

Backend_type column and process name are now synchronized. The value of the backend_type column of the replication launcher process was "background worker" in PostgreSQL 10, but in PostgreSQL 11 it is "logical replication launcher".

**Example 42.** Refer pg\_stat\_activity catalog

```text
postgres=# SELECT pid,wait_event, backend_type FROM pg_stat_activity ;
  pid  |     wait_event      |         backend_type
-------+---------------------+------------------------------
 17099 | LogicalLauncherMain | logical replication launcher
 17097 | AutoVacuumMain      | autovacuum launcher
 17101 |                     | client backend
 17095 | BgWriterHibernate   | background writer
 17094 | CheckpointerMain    | checkpointer
 17096 | WalWriterMain       | walwriter
(6 rows)
```

##### pg_attribute catalog {#pg11-p37-pg-attribute-catalog}

In PostgreSQL 11, columns with the DEFAULT value and NOT NULL constraint can now be added without updating the actual tuples. Along with this, the columns for this feature have been added to the pg_attribute catalog.

<p class="feature-page-marker" id="pgfeat-11-architecture-pdf-page-38">PDF page 38</p>

**Example 43.** Refer pg\_attribute catalog

```text
postgres=> ALTER TABLE cols1 ADD COLUMN c3 INT NOT NULL DEFAULT 10 ;
ALTER TABLE
postgres=> SELECT atthasmissing, attmissingval FROM pg_attribute
WHERE attname='c3' ;
 atthasmissing | attmissingval
---------------+---------------
 t             | {10}
(1 row)
```

#### 3.4.2 Added ROLE {#pg11-3-4-2-added-role}

The following roles were added. These roles are mainly used to allow general users to execute COPY statements and execute the file_fdw Contrib module.

**Table 14.** Added ROLE

| Role name | Description |
| --- | --- |
| pg_execute_server_program | Execute programs on the server |
| pg_read_server_files | Read files on the server |
| pg_write_server_files | Write files on the server |

**Example 44.** pg\_read\_server\_files role

```text
postgres=# GRANT pg_read_server_files TO user1 ;
GRANT ROLE
postgres(user1)=> COPY copy1 FROM '/tmp/copy1.csv' CSV ;
COPY 2000
```

#### 3.4.3 LLVM integration {#pg11-3-4-3-llvm-integration}

PostgreSQL 11 supports JIT compilation using LLVM ([https://llvm.org/](https://llvm.org/)) to speed up long-running SQL statements caused by processor bottlenecks. SQL statements estimated to exceed more than a certain amount of cost are compiled beforehand and then executed.

##### Installation {#pg11-p38-installation}

In order to use LLVM, it is necessary to specify the option --with-llvm of "configure" command at

<p class="feature-page-marker" id="pgfeat-11-architecture-pdf-page-39">PDF page 39</p>

the time of installation. When executing "configure" command, "llvm-config" command and "clang" command must be included in command execution path (or specified in environment variable LLVM_CONFIG and environment variable CLANG).

##### The behavior of JIT compilation {#pg11-p39-the-behavior-of-jit-compilation}

For SQL statements whose total execution cost exceeds the parameter jit_above_cost (default value 100000), the JIT compilation feature by LLVM runs. If this parameter is specified as "-1" or the parameter jit is set to "off", the JIT feature will be disabled. The behavior of JIT compilation processing is changed by inlining (parameter jit_inline_above_cost) and optimization (parameter jit_optimize_above_cost).

##### Execution plan {#pg11-p39-execution-plan}

By executing EXPLAIN statements for SQL statement using the JIT compilation feature, information starting from "JIT:" is displayed.

**Example 45.** Execution plan of SQL using JIT compilation features

```text
postgres=> EXPLAIN ANALYZE SELECT COUNT(*) FROM jit1 ;
                                    QUERY PLAN
------------------------------------------------------------------------
Aggregate  (cost=179053.25..179053.26 rows=1 width=8)
(actual time=2680.558..2680.559 rows=1 loops=1)
   ->  Seq Scan on jit1  (cost=0.00..154053.60 rows=9999860 width=0)
(actual time=0.022..1424.095 rows=10000000 loops=1)
 Planning Time: 0.024 ms
 JIT:
   Functions: 2
   Generation Time: 1.505 ms
   Inlining: false
   Inlining Time: 0.000 ms
   Optimization: false
   Optimization Time: 0.594 ms
   Emission Time: 8.688 ms
 Execution Time: 2682.166 ms
(12 rows)
```

```text
In order to enable the JIT compile feature, it is necessary to set the parameter "jit" to "on" in advance.
```

<p class="feature-page-marker" id="pgfeat-11-architecture-pdf-page-40">PDF page 40</p>

#### 3.4.4 Predicate locking for GIN / GiST / HASH index {#pg11-3-4-4-predicate-locking-for-gin-gist-hash-index}

Predicate locks are now available for GIN index, GiST index, HASH index. This feature reduces the lock range, improving the concurrency of SQL statements with multiple sessions. The following example is the result of verification using the HASH index. In PostgreSQL 10, the lock range is the relation, but in PostgreSQL 11 it is found to be page.

**Example 46.** Test case

```text
postgres=> CREATE TABLE lock1(c1 INT, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=> CREATE INDEX idx1_lock1 ON lock1 USING hash(c1) ;
CREATE INDEX
postgres=> INSERT INTO lock1 VALUES (generate_series(1, 100000),
'data1') ;
INSERT 0 100000
postgres=> BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=> SELECT * FROM lock1 WHERE c1=10000 FOR UPDATE ;
  c1   |  c2
-------+-------
 10000 | data1
(1 row)
```

**Example 47.** Lock status on PostgreSQL 10

```text
postgres=> SELECT locktype, relation::regclass, mode FROM pg_locks ;
   locktype    |  relation  |      mode
---------------+------------+-----------------
 relation      | pg_locks   | AccessShareLock
 relation      | idx1_lock1 | AccessShareLock
 relation      | lock1      | RowShareLock
 virtualxid    |            | ExclusiveLock
 transactionid |            | ExclusiveLock
 tuple         | lock1      | SIReadLock
 relation      | idx1_lock1 | SIReadLock
(7 rows)
```

<p class="feature-page-marker" id="pgfeat-11-architecture-pdf-page-41">PDF page 41</p>

**Example 48.** Lock status on PostgreSQL 11

```text
postgres=> SELECT locktype, relation::regclass, mode FROM pg_locks ;
   locktype    |  relation  |      mode
---------------+------------+-----------------
 relation      | pg_locks   | AccessShareLock
 relation      | idx1_lock1 | AccessShareLock
 relation      | lock1      | RowShareLock
 virtualxid    |            | ExclusiveLock
 transactionid |            | ExclusiveLock
 page          | idx1_lock1 | SIReadLock
 tuple         | lock1      | SIReadLock
(7 rows)
```

#### 3.4.5 Enhanced LDAP authentication {#pg11-3-4-5-enhanced-ldap-authentication}

"ldapsearchfilter" attribute has been added to the LDAP authentication parameter described in the pg_hba.conf file. LDAP server can be searched more flexibe than "ldapsearchattribute" attribute.

#### 3.4.6 Extended Query timeout {#pg11-3-4-6-extended-query-timeout}

The timeout of conventional Extended Queries was determined by the time until SYNC messages were sent after sending multiple SQL statements. PostgreSQL 11 now takes into account the execution time of individual SQL statements. The example below is the definition file of pgproto ([https://github.com/tatsuo-ishii/pgproto](https://github.com/tatsuo-ishii/pgproto)). In PostgreSQL 10 timeout occurred even if "SET statement_timeout = 4s" was set.

<p class="feature-page-marker" id="pgfeat-11-architecture-pdf-page-42">PDF page 42</p>

**Example 49.** pgproto definition file

```bash
# Test case for statement timeout patch.
'Q'
"SET statement_timeout = '4s'"
'Y'
# Execute statement which takes 3 seconds.
'P'
"S1"
"SELECT pg_sleep(3)" 0
'B'
""
"S1"
0
0
0
PostgreSQL 11
'E'
""
0
Timeout check
'C'
'S'
"S1"
# Execute statement which takes 2 seconds.
'P'
"S2"
"SELECT pg_sleep(2)" 0
PostgreSQL 10
'B'
""
"S2"
0
0
0
PostgreSQL 11
Timeout check
'E'
""
0
Timeout check
'C'
'S'
"S2"
# Issue Sync message and terminate
'S'
'Y'
'X'
```

#### 3.4.7 Change of backup label file {#pg11-3-4-7-change-of-backup-label-file}

A timeline ID is added to the backup_label file created when online backup is executed.

<p class="feature-page-marker" id="pgfeat-11-architecture-pdf-page-43">PDF page 43</p>

**Example 50.** backup\_label file

```text
postgres=# SELECT pg_start_backup(now()::text) ;
pg_start_backup
-----------------
 0/2000060
(1 row)
postgres-# \! cat data/backup_label
START WAL LOCATION: 0/6A000028 (file 00000001000000000000006A)
CHECKPOINT LOCATION: 0/6A000098
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2018-10-22 08:59:10 JST
LABEL: 2018-10-22 08:59:10.132746+09
START TIMELINE: 1
<- Added line
```

#### 3.4.8 Using Huge Pages under Windows environment {#pg11-3-4-8-using-huge-pages-under-windows-environment}

The "Lock Pages In Memory" setting is now available in the Microsoft Windows environment. When parameter huge_pages is set to "try" or "on", shared memory will be reserved in a continuous area. Internally, PAGE_READWRITE, SEC_LARGE_PAGES, and SEC_COMMIT are specified in Windows API CreateFileMapping. Previously only PAGE_READWRITE was specified.

#### 3.4.9 Remove secondary checkpoint information {#pg11-3-4-9-remove-secondary-checkpoint-information}

PostgreSQL 10 saved the last two checkpoint information, but now only the latest checkpoint information is saved.

#### 3.4.10 Error code list {#pg11-3-4-10-error-code-list}

```text
  The {INSTALL_DIR}/share/errcodes.txt file has been added. This file contains error codes, levels,
```

macro names etc. of PostgreSQL, PL/pgSQL, and PL/Tcl.