TRUNCATE: High-Speed Data Removal

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- Basic TRUNCATE Syntax
TRUNCATE TABLE table_name;
 
-- Short form (TABLE keyword optional in many DBMSs)
TRUNCATE table_name;
 
-- Truncate multiple tables
TRUNCATE TABLE table1, table2, table3;
 
-- Control identity column behavior (PostgreSQL)
TRUNCATE TABLE users RESTART IDENTITY;  -- Reset sequences to initial value
TRUNCATE TABLE users CONTINUE IDENTITY; -- Keep current sequence values (default)
 
-- Control foreign key behavior (PostgreSQL)
TRUNCATE TABLE parent_table CASCADE;    -- Also truncate referencing tables
TRUNCATE TABLE parent_table RESTRICT;   -- Fail if referenced by other tables (default)
 
-- Combined options
TRUNCATE TABLE orders RESTART IDENTITY CASCADE;
 
-- Example: Quick table cleanup
TRUNCATE TABLE temp_calculations;
TRUNCATE TABLE staging_import;
TRUNCATE TABLE session_data;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

-- ============================================
-- Performance Demonstration
-- ============================================
-- Setup: Create table with 10 million rows
CREATE TABLE performance_test AS
SELECT 
    generate_series AS id,
    md5(random()::text) AS data,
    NOW() - (random() * interval '365 days') AS created_at
FROM generate_series(1, 10000000);
 
-- DELETE all rows: Takes minutes, generates huge transaction log
-- EXPLAIN ANALYZE DELETE FROM performance_test;
-- Execution Time: ~180000 ms (3 minutes)
-- Transaction log: ~2GB
 
-- TRUNCATE: Takes milliseconds, minimal log
-- EXPLAIN ANALYZE TRUNCATE TABLE performance_test;
-- Execution Time: ~50 ms
-- Transaction log: ~8KB
 
 
-- ============================================
-- When DELETE Is Required
-- ============================================
 
-- 1. Selective deletion (WHERE clause needed)
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2023-01-01';
-- TRUNCATE cannot filter—it's all or nothing
 
-- 2. When triggers must fire
DELETE FROM audit_log WHERE entry_date < '2023-01-01';
-- If audit_log has AFTER DELETE trigger that archives data, you need DELETE
 
-- 3. When referenced by foreign keys (without cascade)
DELETE FROM customers WHERE customer_id = 1000;
-- Works if no orders reference this customer (or FK is ON DELETE CASCADE)
-- TRUNCATE would fail on any table referenced by FK
 
-- 4. During concurrent operations
DELETE FROM temp_data WHERE session_id = 'abc123';
-- DELETE allows other sessions to read/write other rows
-- TRUNCATE locks entire table exclusively
 
 
-- ============================================
-- When TRUNCATE Is Better
-- ============================================
 
-- 1. Clearing entire staging tables
TRUNCATE TABLE staging_import;
-- Fast, clean, no residue
 
-- 2. Resetting test data between test runs
TRUNCATE TABLE test_users, test_orders, test_products RESTART IDENTITY CASCADE;
-- Blazingly fast, resets auto-increment counters
 
-- 3. Clearing log tables
TRUNCATE TABLE application_logs;
-- When you don't need trigger-based archiving
 
-- 4. Refreshing data warehouses
TRUNCATE TABLE fact_sales;
-- Clear before full reload
 
-- 5. When you need space reclaimed immediately
TRUNCATE TABLE huge_temp_table;
-- Space is immediately available (no VACUUM needed)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

-- ============================================
-- Setup: Table with Identity Column
-- ============================================
CREATE TABLE users (
    user_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username    VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL
);
 
-- Insert some rows
INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),     -- user_id = 1
    ('bob', 'bob@example.com'),         -- user_id = 2
    ('charlie', 'charlie@example.com'); -- user_id = 3
 
