What's new in PostgreSQL 9.4
From PostgreSQL wiki
(→Other new features: Add ALTER SYSTEM)
(→REFRESH MATERIALIZED VIEW CONCURRENTLY)
|Line 91:||Line 91:|
==REFRESH MATERIALIZED VIEW CONCURRENTLY==
==REFRESH MATERIALIZED VIEW CONCURRENTLY==
Revision as of 22:59, 5 March 2014
This page contains an overview of PostgreSQL Version 9.3's features, including descriptions, testing and usage information, and links to blog posts containing further information. See also PostgreSQL 9.4 Open Items.
Major new features
TODO: Blog posts, redundancy of wal_keep_segments et al.
Replication slots allow standbys to provide information to the primary or upstream cascading standby as to the point they've reached in the write-ahead log. This information is available to the primary even when the standby is offline or disconnected. This eliminates the need for wal_keep_segments, which required the admin to estimate how many extra WAL files would be needed to be kept around in order to ensure supply to the standby in case of excessive replication lag.
Here is an example:
On the primary server, set max_replication_slots to allow the standby to register to a slot:
max_replication_slots = 1
The wal_level will also need to be set to at least "archive", but as this example will be using a hot standby, we'll set it to "hot_standby":
wal_level = hot_standby
Restart the primary for these settings to take effect, then connect to the primary and create a replication slot:
SELECT * FROM pg_create_physical_replication_slot('standby_replication_slot');
A standby can then use this replication slot by specifying it as the primary_slotname parameter in its recovery.conf:
primary_slotname = 'standby_replication_slot'
On the primary, you can see this being used:
postgres=# SELECT * FROM pg_replication_slots; slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn -----------------------+--------+-----------+--------+----------+--------+------+--------------+------------- standby_physical_slot | | physical | | | t | | | 0/3000420 (1 row)
Now the primary will keep hold of WAL for as along as it needs to, which is until all standbys have reported that they have progressed beyond that point. Note, however, that if a standby goes offline, and the replication slow isn't dropped, WAL will build up on the primary. So if a standby needs to be decommissioned, it's slot should then be dropped like so:
# SELECT pg_drop_replication_slot('standby_replication_slot'); pg_drop_replication_slot -------------------------- (1 row)
Otherwise, ensure there is enough disk capacity available to keep WAL on the primary until the standby returns.
In future logical replication will also take advantage of replication slots.
TODO: Blog posts, explanation of what it's the groundwork for
GIN Index's now faster and smaller
TODO: Blog posts, benchmark results
When a database instance is restarted, its shared buffers are empty again, which means all queries will initially have to read data in direct from disk. pg_prewarm can load relation data back into the buffers to "warm" the buffers back up again. This will mean that queries, that would otherwise have to load parts of a table in bit by bit, can have the data available in shared buffers ready for them.
Other new features
Normally, cluster-wide settings need to be edited in the postgresql.conf, but now changes can be made via the ALTER SYSTEM command.
postgres=# ALTER SYSTEM SET log_min_duration_statement = '5s';
This doesn't actually change postgresql.conf. Instead it writes the setting to a file called postgresql.auto.conf. This file *always* gets read last, so it will always override any settings in postgresql.conf. Setting it to DEFAULT removes the line from postgresql.auto.conf:
postgres=# ALTER SYSTEM SET log_min_duration_statement = DEFAULT;
An advantage of making changes this way is that the settings are more likely to be correct as they are validated before they are added:
postgres=# ALTER SYSTEM SET wal_level = 'like the legend of the phoenix'; ERROR: invalid value for parameter "wal_level": "like the legend of the phoenix" HINT: Available values: minimal, archive, hot_standby, logical.
If this had been set in postgresql.conf instead, and someone attempted to restart the cluster, it would fail. Naturally postgresql.auto.conf should never be edited manually.
REFRESH MATERIALIZED VIEW CONCURRENTLY
Prior to PostgreSQL 9.4, refreshing a materialized view meant locking the entire table, and therefore preventing anything querying it, and if a refresh took a long time to acquire the exclusive lock (while it waits for queries using it to finish), it in turn is holding up subsequent queries. This can now been mitigated with the CONCURRENTLY keyword:
postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY mv_data;
A unique index will need to exist on the materialized view though. Instead of locking the materialized view up, it instead creates a temporary updated version of it, compares the two versions, then applies INSERTs and DELETEs against the materialized view to apply the difference. This means queries can still use the materialized view while it's being updated.
Unlike its non-concurrent form, tuples aren't frozen, and it needs VACUUMing due to the aforementioned DELETEs that will leave dead tuples behind.