Pengecekan Size DB Postgres

Pengecekan size db itu di perlukan agar kita tau pertumbuhan penambahan size db untuk menghitung cost dan kebutuhan server.

Beberapa Perintah untuk pengecekan size database :

1. Untuk pengecekan size 1 database bisa menggunakan perintah berikut :

select pg_size_pretty(pg_database_size('databaseName'));

Untuk databaseName di sesuaikan dengan nama database yang akan di cek sizenya

2. Untuk pengecekan semua database yang ada pada 1 server Postgres bisa menggunakan :

 select t1.datname AS db_name, pg_size_pretty(pg_database_size(t1.datname)) as db_size from pg_database t1 order by pg_database_size(t1.datname) desc;

3. Untuk melakukan pengecekan via terminal menggunakan perintah

\l+

dan

\d+

4. Pengecekan size database dengan satuan kb

SELECT pg_database.datname as "database_name", pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

5. Pengecekan size database dengan alternatif perintah lain :

SELECT database_name, pg_size_pretty(size) from (SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname) AS size FROM pg_database ORDER by size DESC) as ordered;

6. Pengecekan size database dengan list owner database

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS Size
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

7. Alternatif lain pengecekan size database dengan list owner database

SELECT 
    datname AS DatabaseName
    ,pg_catalog.pg_get_userbyid(datdba) AS OwnerName
    ,CASE 
        WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname))
        ELSE 'No Access For You'
    END AS DatabaseSize
FROM pg_catalog.pg_database
ORDER BY 
    CASE 
        WHEN pg_catalog.has_database_privilege(datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(datname)
        ELSE NULL
    END DESC;

Untuk pengecekan spesifik table

Perintahnya menggunakan

SELECT pg_size_pretty(pg_relation_size('table_name'));