6 Architecture
PDF page 36
3.4.1 Modified catalogs
The following catalogs have been changed.
| 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 |
PDF page 37
| Catalog name | Deleted column | Description |
|---|---|---|
| pg_class | relhaspkey | Have primary key |
| pg_proc | proisagg | Is aggregate function |
| proiswindow | Is Window function |
| 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
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”.
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.
PDF page 38
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.
| 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 |
3.4.3 LLVM integration
PostgreSQL 11 supports JIT compilation using LLVM (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
In order to use LLVM, it is necessary to specify the option –with-llvm of “configure” command at
PDF page 39
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
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
By executing EXPLAIN statements for SQL statement using the JIT compilation feature, information starting from “JIT:” is displayed.
PDF page 40
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.
PDF page 41
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
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). In PostgreSQL 10 timeout occurred even if “SET statement_timeout = 4s” was set.
PDF page 42
3.4.7 Change of backup label file
A timeline ID is added to the backup_label file created when online backup is executed.
PDF page 43
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
PostgreSQL 10 saved the last two checkpoint information, but now only the latest checkpoint information is saved.
3.4.10 Error code list
macro names etc. of PostgreSQL, PL/pgSQL, and PL/Tcl.