PostgreSQL (a popular database system) gives you different types of tables depending on what you need. Most of the time, you use regular tables that save your data permanently. But sometimes, you don’t need to keep the data forever — you just want things to run faster.
That’s where Temporary Tables and Unlogged Tables come in. These special types of tables help improve performance, especially when it’s okay to lose the data (like if it can be recreated).
- Temporary Tables
- Unlogged Tables
The Role of WAL in PostgreSQL
PostgreSQL usually follows approach called WAL (Write-Ahead Logging) where it stores every changes made to database in binary format in pg_wal directory. Every change made to the database is first recorded in the WAL, stored in the pg_wal directory in binary format, before being applied. This allows the system to replay changes in the event of a failure, maintaining consistency.
What Are Unlogged Tables?
Unlogged tables are introduced in PostgreSQL 9.1, designed for high-performance, memory-optimized operations. Unlike regular tables, unlogged tables do not write data to the Write-Ahead Log (WAL), making them significantly faster for operations where durability is not a priority becuase these tables are not crash safe.
These tables uses PostgreSQL's shared buffers, which stores in RAM. When a query is executed, PostgreSQL first checks the shared buffer for the required data. If the data is present, it is returned immediately; otherwise, it is fetched from disk and subsequently stored in the buffer for faster future access.
Shared buffers can be initalized mostly with 25-40% of RAM ,depending on available memory.
Syntax to create Unlogged Tables:
create unlogged table unlogged_data(
id primary key,
name text,
created_at timestamp
);
By default, the table is created in the public schema unless explicitly specified, helping avoid naming conflicts in shared environments.
when we use Unlogged tables:
They are ideal for:
- Data can be regenerated if lost.
- Use cases where crash recovery is not required
What Are Temporary Tables?
Temporary tables have been part of PostgreSQL since version 7.3,Temporary tables are scoped to a single database session or transaction. They use local buffers, and PostgreSQL automatically drops them once the session ends. Each session operates on its own isolated version of the table, which is ideal for handling user-specific or session-specific data.
Syntax to create Temporary Tables:
create temp table temp_session_data (
id primary key,
name text,
data jsonb
);
Temporary tables are not visible to other sessions.
When we use Temporary tables:
Temporary tables in PostgreSQL are optimized for scenarios where performance is critical, but data persistence is not required beyond the current session or transaction. They are particularly useful in cases where intermediate data must be processed quickly and discarded afterward.
They are ideal for :
- Session-based operations
- ETL steps within a session
Working with Temporary and Unlogged Tables
1.Using COPY command-Best for Bulk Loading
COPY my_unlogged_table FROM '/path/to/your/file.csv' DELIMITER ',' CSV HEADER;
2.Using INSERT - Standard Row Insertion
INSERT INTO temp_table (col1, col2) VALUES ('val1', 'val2');
These tables (temporary and unlogged) are often used to load data from main tables or external files, where you can perform transformations or apply filters before inserting the processed records back into the main table.
Features Comparison:
Scenario: Bulk Data Import Before Final Insertion
Suppose you’re importing 300,000 rows from a file or a query result into PostgreSQL.
Before inserting this data into the final main table, you load it into a staging area to check or process it.In PostgreSQL, there are three types of staging tables you could use:
- Temporary Table
- Unlogged Table
- Regular Table
The process is the same in all cases:
From file or query result → Insert into staging table → (eventually into main table)
Performance of each table:
Temporary and Unlogged tables are faster because they skip Write-Ahead Logging (WAL), while Regular tables are slower due to WAL overhead for durability and crash recovery.
conclusion:
In terms of performance, both temporary and unlogged tables behave similarly for most use cases. While the time and memory usage are almost the same, the key difference lies in how and when they’re used. The choice between the two should depend on the specific use case and requirements. Based on your scenario, choose the one that best fits your needs.