Re: Proposal: recent access based routing for primary-replica setups

Hello Nadav,

Thank you for the proposal. I have a few questions.

> Hello all,
>
> My name is Nadav Shatz, I’m the CTO at Tailor Brands and have been working
> with PostgreSQL in high-traffic, distributed environments for many years.
> Most of my focus has been on backend architecture, scaling, and performance
> optimization, and I’m a long-time user and admirer of the Postgres
> ecosystem.
>
> I’d like to propose adding a feature to pgpool-II for *recent access based
> routing* in primary-replica setups. The idea is similar to what we’ve
> described in this article
> <https://medium.com/tailor-tech/using-database-read-replicas-in-distributed-systems-d80eaf6bbf8a>,
> and is also reflected in this pgcat PR
> <https://github.com/postgresml/pgcat/pull/864>. The core concept is to
> route read queries to the primary if they occur shortly after a write,
> reducing replica lag inconsistencies while still benefiting from read
> scaling.
>
> *How it would work (high-level):*
>
>
> -
>
>
> *External “effective lag” via config (hot-reloaded): *Instead of relying on
> pgpool-II’s replication delay checks (which don’t map well to Aurora
> semantics), we’ll expose a *config value* representing the effective
> replica lag (or directly the TTL to use for “recency”). This value
> is *pushed
> by an external controller* and *hot-reloaded* (no restarts). The
> relevant knobs might look like:
>
> -
>
> enable_recent_access_routing (boolean, default off)
> -
>
> recent_access_ttl_ms (integer, default 0, can be hot-reloaded)

If my understanding is correct, the "external controller" updates
"recent_access_ttl_ms" to let pgpool know the current delay of
replica. My question is, what if there are multiple replicas. In this
case the "external controller" calculates the average latency of each
replica?

Another question is, how often the external controller updates and
reload pgpool.conf. If it's like every second, probably it could give
unacceptable load to pgpool because reloading pgpool.conf is expensive
operation.

> enable_query_parser (boolean, required for this feature, default off)

What does this do? Why do you need this?

> *In-memory recent-access map: *Each worker maintains a lightweight per-DB
> in-memory map of *recently written relations*. On any write

Is "per-DB in-memory map" in shared memory?

> (INSERT/UPDATE/DELETE/UPSERT/TRUNCATE), we record the touched relations
> with a TTL derived from recent_access_ttl_ms. Entries expire
> automatically; writes refresh them.

How do you automatically expire the entries? Are you going to
implement something like a auto sweeper process?

> *Routing + query parsing: *For incoming statements we parse enough to
> answer two questions: (1) is it a read or a write? and (2) which relations
> are referenced? If a read touches any “recently written” relation, we *force
> route to primary*; otherwise we allow normal read load-balancing to
> replicas.

Pgpool-II already does (1) and (2).

> *Notes on behavior & ops:*
>
>
> -
>
> *Config & hot reload:* Operators (or an external controller) can update
> recent_access_ttl_ms dynamically and trigger hot reload to adapt to
> changing conditions―no reliance on Aurora internals.
> -
>
> *Safety levers:* a global max TTL, optional allow/deny lists, and
> metrics (e.g., “reads forced to primary due to recency”) for visibility.

Please elaborate more on this. Allow/deny what?

> *Defaults & compatibility:* all defaults are safe/off; enabling requires
> explicit opt-in.

Sounds good.

> I’ll prepare the code changes and send a patch/PR, but before diving in I
> wanted to check if anyone has *objections, concerns, or preferred
> alternatives*―particularly around parser hooks, shared memory use, or
> hot-reload mechanics in pgpool-II.

Probably you should consider adding a pcp command to notice pgpool the
"recent_access_ttl_ms". That is far more efficient than reloading
pgpool.conf.

> Thanks for considering,
> --
> Nadav Shatz
> Tailor Brands | CTO

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp