DEV Community: Bolaji Wahab

Data type mismatch in referential integrity and its implications

Bolaji Wahab — Mon, 10 Apr 2023 15:43:19 +0000

I have seen situations where different compatible types were used in referential integrity AKA foreign key constraints. When we say data types are compatible, it means there is a conversion path between the types.

The catalog pg_cast stores data type conversion paths, both built-in and user-defined but it does not represent every type conversion that the system knows how to perform. Yet, we can infer some compatible types from pg_cast.

Let us have a look at compatible types for integer and varchar



postgres=# SELECT castsource::regtype, casttarget::regtype FROM pg_cast WHERE castsource IN ('integer'::regtype, 'varchar'::regtype) ORDER BY castsource;
    castsource     |    casttarget
-------------------+-------------------
 integer           | bigint
 integer           | smallint
 integer           | real
 integer           | double precision
 integer           | numeric
 integer           | money
 integer           | boolean
 integer           | oid
 integer           | regproc
 integer           | regprocedure
 integer           | regoper
 integer           | regoperator
 integer           | regclass
 integer           | regcollation
 integer           | regtype
 integer           | regconfig
 integer           | regdictionary
 integer           | regrole
 integer           | regnamespace
 integer           | "char"
 integer           | bit
 character varying | character
 character varying | "char"
 character varying | name
 character varying | regclass
 character varying | xml
 character varying | character varying
 character varying | text
(28 rows)

Let us assume we have a products table and another table storing orders of those products. We want to ensure that the orders table only contains orders of products that actually exist. So, we define a foreign key constraint in the orders table that references the products table:



postgres=# CREATE TABLE products (
    product_number bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    product_name text,
    price numeric
);
CREATE TABLE



postgres=# CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_number integer REFERENCES products (product_number), 
    quantity integer 
);
CREATE TABLE

Everything seems to work. Let us populate the tables



postgres=# INSERT INTO products (product_name, price) VALUES ('book', 10), ('pen', 5), ('bag', 15);
INSERT 0 3



postgres=# INSERT INTO orders (order_id, product_number, quantity) VALUES (1, 1, 1), (2, 2, 1), (3, 3, 1);
INSERT 0 3

Also, everything works. But what happens when product_number is greater than 2147483648 (max value of integer).



postgres=# SELECT setval('products_product_number_seq', 2147483648);
   setval
------------
 2147483648
(1 row)

postgres=# INSERT INTO products (product_name, price) VALUES ('shoe', 20) RETURNING product_number;
 product_number
----------------
     2147483649
(1 row)

postgres=# INSERT into orders (order_id, product_number, quantity) VALUES (4, 2147483649, 1);
ERROR:  integer out of range

We got integer out of range error. One way to fix this is to change orders.product_number type to bigint but we risk full table rewrite and application downtime for the duration of the change. Let us find out:



postgres=# SELECT relname, relfilenode FROM pg_class WHERE relname = 'orders';
 relname | relfilenode
---------+-------------
 orders  |       42404
(1 row)



postgres=# ALTER TABLE orders ALTER COLUMN product_number TYPE bigint ;
ALTER TABLE
postgres=# SELECT relname, relfilenode FROM pg_class WHERE relname = 'orders';
 relname | relfilenode
---------+-------------
 orders  |       42419

We can see relfilenode has changed, this tells us the table was rewritten. Imagine having to do this in a heavily-loaded large database.
There are other ways of adjusting the type such as using triggers and mirrored columns but that can be tasking.

Most common source of type mismatch in referential integrity happens when using types that have size/length constraint that such as smallint, integer serial, character varying(N).

So, is there a way we can catch this kind of mismatch earlier, maybe during development/review?
We can use the query below which produces nice info/message about type mismatch in referential integrity



WITH pk AS (
    SELECT CASE nc.nspname 
               WHEN 'public' THEN nc.nspname || '.' || c.conrelid::regclass::text
               ELSE c.conrelid::regclass::text
           END AS conrelid,
           c.conindid::regclass,
           string_agg(a.attname, ', ' order by a.attnum) as attnames,
           string_agg(format_type(a.atttypid, a.atttypmod), ', ' order by a.attnum) AS atttypid
      FROM pg_namespace AS nc
      JOIN pg_class AS r ON nc.oid = r.relnamespace
      JOIN pg_attribute AS a ON r.oid = a.attrelid
      JOIN pg_constraint AS c ON r.oid = c.conrelid
     WHERE a.attnum = ANY (c.conkey)
       AND NOT a.attisdropped 
       AND c.contype = 'p' 
       AND r.relkind IN ('r', 'p')
       AND nc.nspname NOT IN ('information_schema', 'pg_catalog')
  GROUP BY nc.nspname, c.conrelid::regclass::text, c.conindid::regclass
), fk AS (
    SELECT CASE nc.nspname 
               WHEN 'public' THEN nc.nspname || '.' || c.conrelid::regclass::text
               ELSE c.conrelid::regclass::text
           END AS conrelid,
           c.conindid::regclass,
           string_agg(a.attname, ', ' order by a.attnum) as attnames,
           string_agg(format_type(a.atttypid, a.atttypmod), ', ' order by a.attnum) AS atttypid
      FROM pg_namespace AS nc
      JOIN pg_class AS r ON nc.oid = r.relnamespace
      JOIN pg_attribute AS a ON r.oid = a.attrelid
      JOIN pg_constraint AS c ON r.oid = c.conrelid
      JOIN pk ON pk.conindid = c.conindid
     WHERE a.attnum = ANY (c.conkey)
       AND NOT a.attisdropped 
       AND c.contype = 'f' 
       AND r.relkind IN ('r', 'p') 
       AND nc.nspname NOT IN ('information_schema', 'pg_catalog')
  GROUP BY nc.nspname, c.conrelid::regclass::text, c.conindid::regclass
)
SELECT 'Parent ' || pk.conrelid || '.{' || pk.attnames || '} datatype {' || pk.atttypid || '} is different from child ' || fk.conrelid || '.{' || fk.attnames || '} datatype {' || fk.atttypid  || '}' AS info
  FROM pk 
  JOIN fk ON pk.conindid = fk.conindid
 WHERE pk.atttypid != fk.atttypid;
                                                                                       info
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Parent public.products.{product_number} datatype {bigint} is different from child public.orders.{product_number} datatype {integer}

The query above can also be incorporated into some continuous integration.

Summary

It is easy to miss out the right data types when creating referential integrity and this can have impactful implications during the lifecycle of an application. Few of the implications are:

Application errors due to size/length constraint such as in integer and character varying(N).
Application downtime when setting the right data type.
If there is a rewrite, the rewrite will temporarily require as much as double the disk space of the table.

Key takeaways

Review the data types in referential integrity as part of your database design review.

Progress Reporting In PostgreSQL

Bolaji Wahab — Mon, 10 Apr 2023 08:57:18 +0000

Can we monitor the progress of certain operations in PostgreSQL? Yes, we can monitor the progress of these operations in PostgreSQL:

create index
create index concurrently
reindex
reindex concurrently
vacuum
vacuum analyze
vacuum full
analyze
copy
cluster
basebackup

Let us jump right into it. The query snippets below can be used to monitor the progress of the listed-above operations.

Monitoring the progress of `create index`, `create index concurrently`, `reindex`, `reindex concurrently`



