TRUNCATE TABLE vs. PURGE_TABLE

[becky]

Hi all,

What is the real difference between the TRUNCATE TABLE command and the PURGE_TABLE function?

The SQL Reference Manual says this about the TRUNCATE TABLE command:

Removes all storage associated with a table, while preserving the table definitions.

... and this about the PURGE_TABLE function:

Permanently removes deleted data from physical storage so that the disk space can be reused.

They appear to do the same thing. If that's true, which method of removing data is recommended?

[scutter]

The purge functions remove rows that have been deleted in epochs prior to the Ancient History Mark (AHM). TRUNCATE TABLE removes all rows.


dbadmin=> create table d(i integer);
CREATE TABLE
dbadmin=> insert into d values(1); commit;
OUTPUT
--------
1
(1 row)

COMMIT
dbadmin=> insert into d values(2); commit;
OUTPUT
--------
1
(1 row)

COMMIT
dbadmin=> insert into d values(3); commit;
OUTPUT
--------
1
(1 row)

COMMIT
dbadmin=> select * from d;
i
---
1
2
3
(3 rows)

dbadmin=> delete from d where i=3;
OUTPUT
--------
1
(1 row)

dbadmin=> select make_ahm_now()
dbadmin-> ;
make_ahm_now
-----------------------------
AHM set (New AHM Epoch: 53)
(1 row)

dbadmin=> insert into d values(4); commit;
OUTPUT
--------
1
(1 row)

COMMIT
dbadmin=> insert into d values(5); commit;
OUTPUT
--------
1
(1 row)

COMMIT
dbadmin=> delete from d where i=4;
OUTPUT
--------
1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> select * from d;
i
---
1
2
5
(3 rows)

dbadmin=> select *, epoch from d;
i | epoch
---+-------
1 | 49
2 | 50
5 | 55
(3 rows)

dbadmin=> select * from delete_vectors;
node_name | schema_name | projection_name | storage_type | dv_oid | storage_oid | deleted_row_count | used_bytes | start_epoch | end_epoch
-----------------+-------------+-----------------+--------------+-------------------+-------------------+-------------------+------------+-------------+-----------
v_test_node0001 | public | d_super | DVROS | 45035996273812093 | 45035996273812083 | 1 | 51 | 52 | 52
v_test_node0001 | public | d_super | DVWOS | 45035996273812109 | 45035996273812095 | 1 | 16384 | 56 | 56
(2 rows)

dbadmin=> select purge_table('d');
purge_table
-----------------------------------------------------------------------
Task: purge operation
(Table: public.d) (Projection: public.d_super)

(1 row)

dbadmin=> select *, epoch from d;
i | epoch
---+-------
1 | 49
2 | 50
5 | 55
(3 rows)

dbadmin=> select * from delete_vectors;
node_name | schema_name | projection_name | storage_type | dv_oid | storage_oid | deleted_row_count | used_bytes | start_epoch | end_epoch
-----------------+-------------+-----------------+--------------+-------------------+-------------------+-------------------+------------+-------------+-----------
v_test_node0001 | public | d_super | DVWOS | 45035996273812109 | 45035996273812095 | 1 | 16384 | 56 | 56
(1 row)

dbadmin=> select make_ahm_now();
make_ahm_now
-----------------------------
AHM set (New AHM Epoch: 57)
(1 row)

dbadmin=> select * from delete_vectors;
node_name | schema_name | projection_name | storage_type | dv_oid | storage_oid | deleted_row_count | used_bytes | start_epoch | end_epoch
-----------------+-------------+-----------------+--------------+-------------------+-------------------+-------------------+------------+-------------+-----------
v_test_node0001 | public | d_super | DVROS | 45035996273812195 | 45035996273812185 | 1 | 51 | 56 | 56
(1 row)

dbadmin=> select purge_table('d');
purge_table
-----------------------------------------------------------------------
Task: purge operation
(Table: public.d) (Projection: public.d_super)

(1 row)

dbadmin=> select * from delete_vectors;
node_name | schema_name | projection_name | storage_type | dv_oid | storage_oid | deleted_row_count | used_bytes | start_epoch | end_epoch
-----------+-------------+-----------------+--------------+--------+-------------+-------------------+------------+-------------+-----------
(0 rows)