New capabilities advance state of the art in Postgres logical replication
Alexandria, VA - April 17, 2024 - pgEdge, Inc., the leading company dedicated to distributed Postgres, today unveiled two major advances in logical replication for the widely adopted open-source PostgreSQL database. These are automatic DDL (Data Definition Language) replication, and snowflake sequences, an innovative solution to the problem of maintaining unique sequence identifiers across a distributed Postgres cluster. pgEdge Distributed PostgreSQL is the first and only fully distributed PostgreSQL database solution that is open (source available) and completely based on the PostgreSQL database itself.
Automatic Replication of DDL Commands. DDL is used to create and modify Postgres objects like tables, indexes, views and constraints through command statements like CREATE, ALTER, and DROP. DML (data manipulation language) is used to perform operations on the data within those database objects through command statements such as SELECT, INSERT, UPDATE, and DELETE.
Traditionally, logical replication in Postgres only allowed for the replication of DML statements and not DDL. Manual efforts were required on each node to modify table definitions through DDL commands that had to be executed on every node.
With the introduction of automatic DDL replication on pgEdge, developers can now update the database schema on a single node and have it automatically propagated to other nodes in the cluster. This feature makes it far easier to support distributed Postgres applications where the database schema is frequently updated because of active development or maintenance.
Snowflake Sequence in Postgres Extension: Managing sequences in a distributed PostgreSQL environment can be complex, especially in multi-master replication scenarios. A Postgres sequence provides a unique integer number to use as an ID for the database table and its records. If you insert new records it will assign the next number in the sequence without issue.
In a distributed application, however, the sequence must be updated across different regions and if each node updates the sequence independently you will have conflicts that cannot be resolved. For example, if a sequence is being used as a primary key, the next value of the sequence on a node in the US could be selected at the exact same time the next value of the sequence is selected on a node in Australia, resulting in a primary key conflict.
To address this challenge, pgEdge has integrated snowflake sequences into a Postgres extension. A snowflake sequence allows for the utilization of a guaranteed unique sequence within a cluster without the need for application code or schema modifications. By automatically installing the Snowflake extension in pgEdge Platform and pgEdge Cloud databases, users can easily maintain unique sequence numbers across different regions, eliminating conflicts and enhancing scalability.
An exclusive presentation and paper on the Snowflake extension will be showcased at the Silicon Valley Postgres conference in San Jose this week.
“By offering these cutting-edge capabilities built on open, standard-based Postgres, we demonstrate our ongoing commitment to making advanced distributed Postgres capabilities available to the Postgres community. Our goal is to make it easier and faster to deliver distributed Postgres applications that are always on, always available and always responsive,” remarked Phillip Merrick, Co-founder and CEO of pgEdge.
Availability Automatic DDL Replication and Snowflake Sequences are available now within both pgEdge Platform (self hosted) or pgEdge Cloud (fully managed cloud service).
For more information, to signup for pgEdge Cloud, or to download pgEdge Platform, visit www.pgedge.com. To learn how to configure pgEdge’s Auto DDL, view the documentation at https://docs.pgedge.com/platform/advanced/autoddl. The pgEdge Snowflake extension is available on the project's GitHub repository at https://github.com/pgEdge/snowflake-sequences.
About pgEdge pgEdge, the leading company dedicated to distributed Postgres, has made its mission to make it easy for developers to build and deploy highly distributed database applications across the global network. Founded by industry veterans who have championed enterprise usage of the PostgreSQL database for several decades and helped run the world’s largest managed database cloud services, pgEdge is headquartered in Northern Virginia. The founders have previously founded and/or led successful companies such as webMethods (NASDAQ: WEBM), EnterpriseDB (acquired by Bain Capital), SparkPost (acquired by MessageBird), OpenSCG (acquired by AWS) and Fugue (acquired by Snyk). Investors in pgEdge include Sands Capital Ventures, Grotech Ventures and Sand Hill East.