SELECT p.datname                                                 AS database_name,

       p.pid,

       clock_timestamp() - a.xact_start                          AS duration_so_far,

       a.application_name,

       a.client_addr,

       a.usename,

       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting,

       p.command,

       trim(trailing ';' from a.query)                           AS query,

       a.state,

       p.index_relid::regclass                                   AS index_name,

       p.relid::regclass                                         AS table_name,

       pg_size_pretty(pg_relation_size(p.relid))                 AS table_size,

       p.phase,

       CASE p.phase

           WHEN 'initializing' THEN '1 of 12'

           WHEN 'waiting for writers before build' THEN '2 of 12'

           WHEN 'building index: scanning table' THEN '3 of 12'

           WHEN 'building index: sorting live tuples' THEN '4 of 12'

           WHEN 'building index: loading tuples in tree' THEN '5 of 12'

           WHEN 'waiting for writers before validation' THEN '6 of 12'

           WHEN 'index validation: scanning index' THEN '7 of 12'

           WHEN 'index validation: sorting tuples' THEN '8 of 12'

           WHEN 'index validation: scanning table' THEN '9 of 12'

           WHEN 'waiting for old snapshots' THEN '10 of 12'

           WHEN 'waiting for readers before marking dead' THEN '11 of 12'

           WHEN 'waiting for readers before dropping' THEN '12 of 12'

       END AS phase_progress,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 2)::text || '%', 'not applicable'),

           p.blocks_done::text,

           p.blocks_total::text

       ) AS scan_progress,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.tuples_done / nullif(p.tuples_total, 0), 2)::text || '%', 'not applicable'),

           p.tuples_done::text,

           p.tuples_total::text

       ) AS tuples_loading_progress,

       format(

           '%s (%s of %s)',

           coalesce((100 * p.lockers_done / nullif(p.lockers_total, 0))::text || '%', 'not applicable'),

           p.lockers_done::text,

           p.lockers_total::text

       ) AS lockers_progress,

       format(

           '%s (%s of %s)',

           coalesce((100 * p.partitions_done / nullif(p.partitions_total, 0))::text || '%', 'not applicable'),

           p.partitions_done::text,

           p.partitions_total::text

       ) AS partitions_progress,

       p.current_locker_pid,

       trim(trailing ';' from l.query) AS current_locker_query

  FROM pg_stat_progress_create_index   AS p

  JOIN pg_stat_activity                AS a ON a.pid = p.pid

  LEFT JOIN pg_stat_activity           AS l ON l.pid = p.current_locker_pid

 ORDER BY clock_timestamp() - a.xact_start DESC;

Monitoring the progress of `vacuum`, `vacuum analyze`



SELECT p.datname                                                 AS database_name,

       p.pid,

       clock_timestamp() - a.xact_start                          AS duration_so_far,

       a.application_name,

       a.client_addr,

       a.usename,

       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting,

       trim(trailing ';' from a.query)                           AS query,

       a.state,

       p.relid::regclass                                         AS table_name,

       pg_size_pretty(pg_relation_size(p.relid))                 AS table_size,

       pg_size_pretty(pg_total_relation_size(p.relid))           AS total_table_size,

       CASE

           WHEN ltrim(a.query) ~* '^autovacuum.to prevent wraparound' THEN 'wraparound'

           WHEN ltrim(a.query) ~ '^vacuum' THEN 'user'

           ELSE 'regular'

       END AS mode,

       p.phase,

       CASE p.phase


           WHEN 'initializing' THEN '1 of 7'

           WHEN 'scanning heap' THEN '2 of 7'

           WHEN 'vacuuming indexes' THEN '3 of 7'

           WHEN 'vacuuming heap' THEN '4 of 7'

           WHEN 'cleaning up indexes' THEN '5 of 7'

           WHEN 'truncating heap' THEN '6 of 7'

           WHEN 'performing final cleanup' THEN '7 of 7'

       END AS vacuum_phase_progress,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 2)::text || '%', 'not applicable'),

           p.heap_blks_scanned::text,

           p.heap_blks_total::text

       ) AS vacuum_scan_progress,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.heap_blks_vacuumed / nullif(p.heap_blks_total, 0), 2)::text || '%', 'not applicable'),

           p.heap_blks_vacuumed::text,

           p.heap_blks_total::text

       ) AS vacuum_progress,

       p.index_vacuum_count,

       p.max_dead_tuples,

       p.num_dead_tuples

  FROM pg_stat_progress_vacuum  AS p

  JOIN pg_stat_activity         AS a ON a.pid = p.pid

 ORDER BY clock_timestamp() - a.xact_start DESC;

Monitoring the progress of `vacuum full`, `cluster`



SELECT p.datname                                                 AS database_name,

       p.pid,

       clock_timestamp() - a.xact_start                          AS duration_so_far,

       a.application_name,

       a.client_addr,

       a.usename,

       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting,

       p.command,

       trim(trailing ';' from a.query)                           AS query,

       a.state,

       p.relid::regclass                                         AS table_name,

       p.phase,

       CASE p.phase


           WHEN 'initializing' THEN '1 of 8'

           WHEN 'seq scanning heap' THEN '2 of 8'

           WHEN 'index scanning heap' THEN '3 of 8'

           WHEN 'sorting tuples' THEN '4 of 8'

           WHEN 'writing new heap' THEN '5 of 8'

           WHEN 'swapping relation files' THEN '6 of 8'

           WHEN 'rebuilding index' THEN '7 of 8'

           WHEN 'performing final cleanup' THEN '7 of 8'

       END AS vacuum_phase_progress,

       cluster_index_relid::regclass AS cluster_index,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 2)::text || '%', 'not applicable'),

           p.heap_blks_scanned::text,

           p.heap_blks_total::text

       ) AS heap_scan_progress,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.heap_tuples_written / nullif(p.heap_tuples_scanned, 0), 2)::text || '%', 'not applicable'),

           p.heap_tuples_written::text,

           p.heap_tuples_scanned::text

       ) AS heap_tuples_written_progress,

       p.index_rebuild_count

  FROM pg_stat_progress_cluster AS p

  JOIN pg_stat_activity         AS a ON a.pid = p.pid

 ORDER BY clock_timestamp() - a.xact_start DESC;

Monitoring the progress of `analyze`



SELECT p.datname                                                 AS database_name,

       p.pid,

       clock_timestamp() - a.xact_start                          AS duration_so_far,

       a.application_name,

       a.client_addr,

       a.usename,

       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting,

       trim(trailing ';' from a.query)                           AS query,

       a.state,

       p.relid::regclass                                         AS table_name,

       pg_size_pretty(pg_relation_size(p.relid))                 AS table_size,

       pg_size_pretty(pg_total_relation_size(p.relid))             AS total_table_size,

       CASE

           WHEN ltrim(a.query) ~* '^analyze' THEN 'user'

           ELSE 'regular'

       END AS mode,

       p.phase,

       CASE p.phase


           WHEN 'initializing' THEN '1 of 6'

           WHEN 'acquiring sample rows' THEN '2 of 6'

           WHEN 'acquiring inherited sample rows' THEN '3 of 6'

           WHEN 'computing statistics' THEN '4 of 6'

           WHEN 'computing extended statistics' THEN '5 of 6'

           WHEN 'finalizing analyze' THEN '6 of 6'

       END AS phase_progress,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.sample_blks_scanned / nullif(p.sample_blks_total, 0), 2)::text || '%', 'not applicable'),

           p.sample_blks_scanned::text,

           p.sample_blks_total::text

       ) AS scan_progress,

       format(

           '%s (%s of %s)',

           coalesce((100 * p.ext_stats_computed / nullif(p.ext_stats_total, 0))::text || '%', 'not applicable'),

           p.ext_stats_computed::text,

           p.ext_stats_total::text

       ) AS extended_statistics_progress,

       format(

           '%s (%s of %s)',

           coalesce((100 * p.ext_stats_computed / nullif(p.child_tables_total, 0))::text || '%', 'not applicable'),

           p.child_tables_done::text,

           p.child_tables_total::text

       ) AS child_tables_progress,

       current_child_table_relid::regclass AS current_child_table

  FROM pg_stat_progress_analyze AS p

  JOIN pg_stat_activity         AS a ON a.pid = p.pid

 ORDER BY clock_timestamp() - a.xact_start DESC;

Monitoring the progress of `copy`



SELECT p.datname                                                      AS database_name,

       p.pid,

       clock_timestamp() - a.xact_start                               AS duration_so_far,

       a.application_name,

       a.client_addr,

       a.usename,

       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false')      AS waiting,

       p.command,

       p.type,

       trim(trailing ';' from a.query)                                AS query,

       a.state,

       p.relid::regclass                                              AS table_name,

       coalesce(pg_size_pretty(pg_relation_size(p.relid)), '-')       AS table_size,

       coalesce(pg_size_pretty(pg_total_relation_size(p.relid)), '-') AS total_table_size,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.bytes_processed / nullif(p.bytes_total, 0), 2)::text || '%', 'not applicable'),

           p.bytes_processed::text,

           p.bytes_total::text

       ) AS bytes_progress,

       p.tuples_processed,

       p.tuples_excluded

  FROM pg_stat_progress_copy  AS p

  JOIN pg_stat_activity       AS a ON a.pid = p.pid

 ORDER BY clock_timestamp() - a.xact_start DESC;

Monitoring the progress of `basebackup`



