Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Working with unlogged tables in Aurora PostgreSQL

Amazon Aurora PostgreSQL supports unlogged tables that are crash-safe and maintain data integrity even after writer instance failures or failovers. In standard PostgreSQL, unlogged tables bypass the Write-Ahead Log (WAL) during write operations, resulting in faster write speeds. However, this comes at the cost of reduced durability, as unlogged tables are not crash-safe and can lose data after a system failure or unclean shutdown. These unlogged tables are automatically truncated after a crash or unclean shutdown. Their contents and indexes are also not replicated to standby servers.

In contrast, Aurora PostgreSQL handles unlogged tables differently due to its distributed storage architecture. This is because Aurora's storage system does not rely on the traditional PostgreSQL WAL for durability. However, the performance benefits typically associated with unlogged tables in standard PostgreSQL may not be as significant in Aurora. This is due to Aurora's distributed storage architecture, which can introduce additional overhead compared to the local storage used in standard PostgreSQL.

When using unlogged tables in Aurora PostgreSQL, consider the following:

Creating unlogged tables

To create an unlogged table in Aurora PostgreSQL, add the UNLOGGED keyword in your CREATE TABLE statement:

CREATE UNLOGGED TABLE staging_sales_data (
    transaction_id bigint,
    customer_id bigint,
    product_id bigint,
    transaction_date date,
    amount NUMERIC
);
        

Converting unlogged tables to logged tables

When you need to convert an unlogged table back to a logged table, you can use the following command:

ALTER TABLE table_name SET LOGGED;
        

This operation rewrites the entire table and places an exclusive lock on it until the operation completes. For large tables, this can result in significant downtime.

Unlogged tables and logical replication

Unlogged tables are generally not included in logical replication because logical replication relies on the WAL to capture and transfer changes. By default, changes to unlogged tables are not WAL-logged and excluded from the replication stream, making them unsuitable for use cases where logical replication is required. However, Aurora PostgreSQL provides a parameter called rds.logically_replicate_unlogged_tables that allows you to control this behavior: