Timescale hyper table on CloudQuery tables

CloudQuery Plugins
1

Hi, has anyone tried creating hypertables on CloudQuery-synced tables?

I have a CloudQuery sync using AWS plugin that ingests AWS inventory data into Postgres, and I’m trying to take advantage of TimescaleDB features like chunking and automatic partitioning.

Right now, the _cq_id column has a unique constraint across the table. However, TimescaleDB expects uniqueness to be enforced per time chunk (in my case, daily partitions). Because of this, I attempted to define the hypertable using a composite key like (_cq_id, _cq_sync_time), but that introduces issues — it effectively adds a constraint on _cq_sync_time, which breaks CloudQuery’s expectation that no additional constraints should be present.

Has anyone successfully worked around this pattern or integrated CloudQuery tables with TimescaleDB hypertables without breaking the sync process?

2

Hi @huss_8225, I think the solution to that would be to use write_mode: append Destination Integrations | CloudQuery

Then CloudQuery won’t add PKs at all, and you can manage those yourself.

If you do go that direction you can validate that CloudQuery doesn’t conflict with your custom PKs, via the cloudquery migrate command (migrate | CloudQuery), then later run cloudquery sync –no-migrate to skip the already done migration phase.

Let me know if that helps,
Erez

3

Hi Perez, thanks for the reply.

here is my destination configuration. I’m already appending the records.

kind: destination
spec:
name: postgresql
registry: local
path: /app/plugins/postgresql
write_mode: append
spec:
connection_string: ${PG_CONNECTION_STR}

here is the unique constraint it creates:

SELECT
constraint_name,
constraint_type,
table_name
FROM information_schema.table_constraints
WHERE table_name = ‘aws_dynamodb_tables’;

constraint_name constraint_type table_name
“aws_dynamodb_tables__cq_id_key” “UNIQUE” “aws_dynamodb_tables”
“274838_275259_3_not_null” “CHECK” “aws_dynamodb_tables”

here is the table schema:

SELECT
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = ‘public’
AND table_name = ‘aws_dynamodb_tables’
ORDER BY ordinal_position;

column_name data_type is_nullable column_default
“_cq_sync_time” “timestamp without time zone” “YES”
“_cq_source_name” “text” “YES”
“_cq_id” “uuid” “NO”
“_cq_parent_id” “uuid” “YES”
“account_id” “text” “YES”
“region” “text” “YES”
“tags” “jsonb” “YES”
“arn” “text” “YES”
“archival_summary” “jsonb” “YES”
“attribute_definitions” “jsonb” “YES”
“billing_mode_summary” “jsonb” “YES”
“creation_date_time” “timestamp without time zone” “YES”
“deletion_protection_enabled” “boolean” “YES”
“global_secondary_indexes” “jsonb” “YES”
“global_table_version” “text” “YES”
“item_count” “bigint” “YES”
“key_schema” “jsonb” “YES”
“latest_stream_arn” “text” “YES”
“latest_stream_label” “text” “YES”
“local_secondary_indexes” “jsonb” “YES”
“provisioned_throughput” “jsonb” “YES”
“replicas” “jsonb” “YES”
“restore_summary” “jsonb” “YES”
“sse_description” “jsonb” “YES”
“stream_specification” “jsonb” “YES”
“table_arn” “text” “YES”
“table_class_summary” “jsonb” “YES”
“table_id” “text” “YES”
“table_name” “text” “YES”
“table_size_bytes” “bigint” “YES”
“table_status” “text” “YES”

The problem I’m facing is the _cq_id column has a unique constraint and to create a hyper table the time column (i.e. _cq_sync_time ) needs to be associated with _cq_id as a composite key (_cq_id,_cq_sync_time) and this breaks the sync because CQ doesn’t expect any constraint other than _cq_i. Basically hyper table expects uniqueness across the time chunks rather than the entire table.

4

Hi @huss_8225 , can you confirm you’re using the latest CloudQuery CLI version?

We fixed removal of unique constraints in append mode via feat!: Remove unique constraints in append mode by bbernays · Pull Request #17129 · cloudquery/cloudquery · GitHub, it was released in v6.

Cheers,
Erez

5

Hi @erez , I was using cloudquery_linux_amd64_v4.3.2. I’ll try v6. Thank you so much!

1 Like