SELECT a.datname                                                      AS database_name,

       p.pid,

       clock_timestamp() - a.query_start                              AS duration_so_far,

       a.application_name,

       a.client_addr,

       a.usename,

       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false')      AS waiting,

       trim(trailing ';' from a.query)                                AS query,

       a.state,

       p.phase,

       CASE p.phase


           WHEN 'initializing' THEN '1 of 6'

           WHEN 'waiting for checkpoint to finish' THEN '2 of 6'

           WHEN 'estimating backup size' THEN '3 of 6'

           WHEN 'streaming database files' THEN '4 of 6'

           WHEN 'waiting for wal archiving to finish' THEN '5 of 6'

           WHEN 'transferring wal files' THEN '6 of 6'

       END AS phase_progress,

       format(

           '%s (%s of %s)',

           coalesce(round(100.0 * p.backup_streamed / nullif(p.backup_total, 0), 2)::text || '%', 'not applicable'),

           p.backup_streamed::text,

           coalesce(p.backup_total::text, '0')

       ) AS backup_progress,

       format(

           '%s (%s of %s)',

           coalesce((100 * p.tablespaces_streamed / nullif(p.tablespaces_total, 0))::text || '%', 'not applicable'),

           p.tablespaces_streamed::text,

           p.tablespaces_total::text

       ) AS tablespace_progress

  FROM pg_stat_progress_basebackup  AS p

  JOIN pg_stat_activity             AS a ON a.pid = p.pid

 ORDER BY clock_timestamp() - a.query_start DESC;

Little explanation:

The above queries have lot of things in common.

There is a phase column which tells what phase the process is currently on.
A phase_progress which gives the ratio of the current phase number and the total expected number of phases, which is gotten from the description of the respective views.
Then we have progress for the phases provided they are available otherwise not applicable.
For system/user commands such as analyze and vacuum, we have this expression trim(leading from a.query) which removes leading spaces before we compare to deduce whether it is autovacuum/autoanalyze or manual vacuum/manual analyze.
The combination of coalesce and nullif produces not applicable when one of the variables is NULL or 0.
Expression coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') gives details whether the process is waiting or not.
Finally, trim(trailing ';' from a.query) removes ; from the query text to give a nice format.

I hope you find these queries useful for your progress reporting.

P.S: All queries were tested on PostgreSQL 14.

Cleanly modifying PostgreSQL configurations

Bolaji Wahab — Sat, 23 Jul 2022 08:43:00 +0000

PostgreSQL has numerous configurations. Making changes to these configurations could occasionally result in issues or even stop the server from starting.
Additionally, a config might have been modified but yet to be applied. This may easily result in a scenario in which we modify a config, reload or restart the server, and then encounter a problem unrelated to our modification.

But the good news is that PostgreSQL provided an infrastructure to check if a config has an error so that it may be fixed before we reload or restart the server.

Ways of adjusting PostgreSQL configs

Editing the config files
Using ALTER SYSTEM

Checking for errors in the config

The SQL query below can be used to check for configuration errors

 SELECT name, sourcefile, sourceline, setting, error FROM pg_catalog.pg_file_settings WHERE error IS NOT NULL;
 name |                    sourcefile                     | sourceline | setting |                error
------+---------------------------------------------------+------------+---------+--------------------------------------
 test | /usr/local/etc/postgresql/14/main/postgresql.conf |          2 | test    | unrecognized configuration parameter
(1 row)

Time: 2.354 ms

Let us try reloading the server. We get this info in the LOG:

2022-07-22 16:00:53.190 CEST [4317] LOG:  received SIGHUP, reloading configuration files
2022-07-22 16:00:53.190 CEST [4317] LOG:  unrecognized configuration parameter "test" in file "/usr/local/etc/postgresql/14/main/postgresql.conf" line 2
2022-07-22 16:00:53.191 CEST [4317] LOG:  configuration file "/usr/local/etc/postgresql/14/main/postgresql.conf" contains errors; no changes were applied

Let us try restarting the server. We got the below error and the server could not be started.

2022-07-22 14:03:45.281 GMT [4940] LOG:  unrecognized configuration parameter "test" in file "/usr/local/etc/postgresql/14/main/postgresql.conf" line 2
2022-07-22 14:03:45.282 GMT [4940] FATAL:  configuration file "/usr/local/etc/postgresql/14/main/postgresql.conf" contains errors
pg_ctl: could not start server
Examine the log output.

The issues can be quickly fixed because we can check the state of the configuration before reloading or restarting.

To avoid surprises, this can also be implemented into a configuration management system.

Checking for pending reload and pending restart

It is wise to confirm the current state of the server before making any configuration changes. This guarantees that we begin in a clean condition and helps prevent cascade problems.

Consider a scenario in which one of the DBAs on your team changed random page cost but the change is not yet effective. You make your modification and reload or restart the server. After a few days, your queries began to perform poorly. You were questioned about the adjustment you made and even requested to reverse it, but there was still no relief.
Investigating such issues can be tiresome and time-consuming; it is better to be safe than sorry.

We can easily check for pending reload and restart with this SQL query:

WITH current_settings AS (
    SELECT name
         , setting
         , CASE
             WHEN unit IN ('kB', 'MB')
              AND setting <> '-1'
               THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(setting) * pg_catalog.pg_size_bytes(format('1%1$s', unit)))
             WHEN unit = '8kB'
              AND setting <> '-1'
               THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(setting) * pg_catalog.pg_size_bytes(unit))
             WHEN unit = 'B'
              AND setting <> '-1'
               THEN pg_catalog.pg_size_pretty(pg_catalog.pg_size_bytes(setting))
             WHEN unit IN ('ms', 's', 'min')
              AND setting <> '-1'
               THEN format('%1$s %2$s', setting, unit)
             WHEN setting = '-1'
               THEN setting
             ELSE setting
           END AS current_setting
         , unit
         , context
      FROM pg_catalog.pg_settings
)
, file_settings AS (
    SELECT row_number() OVER (PARTITION BY pf.name ORDER BY pf.seqno DESC) AS rn
         , pf.name
        -- When autovacuum_work_mem is greater than -1 but less than the min_val of maintenance_work_mem which is 1024 kB,
        -- PostgreSQL sets autovacuum_work_mem to 1024 kB
         , CASE
             WHEN pf.name = 'autovacuum_work_mem'
              AND pf.setting <> '-1'
              AND (
                    pg_catalog.pg_size_bytes(pf.setting)
                    *
                    CASE
                      WHEN pf.setting ~ '\d+\s*[a-zA-Z]?(B)'
                        THEN 1
                      ELSE pg_catalog.pg_size_bytes(format('1%1$s', cs.unit))
                    END
                  ) < pg_catalog.pg_size_bytes('1024 kB')
               THEN '1024 kB'
             WHEN cs.unit IN ('kB', 'MB')
              AND pf.setting <> '-1'
               THEN pg_catalog.pg_size_pretty(
                        pg_catalog.pg_size_bytes(pf.setting)
                        *
                        CASE
                          WHEN pf.setting ~ '\d+\s*[a-zA-Z]?(B)'
                            THEN 1
                          ELSE pg_catalog.pg_size_bytes(format('1%1$s', cs.unit))
                        END
                    )
             WHEN unit = '8kB'
              AND pf.setting <> '-1'
               THEN pg_catalog.pg_size_pretty(
                        pg_catalog.pg_size_bytes(pf.setting)
                        *
                        CASE
                          WHEN pf.setting ~ '\d+\s*[a-zA-Z]?(B)'
                            THEN 1
                          ELSE pg_catalog.pg_size_bytes(format('1%1$s', cs.unit))
                        END
                    )
             WHEN cs.unit = 'B'
              AND pf.setting <> '-1'
               THEN pg_catalog.pg_size_pretty(
                        pg_catalog.pg_size_bytes(pf.setting)
                        *
                        CASE
                          WHEN pf.setting ~ '\d+\s*[a-zA-Z]?(B)'
                            THEN 1
                          ELSE pg_catalog.pg_size_bytes(format('1%1$s', cs.unit))
                        END
                    )
             WHEN cs.unit = 'ms'
              AND pf.setting <> '-1'
               THEN format(
                        '%1$s %2$s'
                      , CAST(
                            floor(
                                EXTRACT(
                                    EPOCH FROM CAST(
                                        format(
                                            '%1$s%2$s'
                                          , pf.setting
                                          , CASE
                                              WHEN pf.setting ~ '\d+\s*([a-zA-Z]+)'
                                                THEN ''
                                              ELSE cs.unit
                                            END
                                        ) AS interval
                                    )
                                ) * 1000
                            ) AS text
                        )
                      , cs.unit
                    )
             WHEN cs.unit = 's'
              AND pf.setting <> '-1'
               THEN format(
                        '%1$s %2$s'
                      , CAST(
                            floor(
                                EXTRACT(
                                    EPOCH FROM CAST(
                                        format(
                                            '%1$s%2$s'
                                          , pf.setting
                                          , CASE
                                              WHEN pf.setting ~ '\d+\s*([a-zA-Z]+)'
                                                THEN ''
                                              ELSE cs.unit
                                            END
                                        ) AS interval
                                    )
                                )
                            ) AS text
                        )
                      , cs.unit
                    )
             WHEN cs.unit = 'min'
              AND pf.setting <> '-1'
               THEN format(
                        '%1$s %2$s'
                      , CAST(
                            floor(
                                EXTRACT(
                                    EPOCH FROM CAST(
                                        format(
                                            '%1$s%2$s'
                                          , pf.setting
                                          , CASE
                                              WHEN pf.setting ~ '\d+\s*([a-zA-Z]+)'
                                                THEN ''
                                              ELSE cs.unit
                                            END
                                        ) AS interval
                                    )
                                ) / 60
                            ) AS text
                        )
                      , cs.unit
                    )
             WHEN pf.setting = '-1'
               THEN pf.setting
             ELSE pf.setting
           END AS file_setting
         , pf.sourcefile
         , pf.sourceline
         , pf.error
      FROM current_settings AS cs
     INNER JOIN pg_catalog.pg_file_settings AS pf
        ON pf.name = cs.name
)
SELECT cs.name
     , cs.current_setting
     , fs.file_setting AS pending_setting
     , cs.context
     , fs.sourcefile
     , fs.sourceline
     , fs.error
     , CASE
         WHEN cs.current_setting <> fs.file_setting
          AND cs.context = 'sighup'
           THEN TRUE
         ELSE FALSE
       END AS pending_reload
     , CASE
         WHEN cs.current_setting <> fs.file_setting
          AND cs.context = 'postmaster'
           THEN TRUE
         ELSE FALSE
       END AS pending_restart
  FROM current_settings AS cs
 INNER JOIN file_settings AS fs
    ON cs.name = fs.name
 WHERE fs.rn = 1
   AND cs.current_setting <> fs.file_setting
 ORDER BY pending_reload DESC, pending_restart DESC;

Sample output:

        name         | current_setting | pending_setting |  context   |                         sourcefile                         | sourceline | error  | pending_reload | pending_restart
---------------------+-----------------+-----------------+------------+------------------------------------------------------------+------------+--------+----------------+-----------------
 archive_command     | (disabled)      | /bin/true       | sighup     | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         28 | <null> | true           | false
 autovacuum_work_mem | 10 MB           | 1024 kB         | sighup     | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         20 | <null> | true           | false
 max_wal_size        | 1024 MB         | 10 GB           | sighup     | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         26 | <null> | true           | false
 archive_mode        | off             | on              | postmaster | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         27 | <null> | false            | true
 shared_buffers      | 160 MB          | 1024 MB         | postmaster | /usr/local/var/lib/postgresql/14/main/postgresql.auto.conf |         25 | <null> | false            | true
(5 rows)

Time: 8.198 ms

To avoid surprises, this can also be implemented into a configuration management system.

Generating nicely parsed schema files with pg_dump_schema_parser

Bolaji Wahab — Tue, 07 Jun 2022 23:35:20 +0000

Ever had to go through thousands of lines of a schema file generated by pg_dump, it can be tedious and tasking. A little background 4-thousand-lines-of-code-in-a-single-file

A proper database design/development should be version controlled with a proper schema migration in place but some times we may want a schema dump for several reasons.

pg_dump is a tool supplied with PostgreSQL, used in taking bakckups and schema dumps of a single database.

I have had to look through 7000 lines of a schema file and it wasn't funny at all, I lost interest along the way. This prompted me to do some search for a tool I could use to split the schema dump into smaller pieces. I stumbled upon few stackoverflow questions around it, notably the following

While the above questions were limited to tables, I needed a tool that targets all object types.

After an exhaustive search and I couldn't find a tool to do the job properly, I decided to write one to do the job. This gave birth to pg_schema_dump_parser.

pg_schema_dump_parser is a tool written in python which runs pg_dump in the background and parses the generated schema into the respective object name and according to the type of the object. Have a look at how to use pg_schema_dump_parser

Having a schema dump which is nicely parsed into smaller pieces can greatly improve developer experience, imagine a developer having to go through a schema dump of 7000 thousands of lines just to find the definition for a particular table and another developer locating the specific file with the definition of a table of interest. The second developer would have a nice experience and find the table's definition in lesser time.

Tracking dropped database objects in PostgreSQL

Bolaji Wahab — Wed, 04 May 2022 18:06:00 +0000

Ever been in the situation where you need to perform a point-in-time recovery to recover a dropped database object and you need to find the exact time the object was dropped?.
Finding the exact time is not always easy, most times it is all about guesses, we have all been there.
This is why I wrote a simple extension pg_drop_events some times back.

pg_drop_events is a wrapper which utilises PostgreSQL's event triggers, wraps around the dropping statement and logs the info into pg_drop_events table.

A sample data looks like below:



postgres=# SELECT pid, usename, query, xact_id, wal_position, objid, object_name, object_type, xact_time FROM pg_drop_events;
  pid  | usename   |             query              | xact_id | wal_position | objid | object_name | object_type  |             xact_time
-------+-----------+--------------------------------+---------+--------------+-------+-------------+--------------+-------------------------------
 54630 | bolaji    | DROP TABLE t.t3                |   25184 | 1/A266B090   | 51293 | t.t3        | table        | 2022-05-04 17:16:32.913969+00
 54633 | bolaji    | ALTER TABLE t.t1 DROP COLUMN a |   25185 | 1/A266BBF8   | 51287 | t.t1.a      | table column | 2022-05-04 17:16:39.033796+00
 54638 | postgres  | DROP SCHEMA t CASCADE          |   25186 | 1/A266BEC0   | 51287 | t.t1        | table        | 2022-05-04 17:16:56.094366+00
 54639 | postgres  | DROP SCHEMA t CASCADE          |   25186 | 1/A266BEC0   | 51290 | t.t2        | table        | 2022-05-04 17:16:56.094366+00

The info from pg_drop_events can be used to perform point-in-time recovery, well that is the aim of the extension, to aid point-in-time recovery.

PostgreSQL provides various runtime config to perform point-in-time recovery. The full list can be found here recovery target

Personally, I prefer using xact_id to perform point-in-time recovery of a dropped database object. I will talk about the reasons in the next chapter.

PostgreSQL WAL activities

Bolaji Wahab — Mon, 31 Jan 2022 22:31:34 +0000

PostgreSQL is ACID-compliant, ACID meaning

A - Atomicity
C - Consistency
I - Isolation
D - Durability

PostgreSQL has various internal implementations enforcing ACID but our focus here is on its Durability implementation.

Durability

Durability guarantees that transactions that have committed are stored permanently. Such transactions will survive any fault to any component of the database system or failure to the whole system.

Every relational database management system has different ways of enforcing durability but the concept is generally the same.

PostgreSQL uses WAL(Write-Ahead Log) to implement Durability.
Modifications are written to the WAL files before they are written to the data files. This way if the database system should crash for any reason, we are sure not to loose any committed transactions, we simply perform what we call crash recovery.

Both streaming replication and archive recovery rely on WAL files.

WAL files are written to pg_xlog (< PG10) and pg_wal(>= PG10).

There are various configuration as regards to WAL. You can checkout the documentation at WAL configation

WAL archival

WAL files can be archived. This simply means copying the WAL files somewhere usually outside of the database server. This serves two main purposes:

Recovery with prior restoration of a basebackup.
Replication through archive recovery.

Have a look at Archiving

WAL files can also be read through pg_xlogdump(< PG10) and pg_waldump(>= PG10).

Monitoring WAL activities

PostgreSQL does not provide any catalog out of the box for monitoring WAL activities but we can monitor through snapshotting and comparison.
Few activities we can monitor are:

WAL generation rate
WAL archival rate
WAL archival lag
WAL recovery rate

We can achieve the above with psql watch meta command. We get the actual metrics after a complete cycle(here I used 60s).

WAL generation rate

PG >= 10

WITH wal_data AS (
SELECT
now()::timestamp(0),
pg_wal_lsn_diff(
x,
current_setting($$my.wal_loc$$, true)::pg_lsn
) AS wal_data_generated,
set_config($$my.wal_loc$$::text, x::text, false) AS wal_pos
FROM pg_current_wal_lsn() AS l(x)
)
SELECT
now,
round(wal_data_generated / y.bytes_per_wal_segment, 2) AS wal_files_generated_per_minute,
pg_size_pretty(wal_data_generated) AS wal_data_generated_per_minute
FROM wal_data, pg_control_init() AS y
WHERE wal_data_generated IS NOT NULL
\watch 60

