Connecting to your standby server

Tembo offers an alternative subdomain that can be used to connect to your standby server.

In Postgres, there is a primary server which can serve reads and writes. When running with high availability (HA), there is also a standby server, which can serve eventually-consistent reads. To learn more about Tembo’s configuration of HA, and how to enable HA, please review the High Availability documentation.

Limitations of Hot Standby

Hot standby is the term used to describe the ability to connect to the server and run read-only queries while the server is in archive recovery or standby mode.

• Postgres documentation

It is important to understand the limitations of reading from your standby server. Postgres standby instances must regularly apply changes from the primary server as received through the Write-Ahead Log (WAL). These are binary-level updates and cannot be applied while a modified record is in use. As a result, a standby server being used as a read replica may cancel local queries which are using these tuples. This allows the standby system to serve its principal role as a failover target.

… the WAL-logged action already occurred on the primary so the standby must not fail to apply it. Furthermore, allowing WAL application to wait indefinitely may be very undesirable, because the standby’s state will become increasingly far behind the primary’s. Therefore, a mechanism is provided to forcibly cancel standby queries that conflict with to-be-applied WAL records.

• Postgres documentation

The easiest way to prohibit this behavior is to enable the hot_standby_feedback parameter. This causes the standby server to transmit row usage and lock information to the primary system. The primary node will then refrain from maintenance which would normally remove affected row versions until they’re no longer in use on either server.

Because this can cause a certain amount of overhead on the primary system, we also recommend setting the following parameters on the standby system:

• idle_in_transaction_session_timeout - If a user or application leaves a transaction uncommitted for long periods, this can prevent necessary maintenance on the primary system for long periods of time when hot_standby_feedback is enabled. This parameter will terminate any session which is in a transaction state for longer than the specified interval. We recommend a value no longer than one hour, or 1h.
• statement_timeout - Unexpectedly slow queries can also hold rows for extended periods of time and prevent cleanup when hot_standby_feedback is enabled. This parameter will cancel queries that exceed the stated runtime interval. We recommend setting this to a value slightly longer than the slowest legitimate application or user-level query, such as 5min.

For more information on changing configuration parameters, please refer to Tembo’s Postgres Configuration documentation.

Connecting to your standby

First, enable High Availability to deploy a standby server.

Then, connect to your instance by appending -ro to the subdomain of your Postgres host.

For example, if your host is org-tembo-test-inst-tembo-rag.data-1.use1.tembo.io, you can connect with the host org-tembo-test-inst-tembo-rag-ro.data-1.use1.tembo.io. You should connect with the readonly user.