September 2023 Summaries

Sequin, an integration service, explored the challenges and solutions of writing data back to APIs from a Postgres database, initially attempting asynchronous writes that presented issues such as indeterminate API validation and potential data drift. The company sought synchronous writes, ensuring immediate error handling and API validation but faced limitations with existing methods like synchronous replication and foreign data wrappers. Ultimately, they developed a Postgres proxy, allowing seamless synchronous writes with support for SQL operations and returning clauses, though it introduced challenges such as increased latency and inability to support Postgres transactions. Despite these hurdles, Sequin sees potential in refining this approach in the future, prioritizing HTTP for current API interactions.

Change Data Capture (CDC) in Postgres helps address the need for real-time data streaming and workflow triggers by capturing and delivering changes made to data in a database to downstream systems. Various methods for implementing CDC in Postgres include Listen/Notify, which uses Postgres' interprocess communication feature but has limitations such as transient notifications and payload size restrictions; polling the table, which is robust but cannot detect deletions or provide data diffs; and audit tables, which log changes but can lead to write amplification and scaling issues. Foreign Data Wrappers (FDWs) offer a way to read and write to external data sources but are complex and less suitable for change capture. Logical replication offers a robust solution by using Postgres' write-ahead-log (WAL) but is complex and requires specific management strategies. Sequin, a third-party tool, utilizes logical replication to stream Postgres changes to various queues, streams, and endpoints, simplifying the process by abstracting complexities and ensuring exactly-once processing guarantees. Each method has its own advantages and downsides, making the choice dependent on specific use cases and requirements.