> **PG < 10**

WITH wal_data AS (
SELECT
now()::timestamp(0),
pg_xlog_location_diff(
x,
current_setting($$my.wal_loc$$, true)::pg_lsn
) AS wal_data_generated,
set_config($$my.wal_loc$$::text, x::text, false) AS wal_pos
FROM pg_current_xlog_location() AS l(x)
)
SELECT
now,
round(wal_data_generated / y.bytes_per_wal_segment, 2) AS wal_files_generated_per_minute,
pg_size_pretty(wal_data_generated) AS wal_data_generated_per_minute
FROM wal_data, pg_control_init() AS y
WHERE wal_data_generated IS NOT NULL
\watch 60

##### WAL archival rate
> **PG >= 10**

WITH archival_data AS (
SELECT
now()::timestamp(0),
CASE
WHEN current_setting('archive_mode') IN ('on', 'always') THEN
pg_wal_lsn_diff(
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn,
current_setting($$my.wal_loc$$, true)::pg_lsn
)
ELSE NULL
END AS wal_data_archived,
CASE
WHEN current_setting('archive_mode') IN ('on', 'always') THEN
set_config($$my.wal_loc$$::text, regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn::text, false)
ELSE NULL
END AS wal_pos
FROM pg_stat_archiver
)
SELECT
now,
round(wal_data_archived / y.bytes_per_wal_segment, 2)AS wal_files_archived_per_minute,
pg_size_pretty(wal_data_archived) AS wal_data_archived_per_minute
FROM archival_data, pg_control_init() AS y
WHERE wal_data_archived IS NOT NULL
\watch 60

> **PG < 10**

WITH archival_data AS (
SELECT
now()::timestamp(0),
CASE
WHEN current_setting('archive_mode') IN ('on', 'always') THEN
pg_xlog_location_diff(
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn,
current_setting($$my.wal_loc$$, true)::pg_lsn
)
ELSE NULL
END AS wal_data_archived,
CASE
WHEN current_setting('archive_mode') IN ('on', 'always') THEN
set_config($$my.wal_loc$$::text, regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn::text, false)
ELSE NULL
END AS wal_pos
FROM pg_stat_archiver
)
SELECT
now,
round(wal_data_archived / y.bytes_per_wal_segment, 2)AS wal_files_archived_per_minute,
pg_size_pretty(wal_data_archived) AS wal_data_archived_per_minute
FROM archival_data, pg_control_init() AS y
WHERE wal_data_archived IS NOT NULL
\watch 60

##### WAL archival lag
> **PG >= 10**

SELECT
now()::timestamp(0),
CASE
WHEN current_setting('archive_mode') IN ('on', 'always') AND NOT pg_is_in_recovery() THEN
(round(pg_wal_lsn_diff(pg_current_wal_lsn(),
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) / y.bytes_per_wal_segment::numeric,2) - 1
)
WHEN current_setting('archive_mode') IN ('on', 'always') AND pg_is_in_recovery() THEN
(round(pg_wal_lsn_diff(pg_last_wal_replay_lsn(),
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) / y.bytes_per_wal_segment::numeric,2) - 1
)
ELSE 0
END AS wal_files_ready_to_be_archived,
CASE
WHEN current_setting('archive_mode') IN ('on', 'always') AND NOT pg_is_in_recovery() THEN
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) - y.bytes_per_wal_segment
)
WHEN current_setting('archive_mode') IN ('on', 'always') AND pg_is_in_recovery() THEN
pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_replay_lsn(),
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) - y.bytes_per_wal_segment
)
ELSE '0 MB'
END AS archival_lag
FROM pg_stat_archiver, pg_control_init() AS y

> **PG < 10**

SELECT
now()::timestamp(0),
CASE
WHEN current_setting('archive_mode') IN ('on', 'always') AND NOT pg_is_in_recovery() THEN
(round(pg_xlog_location_diff(pg_current_xlog_location(),
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) / y.bytes_per_wal_segment::numeric, 2) - 1
)
WHEN current_setting('archive_mode') IN ('on', 'always') AND pg_is_in_recovery() THEN
(round(pg_xlog_location_diff(pg_last_xlog_replay_location(),
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) / y.bytes_per_wal_segment::numeric, 2) - 1
)
ELSE 0
END AS wal_files_ready_to_be_archived,
CASE
WHEN current_setting('archive_mode') IN ('on', 'always') AND NOT pg_is_in_recovery() THEN
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) - y.bytes_per_wal_segment
)
WHEN current_setting('archive_mode') IN ('on', 'always') AND pg_is_in_recovery() THEN
pg_size_pretty(pg_xlog_location_diff(pg_last_xlog_replay_location(),
regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) - y.bytes_per_wal_segment
)
ELSE '0 MB'
END AS archival_lag
FROM pg_stat_archiver, pg_control_init() AS y

##### WAL recovery rate
> **PG >= 10**

WITH wal_data AS (
SELECT
now()::timestamp(0),
CASE
WHEN pg_is_in_recovery() THEN
pg_wal_lsn_diff(
x,
current_setting($$my.wal_loc$$, true)::pg_lsn
)
ELSE NULL
END AS wal_data_replayed,
set_config($$my.wal_loc$$::text, x::text, false) AS wal_pos
FROM pg_last_wal_replay_lsn() AS l(x)
)
SELECT
now,
round(wal_data_replayed / y.bytes_per_wal_segment, 2) AS wal_files_replayed_per_minute,
pg_size_pretty(wal_data_replayed) AS wal_data_replayed_per_minute
FROM wal_data, pg_control_init() AS y
WHERE wal_data_replayed IS NOT NULL
\watch 60

> **PG < 10**

WITH wal_data AS (
SELECT
now()::timestamp(0),
CASE
WHEN pg_is_in_recovery() THEN
pg_xlog_location_diff(
x,
current_setting($$my.wal_loc$$, true)::pg_lsn
)
ELSE NULL
END AS wal_data_replayed,
set_config($$my.wal_loc$$::text, x::text, false) AS wal_pos
FROM pg_last_xlog_replay_location() AS l(x)
)
SELECT
now,
round(wal_data_replayed / y.bytes_per_wal_segment, 2) AS wal_files_replayed_per_minute,
pg_size_pretty(wal_data_replayed) AS wal_data_replayed_per_minute
FROM wal_data, pg_control_init() AS y
WHERE wal_data_replayed IS NOT NULL
\watch 60

Example output:
- WAL generation rate

             Mon Jan 31 22:16:15 2022 (every 60s)

now	wal_files_generated_per_minute	wal_data_generated_per_minute

(0 rows)

Time: 40.007 ms
Mon Jan 31 22:17:15 2022 (every 60s)

now	wal_files_generated_per_minute	wal_data_generated_per_minute
2022-01-31 21:17:16	266.23	4260 MB

(1 row)

Time: 27.568 ms

- WAL archival rate

now	wal_files_archived_per_minute	wal_data_archived_per_minute

(0 rows)

Time: 62.165 ms
Mon Jan 31 23:22:13 2022 (every 60s)

now	wal_files_archived_per_minute	wal_data_archived_per_minute
2022-01-31 22:22:13	213.00	3408 MB

(1 row)

Time: 44.091 ms

- WAL archival lag

now	wal_files_ready_to_be_archived	archival_lag
2022-01-31 21:41:19	1.02	16 MB

(1 row)

Time: 35.998 ms

- WAL recovery rate

            Mon Jan 31 22:42:12 2022 (every 60s)

now	wal_files_replayed_per_minute	wal_data_replayed_per_minute

(0 rows)

Time: 31.892 ms
Mon Jan 31 22:43:12 2022 (every 60s)

now	wal_files_replayed_per_minute	wal_data_replayed_per_minute
2022-01-31 21:43:13	251.53	4024 MB

(1 row)

Time: 25.209 ms

PostgreSQL temp files usage

Bolaji Wahab — Sat, 22 Jan 2022 02:45:12 +0000

Temporary files

Certain query operations such as sort or hash table require some memory facility. This memory is provided by a runtime config work_mem.
From the official documentation work_mem

work_mem (integer)
Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.
Note that for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies before it starts to write data into temporary files.
Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, result cache nodes, and hash-based processing of IN subqueries.

Once the set work_mem is exceeded, the operation starts writing to the temporary disk files.

Another operation that can write temp files is CREATE INDEX. This is controlled by a different runtime config maintenance_work_mem.
From the official documentation maintenance_work_mem

