Support separate read connection for archival history reads

[apoorvam1]

Is your feature request related to a problem? Please describe.

Temporal's archival system reads full workflow histories from the database via ExecutionManager.ReadHistoryBranchByBatch using the same connection pool as the main execution path. In deployments with database read replicas (e.g., Aurora with 1 writer + 2 readers), archival reads compete with execution reads and writes on the writer node — even though archival only reads closed, immutable workflows where replication lag is harmless.

The ExecutionManager injected into the archiver provider (common/resource/fx.go) is the same singleton used by shard, execution, and task stores. All reads and writes go through a single ConnectAddr per datastore. There's no mechanism to configure a separate connection for read-only operations like archival. This means read replicas sit idle from Temporal's perspective, while archival load (which can spike during backlog catch-up with large histories) contends with latency-sensitive execution operations on the writer.

Describe the solution you'd like

Allow configuring an optional read-only datastore connection for archival history reads:

1. Add an optional datastore reference (e.g., ArchivalStore or ReadReplicaStore) to the Persistence config that points to a read replica endpoint
2. If configured, construct a separate ExecutionManager backed by the replica connection pool
3. Inject this replica ExecutionManager into the ArchiverProvider instead of the shared one
4. If not configured, fall back to the existing behavior (shared ExecutionManager) — fully backward compatible

This would reduce read load on the primary database with zero correctness risk, since archived workflows are immutable and not latency-sensitive. Archival already runs at preemptable priority and is rate-limited to 15% of persistence RPS (archivalQueuePersistenceMaxRPSRatio = 0.15) — offloading it to a replica would free that budget on the writer for execution operations.

Describe alternatives you've considered

• Database proxy (ProxySQL, RDS Proxy): Could sit between Temporal and the database and route read queries to replicas. However, since archival uses the same connection pool as execution operations, the proxy cannot distinguish archival reads from execution reads — making targeted routing impossible without application-level changes.
• Aurora reader endpoint as the sole ConnectAddr: Not viable — Temporal requires read-your-writes consistency for the execution path. The MySQL driver is configured with RejectReadOnly = true specifically to avoid read-only nodes.
• Reducing archival rate limit further: The 15% RPS cap already limits archival throughput, but this trades off archival SLA (backlog grows) rather than solving the resource contention.

Additional context

• Deployment: Aurora with 1 writer and 2 readers — readers are effectively idle from Temporal's perspective
• Archival backend: S3
• This could potentially be extended to other read-only operations in the future (e.g., GetWorkflowExecutionHistory for UI/CLI queries, visibility reads), but archival is the lowest-risk starting point since it operates on closed workflows only