Let’s create our tables and populate them with some data, to help us visualize. I want to fetch the most recent videos of each tag. To make things easier, a movie can only have one tag.

Imagine that we have a table tags and a table movies, with a one-to-many relationship. We need to fetch the most recent items of each tag in a list. Quite similar to a for loop that iterates through the rows returned by a SQL query. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)īasically, what it does is that for each row in the main select, it evaluates the sub-select using the main select row as a parameter. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. The LATERAL keyword can precede a sub-SELECT FROM item. If you’re curious about trying Citus or learning how more works we encourage you to join the conversation with us on Slack.Since PostgreSQL 9.3 we have a new LATERAL option for FROM-clause subqueries and function calls.Īccording to the documentation what it does is: We hope you found the insight into how we perform distributed outer joins valuable. If you’re sharding on some shared id, as is common in certain use cases, then Citus will do the join on the appropriate node without any inter-worker communication. In many cases you don’t even have to think about this as Citus simply takes care of you.

With 1-1 matching of shards, the distributed outer join is equivalent to the union of all outer joins of individual matching shards. This is because Citus stores the data in standard postgres tables called shards with the name as. The resulting queries may seem complex at first but you can see that they are actually the same with the original query with just the table names are a bit different. a ))) FULL JOIN table4_102374 table4 ON (( table1. a ))) FULL JOIN table3_102370 table3 ON (( table1. Let’s look at how Citus handles an outer join query: How Citus Processes OUTER JOINs When one-to-one matching between shards exists, then performing an outer join on large tables is equivalent to combining outer join results of corresponding shards. join condition is not equality: SELECT * FROM user OUTER JOIN purchase ON er id < erid ``` join condition is not on the partition columns: SELECT * FROM user OUTER JOIN purchase ON er_id = purchase.amount The following queries are not supported out of the box: ```sql - user and comment tables doesn’t have the same number of shards: SELECT * FROM user OUTER JOIN comment ON er id = erid

SELECT * FROM user OUTER JOIN purchase ON er_id = er_id The following query would work since distributed tables user and purchase have the same number of shards and the join criteria is equality of partition columns: sql each shard of table A should overlap with one and only one shard from table B.įor example lets assume we 3 hash distributed tables X, Y and Z and let X and Y have 4 shards while Z has 8 shards.ĬREATE TABLE user ( user_id int, name text ) SELECT master_create_distributed_table ( 'user', 'user_id', 'hash' ) SELECT master_create_worker_shards ( 'user', 4, 1 ) CREATE TABLE purchase ( user_id int, amount int ) SELECT master_create_distributed_table ( 'purchase', 'user_id', 'hash' ) SELECT master_create_worker_shards ( 'purchase', 4, 1 ) CREATE TABLE comment ( user_id int, comment text, rating int ) SELECT master_create_distributed_table ( 'comment', 'user_id', 'hash' ) SELECT master_create_worker_shards ( 'comment', 8, 1 ) Copy The query should join the distributed tables on the equality of partition columns (table1.a = table2.a) - Shards of the distributed table should match one to one, i.e. Join criteria should be on partition columns of the distributed tables. it is not possible to outer join a sharded table with a regular PostgreSQL table. Currently, Citus support distributed outer joins under some criteria: - Outer joins should be between distributed(sharded) tables only, i.e. The Citus extension allows PostgreSQL to distribute big tables into smaller fragments called “shards” and performing outer joins on these distributed tables becomes a bit more challenging, since the union of outer joins between individual shards does not always give the correct result.

Postgres join example