maintenance_work_mem (integer)
Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

The particular location where these temporary disk files are written to is controlled by runtime config temp_tablespaces
Again from the official documentation temp_tablespaces

temp_tablespaces (string)
This variable specifies tablespaces in which to create temporary objects (temp tables and indexes on temp tables) when a CREATE command does not explicitly specify a tablespace. Temporary files for purposes such as sorting large data sets are also created in these tablespaces.
The value is a list of names of tablespaces. When there is more than one name in the list, PostgreSQL chooses a random member of the list each time a temporary object is to be created; except that within a transaction, successively created temporary objects are placed in successive tablespaces from the list. If the selected element of the list is an empty string, PostgreSQL will automatically use the default tablespace of the current database instead.
When temp_tablespaces is set interactively, specifying a nonexistent tablespace is an error, as is specifying a tablespace for which the user does not have CREATE privilege. However, when using a previously set value, nonexistent tablespaces are ignored, as are tablespaces for which the user lacks CREATE privilege. In particular, this rule applies when using a value set in postgresql.conf.
The default value is an empty string, which results in all temporary objects being created in the default tablespace of the current database.

Default tablespace vs Specific temp tablespaces

Default installation of PostgreSQL has default tablespace(pg_default) set as the temp_tablespaces.
You might want to separate writes of actual data from temp files, improving disk IO. That is where temp_tablespaces is most useful.
Also, you can point your temp_tablespaces to a faster disk such as nvme which is faster.

Setting up a tablespace for temporary files

You can use the following sample code to set up temp_tablespaces

Create a new tablespace:

CREATE TABLESPACE temp LOCATION 'actual_location';

Set cluster-wide temp_tablespaces:

ALTER SYSTEM SET temp_tablespaces = 'temp';
SELECT pg_reload_conf();

Caveats of temp files

Temp files can use up the available disk space when a query needs to create a lot of temp files, with PostgreSQL reporting an error such as could not write block .... of temporary file no space left on device ... and the query being canceled.
Temp files are only kept around for the duration of a query. Once the query finishes or cancels, the temp files are cleaned up. Once in a while DBAs experience (1) and when they check the disk usage, they find out there is still space and start troubleshooting for where the issue might be. The issue is described in (2). When this happens, it might be high time you optimized your queries. The other workaround would be, using a larger free disk as your temp_tablespaces.

Monitoring temp files usage

We can log temp files with the help of a runtime config log_temp_files

log_temp_files (integer)
Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. If enabled by this setting, a log entry is emitted for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified amount of data. If this value is specified without units, it is taken as kilobytes. The default setting is -1, which disables such logging. Only superusers can change this setting.

While log_temp_files logs information about a temporary file when it is deleted, you might want to monitor the temporary file while it's being created as well, keeping an eye on things.
Also, you might want to know which queries are making use of temporary files and maybe optimize them for the faster work_mem.

We would be doing this from SQL.
Query:

WITH tablespaces AS (
    SELECT spcname AS tbl_name
         , COALESCE(NULLIF(pg_tablespace_location(oid), ''), current_setting('data_directory') || '/base') AS tbl_location
      FROM pg_tablespace
)
, tablespace_suffix AS (
    SELECT tbl_name
         , tbl_location || '/pgsql_tmp' AS path
      FROM tablespaces
     WHERE tbl_name = 'pg_default'
     UNION ALL
    SELECT tbl_name
         , ((tbl_location || '/') || path) || '/pgsql_tmp'
      FROM tablespaces
         , LATERAL pg_ls_dir(tbl_location) AS path
     WHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d\.\d\d?|\d+)'))
)
, stat AS (
    SELECT substring(file FROM '\d+\d') AS pid
         , tbl_name AS temp_tablespace
         , pg_size_pretty(sum(pg_size_bytes(size))) AS size
      FROM tablespace_suffix
         , LATERAL pg_ls_dir(path, TRUE, FALSE) AS file
         , LATERAL pg_size_pretty(((pg_stat_file((path || '/') || file, TRUE))).size) AS size
     GROUP BY pid
            , temp_tablespace
)
SELECT a.datname
     , a.pid
     , COALESCE(size, '0 MB') AS temp_size_written
     , COALESCE(temp_tablespace, 'not using temp files') AS temp_tablespace
     , a.application_name
     , a.client_addr
     , a.usename
     , CAST(clock_timestamp() - a.query_start AS interval (0)) AS duration
     , CAST(clock_timestamp() - a.state_change AS interval (0)) AS duration_since_state_change
     , trim(TRAILING ';' FROM "left"(query, 1000)) AS query
     , a.state
     , (a.wait_event_type || ':') || a.wait_event AS wait
  FROM pg_stat_activity AS a
  LEFT JOIN stat
    ON a.pid = CAST(stat.pid AS integer)
 WHERE a.pid <> pg_backend_pid()
 ORDER BY temp_size_written DESC;

Example output:

 datname | pid  | temp_size_written |   temp_tablespace    | application_name | client_addr | usename | duration | duration_since_state_change |                        query                        | state  |             wait
---------+------+-------------------+----------------------+------------------+-------------+---------+----------+-----------------------------+-----------------------------------------------------+--------+------------------------------
 bolaji  | 4943 | 1911 MB           | temp                 | bolaji-psql      | <null>      | bolaji  | 00:00:51 | 00:00:51                    | CREATE INDEX CONCURRENTLY ON folder (id)            | active | <null>
 bolaji  | 7518 | 1338 MB           | pg_default           | bolaji-psql      | <null>      | bolaji  | 00:00:48 | 00:00:48                    | select generate_series(1,100000000) as a order by a | active | <null>
 <null>  | 3819 | 0 MB              | not using temp files |                  | <null>      | <null>  | <null>   | <null>                      |                                                     | <null> | Activity:AutoVacuumMain
 <null>  | 3818 | 0 MB              | not using temp files |                  | <null>      | <null>  | <null>   | <null>                      |                                                     | <null> | Activity:WalWriterMain
 <null>  | 3816 | 0 MB              | not using temp files |                  | <null>      | <null>  | <null>   | <null>                      |                                                     | <null> | Activity:CheckpointerMain
 <null>  | 3821 | 0 MB              | not using temp files |                  | <null>      | bolaji  | <null>   | <null>                      |                                                     | <null> | Activity:LogicalLauncherMain
 bolaji  | 5935 | 0 MB              | not using temp files | bolaji-psql      | <null>      | bolaji  | 00:31:39 | 00:31:39                    | show work_mem                                       | idle   | Client:ClientRead
 <null>  | 3817 | 0 MB              | not using temp files |                  | <null>      | <null>  | <null>   | <null>                      |                                                     | <null> | Activity:BgWriterHibernate
(8 rows)

Time: 2.960 ms

Finally, there is a new function pg_ls_tmpdir available from PG12. I decided not to use this function in the above query because it is not available in lower versions.

UPDATES: 27-05-2025
The above query does not work in a restricted environment (an example is RDS) due to access to the OS files. We can use this modified query to achieve the same in such environments but this requires that you are running on at least PostgreSQL 12.

WITH tablespaces AS (
    SELECT oid AS tbl_oid
         , spcname AS tbl_name
         , COALESCE(NULLIF(pg_tablespace_location(oid), ''), current_setting('data_directory') || '/base') AS tbl_location
      FROM pg_tablespace
      -- Exclude pg_global as it resides in the same directory as pg_default
     WHERE spcname <> 'pg_global'
)
, stat AS (
    SELECT substring(name FROM '\d+\d') AS pid
         , pg_size_pretty(sum(size)) AS size
         , tbl_name AS temp_tablespace
      FROM tablespaces
     INNER JOIN LATERAL pg_ls_tmpdir(tbl_oid)
        ON TRUE
     GROUP BY pid
            , tbl_name
)
SELECT a.datname
     , a.pid
     , COALESCE(size, '0 MB') AS temp_size_written
     , COALESCE(temp_tablespace, 'not using temp files') AS temp_tablespace
     , a.application_name
     , a.client_addr
     , a.usename
     , CAST(clock_timestamp() - a.query_start AS interval (0)) AS duration
     , CAST(clock_timestamp() - a.state_change AS interval (0)) AS duration_since_state_change
     , trim(TRAILING ';' FROM "left"(query, 1000)) AS query
     , a.state
     , (a.wait_event_type || ':') || a.wait_event AS wait
  FROM pg_stat_activity AS a
  LEFT JOIN stat
    ON a.pid = CAST(stat.pid AS integer)
 WHERE a.pid <> pg_backend_pid()
 ORDER BY temp_size_written DESC;

