PostgreSQL 异步消息(LISTEN/NOTIFY)缓存多大？

背景

PostgreSQL异步消息功能的一些应用：

《PostgreSQL 流式处理应用实践 - 二手商品实时归类》

《PostgreSQL 事件触发器应用 - DDL审计记录 + 异步通知(notify)》

《从电波表到数据库小程序之 - 数据库异步广播(notify/listen)》

《use PostgreSQL async Notification as a chat group》

《PostgreSQL Notify/Listen Like ESB》

那么一条异步消息支持多大的容量，当客户端消费堵塞时，数据库端最多可以HOLD多少条异步消息(或者多少容量)呢？

单条异步消息的上限

单条异步消息的上限

/*  
 * Maximum size of a NOTIFY payload, including terminating NULL.  This  
 * must be kept small enough so that a notification message fits on one  
 * SLRU page.  The magic fudge factor here is noncritical as long as it's  
 * more than AsyncQueueEntryEmptySize --- we make it significantly bigger  
 * than that, so changes in that data structure won't affect user-visible  
 * restrictions.  
 */  
#define NOTIFY_PAYLOAD_MAX_LENGTH       (BLCKSZ - NAMEDATALEN - 128)  

异步消息结构

/*  
 * Struct representing an entry in the global notify queue  
 *  
 * This struct declaration has the maximal length, but in a real queue entry  
 * the data area is only big enough for the actual channel and payload strings  
 * (each null-terminated).  AsyncQueueEntryEmptySize is the minimum possible  
 * entry size, if both channel and payload strings are empty (but note it  
 * doesn't include alignment padding).  
 *  
 * The "length" field should always be rounded up to the next QUEUEALIGN  
 * multiple so that all fields are properly aligned.  
 */  
typedef struct AsyncQueueEntry  
{  
        int                     length;                 /* total allocated length of entry */  
        Oid                     dboid;                  /* sender's database OID */  
        TransactionId xid;                      /* sender's XID */  
        int32           srcPid;                 /* sender's PID */  
        char            data[NAMEDATALEN + NOTIFY_PAYLOAD_MAX_LENGTH];  
} AsyncQueueEntry;  

数据库端最多可以HOLD多少异步消息

/*  
 * Define SLRU segment size.  A page is the same BLCKSZ as is used everywhere  
 * else in Postgres.  The segment size can be chosen somewhat arbitrarily;  
 * we make it 32 pages by default, or 256Kb, i.e. 1M transactions for CLOG  
 * or 64K transactions for SUBTRANS.  
 *  
 * Note: because TransactionIds are 32 bits and wrap around at 0xFFFFFFFF,  
 * page numbering also wraps around at 0xFFFFFFFF/xxxx_XACTS_PER_PAGE (where  
 * xxxx is CLOG or SUBTRANS, respectively), and segment numbering at  
 * 0xFFFFFFFF/xxxx_XACTS_PER_PAGE/SLRU_PAGES_PER_SEGMENT.  We need  
 * take no explicit notice of that fact in slru.c, except when comparing  
 * segment and page numbers in SimpleLruTruncate (see PagePrecedes()).  
 */  
#define SLRU_PAGES_PER_SEGMENT  32  

 * The amount of shared memory used for notify management (NUM_ASYNC_BUFFERS)  
 * can be varied without affecting anything but performance.  The maximum  
 * amount of notification data that can be queued at one time is determined  
 * by slru.c's wraparound limit; see QUEUE_MAX_PAGE below.  

/*  
 * slru.c currently assumes that all filenames are four characters of hex  
 * digits. That means that we can use segments 0000 through FFFF.  
 * Each segment contains SLRU_PAGES_PER_SEGMENT pages which gives us  
 * the pages from 0 to SLRU_PAGES_PER_SEGMENT * 0x10000 - 1.  
 *  
 * It's of course possible to enhance slru.c, but this gives us so much  
 * space already that it doesn't seem worth the trouble.  
 *  
 * The most data we can have in the queue at a time is QUEUE_MAX_PAGE/2  
 * pages, because more than that would confuse slru.c into thinking there  
 * was a wraparound condition.  With the default BLCKSZ this means there  
 * can be up to 8GB of queued-and-not-read data.  
 *  
 * Note: it's possible to redefine QUEUE_MAX_PAGE with a smaller multiple of  
 * SLRU_PAGES_PER_SEGMENT, for easier testing of queue-full behaviour.  
 */  
#define QUEUE_MAX_PAGE                  (SLRU_PAGES_PER_SEGMENT * 0x10000 - 1)  

当blocksize, pagesize=8KB时，最大可以HOLD约16GB。

8k * 65535 = 16GB  

被HOLD的异步消息存在哪里？

digoal@iZbp13nu0s9j3x3op4zpd4Z-> ll  
total 300K  
-rw------- 1 digoal digoal  193 Nov  8  2017 backup_label.old  
drwx------ 9 digoal digoal 4.0K Dec 15  2017 base  
-rw------- 1 digoal digoal   30 Jun 16 11:37 current_logfiles  
drwx------ 2 digoal digoal 4.0K Jun 16 11:37 global  
drwx------ 2 digoal digoal 4.0K Nov 13  2017 log  
drwx------ 2 digoal digoal 4.0K Nov  7  2017 pg_commit_ts  
drwx------ 2 digoal digoal 4.0K Nov  7  2017 pg_dynshmem  
-rw------- 1 digoal digoal 4.5K Nov  7  2017 pg_hba.conf  
-rw------- 1 digoal digoal 1.6K Nov  7  2017 pg_ident.conf  
drwx------ 4 digoal digoal 4.0K Jun 16 11:37 pg_logical  
drwx------ 4 digoal digoal 4.0K Nov  7  2017 pg_multixact  
drwx------ 2 digoal digoal  36K Jun 16 11:37 pg_notify  
drwx------ 4 digoal digoal 4.0K Dec 27  2017 pg_replslot  
drwx------ 2 digoal digoal 4.0K Nov  7  2017 pg_serial  
drwx------ 2 digoal digoal 4.0K Nov  7  2017 pg_snapshots  
drwx------ 2 digoal digoal 4.0K Jun 16 11:37 pg_stat  
drwx------ 2 digoal digoal 4.0K Jun 16 11:37 pg_stat_tmp  
drwx------ 2 digoal digoal 132K Dec 27  2017 pg_subtrans  
drwx------ 2 digoal digoal 4.0K Nov  9  2017 pg_tblspc  
drwx------ 2 digoal digoal 4.0K Nov  7  2017 pg_twophase  
-rw------- 1 digoal digoal    3 Nov  7  2017 PG_VERSION  
lrwxrwxrwx 1 digoal digoal   22 Nov  7  2017 pg_wal -> /data02/pg/pg_wal_1999  
drwx------ 2 digoal digoal  20K Dec 27  2017 pg_xact  
-rw------- 1 digoal digoal 2.5K Jan 11  2018 postgresql.auto.conf  
-rw------- 1 digoal digoal  23K Jan 11  2018 postgresql.conf  
-rw------- 1 digoal digoal   34 Jun 16 11:37 postmaster.opts  
  
digoal@iZbp13nu0s9j3x3op4zpd4Z-> cd pg_notify/  
digoal@iZbp13nu0s9j3x3op4zpd4Z-> ll  
total 8.0K  
-rw------- 1 digoal digoal 8.0K Jun 16 11:37 0000  

参考

src/include/access/slru.h

src/backend/commands/async.c

digoal’s 大量PostgreSQL文章入口