blog

Comparing Temporary Tables for PostgreSQL & Oracle GTT

Hugo Dias

Published: August 5, 2019
Last Updated: May 4, 2022

The temporary tables are a useful concept present in most SGBDs, even though they often work differently.

This blog describes the technical features for this kind of tables either in PostgreSQL (version 11) or Oracle (version 12c) databases with some specific examples. Although the purpose of these tables could be the same for all SGBD’s, their specifics, or the way of implementation and manipulation, are completely different.

This feature could be used both by developers or database administrators to store intermediate results that will be needed for further processing in order to provide good performance metrics.

Temporary Tables in PostgreSQL

In PostgreSQL these objects are valid only for the current session: they are created, used and dropped along the same session: the structure of the table and managed data only are visible for the current session, thus the other sessions don’t have access to the temporary tables created on the other sessions.

Below it’s showed a simple example to create a temporary table:

CREATE TEMPORARY TABLE tt_customer
(
     customer_id INTEGER
)
ON COMMIT DELETE ROWS;

The temporary tables are created in a temporary schema: pg_temp_nn and it’s possible to create indexes on these tables:

creation index  tt_cusomer_idx_1 on tt_customer(customer_id)

As the data rows on these tables could be also deleted, it’s possible to release the occupied storage through the execution of vaccum command:

VACUUM VERBOSE tt_customer

The analyze command can be executed as well on the temporary tables in order to collect the statistics:

ANALYZE VERBOSE tt_customer;

Both commands can be executed for this kind of table as SQL command, however, the autovaccum daemon that execute them does not act on the temporary tables.

Another important point to consider it’s related to the permanent and temporary tables with the same name: once it happens the permanent table only is taken into account when called with its schema as a prefix.

web_db=# BEGIN TRANSACTION;
BEGIN
web_db=# SELECT COUNT(*) FROM customers;
  count  
---------
 1030056
(1 row)

web_db=# CREATE TEMPORARY TABLE customers(
web_db(#   id INTEGER
web_db(# )
web_db-# ON COMMIT PRESERVE ROWS;
CREATE TABLE
web_db=# INSERT INTO customers(id) VALUES(1023);
INSERT 0 1
web_db=# SELECT COUNT(*) FROM customers;
 count 
-------
     1
(1 row)
web_db=# dt *customers*
                  List of relations
  Schema   |         Name         | Type  |  Owner   
-----------+----------------------+-------+----------
 pg_temp_5 | customers            | table | postgres
 web_app   | customers            | table | postgres
 web_app   | customers_historical | table | postgres
(3 rows)
web_db=# DROP TABLE customers;
DROP TABLE
web_db=# dt *customers*
                 List of relations
 Schema  |         Name         | Type  |  Owner   
---------+----------------------+-------+----------
 web_app | customers            | table | postgres
 web_app | customers_historical | table | postgres
(2 rows)
web_db=# SELECT COUNT(*) FROM web_app.customers; 
  count  
---------
 1030056
(1 row)
web_db=# SELECT COUNT(*) FROM customers; 
  count  
---------
 1030056
(1 row)

From the previous example while the temporary table exists all reference to the customers refers to this table instead to the permanent one.

Developer Tips for Temporary Tables

The purpose of this example is to assign a bonus for the customers who have not made purchases or login for more than a year, so the script of the developer instead to use sub-queries in queries as a possible solution (or the using of CTEs statement) can use temporary tables (that usually it’s faster than using sub-queries):

web_db=# BEGIN TRANSACTION;
BEGIN
web_db=# CREATE TEMPORARY TABLE tt_customers(
web_db(#   id INTEGER
web_db(# )
web_db-# ON COMMIT DELETE ROWS;
CREATE TABLE
web_db=# SELECT COUNT(*) FROM tt_customers;
 count 
-------
     0
(1 row)
web_db=# INSERT INTO tt_customers(id)
web_db-# SELECT customer_id
web_db-#   FROM web_app.orders
web_db-# WHERE order_dt <= NOW()-INTERVAL '6 MONTH';
INSERT 0 1030056
web_db=# SELECT COUNT(*) FROM tt_customers;
  count  
---------
 1030056
(1 row)
web_db=# DELETE FROM tt_customers c
web_db-# WHERE EXISTS(SELECT 1 
web_db(#                FROM web_app.users u JOIN web_app.login l 
web_db(#                       ON (l.user_id=u.user_id) 
web_db(#               WHERE u.customer_id=c.id 
web_db(#                 AND l.login_dt > NOW()-INTERVAL '6 MONTH'
web_db(#                 );
DELETE 194637
web_db=# SELECT COUNT(*) FROM tt_customers;
 count  
--------
 835419
(1 row)
web_db=# UPDATE web_app.customers as c SET BONUS=5
web_db-# FROM tt_customers t
web_db-# WHERE t.id = c.id;
UPDATE 835419
web_db=# SELECT COUNT(*) FROM tt_customers;
 count  
--------
 835419
(1 row)
web_db=# COMMIT TRANSACTION;
COMMIT
web_db=# SELECT COUNT(*) FROM tt_customers;
 count 
-------
     0
(1 row)

DBA Tips for Temporary Tables

A typical task for database administrators is to purge any huge tables that contain data that is no longer needed. This needs to be completed very quickly and it happens often. The standard approach is to move this data to a historical table in another schema or to a database that is accessed less often.

So, in order to perform this moving, due to performance issues the best solution could be using temporary tables:

CREATE TEMPORARY TABLE tt_customer
(
     customer_id INTEGER
)
ON COMMIT DROP;

In this example, the temporary table was created with the DROP option, so it means that will be dropped at the end of the current transaction block.

Here is some other important info on PostgreSQL temporary tables:

Temporary tables are automatically dropped at the end of a session or, as presented in the previous example, at the end of the current transaction
Permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names
Any indexes created on a temporary table are automatically temporary as well
ON COMMIT preserve rows it’s the default behavior
Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference in PostgreSQL and it’s deprecated
The autovacuum daemon cannot access to these tables and therefore cannot vacuum or analyze temporary tables, however, as shown previously the autovacuum and analyze commands can be used as SQL commands.

Global Temporary Tables (GTT) in Oracle

This kind of tables is known in the Oracle world as a Global Temporary Table (or GTT). These objects are persistent in the database and can be summarized by the following characteristics:

The structure is static and visible for all users, however, its content is only visible for the current session
It can be created in a specific schema (by default will be owned by the user that issuing the command) and they are built in the TEMP tablespace
Once created in the database it cannot be created again in each session, however, the data managed by a session are not visible for the others sessions
It’s possible the creation of indexes and generation of statistics
As the structure of these tables is also defined in the database isn’t possible to assign its name to a permanent table (in Oracle two objects cannot have the same name even from different types)
Do not generate too many redo logs and the undo overhead it’s also less comparing with a permanent table (only for these reasons the using of GTT it’s faster) for any versions prior to 12c. From 12c version there is a concept of temporary undo, allowing the undo for a GTT to be written to the temporary tablespace, thus it’s reducing undo and redo.

Following the same example presented in PostgreSQL, the creation of a GTT is quite similar:

CREATE GLOBAL TEMPORARY TABLE tt_customer
(
     customer_id NUMBER
)
ON COMMIT DELETE ROWS;

It’s possible also the creation of indexes.

creation index  tt_cusomer_idx_1 on tt_customer(customer_id)

Prior Oracle 12c the generation of statistics for a global temporary tables had a behavior in a global way: the statistics generated in a specific session for a specific GTT were visible and used for the others sessions (only statistics not the data!), however, from version 12c it’s possible for each session to generate its own statistics.

First of all it’s necessary to set the preference global_temp_table_stats to session:

exec dbms_stats.set_table_prefs(USER,’TT_CUSTOMER’,’GLOBAL_TEMP_TABLE_STATS’,’SESSION’);

and then the generation of statistics:

exec dbms_stats.gather_table_stats(USER,’TT_CUSTOMER’);

The existing global temporary table could be checked by the execution of the following query:

select table_name from all_tables where temporary = 'Y';

Developer Tips for Global Temporary Tables (GTT)

Following the example on PostgreSQL section: to assign a bonus for the customers who have not made purchases or login for more than a year, the use of global temporary tables in Oracle has the same goal that in PostgreSQL: to achieve better performance either in the usage of resource or in execution speed.

SQL> SELECT COUNT(*) FROM tt_customers;
  COUNT(*)
----------
         0
SQL>
SQL> INSERT INTO tt_customers(id)
  2  SELECT customer_id
  3    FROM orders
  4  WHERE order_dt <= ADD_MONTHS(SYSDATE,-6);
1030056 rows created.
SQL>
SQL> SELECT COUNT(*) FROM tt_customers;
  COUNT(*)
----------
   1030056
SQL>
SQL> DELETE FROM tt_customers c
  2  WHERE EXISTS(SELECT 1
  3                 FROM users u JOIN login l
  4                        ON (l.user_id=u.user_id)
  5                WHERE u.customer_id=c.id
  6                  AND l.login_dt > ADD_MONTHS(SYSDATE,-6)
  7                  );
194637 rows deleted.
SQL>
SQL> SELECT COUNT(*) FROM tt_customers;
  COUNT(*)
----------
    835419
SQL>
SQL> UPDATE CUSTOMERS c SET BONUS=5
  2  WHERE EXISTS(SELECT 1 FROM tt_customers tc WHERE tc.id=c.id);
835419 rows updated.
SQL>
SQL> SELECT COUNT(*) FROM tt_customers;
  COUNT(*)
----------
    835419
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT COUNT(*) FROM tt_customers;
  COUNT(*)
----------
         0

SQL>

By default in Oracle a SQL/PLSQL block/statement starts implicitly a transaction.

DBA Tips for Global Temporary Tables (GTT)

As the statement drop doesn’t exist for global temporary tables the command to create the table it’s the same as the previous one:

CREATE GLOBAL TEMPORARY TABLE tt_customer
(
     customer_id NUMBER
)
ON COMMIT DELETE ROWS;

The equivalent snippet of code in Oracle to purge the customer table it’s the following:

SQL> INSERT INTO tt_customers(id)
  2  SELECT l.user_id
  3    FROM users u JOIN login l
  4           ON (l.user_id=u.user_id)
  5   WHERE l.login_dt < ADD_MONTHS(SYSDATE,-12);
194637 rows created.
SQL>
SQL> INSERT INTO tt_customers(id)
  2  SELECT user_id
  3    FROM web_deactive;
2143 rows created.
SQL>
SQL> INSERT INTO tt_customers(id)
  2  SELECT user_id
  3    FROM web_black_list;
4234 rows created.
SQL>
SQL> INSERT INTO customers_historical(id,name)
  2  SELECT c.id,c.name
  3  FROM customers c,
  4  tt_customers tc
  5  WHERE tc.id = c.id;
201014 rows created.
SQL>
SQL> DELETE FROM customers c
  2  WHERE EXISTS (SELECT 1 FROM  tt_customers tc WHERE tc.id = c.id );
201014 rows deleted.

The pg_global_temp_tables Library

As mentioned above, the temporary tables in PostgreSQL can not be invoked using the notation schema.table, so the pg_global_temp_tables library (there are some similar libraries available on github) it’s a workaround very useful to be used in database migrations from Oracle to PostgreSQL.

In order to keep the Oracle notation schema.temporary_table in queries or stored procedures:

SELECT c.id,c.nam
    FROM web_app.tt_customers tc,
                 Web_app.customers c
    WHERE c.id = tc.id

It allows to remain the temporary tables over the code with the schema notation.

Basically, it consists in a view: web_app.tt_customers created under the schema on which it’s supposed to have the temporary table and this view will query the temporary table tt_customers through a function called web_app.select_tt_customers:

CREATE OR REPLACE VIEW WEB_APP.TT_CUSTOMERS AS 
  SELECT * FROM WEB_APP.SELECT_TT_CUSTOMERS();

This function returns the contents of temporary table:

CREATE OR REPLACE FUNCTION WEB_APP.SELECT_TT_CUSTOMERS() RETURNS TABLE(ID INR, NAME VARCHAR) AS $$
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS TT_CUSTOMERS(ID INT, NAME) ON COMMIT DROP;
    RETURN QUERY SELECT * FROM TT_CUSTOMERS;
END;
$$ LANGUAGE PLPGSQL;

Summary

The temporary tables are used essentially to store intermediate results and thus avoid complex and heavy computing,

Thereafter it’s listed some characteristics of temporary tables either in PostgreSQL or Oracle:

It can be used on view
It can use the TRUNCATE command
It can not be partitioned
The Foreign key constraint on temporary tables are not allowed
This kind of tables are an alternative for CTEs (Common Table Expressions) also known for the Oracle professionals as WITH clause
In terms of security and privacy, these tables are a valuable asset because the data is only visible for a current session
The temporary tables are automatically dropped (in PostgreSQL) or deleted(in Oracle) once the session/transaction ends.

For the temporary tables in PostgreSQL it’s advisable do not use the same name of a permanent table in a temporary table. On the Oracle side it’s a good practice the generation of statistics for the sessions that include considerable volume of data in GTT in order to force the Cost-Based Optimizer (CBO) to choose the best plan for the queries that are using these kind of tables.

Beyond Semantics: Enhancing Retrieval Augmented Generation with Hybrid Search (pgvector + Elasticsearch)

Simplifying database orchestration anywhere with Terraform and ClusterControl

ClusterControl now easily deployable in Kubernetes with our Helm Chart

Improving LLM Fidelity with Retrieval Augmented Generation using pgvector