Read Only Replication

Use Cases

Read-only replication instances are designed for load isolation. A primary instance can connect to multiple read-only replication instances. While these instances share storage, their computing resources are independent. Tacnode supports databases, data warehouses, and search engines. Load isolation is crucial in scenarios such as:

Mixed Load

In the early stages of business growth, when data volume and access are low, a single Tacnode instance can handle both OLTP and OLAP queries. As the business grows, increased data volume, user concurrency, and complex analyses can cause conflicts between query types. Large analytical SQL queries can disrupt online operations that require high-concurrency point queries or data writes. Load isolation for both OLAP and OLTP is necessary.

Business Isolation

Businesses often use a shared database to protect resources and minimize potential issues. Rapid growth can increase query traffic and complexity, raising system load and potentially affecting other online businesses with strict SLAs. By creating separate instances for each business, queries are isolated, preventing interference and enhancing online service stability.

Read-Write Isolation

In Tacnode's data warehouse application, upstream data is often generated and written in batches, involving large data imports that take time. Users may execute Ad-Hoc queries simultaneously. Due to varying SQL skills, users might create inefficient queries that consume resources, causing batch write task failures.

Basic Concepts

Tacnode distinguishes the primary instance from the read-only replication instance by labeling the Database as Primary or Secondary. These terms indicate the Database's status.

Primary Database

Allows both reading and writing operations.

Secondary Database

Permits reading operations.

Primary Instance

Associated with the Primary Database.

Read-only Replication Instance

Bound to at least one secondary database. Can be associated with multiple Secondary Databases but not with any Primary Database.

Create Read-Only Replication

1. Create a new Warehouse, specifying the number of units as needed. Typically, the unit count for the read-only replication instance should be less than or equal to that of the primary instance.

2. In the console, go to the "Database" section on the left and select the database to designate as Secondary.

3. Click the extended operation button on the right side of the database to start the "attach" operation.

4. Connect the role labeled as Secondary in the database to the newly created Warehouse. Once binding is successful, the new instance will function as a read-only replication instance.

5. Wait for the binding process to complete. After it succeeds, you can view the Warehouse instances indicating the database's Primary and Secondary roles.

FAQ

• How can you tell if the current instance is primary or read-only?

Use the command below. If t is returned, the current database is in the Secondary role.

plutus=> select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

• Will the permission settings be automatically synchronized to the read-only instance?

Permissions for databases, schemas, and tables in the read-only instance will mirror those of the primary instance.

• How can you assess the latency of a read-only instance compared to the primary instance?

Run the following command on the read-only replication instance to measure the delay between the primary and replication instances:

select now()-pg_last_xact_replay_timestamp() as replication_lag;
replication_lag
-----------------
00:00:01.0009
(1 row)