R2DBC adjacency list get all children

Question

I have a table which has id and parentId columns: i refer to this structure as an adjacency list.
So, now i want to get all children of arbitrary id. Classic solution of this problem use recursion, for example here is Postgres procedure or CTE implementation.

I'm currently using Spring Webflux and Spring Data R2DBC + Postgres R2DBC driver (which doesn't support stored procedures yet).
How can i approach this problem in reactive style? Is it even possible or am i missing something conceptually wrong?

UPD 1:
Let's image we've data like:

+-------------+---------+
|id           |parent_id|
+-------------+---------+
|root         |NULL     |
|id1          |root     |
|dir1         |root     |
|dir1_id1     |dir1     |
|dir1_dir1    |dir1     |
|dir1_dir1_id1|dir1_dir1|
+-------------+---------+

Now i want to have a method inside a ReactiveCrudRepository, which will return all children of provided id.

For example, using sample data: by providing id='dir1', i want to get children with ids: ['dir1_id1', "dir1_dir1", "dir1_dir1_id1"].

Answer 1

using proc or cte has nothing to do with full scan.

in your case scenario, you only have to use recursive cte , but adding an index on id, parentid will surely help

create index idx_name on tablename (parentid , id);

also 10k rows its not that big , index will definitely improve cte alot.

Answer 2

I think the best sql approach is recursive CTE (Common Table Expressions), did you try it? I never tried it with many rows.

WITH recursive nodes AS (
   SELECT id, parent_id
   FROM t
   WHERE parent_id = 'dir1'
   
   UNION ALL
   
   SELECT t.id, t.parent_id
   FROM nodes n
   INNER JOIN t ON t.parent_id = n.id
)

SELECT id 
FROM nodes;

Output for parent_id = 'dir1'

id
dir1_id1
dir1_dir1
dir1_dir1_id1