Useful Postgresql Management Queries

postgresql_management_queries.sql

-----------------------------------------------------------------------------------------------------------------------------------
-- USEFUL POSTGRESQL MANAGEMENT QUERIES                                                                                          --
-- See https://medium.com/compass-true-north/dealing-with-significant-postgres-database-bloat-what-are-your-options-a6c1814a03a5 --
-----------------------------------------------------------------------------------------------------------------------------------

-- SHOW RUNNING QUERIES

SELECT
    state,
    pid,
    usename,
    pg_stat_activity.query_start,
    now() - pg_stat_activity.query_start AS duration,
    query
FROM
    pg_stat_activity
WHERE
    now() - pg_stat_activity.query_start > interval '10 minutes'
AND
    state != 'idle';



--  FIND ALL TABLES AND WHEN THEY WERE LAST VACUUMED/ANALYZED, EITHER MANUALLY OR AUTOMATICALLY
SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM
    pg_stat_all_tables
WHERE
    schemaname = 'public'
ORDER BY
    last_vacuum DESC;


-- FIND ANY RUNNING PROCESSES THAT ARE DOING AUTOVACUUM AND WHICH TABLES THEY'RE WORKING ON
SELECT
    pid,
    Age(query_start, Clock_timestamp()),
    usename,
    query
FROM
    pg_stat_activity
WHERE
    query != '<IDLE>'
AND
    query ilike '%vacuum%'
ORDER BY
    query_start ASC;


-- Find table/index sizes for all tables in a schema
SELECT
    *,
    Pg_size_pretty(total_bytes) AS total,
    Pg_size_pretty(index_bytes) AS INDEX,
    Pg_size_pretty(toast_bytes) AS toast,
    Pg_size_pretty(table_bytes) AS TABLE
FROM (
    SELECT
        *,
        total_bytes - index_bytes - Coalesce(toast_bytes, 0) AS table_bytes
    FROM (
        SELECT
            c.oid,
            nspname                               AS table_schema,
            relname                               AS table_name,
            c.reltuples                           AS row_estimate,
            Pg_total_relation_size(c.oid)         AS total_bytes,
            Pg_indexes_size(c.oid)                AS index_bytes,
            Pg_total_relation_size(reltoastrelid) AS toast_bytes
        FROM
            pg_class c
        LEFT JOIN
            pg_namespace n
        ON
            n.oid = c.relnamespace
        WHERE
            relkind = 'r') a
    WHERE
        table_schema = 'public'
    ORDER BY
        total_bytes DESC) a;

-- Show fillfactor (and other options) for tables
SELECT
    t.relname as table_name,
    t.reloptions
FROM
    pg_class t
JOIN
    pg_namespace n
ON
    n.oid = t.relnamespace
WHERE
    t.relname IN ('product_ads_report_y2020m11', 'product_ads_report_y2020m08');

-- Approximate row count for large tables
SELECT
    cl.relname AS "table_name",
    cl.reltuples AS "approximate_number_of_rows"
FROM
    pg_catalog.pg_class cl
INNER JOIN
    pg_catalog.pg_statio_user_tables utables
ON
    cl.relname = utables.relname
WHERE
    cl.relname = 'tblname'
ORDER BY
    cl.reltuples DESC;