Stream data from PostgreSQL databases

This section contains information about:

  • The behavior of how Datastream handles data that's being pulled from a source PostgreSQL database
  • The versions of PostgreSQL database that Datastream supports
  • An overview of how to setup a source PostgreSQL database so that data can be streamed from it to a destination
  • Known limitations for using PostgreSQL database as a source

Behavior

The source PostgreSQL database relies upon its logical decoding feature. Logical decoding exposes all changes committed to the database and allows consuming and processing these changes in a user-friendly format using an output plugin. Datastream uses the pgoutput plugin, which is the standard PostgreSQL logical decoding plugin for PostgreSQL 10 and later.

  • All schemas or specific schemas from a given PostgreSQL source, as well as all tables from the schema or specific tables, can be selected.
  • All historical data is replicated.
  • All data manipulation language (DML) changes, such as inserts, updates, and deletes from the specified databases and tables, are replicated.
  • Only committed changes are replicated.
  • If you define a REPLICA IDENTITY on a table, Datastream treats the specified columns as primary keys.
  • Datastream periodically sends heartbeat messages to the source database when it's connected to a primary instance. As a result, logical decoding message events (op:"m") are inserted directly into the WAL file. These messages are required by Datastream to ensure source availability and to calculate freshness. When you use a read replica as a source, you must configure heartbeat messages externally. See Replication from read replicas for more information. We recommend taking that into consideration if other replication setups read from the same source database.

Versions

Datastream supports PostgreSQL version 10 and later.

Datastream supports the following types of PostgreSQL database:

  • Self-hosted PostgreSQL
  • Cloud SQL for PostgreSQL
  • AlloyDB for PostgreSQL
  • AlloyDB Omni
  • Amazon RDS for PostgreSQL
  • Amazon Aurora PostgreSQL

Free Tier

Datastream lets you stream from AlloyDB for PostgreSQL to BigQuery using the Free Tier, providing up to 100 GiB of change data capture data free every month. For more information, see Datastream pricing.

Best practices

This section describes recommended best practices for configuring your PostgreSQL source for use with Datastream.

Use multiple streams to prevent head-of-line blocking

For PostgreSQL sources, Datastream uses a single logical replication slot for an entire stream. A large transaction or multiple updates on one high-volume table can delay data replication for all other tables in the same stream.

To prevent head-of-line blocking, create separate streams for different sets of tables. For example, you can create one stream for high-volume tables, and another stream for low-volume tables. This isolates high-churn tables and prevents them from delaying replication for other tables.

Recommendation: identify tables with exceptionally high write (INSERT/UPDATE/DELETE) rates and place them in their own dedicated Datastream stream with a separate replication slot.

Avoid long-running transactions

Long-running transactions can lead to WAL log buildup. Because WAL is sequential, PostgreSQL can't remove old WAL files needed by the replication slot until the long transaction completes. This increases the WAL disk usage.

Furthermore, this can slow down logical decoding. The slowdown is caused by large transactions spilling changes to disk, which then requires slow, I/O-intensive reassembly upon commit, blocking the replication of all subsequent transactions.