A star model

A join in SynSQL runs partition by partition, on the data broker that holds the partition. It finds the pairs of rows that sit on the same partition. The star model is how you design your tables so that all the rows you want to join are there.

The idea

Pick the identifier around which your data is organised (the visitor, the patient, the source). Give every table this identifier as its hash key, and deploy the tables with the same number of partitions. All the rows about one visitor are then in the same partition number, in every table.

create table visitors ( id bigint not null, name varchar(40) );
create table visit_history ( visitor_id bigint not null, visit_date date, place varchar(40) );

alter table visitors set datacenter 'DC1';
alter table visitors set hash key id;
deploy table visitors on 8 partitions;

alter table visit_history set datacenter 'DC1';
alter table visit_history set hash key visitor_id;
deploy table visit_history close to visitors;

close to creates the second table on the same data brokers as the first one, with the same number of partitions.

select v.name, h.visit_date
from visitors v
inner join visit_history h on v.id = h.visitor_id
where h.visit_date > '2026-01-01';

The rule

A join is correct when the columns of the on clause are the hash keys of the two tables. SynSQL does not check this: if you join on other columns, you silently lose the pairs that sit on different partitions.

The same applies to a sub-query in a where clause of delete or update: it runs on each data broker with its own data (see delete).

Gaia

gaia_source and astrophysical_parameters are both hashed on source_id and both have 600 partitions. This is why a join between 1.8 and 1.5 billion rows returns in less than a second: each partition joins its own small share.

Wide tables

A wide table cannot be joined at all. To combine a wide table with a narrow one, run two selects and combine the results in your program.