18 Oca by Şahap AŞÇI

PostgreSQL – Tablo Boyutlarının Listelenmesi

Çoğu veritabanı sisteminde olduğu gibi PostgreSQL de objelerin disk boyutlarının kolayca hesaplanabilmesi için bize çeşitli sistem fonksiyonları sunuyor. Bir tablonun boyutunu bu fonksiyonları kullanarak alabiliyoruz. Bu fonksiyonlardan;

pg_table_size Bir tablonun indexleri hariç kapladığı alan
pg_total_relation_size Bir tablonun her şey dahil kapladığı alan
pg_relation_size Bir objenin (tablo index vs ) diskte kapladığı alan. Bu fonksiyondan ek parametre ile daha detaylı bilgi almak mümkün.
pg_size_pretty Diğer fonksiyonlar byte olarak döner. Bunu okunabilir formata çevirir (kb, mb, gb gibi)

Bir tablonun boyutuna bakalım; size_test_table tablosunun içerisinde 1 adet index bulunuyor (primary key). Tablo buyutunu aşağıdaki şekilde alıyoruz;

SELECT pg_table_size('size_test_table') AS data_size,
       pg_relation_size('size_test_table_pkey') AS index_size,
       pg_table_size('size_test_table') + pg_relation_size('size_test_table_pkey') AS total_size1,
       pg_total_relation_size('size_test_table') AS total_size2;

Sonuç;

Daha güzel gösterelim;

SELECT pg_size_pretty(pg_total_relation_size('size_test_table'));

Bir veritabanı üzerindeki tüm tabloları schema, tablo adı ve index ve tablespace olarak listeleyelim. Böylece hangi tablo veya index aslen hangi diskimizde ne kadar yer kaplıyor görebiliriz. PostgreSQL’e bir tabloya ait indexler farklı tablespace’de (dolayısıyla istenirse farklı diskte) olabilir.

WITH tbl_spc AS (
  SELECT ts.spcname AS spcname
    FROM pg_tablespace ts 
    JOIN pg_database db ON db.dattablespace = ts.oid
    WHERE db.datname = current_database()
)
(
SELECT
    t.schemaname,
    t.tablename,
    pg_table_size('"' || t.schemaname || '"."' || t.tablename || '"') AS table_disc_size,
    NULL as index,
    0 as index_disc_size,
    COALESCE(t.tablespace, ts.spcname) AS tablespace
  FROM pg_tables t, tbl_spc ts

UNION ALL 

SELECT
    i.schemaname,
    i.tablename,
    0,
    i.indexname,
    pg_relation_size('"' || i.schemaname || '"."' || i.indexname || '"'),
    COALESCE(i.tablespace, ts.spcname)
  FROM pg_indexes i, tbl_spc ts
);

Dilerseniz yukarıdaki sorgudan bir view oluşturup view’dan sorgulama yapabilirsiniz;

CREATE OR REPLACE VIEW v_disc_spaces AS
WITH tbl_spc AS (
  SELECT ts.spcname AS spcname
    FROM pg_tablespace ts 
    JOIN pg_database db ON db.dattablespace = ts.oid
    WHERE db.datname = current_database()
)
(
SELECT
    t.schemaname,
    t.tablename,
    pg_table_size('"' || t.schemaname || '"."' || t.tablename || '"') AS table_disc_size,
    NULL as index,
    0 as index_disc_size,
    COALESCE(t.tablespace, ts.spcname) AS tablespace
  FROM pg_tables t, tbl_spc ts

UNION ALL 

SELECT
    i.schemaname,
    i.tablename,
    0,
    i.indexname,
    pg_relation_size('"' || i.schemaname || '"."' || i.indexname || '"'),
    COALESCE(i.tablespace, ts.spcname)
  FROM pg_indexes i, tbl_spc ts
);

Sonrasında bir şemadaki tüm tabloların alfabetik olarak boyutlarıyla beraber listeme;

SELECT tablename,
       pg_size_pretty(sum(table_disc_size)) AS table_disc_size,
       pg_size_pretty(sum(index_disc_size)) AS index_disc_size
FROM v_disc_spaces
WHERE schemaname = 'public'
GROUP BY tablename
ORDER BY tablename;

Toplam boyutu en büyük 10 tablonun listelenmesi için;

SELECT
  t.tablename,
  pg_size_pretty(pg_total_relation_size('"' || t.schemaname || '"."' || t.tablename || '"')) AS table_total_disc_size
FROM pg_tables t
WHERE
  t.schemaname = 'public'
ORDER BY
  pg_total_relation_size('"' || t.schemaname || '"."' || t.tablename || '"') DESC
LIMIT 10;

Yukarıdaki sorgular farklı sistem viewları içerisinden alınabilir. Örneğin, pg_tables yerine pg_class kullanarak da tabloları listeleyebilirdik. Artık orası sizin yaratıcılığınız.

Referanslar