If you have been relying on Oracle Database as your primary system for analytics and the generation of MIS reports, you are probably familiar with the use of temporary tables within stored procedures. These temporary tables play an important role in managing intermediate data, performing complex calculations, and streamlining the overall data processing workflow. Temporary tables help in handling large volumes of data, break down queries into manageable steps, and produce complex analytical reports efficiently.

However, when organizations migrate these systems to Azure PostgreSQL, most automated code converters simply translate Oracle temporary tables into Azure PostgreSQL temporary tables without highlighting the key difference.

Understand the misconception

In Oracle the Global Temporary Table is a persistent schema object whose structure is permanent, but data is temporary. Internally, Oracle stores all data inserted into a GTT in the temporary tablespace, isolating it per session by using temporary segments that are dynamically allocated and cleaned up at the end of the session or transaction, depending on whether the table is defined with ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS. While the table metadata remains in the data dictionary, the data itself is never written to the redo logs.

Oracle also introduced Private Temporary table in version 18C which has an extra option of ON COMMIT DROP DEFINITION which drops the table at transaction commit.

Azure PostgreSQL too has a temporary table object that supports all the three commit clauses available in Oracle i.e. ON COMMIT DELETE ROWS, ON COMMIT PRESERVE ROWS and ON COMMIT DROP but the object is private to the session that created it with both its structure and data completely invisible to other sessions and the table itself is dropped automatically when the session ends.

Oracle Global Temporary Tables have a permanent table definition accessible by all sessions but store data privately per session or transaction, whereas Azure PostgreSQL temporary tables exist only for the duration of a session and are dropped automatically at session end.

At first glance, this difference might seem trivial, after all, you can simply add a CREATE TABLE statement in your code to recreate the temporary table at the start of every session. But what appears to be a small tweak can quickly spiral into a performance nightmare, overloading your system in ways you wouldn’t expect if not managed carefully.

Whole Azure PostgreSQL is built on an MVCC architecture, which means even its internal tables and system catalogue tables retain deleted rows of dropped objects. If you relook at the key difference between temp tables, you will understand that every time a temp table is created and drop per session, it adds few rows and deletes it from many system catalogue tables. See example below

Following is the output of Pgstattuple for three of the system tables.

Now I run a function a few times sequentially that joins multiple tables and writes the data into a temp table and return the response. You can see that there is a slight increase but nothing to be concerned about.

But if the same function is called by 500 sessions concurrently, you can see that the increase is dramatic.

There is also a marked increase in IOPS consumption as shown below

Understand the impact

As seen above, system catalogue tables like pg_class, pg_attribute, and pg_type can grow rapidly in size as each session that creates and drops temporary tables leaves behind dead tuples in these catalogues, contributing to significant bloat. This accumulation happens because Azure PostgreSQL records metadata for every temporary table in the system catalogues, and when the tables are dropped (typically at session end), their metadata is simply marked as dead rather than immediately removed.

In highly transactional environments, this bloat can escalate dramatically, sometimes increasing by hundreds or even thousands of times within just a few hours. Azure PostgreSQL relies heavily on its system catalogue during parsing, planning, and execution phases of every SQL statement.

Also, every temp table created will try to utilize temp buffer to store the data but if the data is large and temp buffer is small then naturally the data is stored on disk. This frequent creation and deletion of files adds a lot of disk IO. Under normal conditions, this will be taken care of by the file management. However, when the system is under heavy load, this process can become a bottleneck and slow down even normal select statements.

This catalogue bloat and frequent file and buffer management under heavy or repeated use of temporary tables will lead to high CPU consumption and will slow down existing users which will intern add more CPU load and quickly the system will get inundated and possibly crash.

Below example shows almost 3 times increase in planning time with bloated system table as compared to without bloat

Conclusion

It’s important to recognize that Azure PostgreSQL and Oracle implement temporary tables differently: Oracle’s global temporary tables are persistent schema objects that do not add significant system load whereas Azure PostgreSQL’s temporary tables are always session-specific and are dropped at session, this along with its MVCC architecture adds significant load on the system in certain situations. This fundamental difference means that if not handled properly it can cause database to crash.

When migrating workloads from Oracle to Azure PostgreSQL, developers should carefully consider whether a temporary table is truly necessary, or if the requirement can be addressed more elegantly using alternatives like CTEs or views.

In some scenarios, temporary tables are indispensable, for example, they provide an efficient way to store intermediate results, for simplifying complex query logic or for collecting data from ref-cursor and no workarounds fully match the flexibility of the temp tables for these use cases.

If you can’t get rid of temp tables, then it’s absolutely necessary to have a robust alerting on system table bloat and having a custom job that does frequent vacuuming on these tables.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Databases SQL Customer Success Engineering (Ninja) Team (datasqlninja@microsoft.com). Thanks for your support!

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.