Using Firebird Global Temporary Tables for Efficient ETL Staging

Why GTTs Matter for ETL in Firebird

When you need a scratchpad for batch loads or session‑scoped work, Firebird’s Global Temporary Tables (GTTs) are the native solution. Unlike permanent tables, GTTs keep their data only in the database’s temporary sort space, never in the main data pages. This keeps the main database file lean, avoids unnecessary backups of staging data, and lets you isolate work per connection or transaction.

Defining a GTT: Syntax and ON COMMIT

GTTs are created with the same CREATE TABLE syntax as regular tables, but you must add the GLOBAL TEMPORARY keyword and an ON COMMIT clause. The clause determines when rows are discarded:

CREATE GLOBAL TEMPORARY TABLE etl_stage (
    id          BIGINT NOT NULL,
    payload     BLOB SUB_TYPE 1,
    ingested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ON COMMIT DELETE ROWS;

DELETE ROWS clears the table at the end of each transaction, making it ideal for per‑batch staging. If you need the data to persist across transactions within the same connection, use PRESERVE ROWS and remember to delete explicitly.

Isolation Semantics

GTT data is private to the session that inserted it. Even if you commit the transaction, another connection will see an empty table:

-- Session 1
INSERT INTO etl_stage VALUES (1, 'foo');
COMMIT;

-- Session 2
SELECT * FROM etl_stage;  -- returns 0 rows

When PRESERVE ROWS is used, the data remains until the connection closes or you issue DELETE FROM etl_stage. This isolation guarantees that staging doesn’t leak between users but also means you can’t share a GTT across parallel processes.

Statistics and the Optimizer

Unlike permanent tables, Firebird does not automatically gather statistics for GTT indexes. Consequently, the optimizer treats GTT rows as having unknown selectivity, which can lead to suboptimal join plans (e.g., nested‑loop joins instead of hash or merge). If you notice performance regressions when querying a GTT, consider:

• Using a permanent staging table for the bulk load and then copying the data into the GTT for the final merge.
• Running SET STATISTICS INDEX on the GTT’s indexes if your Firebird version supports it (this is optional and may be ignored on some releases).
• Re‑creating the GTT after a large load so that the optimizer can re‑estimate row counts.

Practical ETL Example: Batch Load into a Permanent Table

1. Define the GTT as shown above.

2. Load data via an EXECUTE BLOCK (or a client‑side bulk insert if your driver supports it). For illustration:

   EXECUTE BLOCK AS
   BEGIN
       FOR i = 1 TO 100000 DO
       BEGIN
           INSERT INTO etl_stage (id, payload) VALUES (i, 'data ' || i);
       END
   END;
   

3. Merge into the target table. All steps run in a single transaction to keep lock contention on the permanent table minimal:

   MERGE INTO customers tgt
   USING etl_stage src
   ON (tgt.id = src.id)
   WHEN MATCHED THEN UPDATE SET
       tgt.payload = src.payload,
       tgt.updated_at = CURRENT_TIMESTAMP
   WHEN NOT MATCHED THEN INSERT (id, payload, created_at)
   VALUES (src.id, src.payload, CURRENT_TIMESTAMP);
   

4. Commit the transaction. With ON COMMIT DELETE ROWS, the GTT is automatically cleared.

Monitoring Temp Space Usage

Large GTT loads consume the temporary sort space defined by the TempBlockSize and TempCacheLimit settings in firebird.conf (the default TempCacheLimit is 64 MB in Firebird 3+). To check how much temp space a GTT is using, query the monitoring view MON$STATISTICS:

SELECT MON$STATISTICS_NAME, MON$STATISTICS_VALUE
FROM MON$STATISTICS
WHERE MON$STATISTICS_NAME IN ('TEMP_SPACE_USED', 'TEMP_SPACE_LIMIT');

If TEMP_SPACE_USED approaches TEMP_SPACE_LIMIT, consider splitting the load into smaller batches or increasing TempCacheLimit in firebird.conf and reloading the database.

Foreign Key Constraints and Replication

• GTTs can reference permanent tables via foreign keys, but permanent tables cannot reference GTT rows. This is because permanent data must persist beyond the lifetime of a temporary table.
• GTT metadata is replicated to replicas, but the data itself is not. After a restore, GTTs exist but are empty.
• Backups (gbak) include GTT definitions but never the data. This keeps backup sizes small.

Trade‑Offs and Limitations

• No Cross‑Connection Sharing: GTTs are session‑private. If you need shared temp space, use a permanent staging table.
• Long‑Running Transactions: With PRESERVE ROWS, data lives until the connection closes. In a connection‑pooled environment, forgetting to clean up can exhaust temp space.
• Statistics Not Auto‑Collected: The optimizer may choose suboptimal plans for large GTT loads. Manual statistics or a permanent staging table can mitigate this.

Actionable Checklist for Production Use

1. Define GTTs with ON COMMIT DELETE ROWS unless you need persistence across transactions.
2. Load data in batches; monitor temp space via MON$STATISTICS to avoid spills.
3. After each load, consider running SET STATISTICS INDEX if supported, or recreate the GTT to refresh optimizer estimates.
4. Use a single transaction for the load and merge to keep locks short.
5. In connection‑pooled apps, explicitly DELETE FROM etl_stage before reusing a session or rely on PRESERVE ROWS with a cleanup routine.
6. Document that GTTs are local to the node; they are not replicated.

By following these guidelines, you can harness Firebird’s GTTs for fast, isolated ETL staging while keeping temp space usage predictable and the optimizer aware of your data volumes.