Support for Index Hints in Staging-Optimized Replace Strategy

[gxslash]

Feature description

I would like to have the ability to provide index hints during the staging-optimized replace strategy when using the PostgreSQL destination in dlt. This feature would allow the creation of indexes on staging tables, which can then be preserved and moved to the actual schema after the replace operation. This enhancement would streamline workflows and ensure that indexes are maintained as part of the load process without manual intervention.

Are you a dlt user?

Yes, I run dlt in production.

Use case

This feature addresses the problem of losing indexes when using the staging-optimized replace strategy. Currently, any indexes created on tables are dropped and must be manually recreated after the replace operation. This adds extra steps and affects performance, as indexes cannot be utilized during the staging phase.

For example, in my use case, I need to create various types of indexes (e.g., multi-column, hash, and btree). These indexes are essential for optimizing query performance but must currently be managed separately, which is both inefficient and error-prone.

Proposed solution

Introduce an "index hint" feature where users can specify the columns and types of indexes they want in the table schema. During the staging-optimized replace process, dlt would create these indexes on the staging tables and seamlessly transfer them to the actual schema once the replace operation is complete. This solution would ensure that indexes are preserved automatically without manual intervention.

Related issues

No specific issue currently logged.