Table administration

Published on July 23, 2025

Below are some useful commands for administering your database tables.

Drop a table

If a table no longer has any value in your project, drop it

DROP TABLE IF EXISTS table_name
;

Truncate / clear all data.

Remove all data but keep its schema intact

TRUNCATE TABLE your_table_name
;

The above command will work unless there are foreign key constraints. For PostgreSQL cascade the truncation to the related tables.

TRUNCATE TABLE your_table_name CASCADE
;

Disk space usage

This command gives you the tablename and the associated filesystem space usage.

SELECT 
  schemaname || '.' || relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10
;
Output… 
           table_name            | total_size | table_size | index_size
---------------------------------+------------+------------+------------
 public.dispatch_scada           | 6228 MB    | 4198 MB    | 2030 MB
 public.actual_generation        | 661 MB     | 440 MB     | 221 MB
 public.dispatch_price           | 223 MB     | 157 MB     | 66 MB
 public.tradingis_price          | 133 MB     | 133 MB     | 72 kB
 public.tradingis_interconnector | 90 MB      | 90 MB      | 56 kB
 public.bom_weather_metrics      | 52 MB      | 38 MB      | 14 MB
 public.rooftop_pv_actual        | 35 MB      | 19 MB      | 17 MB
 public.stock_prices             | 18 MB      | 13 MB      | 4600 kB
 public.market_notice            | 1192 kB    | 1104 kB    | 88 kB
 public.auction_units            | 384 kB     | 264 kB     | 120 kB
(10 rows)

Previous

Next