Designing a Schema for pgEdge Active-Active Replication

When designing a schema for use with pgEdge Platform, you should ensure that you:

Include a Primary Key in Each Table

All tables must include a primary key; this allows pgEdge to replicate INSERT, UPDATE, and DELETE statements. If your table does not include a primary key, only INSERT statements will be replicated to the table.

Use Snowflake Sequences for Robust Sequence Support

If you use a PostgreSQL sequence (opens in a new tab) as part of your primary key, you should convert your sequences to Snowflake sequences.

Snowflake sequences are composed of multiple data types that ensure a unique transaction sequence when processing your data in multiple regions. This helps pgEdge accurately preserve the order in which globally distributed transactions are performed, and alleviates concerns that network lag could disrupt sequences in distributed transactions.

Start with Identical Schema Objects on Each Node

Before enabling pgEdge's Automatic DDL replication (opens in a new tab), you must ensure that all schema objects exist on each node of the cluster before starting replication. If you start replication between schemas that don't match, pgEdge will return an error (object not found).

import { Callout } from 'nextra/components'

If an object is not involved in replication or actively used by another replicated process, it can reside in the schema, but will be ignored.

Include the ENABLE ALWAYS clause in Triggers

PostgreSQL triggers will fire only from the node on which they were invoked. If you have triggers that are related to the replication process, you should include the ENABLE ALWAYS clause when attaching a trigger to a table:

CREATE TRIGGER ins_trig AFTER INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE ins_history();
ALTER TABLE trans_history ENABLE ALWAYS TRIGGER ins_trig;

After creating or adjusting your schema, you're ready to install pgEdge Platform.

Use Conflict-Free Delta-Apply Columns

Replicated systems used heavily for transaction processing have to ensure that in the event of a transaction conflict, transactions are applied in order to maintain an accurate running balance. Making your NUMERIC columns conflict-free delta-apply columns ensures that transactions are applied to an account in the order that they're received.

On Spock extension 4.0 (or later), connect with psql and use the following command to update a column:

ALTER TABLE table_name ALTER COLUMN column_name SET (log_old_value=true, delta_apply_function=spock.delta_apply);

If you're using an older version of the Spock extension (prior to 4.0), use the form:

ALTER TABLE table_name ALTER COLUMN column_name SET (log_old_value=true);