Presented by Andrew Dunstan of EnterpriseDB
What's new in 17
This is a partial list. For the full list see the release notes.
json_table
Part of SQL standard that allows JSON data to be treated as an SQL table. Syntax is somewhat complex, uses jsonpath expressions for items inside the JSON.
SELECT
FROM JSON_TABLE('{
"employees": [
{ "name": "John Doe", "age": 42, "position": "President" },
{ "name": "Jane Smith", "age": 33, "position": "CEO" },
]
}'::jsonb,
'$.employees[*]')
COLUMNS(
NAME TEXT PATH '$.name',
AGE NUMERIC PATH '$.age',
POSITION TEXT PATH '$.position'
)Other SQL/JSON features
- JSON()
- JSON_SCALAR()
- JSON_SERIALIZE()
- JSON_EXISTS()
- etc.
Incremental backups
After creating full backup, can create a series of incrementals.
Backups can be combined, don't necessarily need another full backup after the first.
pg_createsubscriber
Turn a physical replica into a logical replica. Much faster than creating logical replica directly.
Close to zero-downtime upgrade:
- Create physical standby
- Convert to logical replica
- Stop replica
- Upgrade with pg_upgrade
- Restart and wait to catch up
- Switch to upgraded server
MAINTAIN privilege
Allows non-superuser to do maintenance activities:
- VACUUM
- ANALYZE
- REINDEX
- REFRESH MATERIALIZED VIEW
- CLUSTER
- LOCK TABLE
Vacuum
More efficient, removed 1GB limit on memory.
COPY ... ON_ERROR ignore
Proceed after an error is discovered, default action is STOP.
Emits a NOTICE containing count of ignored rows if any.
More work going on for error processing.
What's coming in 18 (due Sept.)
OAuth authentication
Allows authentication against third-party service.
Still a lot of work to be done.
UUID v7
Time-ordered UUIDs, sub-millisecond precision
New uuidv7() function, optional argument for epoch start.
checksums on by default with initdb
Previously required an argument.
Basic data-integrity protection. Can have a performance impact.
limit COPY errors
COPY ... REJECT_LIMIT=nn
Requires ON_ERROR ignore.
Async IO subsystem
Requests can be queued.
More efficient sequential scans, bitmap heap scans, vacuum.
Remove bias against non-btree indexes
Currently some important operations will only use btree indexes.
Allows development of new index types for particular data types.
What might be in 19
Non-text mode for pg_dumpall
Originally targeted for 18, will be back for 19. Reverted due to concerns about security and robustness.
Dump all databases in a cluster in custom, tar, or directory format.
REPACK table concurrently
Based on pg_squeeze. Better than pg_repack.
Uses logical decoding, not triggers.
COPY errors saved to table
COPY TO ... FORMAT JSON
JSON simplified accessor support
json_var.a.b[2].c
JSON_TABLE PLAN clause
SQL Property Graphs
Define tables that contain edges and vertices, use graph-style queries on them.
Fancy way of doing JOINs.
Creation DDL
Function to output creation DDL for (nearly) arbitrary objects.