Skip to content

Instantly share code, notes, and snippets.

@amitsaxena

amitsaxena/gist:0115d1d28fa90a5324560716b586d496

Last active May 24, 2024 15:24
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save amitsaxena/0115d1d28fa90a5324560716b586d496 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save amitsaxena/0115d1d28fa90a5324560716b586d496 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. amitsaxena revised this gist Jan 18, 2023. No changes.
  2. amitsaxena revised this gist Jan 18, 2023. 1 changed file with 22 additions and 0 deletions.
    22 changes: 22 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -40,3 +40,25 @@ order by L.granted, L.pid DESC;
    // List all tables of a specific schema

    \dt schema_name.*


    // Get number of rows per table

    WITH tbl AS
    (SELECT table_schema,
    TABLE_NAME
    FROM information_schema.tables
    WHERE TABLE_NAME not like 'pg_%'
    AND table_schema in ('public'))
    SELECT table_schema,
    TABLE_NAME,
    (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
    FROM tbl
    ORDER BY rows_n DESC;

    // Get size of databases

    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. amitsaxena revised this gist Jun 28, 2021. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -36,3 +36,7 @@ FROM pg_stat_activity S
    inner join pg_locks L on S.pid = L.pid
    order by L.granted, L.pid DESC;


    // List all tables of a specific schema

    \dt schema_name.*
  4. amitsaxena revised this gist May 14, 2021. 1 changed file with 13 additions and 0 deletions.
    13 changes: 13 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -23,3 +23,16 @@ DROP INDEX CONCURRENTLY vote_texts_multi_column;

    SELECT locktype, relation::regclass, mode, pid FROM pg_locks;

    // See all locks with relevant query

    SELECT
    S.pid,
    age(clock_timestamp(), query_start),
    query,
    L.mode,
    L.locktype,
    L.granted
    FROM pg_stat_activity S
    inner join pg_locks L on S.pid = L.pid
    order by L.granted, L.pid DESC;

  5. amitsaxena revised this gist May 14, 2021. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -8,6 +8,7 @@ set statement_timeout = '10s';
    alter database mentimeter set statement_timeout = '10s';

    // Time queries

    \timing

    // Create multicolumn index concurrenty
    @@ -18,3 +19,7 @@ CREATE INDEX CONCURRENTLY vote_texts_multi_column ON vote_texts (question_id, id

    DROP INDEX CONCURRENTLY vote_texts_multi_column;

    // See all locks

    SELECT locktype, relation::regclass, mode, pid FROM pg_locks;

  6. amitsaxena created this gist May 14, 2021.
    20 changes: 20 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    // Change SQL client timeout

    set statement_timeout = '0s';
    set statement_timeout = '10s';

    // Change timeout globally

    alter database mentimeter set statement_timeout = '10s';

    // Time queries
    \timing

    // Create multicolumn index concurrenty

    CREATE INDEX CONCURRENTLY vote_texts_multi_column ON vote_texts (question_id, identifier_id);

    // Drop index

    DROP INDEX CONCURRENTLY vote_texts_multi_column;