@kapa.ai How should I define indexes for a table I'm syncing with airbyte?

The knowledge sources only give limited, indirect guidance on indexing strategy; they don’t provide a prescriptive “how to index your own tables” guide. With that limitation in mind, here’s what can be inferred. 1. If you use Incremental Sync (especially from databases like Snowflake) Airbyte’s incremental sync relies on a cursor field and, for deduped history mode, a primary key: - Cursor field is used in queries like

WHERE cursor_field > last_cursor_value

and

ORDER BY cursor_field ASC

to fetch new/updated rows. [[Snowflake incremental](https://docs.airbyte.com/integrations/sources/snowflake#incremental-sync)] - For good performance on large tables, the cursor field should be indexed. [[Snowflake incremental](https://docs.airbyte.com/integrations/sources/snowflake#incremental-sync)] - For Incremental | Deduped History, a primary key is required so Airbyte can deduplicate and keep only the latest version of each record. [[Incremental tutorial](https://airbyte.com/tutorials/incremental-data-synchronization#step-2)] Practical implication for your source table: - Ensure the cursor column (e.g.,

updated_at

,

created_at

, or an increasing ID) has an index. - Ensure the primary key used for deduplication is defined and indexed (usually automatic when you define a PK). 2. Destination-side internal indexes (Postgres example) For some destinations (e.g., Postgres), Airbyte itself creates internal indexes on its metadata columns in raw tables, such as

_airbyte_raw_id

,

_airbyte_extracted_at

, and

_airbyte_loaded_at

. [[Postgres index issue](https://github.com/airbytehq/airbyte/issues/61349)] This is handled by Airbyte; you generally don’t need to add your own indexes on these internal columns, and adding duplicates can waste disk space. [[Postgres index issue](https://github.com/airbytehq/airbyte/issues/61349)] 3. General guidance from the docs (what’s explicitly stated) - Cursor fields for incremental sync should be indexed for performance. [[Snowflake incremental](https://docs.airbyte.com/integrations/sources/snowflake#incremental-sync)] - A primary key is required for Incremental | Deduped History to correctly deduplicate records. [[Incremental tutorial](https://airbyte.com/tutorials/incremental-data-synchronization#step-2)] Beyond these points, the knowledge sources do not provide a detailed, destination-agnostic indexing strategy (e.g., which additional business columns to index, composite indexes, etc.), so any further recommendations would be outside the provided documentation.