Is it a typical thing that Airbyte misreading the backing PK index and treating the included column as part of the primary key?

Airbyte does not intentionally treat non‑PK index columns as part of the primary key, but there are a few documented bugs and edge cases around how primary keys are discovered or defined. From the knowledge you provided: • JDBC metadata / discovery bugs: ◦ For MySQL on Azure, schema discovery can fail with

Duplicate key item_id

because the JDBC metadata introspection merges column and PK metadata incorrectly, effectively “double counting” the same column when building the JSON schema, even though the DB schema itself is clean. [MySQL duplicate key] ◦ For Postgres, there was an issue where the connector fetched primary keys without respecting the configured schema filter, leading to very heavy metadata queries across all schemas. This is about scope rather than misreading PKs, but it shows that PK discovery logic has had problems. [Postgres PK discovery] • Source‑defined PKs being wrong or incomplete: ◦ Apple Search Ads reports had incorrect source‑defined PKs (

date

,

campaignId

) that didn’t match the real aggregation grain (also

countryOrRegion

), causing deduplication to drop valid rows. The “fix” in that PR was to remove PKs entirely, which also shows that PK handling can be imperfect. [Apple Search Ads PKs] ◦ Google Ads had a nullable field

bidding_strategy.id

included in the PK for some streams; this caused failures in destinations that require non‑null PKs, and the fix was to remove that field from the PK. [Google Ads nullable PK] • Destination behavior around PK nullability / types: ◦ ClickHouse destination tried to alter a primary‑key column to

Nullable(...)

on a second incremental sync when deduplication was enabled, which ClickHouse rejects. This is more about type/nullability handling than misreading which columns are in the PK, but it shows PK handling can be brittle. [ClickHouse PK nullable] ◦ S3 Data Lake / Iceberg explicitly rewrites number‑type PKs as strings for identifier fields, which can surprise users but is documented behavior, not a misread. [S3 deduplication] So: • There is no statement in the docs that Airbyte normally “reads backing indexes” and automatically treats all indexed columns as PK columns. • However, there are known bugs and misconfigurations where: ◦ PK discovery via JDBC metadata is incorrect or overly broad. [MySQL duplicate key] ◦ Source‑defined PKs in connectors don’t match the true uniqueness grain, leading to bad deduplication. [Apple Search Ads PKs] If you’re seeing Airbyte treat an index‑only column as part of the PK, that would be consistent with these kinds of bugs, but the knowledge sources don’t explicitly document a general pattern of “misreading backing PK index and including extra columns” beyond the specific issues above. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai yeah my case is where we have a postgres cdc connection and one of the tables we have configured, Airbyte sees the backing indexing column as another primary and and we have two primary keys configured on that table sync, which breaks the uniqueness in the resulting table in the destination

The knowledge sources don’t describe this exact Postgres CDC behavior (misreading a backing index column as part of the primary key), so I can’t confirm it as a known, documented pattern. I can only relate it to nearby behavior and limitations. Relevant points from the docs and issues: • CDC generally requires PKs: Airbyte’s CDC docs state that incremental CDC is only supported for tables with primary keys for most sources. [CDC limitations] For Postgres CDC specifically, incremental sync is only supported for tables with primary keys; otherwise you must use Full Refresh. [Postgres CDC limitations] • PKs are used for deduplication: Incremental + deduped history relies on the primary key to deduplicate downstream. [Incremental deduped] If Airbyte “thinks” there is a composite PK (real PK + backing index column), that would indeed break uniqueness in the destination, as you’re seeing. • There are known PK‑handling bugs, but not this exact one: ◦ For Postgres source, there was a bug in how primary keys were fetched (heavy query across all schemas), but it’s about scope, not mis‑identifying PK columns. [Postgres PK discovery] ◦ For other connectors, there are examples where PKs are mis‑defined or mishandled (e.g., nullable PK fields, wrong PK grain), which then break deduplication or destination constraints. [CDC limitations] But none of the provided sources show Postgres CDC specifically treating an index‑only column as part of the PK. Given that: • From the available knowledge, this is not documented as a typical or expected behavior for Postgres CDC. • It does fit the general class of PK‑related bugs Airbyte has had in other connectors (wrong PK definition leading to bad dedup), but I don’t have a specific GitHub issue or doc confirming your exact scenario. Because the docs do not show a configuration knob to override the discovered PK for Postgres CDC at the connector level, the usual guidance for CDC schema changes / PK issues is: 1. Refresh source schema on the connection so Airbyte re‑discovers the table metadata. [Postgres CDC schema changes] 2. If the problem persists, it likely needs to be raised as a bug against

source-postgres

with: ◦ The table DDL (including PK and indexes), ◦ The Airbyte‑discovered catalog for that stream (showing the two PK fields), ◦ Connector + platform versions. With the information I have, I can’t offer a documented workaround (like “turn off index‑based PK detection”) for Postgres CDC; the sources simply don’t mention such a setting. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.