-- Current sequence value is 3
 
 
-- ============================================
-- CONTINUE IDENTITY (Default in PostgreSQL)
-- ============================================
TRUNCATE TABLE users CONTINUE IDENTITY;
 
-- All rows deleted, but sequence continues
INSERT INTO users (username, email) VALUES ('david', 'david@example.com');
-- david gets user_id = 4 (continues from where sequence left off)
 
SELECT * FROM users;
-- user_id | username | email
-- --------|----------|------------------
-- 4       | david    | david@example.com
 
 
-- ============================================
-- RESTART IDENTITY
-- ============================================
TRUNCATE TABLE users RESTART IDENTITY;
 
-- All rows deleted AND sequence resets
INSERT INTO users (username, email) VALUES ('eve', 'eve@example.com');
-- eve gets user_id = 1 (sequence restarted)
 
SELECT * FROM users;
-- user_id | username | email
-- --------|----------|---------------
-- 1       | eve      | eve@example.com
 
 
-- ============================================
-- When to Use Which
-- ============================================
 
-- RESTART IDENTITY: Use when...
-- - Resetting test environments (fresh start)
-- - IDs have semantic meaning you want to preserve
-- - You want consistent, reproducible IDs across test runs
 
-- CONTINUE IDENTITY: Use when...
-- - Old IDs might still be referenced externally (URLs, logs, APIs)
-- - Reusing IDs could cause confusion or bugs
-- - You're clearing staging data but IDs are cached elsewhere
 
 
-- ============================================
-- Manual Sequence Reset (Alternative)
-- ============================================
-- If you need to reset sequence independently:
ALTER SEQUENCE users_user_id_seq RESTART WITH 1;
 
-- Or set to a specific value (next value will be 100)
ALTER SEQUENCE users_user_id_seq RESTART WITH 100;
 
-- Check current sequence value
SELECT currval('users_user_id_seq');  -- Last returned value
SELECT nextval('users_user_id_seq');  -- Get next value (advances sequence!)
 
 
-- ============================================
-- MySQL Equivalent: AUTO_INCREMENT
-- ============================================
-- MySQL resets AUTO_INCREMENT on TRUNCATE by default
-- To keep the counter, you'd need to save/restore manually:
 
-- Save current AUTO_INCREMENT value
-- SELECT AUTO_INCREMENT FROM information_schema.tables 
-- WHERE table_name = 'users';
 
-- After TRUNCATE, restore if needed
-- ALTER TABLE users AUTO_INCREMENT = 1000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

