Modern Database

Logical Decoding of Log Information

The transaction log in PostgreSQL is the database's insurance against data loss. Any changes to the databases are logged in the transaction log. PostgreSQL 9.0 introduced the streaming replication protocol several years ago. It allows the replication of transaction logs to a remote standby server. As the transaction log's binary block contents are replicated, this is referred to as physical replication.

A fundamental innovation in the new PostgreSQL version is a plugin interface for logically decoding transaction log data. The contents of the transaction log can be read out and interpreted via the new interface or converted into specific output formats. This allows for the output of the transaction log in SQL or JSON, for example. One field of application is logical replication, which replicates the logical changes to a database based on the output format (e.g., into another database).

Logical decoding requires more information in the transaction than before. For this reason, the wal_level parameter is logically set to the new value to be able to use logical decoding. Furthermore, a replication slot is required that has been generated for logic decoding and has an appropriate output plugin. A simple plugin for testing this infrastructure is included in the PostgreSQL 9.4 package in the form of test_decoding. It might be necessary to install packages later, depending on the type of installation. test_decoding is included in the package postgresql94-contrib in the community's RPM repositories. If everything is installed correctly, the superuser can create a logical replication slot (Listing 3).

#= SELECT * FROM pg_create_logical_replication_slot('test', 'test_decoding');
    slot_name     |    xlog_position
    --------------+---------------
    test          |    0/760216D8
(1 row)

Logical decoding offers functions that provide access to decoded transaction log information for SQL. These include the functions pg_logical_slot_get_changes() and pg_logical_slot_peek_changes(). The latter in fact does not actually consume decoded information but instead simply returns it. A repeat call contains the same information. Using pg_logical_decoding_get_changes(), on the other hand, consumes the changes; when called again, it no longer contains the returned information.

Figure 2 shows an application with pg_logical_slot_get_changes(). Notice the empty transaction 90116 at the beginning. Logical decoding cannot currently detect any changes to tables themselves (DDL). The transaction that was started implicitly for the CREATE TABLE command is therefore empty.

Figure 2: Certain requests for the transaction log are "consumed"; that is, they only deliver a result the first time.

Logical decoding covers a wide range of application possibilities. Replication solutions for PostgreSQL, which are based on the infrastructure, are also currently under development. The possible uses are diverse and range from replication and upgrades to interoperability with external systems for exchanging data.

Support for Linux Huge Pages

Thanks to the new configuration parameter, huge_pages, PostgreSQL 9.4 can use the Linux HugePages feature. This feature addresses the main memory in larger blocks so that the page tables are much smaller, thereby reducing the load on the CPU. This is particularly interesting for database systems that use a large amount of RAM. The default setting is try, so that PostgreSQL attempts to use HugePages.

autovacuum_work_mem: Settings for autovacuum

The autovacuum tool is essential for high-performance databases with PostgreSQL. The setting for the permitted use on the system's main memory was split in PostgreSQL 9.4 to offer administrators more flexible resource control. Where the parameter maintenance_work_mem was previously responsible for all maintenance commands, such as ALTER TABLE, CREATE INDEX, and autovacuum, administrators can now configure separate values for automatic vacuuming. Note that this setting will only work for the autovacuum worker processes. Manual VACUUM does not allow this option; instead, it still uses maintenance_work_mem.

Delayed Replication

Replication delay is a function long desired by database administrators, with the idea being that transaction logs would be consumed asynchronously by standby servers at a certain time interval. The new parameter recovery_min_apply_delay now provides this functionality in PostgreSQL 9.4.

Times can, as usual, be assigned to the parameter with common units such as 2H or 180min. A delay affects the COMMIT or the restore points in a transaction. Changes that occur during a transaction can nevertheless take effect immediately on the standby. A DROP TABLE on the master blocks a simultaneous query on the standby before the end of the delay since the corresponding transaction log changes are loaded immediately. The COMMIT is loaded on the standby after the corresponding wait from recovery_min_apply_delay.