path: root/docs/queries/postgres.rst

PostgreSQL queries
==================

Disk usage
----------

Most of these queries vary based on the database you are connected to.

General Table Size Information Grouped For Partitioned Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: sql

   WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
       (select inhrelid, inhparent
       FROM pg_inherits
       UNION
       SELECT child.inhrelid, parent.inhparent
       FROM pg_inherit child, pg_inherits parent
       WHERE child.inhparent = parent.inhrelid),
   pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
   SELECT table_schema
       , TABLE_NAME
       , row_estimate
       , 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
                 , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate
                 , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes
                 , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes
                 , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes
                 , parent
             FROM (
                   SELECT pg_class.oid
                       , reltuples
                       , relname
                       , relnamespace
                       , pg_class.reltoastrelid
                       , COALESCE(inhparent, pg_class.oid) parent
                   FROM pg_class
                       LEFT JOIN pg_inherit_short ON inhrelid = oid
                   WHERE relkind IN ('r', 'p')
                ) c
                LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
     ) a
     WHERE oid = parent
   ) a
   ORDER BY total_bytes DESC;

General Table Size Information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: sql

   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
   ) a;

Finding the largest databases in your cluster
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: sql

   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;

Finding the size of your biggest relations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Relations are objects in the database such as tables and indexes, and
this query shows the size of all the individual parts.

.. code:: sql

   SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_relation_size(C.oid)) AS "size"
     FROM pg_class C
     LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
     WHERE nspname NOT IN ('pg_catalog', 'information_schema')
     ORDER BY pg_relation_size(C.oid) DESC
     LIMIT 20;

Finding the total size of your biggest tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: sql

   SELECT nspname || '.' || relname AS "relation",
       pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
     FROM pg_class C
     LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
     WHERE nspname NOT IN ('pg_catalog', 'information_schema')
       AND C.relkind <> 'i'
       AND nspname !~ '^pg_toast'
     ORDER BY pg_total_relation_size(C.oid) DESC
     LIMIT 20;

Indexes
-------

Index summary
~~~~~~~~~~~~~

.. code:: sql

   SELECT
       pg_class.relname,
       pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
       pg_class.reltuples AS num_rows,
       count(indexname) AS number_of_indexes,
       CASE WHEN x.is_unique = 1 THEN 'Y'
          ELSE 'N'
       END AS UNIQUE,
       SUM(case WHEN number_of_columns = 1 THEN 1
                 ELSE 0
               END) AS single_column,
       SUM(case WHEN number_of_columns IS NULL THEN 0
                WHEN number_of_columns = 1 THEN 0
                ELSE 1
              END) AS multi_column
   FROM pg_namespace
   LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
   LEFT OUTER JOIN
          (SELECT indrelid,
              max(CAST(indisunique AS integer)) AS is_unique
          FROM pg_index
          GROUP BY indrelid) x
          ON pg_class.oid = x.indrelid
   LEFT OUTER JOIN
       ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
              JOIN pg_class c ON c.oid = x.indrelid
              JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
       AS foo
       ON pg_class.relname = foo.ctablename
   WHERE
        pg_namespace.nspname='public'
   AND  pg_class.relkind = 'r'
   GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
   ORDER BY 2;

Index size/usage statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: sql

   SELECT
       t.schemaname,
       t.tablename,
       indexname,
       c.reltuples AS num_rows,
       pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(t.tablename)::text)) AS table_size,
       pg_size_pretty(pg_relation_size(quote_ident(t.schemaname)::text || '.' || quote_ident(indexrelname)::text)) AS index_size,
       CASE WHEN indisunique THEN 'Y'
           ELSE 'N'
       END AS UNIQUE,
       number_of_scans,
       tuples_read,
       tuples_fetched
   FROM pg_tables t
   LEFT OUTER JOIN pg_class c ON t.tablename = c.relname
   LEFT OUTER JOIN (
       SELECT
           c.relname AS ctablename,
           ipg.relname AS indexname,
           x.indnatts AS number_of_columns,
           idx_scan AS number_of_scans,
           idx_tup_read AS tuples_read,
           idx_tup_fetch AS tuples_fetched,
           indexrelname,
           indisunique,
           schemaname
       FROM pg_index x
       JOIN pg_class c ON c.oid = x.indrelid
       JOIN pg_class ipg ON ipg.oid = x.indexrelid
       JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid
   ) AS foo ON t.tablename = foo.ctablename AND t.schemaname = foo.schemaname
   WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY 1,2;

Duplicate indexes
~~~~~~~~~~~~~~~~~

.. code:: sql

   SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
          (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
          (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
   FROM (
       SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                                            coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key
       FROM pg_index) sub
   GROUP BY key HAVING count(*)>1
   ORDER BY sum(pg_relation_size(idx)) DESC;

Maintenance
-----------

`PostgreSQL wiki <https://wiki.postgresql.org/wiki/Main_Page>`__

CLUSTER-ing
~~~~~~~~~~~

`CLUSTER <https://www.postgresql.org/docs/current/sql-cluster.html>`__

.. code:: sql

   CLUSTER [VERBOSE] table_name [ USING index_name ]
   CLUSTER [VERBOSE]

``CLUSTER`` instructs PostgreSQL to cluster the table specified by
``table_name`` based on the index specified by ``index_name``. The index
must already have been defined on ``table_name``.

When a table is clustered, it is physically reordered based on the index
information.

The
`clusterdb <https://www.postgresql.org/docs/current/app-clusterdb.html>`__
CLI tool is recommended, and can also be used to cluster all tables at
the same time.

VACUUM-ing
~~~~~~~~~~

Proper vacuuming, particularly autovacuum configuration, is crucial to a
fast and reliable database.

`Introduction to VACUUM, ANALYZE, EXPLAIN, and
COUNT <https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT>`__

It is not advised to run ``VACUUM FULL``, instead look at clustering.
VACUUM FULL is a much more intensive task and acquires an ACCESS
EXCLUSIVE lock on the table, blocking reads and writes. Whilst
``CLUSTER`` also does acquire this lock it’s a less intensive and faster
process.

The
`vacuumdb <https://www.postgresql.org/docs/current/app-vacuumdb.html>`__
CLI tool is recommended for manual runs.

Finding number of dead rows
^^^^^^^^^^^^^^^^^^^^^^^^^^^

.. code:: sql

   SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup <> 0 ORDER BY 2 DESC;

Finding last vacuum/auto-vacuum date
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.. code:: sql

   SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

Checking auto-vacuum is enabled
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.. code:: sql

   SELECT name, setting FROM pg_settings WHERE name='autovacuum';

View all auto-vacuum setting
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.. code:: sql

   SELECT * from pg_settings where category like 'Autovacuum';

Locks
-----

Looking at granted locks
~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: sql

   SELECT relation::regclass, * FROM pg_locks WHERE NOT granted;

Сombination of blocked and blocking activity
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: sql

   SELECT blocked_locks.pid     AS blocked_pid,
            blocked_activity.usename  AS blocked_user,
            blocking_locks.pid     AS blocking_pid,
            blocking_activity.usename AS blocking_user,
            blocked_activity.query    AS blocked_statement,
            blocking_activity.query   AS current_statement_in_blocking_process
      FROM  pg_catalog.pg_locks         blocked_locks
       JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
       JOIN pg_catalog.pg_locks         blocking_locks
           ON blocking_locks.locktype = blocked_locks.locktype
           AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
           AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
           AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
           AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
           AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
           AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
           AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
           AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
           AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
           AND blocking_locks.pid != blocked_locks.pid

       JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
      WHERE NOT blocked_locks.granted;