Speeding up PostgreSQL dump/restore snapshots

Summary

The last few pgstream releases have focused on optimizing snapshot performance, specifically for PostgreSQL targets. In this blog post, we’ll walk through the key improvements we made, share the lessons we learnt, and explain how they led to significant performance gains. But first, some context!What is pgstream?pgstream is an open source CDC(Change Data Capture) tool and library that offers Postgres replication support with DDL changes. Some of its key features include:Replication of DDL changes: schema changes are tracked and seamlessly replicated downstream alongside the data, avoiding manual intervention and data loss.Modular deployment configuration: pgstream modular implementation allows it to be configured for simple use cases, removing unnecessary complexity and deployment challenges. However, it can also easily integrate with Kafka for more complex use cases.Out of the box supported targets:Postgres: replication to Postgres databases with support for schema changes and batch processing.Elasticsearch/Opensearch: replication to search stores with special handling of field IDs to minimise re-indexing.Webhooks: subscribe and receive webhook notifications whenever your source data changes.Snapshots: capture a consistent view of your Postgres database at a specific point in time, either as an initial snapshot before starting replication or as a standalone process when replication is not needed.Column transformations: modify column values during replication or snapshots, which is particularly useful for anonymizing sensitive data.For more details on how pgstream works under the hood, check out the full documentation.🔍 The Original ImplementationThe snapshot phase is a critical part of logical replication. It captures a consistent view of the source database to initialize the target and needs to complete reasonably fast without putting too much strain on the source. Otherwise, onboarding existing databases, especially large or busy ones, becomes slow, disruptive, o...