-- ============================================
-- Setup: Parent-Child Tables with Foreign Keys
-- ============================================
CREATE TABLE departments (
    dept_id     INTEGER PRIMARY KEY,
    dept_name   VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees (
    emp_id      INTEGER PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    dept_id     INTEGER REFERENCES departments(dept_id)
);
 
CREATE TABLE projects (
    project_id  INTEGER PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    lead_emp_id INTEGER REFERENCES employees(emp_id)
);
 
-- Insert sample data
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Marketing');
INSERT INTO employees VALUES (1, 'Alice', 1), (2, 'Bob', 1), (3, 'Charlie', 2);
INSERT INTO projects VALUES (1, 'WebApp', 1), (2, 'Campaign', 3);
 
 
-- ============================================
-- RESTRICT (Default): Prevent Orphan References
-- ============================================
TRUNCATE TABLE departments;
-- ERROR: cannot truncate a table referenced in a foreign key constraint
-- DETAIL: Table "employees" references "departments".
-- HINT: Truncate table "employees" at the same time, or use TRUNCATE ... CASCADE.
 
 
-- ============================================
-- CASCADE: Propagate Truncation
-- ============================================
TRUNCATE TABLE departments CASCADE;
-- NOTICE: truncate cascades to table "employees"
-- NOTICE: truncate cascades to table "projects"
 
-- All three tables are now empty!
SELECT COUNT(*) FROM departments;  -- 0
SELECT COUNT(*) FROM employees;    -- 0
SELECT COUNT(*) FROM projects;     -- 0
 
 
-- ============================================
-- Visualizing the Cascade Chain
-- ============================================
-- departments 
--     └── employees (FK: dept_id → departments.dept_id)
--              └── projects (FK: lead_emp_id → employees.emp_id)
--
-- CASCADE on departments travels the entire chain
 
 
-- ============================================
-- Truncating Multiple Tables (Alternative to CASCADE)
-- ============================================
-- If you know the dependencies, truncate all at once
TRUNCATE TABLE projects, employees, departments;
-- Works because all related tables are truncated together
 
-- Order doesn't matter when truncating together
TRUNCATE TABLE departments, employees, projects;
-- Same result—PostgreSQL handles the ordering internally
 
 
-- ============================================
-- Finding Tables That Would Be Cascaded
-- ============================================
-- Before using CASCADE, check what would be affected:
WITH RECURSIVE cascade_chain AS (
    -- Start with direct references to target table
    SELECT 
        tc.table_name::text AS source_table,
        kcu.table_name::text AS referencing_table,
        1 AS depth
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage ccu
        ON ccu.constraint_name = tc.constraint_name
    WHERE tc.constraint_type = 'FOREIGN KEY'
        AND ccu.table_name = 'departments'
    
    UNION
    
    -- Recursively find tables referencing those tables
    SELECT 
        cc.referencing_table,
        kcu.table_name::text,
        cc.depth + 1
    FROM cascade_chain cc
    JOIN information_schema.table_constraints tc
        ON tc.constraint_type = 'FOREIGN KEY'
    JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_name = cc.referencing_table
)
SELECT DISTINCT referencing_table, depth
FROM cascade_chain
ORDER BY depth;
 
-- Result:
-- referencing_table | depth
-- ------------------|------
-- employees         | 1
-- projects          | 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

-- ============================================
-- TRUNCATE Is Transactional (PostgreSQL, SQL Server)
-- ============================================
BEGIN;
 
-- Save current row count
SELECT COUNT(*) FROM important_data;  -- Returns 50000
 
-- Truncate the table
TRUNCATE TABLE important_data;
 
-- Verify it's empty
SELECT COUNT(*) FROM important_data;  -- Returns 0
 
-- Oops, realized we truncated the wrong table!
ROLLBACK;
 
-- Data is restored!
SELECT COUNT(*) FROM important_data;  -- Returns 50000
 
 
-- ============================================
-- Safe TRUNCATE Pattern with Verification
-- ============================================
BEGIN;
 
-- Record pre-truncate state
SELECT 'Before: ' || COUNT(*) AS status FROM staging_data;
 
-- Perform truncate
TRUNCATE TABLE staging_data RESTART IDENTITY;
 
-- Verify state
SELECT 'After: ' || COUNT(*) AS status FROM staging_data;
 
-- Import new data
INSERT INTO staging_data (col1, col2)
SELECT col1, col2 FROM import_source;
 
-- Verify import succeeded
SELECT 'Imported: ' || COUNT(*) AS status FROM staging_data;
 
-- If everything looks good:
COMMIT;
 
-- If something went wrong, we could have used ROLLBACK
 
 
-- ============================================
-- TRUNCATE with Savepoints
-- ============================================
BEGIN;
 
-- Do some work
INSERT INTO log_table VALUES ('Step 1 completed');
 
SAVEPOINT before_truncate;
 
TRUNCATE TABLE temp_data;
 
-- Decide we need the temp data after all
ROLLBACK TO SAVEPOINT before_truncate;
 
-- temp_data is restored, log_table insert preserved
 
COMMIT;
 
 
-- ============================================
-- MySQL Gotcha: Engine-Dependent Behavior
-- ============================================
-- In MySQL with InnoDB:
-- - TRUNCATE IS transactional (can be rolled back... mostly)
-- - But TRUNCATE causes an implicit COMMIT before and after
-- - So practical effect: Cannot roll back TRUNCATE in MySQL
 
-- MySQL:
-- START TRANSACTION;
-- TRUNCATE TABLE test;  -- Implicit COMMIT here!
-- ROLLBACK;             -- Too late—nothing to roll back
-- SELECT * FROM test;   -- Still empty
 
 
-- ============================================
-- DDL Locking Behavior
-- ============================================
-- TRUNCATE acquires ACCESS EXCLUSIVE lock
-- This blocks ALL other operations on the table
 
-- Session 1:
BEGIN;
TRUNCATE TABLE busy_table;
-- Lock held until COMMIT/ROLLBACK
-- ... long-running transaction ...
 
-- Session 2 (concurrent):
SELECT * FROM busy_table;  -- BLOCKED! Waits for Session 1
 
-- This is why TRUNCATE on busy tables should be quick
-- Keep the transaction as short as possible

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

-- ============================================
-- Use Case 1: ETL Data Warehouse Refresh
-- ============================================
BEGIN;
 
-- Clear target tables (in dependency order or with CASCADE)
TRUNCATE TABLE fact_sales, dim_products, dim_customers RESTART IDENTITY CASCADE;
 
-- Load fresh dimension data
INSERT INTO dim_customers 
SELECT * FROM staging_customers;
 
INSERT INTO dim_products
SELECT * FROM staging_products;
 
-- Load fact table
INSERT INTO fact_sales
SELECT 
    c.customer_key,
    p.product_key,
    s.sale_date,
    s.quantity,
    s.amount
FROM staging_sales s
JOIN dim_customers c ON s.customer_id = c.source_customer_id
JOIN dim_products p ON s.product_id = p.source_product_id;
 
-- Verify row counts
SELECT 'fact_sales' AS table_name, COUNT(*) AS rows FROM fact_sales
UNION ALL
SELECT 'dim_customers', COUNT(*) FROM dim_customers
UNION ALL
SELECT 'dim_products', COUNT(*) FROM dim_products;
 
-- If counts look right:
COMMIT;
 
 
-- ============================================
-- Use Case 2: Test Fixture Reset
-- ============================================
-- Test helper function to reset all test tables
CREATE OR REPLACE FUNCTION reset_test_fixtures() 
RETURNS void AS $$
BEGIN
    -- Truncate in cascade order
    TRUNCATE TABLE 
        test_order_items,
        test_orders,
        test_products,
        test_customers
    RESTART IDENTITY CASCADE;
    
    -- Insert baseline test data
    INSERT INTO test_customers (name, email) VALUES
        ('Test User 1', 'test1@example.com'),
        ('Test User 2', 'test2@example.com');
    
    INSERT INTO test_products (name, price) VALUES
        ('Test Product A', 10.00),
        ('Test Product B', 20.00);
        
    RAISE NOTICE 'Test fixtures reset successfully';
END;
$$ LANGUAGE plpgsql;
 
-- Call before each test suite
SELECT reset_test_fixtures();
 
 
-- ============================================
-- Use Case 3: Staging Table Pipeline
-- ============================================
-- Step 1: Clear staging for new batch
TRUNCATE TABLE staging_import RESTART IDENTITY;
 
-- Step 2: Load raw data from file (COPY is fastest)
COPY staging_import (col1, col2, col3) 
FROM '/data/daily_import.csv' CSV HEADER;
 
-- Step 3: Transform and load to production
INSERT INTO production_data (col_a, col_b, processed_at)
SELECT 
    UPPER(col1),
    col2::INTEGER,
    NOW()
FROM staging_import
WHERE col3 IS NOT NULL;
 
-- Step 4: Log the batch
INSERT INTO batch_log (batch_date, rows_processed)
SELECT CURRENT_DATE, COUNT(*) FROM staging_import WHERE col3 IS NOT NULL;
 
-- Staging stays populated until next run
-- (Some prefer to truncate immediately)
 
 
-- ============================================
-- Use Case 4: Partitioned Table Management
-- ============================================
-- Truncate a specific partition (PostgreSQL 12+)
TRUNCATE TABLE sales_2023;  -- Assuming sales_2023 is a partition
 
-- Attach fresh partition
CREATE TABLE sales_2024 (LIKE sales INCLUDING ALL);
ALTER TABLE sales ATTACH PARTITION sales_2024
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

-- ============================================
-- Gotcha 1: Triggers Not Firing
-- ============================================
-- Setup: Audit trigger
CREATE TABLE audit_log (action TEXT, table_name TEXT, row_count BIGINT);
 
CREATE OR REPLACE FUNCTION log_delete() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log VALUES ('DELETE', TG_TABLE_NAME, 1);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER users_audit_delete
    AFTER DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION log_delete();
 
-- DELETE: Trigger fires for each row
DELETE FROM users WHERE status = 'inactive';  -- Audit log entries created
 
-- TRUNCATE: Trigger NEVER fires!
TRUNCATE TABLE users;  -- No audit log entries!
 
-- Solution: Use TRUNCATE-specific triggers (PostgreSQL 14+)
CREATE TRIGGER users_truncate_audit
    ON TRUNCATE ON users
    EXECUTE FUNCTION log_truncate();
 
 
-- ============================================
-- Gotcha 2: Concurrent Access Blocked
-- ============================================
-- Session 1:
BEGIN;
TRUNCATE TABLE reports;
-- Lock held...
 
-- Session 2 (runs concurrently):
SELECT * FROM reports;  -- BLOCKED! Hangs indefinitely
                        -- Will wait until Session 1 commits/rollbacks
 
-- Solution: Keep TRUNCATE transactions as short as possible
-- Or schedule during maintenance windows
 
 
-- ============================================
-- Gotcha 3: MVCC Phantom Data
-- ============================================
-- Session 1:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM data;  -- Returns 1000000
 
-- Session 2:
TRUNCATE TABLE data;  -- Succeeds (Session 1 is only reading)
 
-- Session 1 (continues):
SELECT COUNT(*) FROM data;  -- Still returns 1000000!
                            -- Session 1 sees pre-truncate snapshot
 
COMMIT;  -- Now would see 0
 
 
-- ============================================
-- Gotcha 4: MySQL Cannot Rollback TRUNCATE
-- ============================================
-- This DOES NOT work in MySQL:
-- START TRANSACTION;
-- TRUNCATE TABLE important_data;  -- Implicit COMMIT here!
-- ROLLBACK;                       -- Has no effect
-- SELECT * FROM important_data;   -- Empty!
 
-- Always backup before TRUNCATE in MySQL
 
 
-- ============================================
-- Gotcha 5: Unexpected CASCADE Scope
-- ============================================
-- Before cascading, check the full chain:
WITH RECURSIVE ref_tree AS (
    SELECT 
        ccu.table_name::text AS parent,
        tc.table_name::text AS child,
        1 AS level,
        ccu.table_name::text AS path
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
    WHERE tc.constraint_type = 'FOREIGN KEY'
      AND ccu.table_name = 'master_table'
    
    UNION ALL
    
    SELECT 
        ref_tree.child,
        tc.table_name::text,
        ref_tree.level + 1,
        ref_tree.path || ' → ' || tc.table_name::text
    FROM ref_tree
    JOIN information_schema.constraint_column_usage ccu 
        ON ccu.table_name = ref_tree.child
    JOIN information_schema.table_constraints tc 
        ON tc.constraint_name = ccu.constraint_name
    WHERE tc.constraint_type = 'FOREIGN KEY'
)
SELECT path, level FROM ref_tree ORDER BY level;
 
-- May reveal unexpected tables in the chain!