Pg flo
lastmod 2024-11-06created 2024-11-061 edits
Stream, transform, and route PostgreSQL data in real-time.
Stream, transform, and re-route PostgreSQL data in real time
Features
- Provides the easiest way to move and transform data between PostgreSQL databases
- Real-time streaming : streams data changes in near real time using PostgreSQL logical replication
- Bulk copy : performs initial data sync quickly with parallelizable bulk copy
- Powerful transformations : applies regular expression-based transformations, masks sensitive data, and filters by column values
- Flexible routing : routes data seamlessly between tables, to tables with different names or to the same table, with custom column mapping
- Transformation rule examples
- Email address masking: converts the email column into mask characters.
- Phone number formatting: converts phone number formats using regular expressions.
- Flexible table routing: routes the users table to the customers table, mapping ID and username to customer ID and customer name.
- Multi-table routing: routes the orders table to the transactions table, mapping ID and total amount to transaction ID and amount.
- Real-time data replication between PostgreSQL DBs
- Building ETL pipelines with data transformation
- Data re-routing, masking, and filtering
- Running database migrations without downtime
- Event streaming from PostgreSQL
- pg_flo leverages PostgreSQL's logical replication system to capture and stream data changes.
- It uses NATS as a message broker to decouple reading from the WAL from the replicator and worker processes, providing flexibility and scalability.
- Transformations and filtering are applied before data reaches its destination.
Hacker News comments
- pg_flo – Stream, transform, and re-route PostgreSQL data in real-time | Hacker News
- The pg_flo project looks very interesting; archiving to S3 and a cheaper version of the tooling are especially appealing
- Compared with tools like Debezium, it can work with small data (GBs) instead of large data (TBs), making local testing easy
- Using Kafka and Debezium is a short-term effort, and they are evaluating how much hassle it is
- Another solution the team is evaluating is peerdb
- Some tables must be available on a secondary Postgres instance for read operations, which requires replication
- Two kinds of delete operations on the main database are the problem
- The first delete is for data cleanup, and they don't want it reflected in the replicated database
- The second delete is for rollbacks; it changes the data state, so it must be replicated
- Wonders if there is a way to distinguish these two kinds of deletes and replicate only the rollback deletes
- Wonders if the failure modes could be explained
- For example, wants to know what happens when a NATS server (or worker/replicator) node dies
- Wonders how hard it is to move data from Postgres to ElasticSearch/ClickHouse
- Debezium must first copy the entire CDC state to Kafka and requires the Kafka retention time to be set to infinite, which many Kafka service systems do not allow
- This may actually be an advantage
- Wonders if pg_flo can be used with Postgres as a service instances such as Azure Postgres, Supabase, and Neon
- Wonders if it can read the WAL without installing extensions the vendor has not approved
- Wonders if it can handle network disconnects/instability in the middle of a copy
- Looking for a tool that enables CDC event-driven workflows while also allowing the event stream to be replayed arbitrarily as needed
- Wonders if doing bulk copies (e.g., nightly) with various transformations, without continuous streaming, is supported