What is a temporary table in a relational database, and how do its visibility and lifetime differ from an ordinary table?
answer
- real table, private to one session
- auto-dropped at session or transaction end
- tempdb / temp schema / temp tablespace
- unlogged: fast, not crash-safe, not on replicas
- costs: catalog churn + missing statistics
basics
~20 sA temporary table holds intermediate rows for one session (or one transaction). Its data is private to the creating session, it lives in a special temporary namespace, it is dropped automatically at session or transaction end, and it is typically unlogged and never crash-recovered.
solid answer
~60 sA temporary table is a real table — real columns, real indexes, real statistics — whose **contents and usually whose definition are private to one session**. Two sessions can create temp tables with the same name and see completely different data; neither can see the other's rows. Key properties: - **Lifetime**: dropped automatically when the session ends, or at transaction end if declared that way. No cleanup code required. - **Visibility**: rows are visible only to the owning session. This is stronger than transaction isolation — it is a separate object. - **Storage**: lives in a temporary schema or a dedicated space (`tempdb`, a temp tablespace), typically with reduced or no write-ahead logging, so it is fast to fill but not crash-safe and generally not replicated. - **Catalog cost**: creating one writes catalog rows, so creating them at very high frequency has real overhead. Use them to stage an intermediate result you will read several times, index, or join — a computed working set in a multi-step procedure or ETL job.
code
sql · 11 linesCREATE TEMP TABLE recent_orders AS
SELECT id, customer_id, total
FROM orders
WHERE created_at >= current_date - 7;
CREATE INDEX ON recent_orders (customer_id);
ANALYZE recent_orders;
SELECT c.name, count(*), sum(r.total)
FROM recent_orders r JOIN customer c ON c.id = r.customer_id
GROUP BY c.name;go deeper
State the core recall: a real table private to your session that the database drops automatically, used to hold intermediate results.
Add the two scopes, reduced logging and its consequences, and the three reasons to materialize: reuse, statistics, indexability.
Bring in operational costs — catalog churn at high call rates, missing statistics, shared temp space exhaustion, and stale tables on pooled connections.
Discuss when staging belongs in the database at all versus in the application or a pipeline tool, and set conventions for scope and cleanup that survive connection pooling.
## What it is A temporary table is an ordinary table in almost every respect — typed columns, constraints, indexes, statistics, a query plan built over it — with one difference that changes everything: **it belongs to a session**, not to the database's shared, durable content. When a session creates one, the engine places it in a private namespace: a per-session temporary schema in PostgreSQL, `tempdb` with a name-mangled suffix in SQL Server, a temporary tablespace segment in Oracle, per-connection storage in MySQL. Another session creating a temp table of the same name gets a distinct object. Neither sees the other's data. ## Lifetime The defining convenience is automatic cleanup. Two scopes exist: - **Session-scoped** (the default nearly everywhere): the table lives until the session disconnects, then vanishes with its data. - **Transaction-scoped**: the table or its rows disappear at COMMIT, controlled by an `ON COMMIT` clause. This is what makes temp tables safe inside procedures that run many times per connection. Because cleanup is automatic, a crashed client cannot leave debris behind — a genuine advantage over "scratch" permanent tables that must be dropped explicitly and get orphaned when something fails halfway. ## Storage and durability Temporary data does not need to survive a crash: if the server restarts, the session is gone and the data is meaningless. Engines exploit that by reducing or eliminating write-ahead logging for temp tables, keeping them in unshared buffers, and skipping replication. The result is fast writes — a bulk insert into a temp table is much cheaper than into a logged permanent table — and three consequences to remember: 1. Not crash-safe, by design. 2. Not visible on replicas, so you cannot stage on a primary and read on a standby. 3. Temporary space is finite. A runaway job filling a temp tablespace or `tempdb` can affect *every* session that needs sort or hash spill space there, which is why temp-space exhaustion is a shared-resource incident, not a private one. ## Why a temp table rather than a subquery The reasons are always the same three: - **Reuse**: an expensive intermediate consumed several times is computed once. - **Statistics**: after populating and analyzing, the optimizer has real row counts and histograms for later joins, instead of estimating through a complex expression. - **Indexing**: you can add an index on the intermediate, which no inline construct lets you do. If none of the three applies, an inline derived table is usually simpler and lets the optimizer see everything at once. ## The costs people forget **Catalog churn.** Creating a temp table writes rows into system catalogs. A procedure that creates one per call, invoked thousands of times a second, becomes a catalog-contention problem — historically a well-known `tempdb` metadata bottleneck in SQL Server and a source of catalog bloat plus aggressive autovacuum pressure in PostgreSQL. The mitigation is either to create the table once per session and truncate/delete rows per use, or to use a transaction-scoped table that at least bounds its lifetime. **Stale or missing statistics.** A freshly created temp table has none, so the first queries against it may plan on defaults. Explicitly gathering statistics after populating it is often the difference between a good and a terrible plan — and is one of the main reasons to have materialized in the first place. **Connection pooling.** Pooled connections are reused by different logical requests. A session-scoped temp table left behind by one request is still there for the next one on the same physical connection, with stale rows. Either scope the table to the transaction or ensure the pool resets the session between checkouts. **Distributed and serverless environments.** Some managed or distributed SQL engines restrict or reinterpret temp tables. Do not assume the behaviour is uniform across platforms. ## A note on terminology The SQL standard distinguishes *created* temporary tables — a persistent definition whose contents are per-session — from *declared local* temporary tables, created on the fly for a session. Vendors diverge from both and from each other; what matters in an interview is that you can articulate the two independent axes: **who can see the rows** (this session only) and **how long they live** (transaction or session).
- Why are temporary tables usually not written to the write-ahead log, and what does that cost you?Their contents are meaningless after a crash, because the owning session no longer exists, so logging them for recovery would be pure overhead. Skipping the log makes bulk population substantially cheaper than filling a permanent table. The costs are that the data is not crash-recoverable and is not shipped to replicas, so a temp table can never be part of a plan that involves reading it from another node.
- Give a concrete reason to prefer a temporary table over an inline derived table.When the intermediate result is consumed more than once, materializing it computes it once instead of per reference. It also lets you gather statistics and build an index on the intermediate, which gives the optimizer real cardinality information for the joins that follow — impossible with an inline construct. If the result is read once and the optimizer already estimates it well, the inline form is simpler and usually faster.
A scratch pad on your own desk: real paper you can write and re-read, nobody else can see it, and it is thrown away when you leave.
saying these in an interview costs you the question
- Thinking a temporary table is visible to other sessions, or that it merely holds "uncommitted" rows.
- Assuming they are always transaction-scoped; session scope is the common default.
- Believing temporary space is private — exhausting it affects every session sharing that space.
- Ignoring that creating them per call causes catalog contention at high rates.
- Expecting the optimizer to plan well over a freshly created temp table with no statistics.
- Assuming a temp table can be read on a replica.