When working with PostgreSQL, many users think that the TRUNCATE command simply removes all rows from a table. While the result appears similar to DELETE without a WHERE clause, the internal behavior is completely different. Instead of scanning and removing each tuple, PostgreSQL performs a much more efficient operation by replacing the table's physical storage with a newly created file.
In this blog, we will explore what happens internally when the TRUNCATE command is executed. Using PostgreSQL system catalogs and storage-related functions, we will examine the table before and after truncation. By comparing the table's file path, relfilenode, page count, and storage information, we will clearly see how PostgreSQL creates a new physical file for the table and releases the old one, making TRUNCATE one of the fastest ways to remove all data from a table.
Now create a sample table to understand the internal working of the truncate command.
CREATE TABLE truncate_test (
id integer,
name text,
amount numeric
);
Now insert some values.
INSERT INTO truncate_test
SELECT
i,
'user_' || i,
i * 10
FROM generate_series(1, 100000) AS i;
Now check the count of this table.
SELECT count(*)
FROM truncate_test;
Result:
count
--------
100000
(1 row)
We can check the metadata of this table from the pg_catalogue named pg_stat_user_tables like this.
select * from pg_stat_user_tables where relname = 'truncate_test';
Result:
-[ RECORD 1 ]----------+---------------------------------
relid | 41306
schemaname | public
relname | truncate_test
seq_scan | 1
last_seq_scan | 2026-10-07 16:32:02.903317+05:30
seq_tup_read | 100000
idx_scan |
last_idx_scan |
idx_tup_fetch |
n_tup_ins | 100000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 0
n_live_tup | 100000
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2026-10-07 16:32:00.746872+05:30
last_analyze |
last_autoanalyze | 2026-10-07 16:32:00.828477+05:30
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 1
total_vacuum_time | 0
total_autovacuum_time | 20
total_analyze_time | 0
total_autoanalyze_time | 82
Now this metadata includes this table related metadata like its id, schema name, sequential scan related metadata, index scan related metadata, tuples related metadata, vacuum and analyze related metadata.
Now execute the ANALYZE and VACUUM commands to refresh the latest statistics of this table for the Postgres planner and clean the dead tuples.
analyze truncate_test ;
VACUUM truncate_test;
Now check the size of this table by using the below query.
SELECT
pg_size_pretty(pg_relation_size('truncate_test')) AS table_size,
pg_relation_size('truncate_test') AS size_bytes;
Result:
-[ RECORD 1 ]-------
table_size | 5096 kB
size_bytes | 5218304
Now check how many pages are exist in this table like this.
SELECT
relname,
relpages,
reltuples
FROM pg_class
WHERE oid = 'truncate_test'::regclass;
Result :
-[ RECORD 1 ]------------
relname | truncate_test
relpages | 637
reltuples | 100000
Postgres has a parameter named block size, and that defines the size of the pages.
During the postgres server initialization, we can change this value, but the default value is 8192 bytes.
SHOW block_size;
Result:
-[ RECORD 1 ]----
block_size | 8192
We can also get the correct page counts without using the pg_class catalogue like this.
SELECT
pg_relation_size('truncate_test') / current_setting('block_size')::integer
AS physical_pages;
Result:
-[ RECORD 1 ]--+----
physical_pages | 637
Now check the location of this file.
SELECT pg_relation_filepath('truncate_test');Result:
pg_relation_filepath
----------------------
base/5/41306
(1 row)
Now check the path of the data directory.
show data_directory;
Result:
data_directory
-----------------------------
/var/lib/postgresql/18/main
(1 row)
Now go to the path inside data directory and check the table’s related file paths.
postgres@cybrosys:/home/cybrosys$ cd /var/lib/postgresql/18/main/base/5/
postgres@cybrosys:~/18/main/base/5$ ls
Result :
112 13427_fsm 13442_fsm 2600 2605_vm 2610_vm 2618 2658 2675 2692 2830 2840_fsm 3257 3439 3541_vm 3602 3764_vm 4149 4166 6110 826
113 13427_vm 13442_vm 2600_fsm 2606 2611 2618_fsm 2659 2678 2693 2831 2840_vm 3258 3440 3542 3602_fsm 3766 4150 4167 6111 827
1247 13430 13445 2600_vm 2606_fsm 2612 2618_vm 2660 2679 2696 2832 2841 3350 3455 3574 3602_vm 3767 4151 4168 6112 828
1247_fsm 13431 13446 2601 2606_vm 2612_fsm 2619 2661 2680 2699 2833 2995 3351 3456 3575 3603 3997 4152 4169 6113 pg_filenode.map
1247_vm 13432 1417 2601_fsm 2607 2612_vm 2619_fsm 2662 2681 2701 2834 2996 3379 3456_fsm 3576 3603_fsm 41306 4153 4170 6116 pg_internal.init
1249 13432_fsm 1418 2601_vm 2607_fsm 2613 2619_vm 2663 2682 2702 2835 3079 3380 3456_vm 3596 3603_vm 41306_fsm 4154 4171 6117 PG_VERSION
1249_fsm 13432_vm 174 2602 2607_vm 2615 2620 2664 2683 2703 2836 3079_fsm 3381 3466 3597 3604 41306_vm 4155 4172 6175
1249_vm 13435 175 2602_fsm 2608 2615_fsm 2650 2665 2684 2704 2836_fsm 3079_vm 3394 3467 3598 3605 41309 4156 4173 6176
1255 13436 2187 2602_vm 2608_fsm 2615_vm 2651 2666 2685 2753 2836_vm 3080 3394_fsm 3468 3599 3606 41310 4157 4174 6228
1255_fsm 13437 2224 2603 2608_vm 2616 2652 2667 2686 2753_fsm 2837 3081 3394_vm 3501 3600 3607 4143 4158 5002 6229
1255_vm 13437_fsm 2228 2603_fsm 2609 2616_fsm 2653 2668 2687 2753_vm 2838 3085 3395 3502 3600_fsm 3608 4144 4159 548 6237
1259 13437_vm 2328 2603_vm 2609_fsm 2616_vm 2654 2669 2688 2754 2838_fsm 3118 3429 3503 3600_vm 3609 4145 4160 549 6238
1259_fsm 13440 2336 2604 2609_vm 2617 2655 2670 2689 2755 2838_vm 3119 3430 3534 3601 3712 4146 4163 6102 6239
1259_vm 13441 2337 2605 2610 2617_fsm 2656 2673 2690 2756 2839 3164 3431 3541 3601_fsm 3764 4147 4164 6104 6351
13427 13442 2579 2605_fsm 2610_fsm 2617_vm 2657 2674 2691 2757 2840 3256 3433 3541_fsm 3601_vm 3764_fsm 4148 4165 6106 6352
Now we can see the files related to the truncate_test table.
When PostgreSQL creates a table, it doesn't just create one file. It usually creates three files for the table:
41306
This is the main relation file that contains the actual data.
41306_fsm
This file is the free Space Map that has the information about how many free spaces are available on each page
41306_vm
This file contains the Visibility Map related metadata, including whether all tuples on a page are visible to every transaction.
Check the complete metadata from the pg_class catalogue based on the table named truncate_test table like this.
SELECT
oid,
relname,
relfilenode,
pg_relation_filepath(oid)
FROM pg_class
WHERE oid = 'truncate_test'::regclass;
Result:
oid | relname | relfilenode | pg_relation_filepath
-------+---------------+-------------+----------------------
41306 | truncate_test | 41306 | base/5/41306
(1 row)
Now truncate the table.
TRUNCATE TABLE truncate_test;
Now check the counts again.
SELECT count(*) FROM truncate_test;
Result:
count
-------
0
(1 row)
Check the size also.
SELECT
pg_size_pretty(pg_relation_size('truncate_test')) AS table_size,
pg_relation_size('truncate_test') AS size_bytes;
Result:
table_size | size_bytes
------------+------------
0 bytes | 0
(1 row)
Now check the pages in the tables also.
SELECT
pg_relation_size('truncate_test') / current_setting('block_size')::integer
AS physical_pages;
Result:
physical_pages
----------------
0
(1 row)
Now check the location of this table’s files.
SELECT
oid,
relname,
relfilenode,
pg_relation_filepath(oid)
FROM pg_class
WHERE oid = 'truncate_test'::regclass;
Result:
oid | relname | relfilenode | pg_relation_filepath
-------+---------------+-------------+----------------------
41306 | truncate_test | 41365 | base/5/41365
(1 row)
Now we can see that after the truncate command execution, the location of this table is changed.
This behavior explains why TRUNCATE is significantly faster than deleting every row individually. Rather than processing each tuple, PostgreSQL replaces the table's underlying storage, allowing the operation to complete quickly regardless of the number of rows. Understanding this internal file replacement mechanism provides a clearer picture of how PostgreSQL efficiently manages storage and why TRUNCATE is the preferred choice when an entire table needs to be emptied.