Re: Backends stalled in 'startup' state

We recently saw many backends (close to max_connection limit) get stalled in 'startup' in one of the production environments for Greenplum (fork of PostgreSQL). Tracing the reason, it was found all the tuples created by bootstrap (xmin=1) in pg_attribute were at super high block numbers (for example beyond 30,000). Tracing the reason for the backend startup stall exactly matched Tom's reasoning in [1]. Stalls became much longer in presence of sub-transaction overflow or presence of long running transactions as tuple visibility took longer. The thread ruled out the possibility of system catalog rows to be present in higher block numbers instead of in front for pg_attribute.

This thread provides simple reproduction on the latest version of PostgreSQL and RCA for how bootstrap catalog entries can move to higher blocks and as a result cause stalls for backend starts. Simple fix to avoid the issue provided at the end.

The cause is syncscan triggering during VACUUM FULL. VACUUM FULL rewrites the table by performing the seqscan as well. And heapam_relation_copy_for_cluster() conveys feel free to use syncscan. Hence logic to not start from block 0 instead some other block already in cache is possible and opens the possibility to move the bootstrap tuples to anywhere else in the table.

------------------------------------------------------------------

Repro

------------------------------------------------------------------

-- create database to play
drop database if exists test;
create database test;
\c test

-- function just to create many tables to increase pg_attribute size
-- (ideally many column table might do the job more easily)
CREATE OR REPLACE FUNCTION public.f(id integer)  
 RETURNS void  
 LANGUAGE plpgsql  
 STRICT  
AS $function$  
declare  
  sql text;  
  i int;  
begin  
  for i in id..id+9999 loop  
    sql='create table if not exists tbl'||i||' (id int)';  
    execute sql;  
  end loop;  
end;  
$function$;

select f(10000);
select f(20000);
select f(30000);
select f(40000);

-- validate pg_attribute size is greater than 1/4 of shared_buffers
-- for syncscan to triggger
show shared_buffers;
select pg_size_pretty(pg_relation_size('pg_attribute'));
select ctid, xmin, attrelid, attname from pg_attribute where xmin = 1 limit 5;

-- perform seq scan of pg_attribute to page past bootstrapped tuples
copy (select * from pg_attribute limit 2000) to '/tmp/p';

-- this will internally use syncscan starting with block after bootstrap tuples 

-- and hence move bootstrap tuples last to higher block numbers
vacuum full pg_attribute;

------------------------------------------------------------------

Sample run

------------------------------------------------------------------

show shared_buffers;
 shared_buffers
----------------
 128MB
(1 row)

select pg_size_pretty(pg_relation_size('pg_attribute'));
 pg_size_pretty
----------------
 40 MB
(1 row)

select ctid, xmin, attrelid, attname from pg_attribute where xmin = 1 limit 5;
 ctid  | xmin | attrelid |   attname    
-------+------+----------+--------------
 (0,1) |    1 |     1255 | oid
 (0,2) |    1 |     1255 | proname
 (0,3) |    1 |     1255 | pronamespace
 (0,4) |    1 |     1255 | proowner
 (0,5) |    1 |     1255 | prolang
(5 rows)

copy (select * from pg_attribute limit 2000) to '/tmp/p';
COPY 2000
vacuum full pg_attribute;
VACUUM
select ctid, xmin, attrelid, attname from pg_attribute where xmin = 1 limit 5;
   ctid    | xmin | attrelid |   attname    
-----------+------+----------+--------------
 (5115,14) |    1 |     1255 | oid
 (5115,15) |    1 |     1255 | proname
 (5115,16) |    1 |     1255 | pronamespace
 (5115,17) |    1 |     1255 | proowner
 (5115,18) |    1 |     1255 | prolang
(5 rows)

Note:

-- used logic causing the problem to fix it as well on the system :-)
-- scan till block where bootstrap tuples are located

select ctid, xmin, attrelid, attname from pg_attribute where xmin = 1 limit 5;
-- now due to syncscan triggering it will pick the blocks with bootstrap tuples first and help to bring them back to front

vacuum full pg_attribute;

------------------------------------------------------------------

Patch to avoid the problem:

------------------------------------------------------------------

diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index a3414a76e8..4c031914a3 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -757,7 +757,17 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap,
                pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
                                                                         PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP);
 
-               tableScan = table_beginscan(OldHeap, SnapshotAny, 0, (ScanKey) NULL);
+               /*
+                * For system catalog tables avoid syncscan, so that scan always
+                * starts from block 0 during rewrite and helps retain bootstrap
+                * tuples in initial pages only. If using syncscan, then bootstrap
+                * tuples may move to higher blocks, which will lead to degraded
+                * performance for relcache initialization during connection starts.
+                */
+               if (is_system_catalog)
+                       tableScan = table_beginscan_strat(OldHeap, SnapshotAny, 0, (ScanKey) NULL, true, false);
+               else
+                       tableScan = table_beginscan(OldHeap, SnapshotAny, 0, (ScanKey) NULL);
                heapScan = (HeapScanDesc) tableScan;
                indexScan = NULL;

------------------------------------------------------------------

1] https://www.postgresql.org/message-id/27844.1338148415%40sss.pgh.pa.us