The IBM FHIR Server stores resources in the PostgreSQL database and uses a relational model to store historical FHIR Resource and enable search on the latest FHIR resources. The resource data is spread in a relational model that is occasionally tweaked in order to improve search or optimize the retrieval using the relational model.

In the IBM FHIR Server Performance Guide, the guide outlines some important alterations to the tables that facilitate an optimized Postgres instance. The guide suggests altering, per your providers recommendation, autovacuum_vacuum_cost_limit, autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold in order to optimize the VACUUM process. With the IBM FHIR Server fhir-persistence-schema-cli, autovacuum_vacuum_scale_factor is not automatically configured, and not recommended on Databases for Postgres on IBM Cloud.

As Postgres uses "multi-version concurrency control (MVCC) to ensure that data remains consistent and accessible in high-concurrency environments", each transaction runs on a snapshot, and needs to be reconciled so dead_rows are removed – vacuumed. The VACUUM process manages dead rows and disk usage (reuse). The VACUUM process (autovacuum) frequently runs – gathering statistics and reconciling the maintenance of the table statitstics and data.

To check for tables that need vacuuming:

 SELECT relname AS "table_name",
        n_tup_ins AS "inserts",
        n_tup_upd AS "updates",
        n_tup_del AS "deletes",
        n_live_tup AS "live_tuples",
        n_dead_tup AS "dead_tuples"
   FROM pg_stat_user_tables
  WHERE schemaname = 'fhirdata'
    AND (relname = 'logical_resources' OR relname LIKE '%_values')
    AND n_dead_tup > 0;

Then a database administrator runs – VACUUM FULL FHIRDATA.PROCEDURE_RESOURCE_TOKEN_REFS; to execute a vacuum, which runs in the background.

While the VACUUM process is running, the pg_stat_progress_vacuum view can be queried to see worker process.

SELECT * 
FROM pg_stat_progress_vacuum;

If you need to update a specific tables settings, you can run with --vacuum-table-name.

java -jar ./fhir-persistence-schema-${VERSION}-cli.jar \
--db-type postgresql --prop db.host=localhost --prop db.port=5432 \
--prop db.database=fhirdb --schema-name fhirdata \
--prop user=fhiradmin --prop password=passw0rd \
--update-vacuum --vacuum-cost-limit 2000 --vacuum-threshold 1000 \
--vacuum-scale-factor 0.01 --vacuum-table-name LOGICAL_RESOURCES

To update all tables in a schema, you can run without the table parameter. If you omit any value, the defaults are picked as described in the Performance guide.

If you hit a lock (ShareUpdateExclusiveLock), the VACUUM worker process is currently churning on the table, and the ALTER statement is waiting.

• wait_type = Lock relation Waiting to acquire a lock on a relation.
• wait_lock_type – ShareUpdateExclusiveLock Acquired by VACUUM and conflicts with ALTER

CHeck for the Blocking PID, and grab the blocking_pid.

 -- list bad connections
   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,
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   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 and blocked_activity.usename = 'fhirserver'

Try canceling the PID, SELECT pg_cancel_backend(205384);

Else, cancel the current Transaction the blocked pid:

-- cancel the blocking trannsaction/pid (hard stop)
SELECT pg_cancel_backend(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,
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   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 and blocked_activity.usename = 'fhirserver'

Now, wait until the VACUUM finishes, and then execute a new ALTER.

You should be all set at this point.

Check wait_type

-- Check Wait Type
SELECT 
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.pid                AS waiting_pid,
    other.locktype             AS other_locktype,
    other.relation::regclass   AS other_table,
    other_stm.query            AS other_query,
    other.mode                 AS other_mode,
    other.pid                  AS other_pid,
    other.granted              AS other_granted
FROM
    pg_catalog.pg_locks AS waiting
JOIN
    pg_catalog.pg_stat_activity AS waiting_stm
    ON (
        waiting_stm.pid = waiting.pid
    )
JOIN
    pg_catalog.pg_locks AS other
    ON (
        (
            waiting."database" = other."database"
        AND waiting.relation  = other.relation
        )
        OR waiting.transactionid = other.transactionid
    )
JOIN
    pg_catalog.pg_stat_activity AS other_stm
    ON (
        other_stm.pid = other.pid
    )
WHERE
    NOT waiting.granted
AND
    waiting.pid <> other.pid   

SELECT
  schemaname, relname,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count, *
FROM pg_stat_user_tables
WHERE schemaname = 'fhirdata' AND relname = 'observation_date_values';

Check with locks

SELECT now()::time, query, backend_start, xact_start, query_start,
         state_change, state,
         now()::time - state_change::time AS locked_since,
         pid, wait_event_type, wait_event
  FROM pg_stat_activity
  WHERE wait_event_type IS NOT NULL and wait_event_type = 'Lock'
ORDER BY locked_since DESC;

Check a PID

SELECT a.usename, a.application_name, a.datname, a.query,
         l.granted, l.mode, transactionid
    FROM pg_locks l
    JOIN pg_stat_activity a ON a.pid = l.pid
    WHERE granted = false AND a.pid = 327589;

Check a tansaction

SELECT a.usename, a.application_name, a.datname, a.query,
        l.granted, l.mode, transactionid,
        now()::time - a.state_change::time AS acquired_since,
        a.pid
   FROM pg_locks l
   JOIN pg_stat_activity a ON a.pid = l.pid
   WHERE granted = true AND transactionid = 3031;

Reference

• IBM FHIR Server: Adjust the Vacuum Settings for PostgreSQL Tables only
• IBM FHIR Server: 4.1.4. Vacuum Monitoring
• Postgres: pg_locks
• Postgres: Lock dependency information
• Postgres: 9.6.1. Table-level locks
• Postgres: 28.4.1. VACUUM Progress Reporting
• StackOverflow: Remove locks without a pid in postgres
• StackOverflow: How to detect query which holds the lock in Postgres?
• Monitoring PostgreSQL VACUUM processes
• Exploring Query Locks in Postgres
• Find transactions that are locked (and who to blame for!)
• PostgreSQL: Decoding Deadlocks
• Locks in PostgreSQL: 1. Relation-level locks
• IBM Cloud – Postgres Settings