PostgreSQL lock trees

Bolaji Wahab — Thu, 13 Jan 2022 22:12:24 +0000

There are two forms of locks in PostgreSQL, row level and object level. While most operations would simply take row level locks, migrations usually take the more aggressive ones (object level locks).

PostgreSQL keeps a queue of locks, with first in first out (FIFO). While readers do not block writers, often times migration ends up blocking several other operations on the database leading to lock contention. This is due to the FIFO nature of the lock queue, there is no jumping the queue.
Imagine a migration which needs to alter a table (requiring access exclusive lock on the table), it blocks other operations even those doing ordinary SELECT. Also, the migration might have to wait to take the necessary lock if there are conflicting locks on the table leading to lock contention.

Often times when there is a lock contention, we end up with questions such as what (human or application) is the main root of the lock contention.

Here is an example of other people's work on the subject which you might find helpful:
postgresql-lock-trees by Nikolay Samokhvalov.

After experimenting with the above example and the drawback being the call on the function pg_blocking_pids(int), I started looking at how we can simply do away with calling the function.
Then I rewrote the query, relying on lock conflicts as listed on the documentation Conflicting Lock Modes, pg_locks and pg_stat_activity.

The query:



WITH conflicts(lock, conflict) AS (VALUES
    ('AccessShare', 'AccessExclusive'), ('RowShare', 'Exclusive'),
    ('RowShare', 'AccessExclusive'), ('RowExclusive', 'Share'),
    ('RowExclusive', 'ShareRowExclusive'), ('RowExclusive', 'Exclusive'),
    ('RowExclusive', 'AccessExclusive'), ('ShareUpdateExclusive', 'ShareUpdateExclusive'),
    ('ShareUpdateExclusive', 'Share'), ('ShareUpdateExclusive', 'ShareRowExclusive'),
    ('ShareUpdateExclusive', 'Exclusive'), ('ShareUpdateExclusive', 'AccessExclusive'),
    ('Share', 'RowExclusive'), ('Share', 'ShareUpdateExclusive'),
    ('Share', 'ShareRowExclusive'), ('Share', 'Exclusive'),
    ('Share', 'AccessExclusive'), ('ShareRowExclusive','RowExclusive'),
    ('ShareRowExclusive', 'ShareUpdateExclusive'), ('ShareRowExclusive', 'Share'),
    ('ShareRowExclusive', 'ShareRowExclusive'), ('ShareRowExclusive', 'Exclusive'),
    ('ShareRowExclusive', 'AccessExclusive'), ('Exclusive', 'RowShare'),
    ('Exclusive', 'RowExclusive'), ('Exclusive', 'ShareUpdateExclusive'),
    ('Exclusive', 'Share'), ('Exclusive', 'ShareRowExclusive'),
    ('Exclusive', 'Exclusive'), ('Exclusive', 'AccessExclusive'),
    ('AccessExclusive', 'AccessShare'), ('AccessExclusive', 'RowShare'),
    ('AccessExclusive', 'RowExclusive'), ('AccessExclusive', 'ShareUpdateExclusive'),
    ('AccessExclusive', 'Share'), ('AccessExclusive', 'ShareRowExclusive'),
    ('AccessExclusive','Exclusive'), ('AccessExclusive','AccessExclusive')
),
activity AS (
    SELECT
        datname,
        pid,
        usename,
        application_name,
        client_addr,
        xact_start,
        query_start,
        state_change,
        backend_xid,
        backend_xmin,
        state,
        wait_event_type,
        wait_event,
        query
    FROM pg_stat_activity
    WHERE pid != pg_backend_pid()
),
locks AS (
   SELECT
        pid,
        locktype,
        granted,
        relation::regclass AS relation,
        left(mode,-4) AS mode,
        ROW(locktype,database,relation::regclass,page,tuple,virtualxid,transactionid,classid,objid,objsubid) AS obj
    FROM pg_locks
),
pairs AS (
    SELECT
        w.pid AS waiter,
        l.pid AS locker,
        l.obj,
        l.mode,
        l.relation
    FROM locks AS w
    JOIN locks AS l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype = w.locktype AND NOT l.pid = w.pid AND l.granted
    WHERE NOT w.granted
),
leaders AS (
    SELECT
        DISTINCT(locker),
        coalesce(relation, (select max(relation) from locks where pid = locker)) AS relation,
        mode
    FROM (SELECT *, rank() OVER (PARTITION BY locker ORDER BY relation NULLS FIRST) AS pos FROM pairs) AS ss
    WHERE pos = 1
),
lock_queue AS (
    SELECT
        array_agg(l.pid::text || '.' || l.mode order by p.query_start) AS queue,
        rel.relation
    FROM locks AS l
    JOIN activity p ON p.pid = l.pid
    JOIN activity a ON a.pid = l.pid,
    LATERAL (SELECT coalesce(relation, max(relation)) AS relation FROM locks WHERE pid = l.pid AND relation IS NOT NULL GROUP BY relation) AS rel
    WHERE NOT l.granted
    GROUP BY rel.relation
),
push_root_on_lock_queue AS (
    SELECT
        l.relation,
        array_prepend(p.locker::text || '.' || p.mode, queue) AS queue
    FROM lock_queue AS l
    JOIN leaders AS p ON l.relation = p.relation
),
lock_queue_element_num AS (
    SELECT
        relation,
        a.element,
        a.num
    FROM push_root_on_lock_queue, unnest(queue) WITH ORDINALITY a(element, num)
),
main AS (
    SELECT
        a.relation,
        split_part(a.element, '.', 1) AS prev_pid,
        split_part(a.element, '.', 2) AS prev_mode,
        split_part(b.element, '.', 1) AS pid,
        split_part(b.element, '.', 2) AS mode,
        a.num AS start,
        b.num AS next,
        CASE
            WHEN EXISTS (SELECT * FROM conflicts AS c WHERE c.lock = split_part(a.element, '.', 2) AND c.conflict = split_part(b.element, '.', 2)) THEN true
            ELSE false
        END AS conflict
    FROM lock_queue_element_num AS a
    JOIN lock_queue_element_num b ON a.relation = b.relation
    WHERE a.num < b.num
    ORDER BY b.num DESC
),
tree AS (
    SELECT
        DISTINCT ON (pid, prev_pid)
        *,
        dense_rank() OVER (PARTITION BY relation ORDER BY start) - 1 AS level
    FROM main
),
stat AS (
    SELECT
        m.pid,
        p.locker,
        array_agg(DISTINCT m.prev_pid || ':' || m.level) AS blocked_by
    FROM pairs AS p
    JOIN tree AS m ON p.waiter = m.pid::int
    WHERE conflict
    AND m.pid != m.prev_pid
    GROUP BY m.pid, p.locker
),
/* sometimes we can miss some blockers in pairs due to the lock path, the query should be fix normally but for now this is workaround
   TODO: fix pairs query to include all path
*/
sub AS (
    SELECT
        pidd,
        count(pidd) AS blocked_cnt
    FROM (
        SELECT DISTINCT ON (pid) split_part(unnest(blocked_by), ':', 1) AS pidd
        FROM stat AS s
    ) AS blocked_cnt
    WHERE NOT EXISTS (SELECT FROM pairs AS p WHERE p.locker = blocked_cnt.pidd::int)
    GROUP BY pidd
),
result AS (
    /* add details of waiters */
    SELECT
        DISTINCT(s.pid::int),
        s.blocked_by,
        (clock_timestamp() - a.xact_start)::interval(0) AS ts_age,
        (clock_timestamp() - a.state_change)::interval(0) AS change_age,
        CASE
            WHEN s.blocked_by = '{}' THEN NULL::interval(0)
            ELSE (clock_timestamp() - a.query_start)::interval(0)
        END AS blocking_age,
        a.backend_xid AS xid,
        a.backend_xmin AS xmin,
        replace(a.state, 'idle in transaction', 'idletx') AS state,
        a.datname,
        a.usename,
        a.client_addr,
        a.application_name,
        a.wait_event_type || ':' || a.wait_event AS wait,
        blocked.cnt AS blocked_cnt,
        CASE
            WHEN s.blocked_by = '{}' THEN true
            ELSE false
        END AS root,
        trim(trailing ';' from format(
            '%s %s%s',
            '[' || s.pid::text || ']',
            coalesce(repeat('.', array_length(s.blocked_by, 1)+1), '') || ' ',
            left(query, 1000)
        )) AS query
    FROM stat AS s
    JOIN activity AS a ON s.pid::int = a.pid,
    LATERAL (
        SELECT
            coalesce((CASE
                WHEN count(p.waiter) = 0 THEN (SELECT blocked_cnt FROM sub WHERE sub.pidd = s.pid UNION ALL SELECT 0 FROM sub WHERE NOT EXISTS (SELECT FROM sub WHERE sub.pidd = s.pid))
                ELSE count(p.waiter)
            END), 0) AS cnt
    FROM pairs AS p WHERE p.locker = s.pid::int)AS blocked
    UNION ALL
    /* add details of lockers */
    SELECT
        DISTINCT(p.locker::int),
        '{}'::text[] AS blocked_by,
        (clock_timestamp() - a.xact_start)::interval(0) AS ts_age,
        (clock_timestamp() - a.state_change)::interval(0) AS change_age,
        NULL::interval AS blocking_age,
        a.backend_xid AS xid,
        a.backend_xmin AS xmin,
        replace(state, 'idle in transaction', 'idletx') AS state,
        a.datname,
        a.usename,
        a.client_addr,
        a.application_name,
        a.wait_event_type || ':' || a.wait_event AS wait,
        blocked.cnt AS blocked_cnt,
        true AS root,
        trim(trailing ';' from format(
            '%s %s%s',
            '[' || p.locker::text || ']',
            coalesce(repeat('.', 0), '') || ' ',
            left(query, 1000)
        )) AS query
    FROM pairs AS p
    JOIN activity AS a ON p.locker::int = a.pid,
    LATERAL (SELECT count(t.prev_pid) AS cnt FROM tree t WHERE t.prev_pid = p.locker::text) blocked
    WHERE NOT EXISTS (SELECT FROM stat AS s WHERE s.pid::int = p.locker)
    /* every other pids not in locker and waiter pairs */
    UNION ALL
    SELECT
        DISTINCT(a.pid),
        '{}'::text[] AS blocked_by,
        (clock_timestamp() - a.xact_start)::interval(0) AS ts_age,
        (clock_timestamp() - a.state_change)::interval(0) AS change_age,
        NULL::interval AS blocking_age,
        a.backend_xid AS xid,
        a.backend_xmin AS xmin,
        replace(state, 'idle in transaction', 'idletx') AS state,
        a.datname,
        a.usename,
        a.client_addr,
        a.application_name,
        a.wait_event_type || ':' || a.wait_event AS wait,
        0::int AS blocked_cnt,
        true AS root,
        trim(trailing ';' from format(
            '%s %s%s',
            '[' || a.pid::text || ']',
            coalesce(repeat('.', 0), '') || ' ',
            left(query, 1000)
        )) AS query
    FROM activity AS a WHERE NOT EXISTS (SELECT FROM pairs AS p WHERE p.locker = a.pid OR p.waiter = a.pid)
    ORDER by blocked_cnt DESC, root, pid
)
SELECT
    pid,
    blocked_by,
    ts_age,
    change_age,
    blocking_age,
    xid,
    xmin,
    state,
    datname,
    usename,
    client_addr,
    application_name,
    wait,
    blocked_cnt,
    root,
    query
