OCI Database with PostgreSQL Storage Best Practices

Auto-scale shared storage handles your database data and write-ahead log (WAL) files. This storage type provides high availability through replication across availability domains.

Here are the key characteristics of auto-scale shared storage:

• Dynamic scaling: Storage scales automatically as you create and drop database objects. No pre-provisioning is required and no downtime occurs during scaling.
• Decoupled architecture: Compute and storage are separated, allowing each to scale independently based on your needs.
• High availability: Data is replicated across availability domains, enabling fast failover with no data loss.
• Hard limit: Maximum capacity is 32 TB per database system.

Auto-scale shared storage stores the following types of content:

• Database data such as tables, indexes, and other database objects.
• WAL files for transaction durability and replication.

Temporary storage is allocated per node and handles operational files that don't require long-term persistence. This storage doesn't auto-scale and is sized based on your compute shape. The formula for this storage size is based on Max(50 GB, 2 GB + numOCPU × 32 GB).

Here are the key characteristics of temporary storage:

• Doesn't auto-scale: To increase capacity, you must upgrade to a larger compute shape.
• Isolated from critical data: If temporary storage fills up, your database data and WAL remain unaffected.

Temporary storage stores the following types of content:

• Temporary/spill files from pgsql_tmp/pg_tempdir and pg_replslot, which are created when queries exceed available memory.
• PostgreSQL log files for monitoring and debugging.
• Replication slot spill files (for logical replication).

The remainder of this topic addresses best practices for handing storage exhaustion issues for these types of storage.

Use the following query to identify WAL retention by replication slots:

SELECT 
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained,
    pg_size_pretty(safe_wal_size) AS safe_wal_remaining
FROM pg_replication_slots;

In this example of a return, 15 GB of WAL is retained and only 5 GB more can be retained before the slot becomes invalidated (if max_slot_wal_keep_size is configured). If max_slot_wal_keep_size = -1, safe_wal_remaining shows NULL.

 slot_name | active | wal_retained | safe_wal_remaining
-----------+--------+--------------+--------------------
 replica_1 | t      | 15 GB        | 5 GB

For instructions on setting up custom alarms, see Create PostgreSQL Custom OCI Monitoring Alarms and Dashboard using Container Instances

We recommend you set up alerts if any of the following conditions occur:

• wal_retained exceeds 50% of available capacity.
• Any slot has active = false for more than one hour.

Use the following mitigation measures as needed:

• Drop abandoned slots: If a subscriber is no longer needed or can be recreated, drop the replication slot:

  SELECT pg_drop_replication_slot('slot_name');

• Set finite retention limit: Configure max_slot_wal_keep_size to a value that balances replication stability with primary protection.

PostgreSQL provides extensive logging parameters useful for debugging. However, aggressive logging settings can generate excessive disk I/O during peak traffic, rapidly filling the temporary storage volume.

Here are common problematic configurations:

• log_statement = 'all': Logs every SQL statement, generating massive log volumes on busy systems.
• log_min_duration_statement = 0: Logs every statement with its duration.
• log_connections/log_disconnections = on: On high-traffic systems, generates entries for every connection.
• Debug-level logging left enabled: Parameters such as log_executor_stats, log_parser_stats, log_planner_stats generate verbose output.

Symptoms of excessive PostgreSQL logging include:

• Temporary storage usage increasing rapidly.
• High disk I/O on the logging volume.

PostgreSQL uses additional memory beyond shared_buffers for query processing. While shared_buffers handles data caching and in-memory DML operations, work_mem is used for query operations such as sorting, joining, and aggregating.

When a query involves JOIN, AGGREGATE, or SORT operations, PostgreSQL first checks if the relevant columns have presorted indexes. If indexes are unavailable, PostgreSQL tries to perform these operations in memory.

If the work_mem setting is insufficient for the data being processed, PostgreSQL generates temporary files and uses disk instead of memory.

Here are common scenarios where inadequate memory settings can occur:

The following example shows a sample log entry when the disk gets full:

2025-12-03 10:55:25.650 UTC [249610] ERROR: could not write block 196800198 of temporary file: No space left on device

Symptoms of excessive inadequate memory settings include:

• Temporary storage filling up during complex query execution.
• Slow query performance on JOIN, SORT, or AGGREGATE operations.
• Increased disk I/O during query processing.

PostgreSQL logical replication uses logical decoding to transform raw WAL records into meaningful, structured row-level changes. Unlike physical replication that copies byte-for-byte disk blocks, logical decoding interprets transactional data and converts it into logical changes (INSERTs, UPDATEs, DELETEs) with actual column values.

Each logical replication slot has an associated WAL sender process responsible for streaming changes to target subscribers. Transactions are sent in commit order, and the subscriber receives, decodes, and applies these changes to the target system.

PostgreSQL supports native logical replication where the subscriber is another PostgreSQL instance. Starting from PostgreSQL 14, subscribers can handle large ongoing transactions efficiently with streaming=on mode enabled:

• In-progress transactions (not yet committed) are streamed incrementally to the subscriber.
• The subscriber applies changes to a temporary/pending state immediately.
• After the transaction commits on the publisher, the subscriber commits those changes.
• If the transaction rolls back, the subscriber discards the pending changes.

This approach prevents the publisher from accumulating large amounts of decoded data in memory or spill files while waiting for long-running transactions to complete.

Third-party or federated subscribers can also consume from PostgreSQL logical replication slots using the PostgreSQL replication protocol. However, unlike native PostgreSQL subscribers, federated subscribers typically don't support streaming=on mode because they can't control how the underlying target system behaves.

When a subscriber doesn't support streaming=on, the PostgreSQL WAL sender keeps decoded changes for in-progress transactions in spill files at:

pg_replslot/<slot_name>/xid-<xid>-lsn-<lsn>.spill

These spill files can grow because of PostgreSQL's strict commit-order guarantee. For example:

• Transaction TRX1 remains open for a long time (idle in transaction or performing bulk operations).
• Other independent transactions (TRX2, TRX3, TRX4, and so forth) continue to commit.
• The WAL sender accumulates committed transaction data in spill files.
• These transactions are only sent to the subscriber after TRX1 issues COMMIT or ROLLBACK.

This is expected behavior when working with federated subscribers that don't support streaming=on mode.

The following example shows spill file details (logged at DEBUG2 level) in the logging:

2025-12-03 10:28:14.650 UTC [249610] DEBUG: spill 4096 changes in XID 750 to disk

If the disk gets full, the log entries change to the following:

2025-12-03 10:32:15.550 UTC [249610] ERROR: could not write to data file for XID <xid>: No space left on device
2025-12-03 10:32:15.554 UTC [249610] ERROR: could not read from reorderbuffer spill file: No space left on device

Symptoms of replication slot spill files include:

• Temporary storage usage increases rapidly.
• Many .spill files are present in the pg_replslot folder.