databasepostgresqlrelationshipjsonbcitus

Citus co-location with jsonb relation

I'm trying to co-locate tables in a cluster using citus, but the relationship is mentioned in the tables in a json scheme, as in the example below:

TABLE person(
    id uuid PRIMARY KEY,
    ...
);

TABLE event(
    id uuid PRIMARY KEY,
    resource jsonb NOT NULL,
    ...
);

and the relation is mentioned in the resource column, as well as in this example:

resource{
    ...,
    subjetc{
        resource_type: person,
        reference: uuid
    }
}

Is it possible to create the distributed tables so that they are co-located through this relationship?

Solution

  • No this is currently not possible. You would have to add a column like subject_id that has the same content as resource->>subject->>reference to the event table to make this work. This does duplicate the data a bit, but it's the only way that is currently supported.