Troubleshooting Postgres Destinations
danger
Postgres, while an excellent relational database, is not a data warehouse. Please only consider using postgres as a destination for small data volumes (e.g. less than 10GB) or for testing purposes. For larger data volumes, we recommend using a data warehouse like BigQuery, Snowflake, or Redshift.
- Postgres is likely to perform poorly with large data volumes. Even postgres-compatible
destinations (e.g. AWS Aurora) are not immune to slowdowns when dealing with large writes or
updates over ~100GB. Especially when using typing and deduplication with
destination-postgres, be sure to monitor your database's memory and CPU usage during your syncs. It is possible for your destination to 'lock up', and incur high usage costs with large sync volumes. - When attempting to scale a postgres database to handle larger data volumes, scaling IOPS (disk throughput) is as important as increasing memory and compute capacity.
- Postgres column name length limitations
are likely to cause collisions when used as a destination receiving data from highly-nested and
flattened sources, e.g.
{63 byte name}_aand{63 byte name}_bwill both be truncated to{63 byte name}which causes postgres to throw an error that a duplicate column name was specified. This limit is applicable to table names too.
warning
Not all implementations or deployments of a database will be the same. This section lists specific limitations and known issues with the connector based on how or where it is deployed.
The Airbyte Postgres destination relies on sending files to the database's temporary storage to then load in bulk. If your Postgres database does not have access to the /tmp file system, data loading will not succeed.