Your Temp Table Isn’t as Temporary as You Think
A data engineer on a Vertica cluster notices something strange: a batch job that spins up a handful of temp tables for intermediate results is making unrelated analyst queries slower. Nobody touched the same tables. Nobody is fighting over a lock on the same rows. And yet, during the batch window, dashboard queries across the warehouse crawl.
The instinct is to blame contention on the data. The actual answer is upstream of the data entirely: CREATE TEMP TABLE is DDL, and DDL has to be recorded somewhere. On that cluster, "somewhere" is the same global catalog every other table's definition lives in — so a burst of temp-table creation collides with anything else that needs to read or write that catalog, whether or not it has anything to do with the batch job's actual data.
That’s the part “temp” hides. And it’s tempting to file this under “Vertica vs. everyone else” — but that framing collapses the moment you look closer. Temp table behavior isn’t one decision. It’s four, and engines pick their own answer to each one independently:
- Does creating or dropping the table contend for a global resource?
- Where does the table’s definition live?
- Where does the table’s data physically live?
- How long does any of it survive?
Six engines — Vertica, Oracle, PostgreSQL, Snowflake, BigQuery, Redshift — turn out to be six specific combinations of answers to those four questions, not six unrelated designs.
The Architecture at a Glance
Same statement — CREATE TEMP TABLE — but four independent dials decide what actually happens: whether the DDL itself contends for a lock, where the definition is tracked, where the data is stored, and how long any of it lasts.
Axis 1: Does Creating the Table Contend for a Global Resource?
This is the axis that produced the original story. Vertica’s CREATE TEMPORARY TABLE supports a GLOBAL or LOCAL scope — and GLOBAL is the default unless the DefaultTempTableLocal configuration parameter says otherwise. A global temp table's definition lands in the same catalog every permanent table uses, and DDL against that shared catalog takes an exclusive lock that can briefly block other queries from starting. A LOCAL temp table skips all of that — no global lock, no footprint on catalog size — but you have to ask for it, either explicitly or by changing the default.
Oracle’s traditional Global Temporary Table (GTT) makes the same trade as Vertica’s GLOBAL mode: its metadata is stored in the data dictionary the moment you create it, which is a dictionary write every other session’s queries may need to read. Oracle’s answer to the Vertica-LOCAL problem arrived later, in Oracle 18c, as the Private Temporary Table (PTT) — defined entirely in session memory, never touching the data dictionary at all.
The pattern worth remembering: the expensive answer is often the path of least resistance. Vertica’s plain CREATE TEMPORARY TABLE — no extra keyword — is the one that pays the catalog-lock cost. The cheap path exists, but you have to know to reach for it.
Axis 2: Where Does the Definition Live?
A temp table’s definition — its columns, types, constraints — has to be tracked somewhere, and “somewhere” splits into three distinct designs.
Global/shared namespace, visible to every session. Vertica’s GLOBAL mode puts the definition in the public schema; Oracle’s GTT puts it in the data dictionary. In both cases, this is a feature, not just a cost: once created, any session can reference the table by name without re-running the DDL. The shared visibility is what makes the lock contention from Axis 1 worth it for some workloads.
Get DataStrata’s stories in your inbox
Join Medium for free to get updates from this writer.
Session-private namespace, invisible to everyone else. Vertica’s LOCAL mode uses a dedicated V_TEMP_SCHEMA namespace, transparently added to the creating session's search path. PostgreSQL gives each session its own local copy of the relevant catalog entries. Redshift — built on PostgreSQL — does the same thing through a session-specific pg_temp_N schema. Oracle's PTT goes further still: it never enters the data dictionary, full stop.
Handed off to an externalized metadata service. This is the cloud-warehouse answer, and it’s structurally different from “session-private” — the definition isn’t tracked by the compute layer at all. Snowflake’s Cloud Services layer owns all metadata — schemas, privileges, query plans — completely separately from the virtual warehouses that execute queries; a temp table is just another session-scoped object that layer tracks, invisible to other sessions and purged when the session ends. BigQuery goes a step further architecturally: without an explicit session, a query’s results land in a hidden anonymous dataset inside a system-managed compute project — not the user’s own dataset at all, with access restricted to the creator. With BigQuery’s Sessions feature, a temp table persists across multiple queries for the life of that session, in that same system-managed space.
Axis 3: Where Does the Data Physically Live?
This axis is easy to conflate with Axis 2, but they’re genuinely separate — a definition can be globally visible while the data behind it sits somewhere completely private, and vice versa.
Vertica and Oracle’s GTT both write temp-table data using the same storage mechanism as a permanent table — disk-backed projections or segments, allocated on first use, just with a shorter expected lifetime and (in Vertica’s case) the option to skip replica projections entirely by setting K-safety to zero for a speed-up, since the data isn’t expected to survive the session anyway.
Oracle’s PTT is the outlier worth noting on this axis specifically: its data lives in memory only and is never written to the redo log at all — not “deleted quickly,” but never durable in the first place.
Redshift keeps temp-table data on local storage on the compute nodes — released back to the node the moment the session ends and the table is dropped. Snowflake’s temp-table data lives in the same internal storage as any other table, but explicitly without Time Travel or Fail-safe — the durability guarantees that make permanent-table storage expensive are switched off. BigQuery’s temp data is stored the same way a regular table’s data is (its standard columnar format), just inside that access-restricted, system-managed dataset rather than the user’s own.
Axis 4: How Long Does Any of It Survive?
This is where the two clocks hiding inside a GLOBAL-style temp table become visible. For Vertica’s GLOBAL mode and Oracle’s GTT, the definition persists until someone explicitly drops it — it can outlive dozens of sessions that each reused the same table name. The data, meanwhile, resets on a completely different schedule, governed by the ON COMMIT clause both engines support: DELETE ROWS (the default in both) truncates the table after every commit, while PRESERVE ROWS keeps the data until the session itself ends.
For every session-private or externalized design, those two clocks collapse into one: Vertica’s LOCAL mode, Oracle’s PTT, PostgreSQL, Redshift, and Snowflake all tie both definition and data to the life of the session — there’s no separate “keep the shell around forever” option, because there’s no shared shell to keep around. BigQuery adds a fifth rhythm: a temp table created inside an explicit Session lives for that session, but a query run without one still leaves its results cached in an anonymous dataset for roughly 24 hours — a retention window tied to neither a session nor a transaction, but to the query result cache itself.
Putting the Four Axes Together
Laid out side by side, the six engines stop looking like six separate inventions and start looking like different settings on the same four dials.
Why This Shape Scales
The engines that still ask you to opt into the cheap answer — Vertica, Oracle — are also the ones with storage and compute coupled on the same hardware: the global-catalog default made sense when every session already shared the same box, and per-session isolation was the add-on. The engines that separated storage from compute — Snowflake, BigQuery — didn’t just decouple data from the compute layer; they decoupled the catalog from it too, by default, with no opt-in required. Redshift and PostgreSQL sit in between, proving the two decisions aren’t welded together: both keep storage and compute reasonably coupled while still isolating the catalog per session, because that isolation was simply how PostgreSQL was built from the start.
Which means the next time a temp-table-heavy job slows down everything around it, the useful question isn’t “which vendor is this.” It’s which of the four dials — DDL contention, definition location, data location, lifetime — is set to the expensive position, and whether there’s a cheaper setting one keyword away.
If you found this useful, follow DataStrata for more data engineering and AI architecture deep dives — technical depth with business context.
Tags: #DataStrata #DataEngineering #DataWarehouse #DatabaseArchitecture #SQL
