A Common Table Expression is a temporary expression. The clause begins with
WITH RECURSIVE, has two parts separated by
UNION [ALL] or
Recursive queries make the SQL language complete, some things could otherwise not be executed. Recursive means that a part of the query is a query itself, with SQL this can be done with a common table expression (CTE):
WITH ctename AS ( SELECT ... ) SELECT ... FROM ctename ...
A recursive query include the term
WITH RECURSIVE ctename AS ( SELECT ... ) SELECT ... FROM ctename ...
Now Postgres will create a working table (instead of a materialized table)
When there is a problem with handling a large numbers of connections, there is an article by Andres Freund about Connection Scalability of Postgres. With more than 100 connections, there is a problem related to OS task scheduling. Postgres implements transaciotn isolation using snapshot based MuliVersion Concurrency Controll MVCC.