Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vkyF0-005Bx6-13 for pgpool-hackers@arkaria.postgresql.org; Wed, 28 Jan 2026 05:37:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vkyEz-00HY8h-0h for pgpool-hackers@arkaria.postgresql.org; Wed, 28 Jan 2026 05:37:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vkyEy-00HY8Y-2r for pgpool-hackers@lists.postgresql.org; Wed, 28 Jan 2026 05:37:21 +0000 Received: from mail-yx1-xb12f.google.com ([2607:f8b0:4864:20::b12f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vkyEw-002gaX-1S for pgpool-hackers@lists.postgresql.org; Wed, 28 Jan 2026 05:37:20 +0000 Received: by mail-yx1-xb12f.google.com with SMTP id 956f58d0204a3-649655f14d7so4071988d50.1 for ; Tue, 27 Jan 2026 21:37:19 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1769578638; cv=none; d=google.com; s=arc-20240605; b=JrnZXfkCNz+UTW1TR3nKxBfPw9LBCnvHYzG/uXmtxU1CuDG63hkE1gJFrnjNI9Mbv9 uzd/DpQUF89GUjp0w1r32RZ01H/pSdUhYiVa1DSGBvhWtpUjb/L1aSq8r6r8ou7H9OjB IcbRdxJfEoxUmgxQ1kVPVFS9h8MAuK/D81QVbF3WJwZcjZrFoeNcB62El7dADwL2uzYb kpPMNqMdbaN7pq8GzJHjSxgOro5TS2SbzW8KM/QV8vQVX7fc4CRxapZf2dA+LM9rBs/I Dfj3a4oVSOH03F1aefSAD4CdivQhrClaJHqzHQEVwW2PD8u3aksBscjBAnVcxsKsb3mJ aX9Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=q3qvvQ8RVJIYmbl6ulHiMc7UwwYIoVFjeYFR2EeBuHA=; fh=m3oiE62fRN5J1XaE+5vpHZRIgCyFefxVI0mFw3d8yxA=; b=dK2L6b18x1xy5Xdg52jVXxkHsr0H6N1wGS43QN7HH7Uje2lGnB3uQvXRfmLPSUv89U vCKqsnw3I8Ot6v/64/iRRyE3BgMBD0JGYVa+inajwvTbrv5nduKakYmBMSHb+ojLGJPz Oh4lwkzqsgBOwZXnNuo7XEdUA9+bI64AygdM2OO5dNsnT0jrAK9XU/13vucqVtGn1ge/ 0iBNC//HMN2yQdNgc1FSiQ/jJd0OjK9a7493lBwDKOvkjgxDIxSYC6XAwULmomD7viXV LluwkhYAgCURy7mb+IU276/Yh/XmbCYX81DZIJAIgzWLejo8TaUw8HxWP6aCIbenWyS+ c6Qg==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tailorbrands.com; s=google; t=1769578638; x=1770183438; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=q3qvvQ8RVJIYmbl6ulHiMc7UwwYIoVFjeYFR2EeBuHA=; b=hhNRX4GLKDzAEqoA/XwIiBoZT1PhARcqSiafTBLCD9JpSx9Q+yKi0TDR53FzLLXtm0 4xTQzEIMc/wbRmsOSUlAnmbJa0vva2axR/54iOckAjczm66DSKSBwXUzrm8tMAAHpHS/ ECI2FlHtWqQYJQfIHpwVLq/Lzai1ySz33BLSAYuVadB+xBk5BQojlnKyvK9ooL0pT9Hx z9FnnVo+Vej7p2moz5uDCD85kKzsctqXsdif81oiulrvnqdok5fiOFgmOTYetWAgoEea DL+r4DKRncn26tv64oHoRvwj0azBhpWZ7VVHJAyrrj8HsjghasHXhxQ23U+vCIHhOlGA 0sQQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1769578638; x=1770183438; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=q3qvvQ8RVJIYmbl6ulHiMc7UwwYIoVFjeYFR2EeBuHA=; b=bMHNUuf6Cvpt8IYVQdjMESIYMbOWpMmBcNWrun1WMYB42EbfSXdMvarHVK2C9hDERa qqBqWmMtJEOOB10KzJhNzdfF0N26gVDYYWVK0nEIABwriXP1wp8sJQh/eAJ1hhUIljXx K8WhO2AePJqW9OV0oGUvCB08Rvv3xbbTwhPt5DVv9qOZvJ1OWHYDdJg5pkcTDAbAfDtX ai6Y/qMT5ybRAfM4gWyVSVJkIqrxMqzPkcZuEPNgwi9NUXy4bt5zGQgzE4wgIj1T3Kpr Z5120gv64NiupL28MY/x2KUv3mbIpx8hgQjHHMgR2FvkHuazM7VYxc/uKwMfMsauF667 TfDg== X-Gm-Message-State: AOJu0YxPRTa1rmLETMlXX9vClp7wDBkhlau0/OttycrOCawknmOzDeY4 5k3xO63xCXsez/UcmaUfZ1+oGfVzflyx4qcH7JmcJSBNQaigl/6IyaYb4jNarSK8xpcpMOy2TE6 aMNGw9JokIXdV7T/CROBBDAUxURreNaw2QvW3QRLQ3WuHbqU47PgL9Js= X-Gm-Gg: AZuq6aLPeOaAkP3dWOwzApSNRotz4CUAiTnj8hEz9IksMjE/HhA3b78wZQrAT8PGsZY /4JSasIm1bY8cf96X78KZDXFzMlfLsFXhjl4bfZetmYEx0of03cIahjVqA0ZuuC+18jTqQSym40 yrjSFOa0S2F0eOOvNkp0/EhazBO8qQdn8qDYo2Do2F4SBRLbyUHZCloPfqqwH7YZHJxw6rsJa4W XNK2m0rDqH3UGVVi72vC/Omke70bYPRv5dehaI+znkZuZGs813lB3gT4sk+jR2V3tUDqMtZgm9y 9B5SU5p1YBhYYT6gjJRV1N9U+wtpq+2ksFovqxMWb6hJdozq1qaBqcBmDk5L3x5jcve/ X-Received: by 2002:a53:d016:0:b0:647:116a:f67e with SMTP id 956f58d0204a3-6498fc77300mr2465775d50.96.1769578638231; Tue, 27 Jan 2026 21:37:18 -0800 (PST) MIME-Version: 1.0 References: <20260128.140840.463288103359200425.ishii@postgresql.org> In-Reply-To: <20260128.140840.463288103359200425.ishii@postgresql.org> From: Nadav Shatz Date: Wed, 28 Jan 2026 07:37:07 +0200 X-Gm-Features: AZwV_QiRfyi673uVM408KOX7Svl7qsNfvUuGp9wUSjELQeBrb9h9uAB26cWHsVM Message-ID: Subject: Re: Proposal: Recent mutated table tracking in memory To: Tatsuo Ishii Cc: pgpool-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000022696c06496c2201" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000022696c06496c2201 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you Tatsuo! Nadav Shatz Tailor Brands | CTO On Wed, Jan 28, 2026 at 7:08=E2=80=AFAM Tatsuo Ishii = wrote: > Hi Nadav, > > Sorry for the late reply. I just your email now. Will check and reply > back soon. > -- > Tatsuo Ishii > SRA OSS K.K. > English: http://www.sraoss.co.jp/index_en/ > Japanese:http://www.sraoss.co.jp > > > Hi all, > > > > Any comments or concerns? can we merge it if not? > > > > On Tue, Jan 6, 2026 at 1:25=E2=80=AFPM Nadav Shatz > wrote: > > > >> Hello, > >> > >> As initially proposed under "Proposal: recent access based routing for > >> primary-replica setups" and then broken into separate tasks - i am > adding > >> here a patch to implement tracking of latest mutated table, and then > using > >> the replication lag as a base - deciding where to point queries when > query > >> load balancing and parsing is enabled. > >> > >> More details as in the patch: > >> Feature: add in-memory table tracking to prevent stale reads from > replicas > >> > >> Implement "memory map" feature that tracks recently-written database > >> tables in shared memory to prevent stale reads during replication lag. > >> When a write (INSERT/UPDATE/DELETE) occurs on a table, that table is > >> marked as "dirty" for a configurable TTL period. Any SELECT on a dirty > >> table within the TTL window is routed to primary instead of replica. > >> > >> Key features: > >> - Shared memory hash table for tracking table mutations with TTL > >> - Query parse cache with LRU eviction for performance > >> - Cold start protection (routes all queries to primary initially) > >> - Automatic TTL calculation: replication_delay =C3=97 configurable fac= tor > >> - Per-table staleness tracking with microsecond precision > >> > >> New configuration parameters: > >> - memory_map_enabled: Enable/disable the feature (default: off) > >> - memory_map_ttl_factor: TTL multiplier for replication delay (default= : > >> 5.0) > >> - memory_map_cold_start_duration: Cold start period in ms (default: > 2000) > >> - memory_map_table_buckets: Hash buckets for table map (default: 1024) > >> - memory_map_table_size: Max tracked tables (default: 2048) > >> - memory_map_query_buckets: Hash buckets for query cache (default: 204= 8) > >> - memory_map_query_cache_size: Max cached queries (default: 10000) > >> > >> Patch applies properly and tests pass. > >> > >> Open to all feedback - thank you! > >> > >> -- > >> Nadav Shatz > >> Tailor Brands | CTO > >> > > > > > > -- > > Nadav Shatz > > Tailor Brands | CTO > --00000000000022696c06496c2201 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you Tatsuo!

Nadav Shatz
Tailor Brands=C2=A0| CTO


O= n Wed, Jan 28, 2026 at 7:08=E2=80=AFAM Tatsuo Ishii <ishii@postgresql.org> wrote:
Hi Nadav,

Sorry for the late reply. I just your email now. Will check and reply
back soon.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

> Hi all,
>
> Any comments or concerns? can we merge it if not?
>
> On Tue, Jan 6, 2026 at 1:25=E2=80=AFPM Nadav Shatz <nadav@tailorbrands.com>= wrote:
>
>> Hello,
>>
>> As initially proposed under "Proposal: recent access based ro= uting for
>> primary-replica setups" and then broken into separate tasks -= i am adding
>> here a patch to implement tracking of latest mutated table, and th= en using
>> the replication lag as a base - deciding where to point queries wh= en query
>> load balancing and parsing is enabled.
>>
>> More details as in the patch:
>> Feature: add in-memory table tracking to prevent stale reads from = replicas
>>
>> Implement "memory map" feature that tracks recently-writ= ten database
>> tables in shared memory to prevent stale reads during replication = lag.
>> When a write (INSERT/UPDATE/DELETE) occurs on a table, that table = is
>> marked as "dirty" for a configurable TTL period. Any SEL= ECT on a dirty
>> table within the TTL window is routed to primary instead of replic= a.
>>
>> Key features:
>> - Shared memory hash table for tracking table mutations with TTL >> - Query parse cache with LRU eviction for performance
>> - Cold start protection (routes all queries to primary initially)<= br> >> - Automatic TTL calculation: replication_delay =C3=97 configurable= factor
>> - Per-table staleness tracking with microsecond precision
>>
>> New configuration parameters:
>> - memory_map_enabled: Enable/disable the feature (default: off) >> - memory_map_ttl_factor: TTL multiplier for replication delay (def= ault:
>> 5.0)
>> - memory_map_cold_start_duration: Cold start period in ms (default= : 2000)
>> - memory_map_table_buckets: Hash buckets for table map (default: 1= 024)
>> - memory_map_table_size: Max tracked tables (default: 2048)
>> - memory_map_query_buckets: Hash buckets for query cache (default:= 2048)
>> - memory_map_query_cache_size: Max cached queries (default: 10000)=
>>
>> Patch applies properly and tests pass.
>>
>> Open to all feedback - thank you!
>>
>> --
>> Nadav Shatz
>> Tailor Brands | CTO
>>
>
>
> --
> Nadav Shatz
> Tailor Brands | CTO
--00000000000022696c06496c2201--