feat: PostgreSQL destination delete stale requires sequential scan

[kd7lxl]

Which problem is this feature request solving?

The postgresql destination plugin deletes stale records like this:

cloudquery/plugins/destination/postgresql/client/delete.go

Lines 18 to 25 in f649bed

	sb.WriteString("delete from ")
	sb.WriteString(pgx.Identifier{msg.TableName}.Sanitize())
	sb.WriteString(" where ")
	sb.WriteString(schema.CqSourceNameColumn.Name)
	sb.WriteString(" = $1 and ")
	sb.WriteString(schema.CqSyncTimeColumn.Name)
	sb.WriteString(" < $2")
	batch.Queue(sb.String(), msg.SourceName, msg.SyncTime)

The filter on _cq_source_name and _cq_sync_time requires a sequential scan of the table because these fields aren't indexed:

explain delete from "gcp_compute_instances" where _cq_source_name = 'foo' and _cq_sync_time < now();
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Delete on gcp_compute_instances  (cost=0.00..20059.78 rows=0 width=0)
   ->  Seq Scan on gcp_compute_instances  (cost=0.00..20059.78 rows=2 width=6)
         Filter: ((_cq_source_name = 'foo'::text) AND (_cq_sync_time < now()))
(3 rows)

On tables with several sources, this could be slow. It seems like it could benefit from an index.

Describe the solution you'd like

Create indexes on _cq_source_name and _cq_sync_time.

Pull request (optional)

• I can submit a pull request

[kd7lxl]

Just waiting for permission to contribute per

cloudquery/CONTRIBUTING.md

Line 20 in 85f0d9b

The CloudQuery framework, SDK and CLI are open source while plugins available under `plugins` are **open core**, hence not all contributions to plugins directory will be accepted if they are part of the commercial plugin offering - please [file an issue](https://github.com/cloudquery/cloudquery/issues/new/choose) before opening a PR.

[erezrokah]

Hi @kd7lxl, thanks for suggesting a contribution. Maybe you can start with a draft PR to show how the implementation would look like? We'll need to also discuss this internally if we want the SDK schema to tell destinations if they should create indexes for columns, or it should be coded in the destination per column

[kd7lxl]

The scope of this request is to create an index to directly support the query used in

cloudquery/plugins/destination/postgresql/client/delete.go

Lines 18 to 25 in f649bed

	sb.WriteString("delete from ")
	sb.WriteString(pgx.Identifier{msg.TableName}.Sanitize())
	sb.WriteString(" where ")
	sb.WriteString(schema.CqSourceNameColumn.Name)
	sb.WriteString(" = $1 and ")
	sb.WriteString(schema.CqSyncTimeColumn.Name)
	sb.WriteString(" < $2")
	batch.Queue(sb.String(), msg.SourceName, msg.SyncTime)

This is not a generic request from the SDK to create indexes, since the SDK or source plugin is not responsible for the design of this delete query. The query is defined in plugins/destination/postgresql/client/delete.go, so the matching index will be created in plugins/destination/postgresql/client/migrate.go.

Supporting indexes for other columns via the SDK would be an interesting discussion, but beyond the scope here.