Is there an established pattern for adding and maintaining indexes to columns in postgres when the plugin runs?
Hey @alfredgamulo 
First of all, thanks for reaching out and joining our community.
Now to answer your question, it mostly depends on your exact needs, that’s why we do not enforce predefined patterns.
There are no big limitations around indexes, just try not to add additional constraints or to modify the data / data types in any way as otherwise the CloudQuery CLI will try to migrate the data during the next sync and if it won’t succeed you’ll either have to migrate it manually to the specified format or to run a forced migration (drop & recreate).
Could you also let us know what source plugins, tables and columns you are interested in optimizing by adding indexes, for future reference?
Does this answer your question?
1 Like
for no specific source plugin or table, but any table with a jsonb column – I’m curious if I can run something like CREATE index ON <table> USING gin (<data>) and if there would be any issues with settings such as “overwrite-delete-stale” or any source/destination plugin version upgrades?
No, I would not expect there to be any issues as all. CloudQuery does not change indexes and as such, they won’t be a problem during migrations.
The only ones I can foresee are the forced migrations which will drop the table entirely, but those are quite rare and only occur between major version bumps of a certain plugin; we also try to maintain backwards compatibility to the best of our abilities, but where the target APIs change there’s not much we can do.
They are also documented in a plugin’s release notes on the hub (see the last major for the Github source plugin, for example).
Either way, what I’d recommend is storing all of these DDL scripts so they can be easily replicated trough a single command in the future if needed.