View a markdown version of this page

Babelfish temporary tables on read replicas - Amazon Aurora
Why temporary tables on read replicas matterEnabling temporary tables on read replicasSupported operationsLimitations and considerations

Babelfish temporary tables on read replicas

Starting with Babelfish 6.2.0 and Babelfish 5.8.0, Babelfish supports local temporary tables and table variables on Aurora PostgreSQL read replicas. With this support, SQL Server Management Studio (SSMS) and T-SQL applications that use temporary tables and table variables as intermediate scratch space can connect and operate normally against read replicas without any application changes.

Why temporary tables on read replicas matter

Amazon Aurora PostgreSQL read replicas let you scale the read operations of your application. By connecting to the reader endpoint of the cluster, Aurora can spread the load for read-only connections across as many Aurora Replicas as you have in the cluster. Aurora Replicas also help to increase availability. If the writer instance in a cluster becomes unavailable, Aurora automatically promotes one of the reader instances to take its place as the new writer.

SQL Server applications and tooling rely heavily on session-local temporary tables (#temp) and table variables (DECLARE @t TABLE) – not just in application code, but at the driver and tooling level. SQL Server Management Studio (SSMS), the standard SQL Server management tool, creates temporary tables internally for IntelliSense, query result caching, and object scripting. Prior to Babelfish 6.2.0 and Babelfish 5.8.0, any attempt to create a temporary table or table variable on an Aurora replica failed with:

ERROR: cannot execute CREATE TABLE in a read-only transaction

This error blocked two critical customer use cases:

  • SSMS connected to an Aurora replica – SSMS could not function because its internal IntelliSense mechanism creates temporary tables automatically on connection.

  • Read-heavy application workloads – T-SQL applications commonly stage intermediate query results in local temporary tables and table variables before aggregation or joining. These patterns failed entirely on read replicas, forcing customers to route workloads to the primary writer or restructure application logic.

Starting with Babelfish 6.2.0 and Babelfish 5.8.0, both limitations are resolved. Local temporary tables and table variables are fully supported on read replicas. Only session-local temporary objects – temporary tables and table variables – are exempt, because they are never written to shared storage.

Enabling temporary tables on Aurora read replicas

Temporary table support on read replicas is controlled by the GUC parameter babelfishpg_tsql.enable_temp_table_on_ro.

New parameter groups

For clusters created on Aurora PostgreSQL 18.6 and Aurora PostgreSQL 17.11 or later, enable_temp_table_on_ro is enabled by default. No action is required.

Existing parameter groups

Clusters upgraded to Aurora PostgreSQL 17.11 or Aurora PostgreSQL 18.6 from an earlier version can update the Aurora cluster parameter group. For more information, see Modifying parameters in a DB parameter group in Amazon Aurora. The change takes effect immediately – no cluster restart is required.

aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name your-cluster-parameter-group-name \
  --parameters \
    "ParameterName=babelfishpg_tsql.enable_temp_table_on_ro,ParameterValue=on,ApplyMethod=immediate"

Supported operations

The following temporary table and table variable operations are supported on Babelfish read replicas.

Local temporary table DDL

  • CREATE TABLE #tablename (...) – creates a session-local temporary table on the read replica.

  • SELECT col1, col2 INTO #tablename FROM source WHERE ... – creates and populates a temporary table from a read query in a single statement.

  • DROP TABLE #tablename – explicitly drops a temporary table within the session; all temporary tables are also dropped automatically when the session ends.

  • TRUNCATE TABLE #tablename – removes all rows from a temporary table without dropping it.

Local temporary table DML

  • INSERT INTO #tablename ...

  • SELECT ... FROM #tablename

  • UPDATE #tablename SET ...

  • DELETE FROM #tablename WHERE ...

Table variables

DECLARE @variable_name TABLE (...) – declares a session-local table variable. Table variables are scoped to the batch or stored procedure in which they are declared and are dropped automatically at the end of that scope.

Procedural and dynamic SQL patterns

  • Stored procedures and batches that create temporary tables and read from permanent tables execute end-to-end on read replicas.

  • sp_executesql with dynamic SQL that creates and uses temporary tables works on read replicas.

Limitations and considerations

The following limitations apply to temporary tables and table variables on read replicas.

Unsupported operations

Temporary tables that reference user-defined objects are not supported. Temporary table definitions that reference user-defined types (UDTs), user-defined functions (UDFs), or other user-defined objects in column definitions or constraints are blocked on read replicas. Use only built-in system types and system functions in temporary table definitions on read replicas.

Per-session temporary object limit

A single session can hold up to 65,536 temp objects due to the pre-defined buffer pool size. If you exceed this limit, you see the following error:

Unable to allocate oid for temp table. Drop some temporary tables or start a new session

You can drop some temporary tables to create new ones or reset the session (drop and re-create the connection) to reset the limit.