FROM result;

Example output:



  pid  |    blocked_by     |  ts_age  | change_age | blocking_age |  xid   |  xmin  | state  | datname | usename | client_addr | application_name |             wait             | blocked_cnt | root |                         query
-------+-------------------+----------+------------+--------------+--------+--------+--------+---------+---------+-------------+------------------+------------------------------+-------------+------+-------------------------------------------------------
 40188 | {}                | 00:10:03 | 00:10:00   | <null>       |  87324 | <null> | idletx | bolaji  | bolaji  | <null>      | bolaji-psql      | Client:ClientRead            |           4 | t    | [40188]  update folder set id = id
 40224 | {40188:0}         | 00:09:57 | 00:09:56   | 00:09:56     |  87325 |  87324 | active | bolaji  | bolaji  | <null>      | bolaji-psql      | Lock:transactionid           |           3 | f    | [40224] .. delete from folder
 40245 | {40188:0,40224:0} | 00:09:51 | 00:09:50   | 00:09:50     |  87326 |  87324 | active | bolaji  | bolaji  | <null>      | bolaji-psql      | Lock:relation                |           2 | f    | [40245] ... alter table folder add column data1 jsonb
 40258 | {}                | 00:09:41 | 00:09:40   | <null>       |  87327 | <null> | idletx | bolaji  | bolaji  | <null>      | bolaji-psql      | Client:ClientRead            |           1 | t    | [40258]  drop table sample
 40222 | {40245:2}         | 00:09:45 | 00:09:45   | 00:09:45     | <null> |  87324 | active | bolaji  | bolaji  | <null>      | bolaji-psql      | Lock:relation                |           0 | f    | [40222] .. select * from folder where id = 1
 40253 | {40245:2}         | 00:09:47 | 00:09:47   | 00:09:47     | <null> |  87324 | active | bolaji  | bolaji  | <null>      | bolaji-psql      | Lock:relation                |           0 | f    | [40253] .. select * from folder
 40260 | {40258:0}         | 00:09:38 | 00:09:38   | 00:09:38     | <null> |  87324 | active | bolaji  | bolaji  | <null>      | bolaji-psql      | Lock:relation                |           0 | f    | [40260] .. select * from sample
  3640 | {}                | <null>   | <null>     | <null>       | <null> | <null> | <null> | <null>  | <null>  | <null>      |                  | Activity:CheckpointerMain    |           0 | t    | [3640]
  3641 | {}                | <null>   | <null>     | <null>       | <null> | <null> | <null> | <null>  | <null>  | <null>      |                  | Activity:BgWriterHibernate   |           0 | t    | [3641]
  3642 | {}                | <null>   | <null>     | <null>       | <null> | <null> | <null> | <null>  | <null>  | <null>      |                  | Activity:WalWriterMain       |           0 | t    | [3642]
  3643 | {}                | <null>   | <null>     | <null>       | <null> | <null> | <null> | <null>  | <null>  | <null>      |                  | Activity:AutoVacuumMain      |           0 | t    | [3643]
  3645 | {}                | <null>   | <null>     | <null>       | <null> | <null> | <null> | <null>  | bolaji  | <null>      |                  | Activity:LogicalLauncherMain |           0 | t    | [3645]
(12 rows)

Time: 4.372 ms

A little explanation:

{40188:0} - translates to pid:lock_level
pid 40245 blocked by {40188:0,40224:0} - translates to pids 40188 on level 0 (the root), 40224 also level 0(the root) and 40245 on level 1.
[40224] .. delete from folder - translates to pid 40224 being on level 1 on the lock queue/tree behind the root.

I hope you find the above query useful for lock issue troubleshooting.

Intro

Bolaji Wahab — Wed, 12 Jan 2022 22:59:42 +0000

Hi there,

My name is Bolaji Wahab, I have been using PostgreSQL for 4 years now and I'm loving it. I currently work as Database Engineer with focus on PostgreSQL.
I am starting a weekly blog series on dev.to on PostgreSQL with focus on installation, configuration, monitoring, performance tips, troubleshooting, debugging and various other PostgreSQL tips.
I hope someone finds the blog series helpful.

Cheers

DEV Community: Bolaji Wahab

Data type mismatch in referential integrity and its implications

Summary

Key takeaways

Progress Reporting In PostgreSQL

Monitoring the progress of create index, create index concurrently, reindex, reindex concurrently

Monitoring the progress of vacuum, vacuum analyze

Monitoring the progress of vacuum full, cluster

Monitoring the progress of analyze

Monitoring the progress of copy

Monitoring the progress of basebackup

Little explanation:

Cleanly modifying PostgreSQL configurations

Ways of adjusting PostgreSQL configs

Checking for errors in the config

Checking for pending reload and pending restart

Generating nicely parsed schema files with pg_dump_schema_parser

Tracking dropped database objects in PostgreSQL

PostgreSQL WAL activities

Durability

WAL archival

Monitoring WAL activities

WAL generation rate

PostgreSQL temp files usage

Temporary files

Default tablespace vs Specific temp tablespaces

Setting up a tablespace for temporary files

Caveats of temp files

Monitoring temp files usage

PostgreSQL lock trees

Intro

Monitoring the progress of `create index`, `create index concurrently`, `reindex`, `reindex concurrently`

Monitoring the progress of `vacuum`, `vacuum analyze`

Monitoring the progress of `vacuum full`, `cluster`

Monitoring the progress of `analyze`

Monitoring the progress of `copy`

Monitoring the progress of `basebackup`