performance

recursive CTE

A Common Table Expression is a temporary expression. The clause begins with WITH RECURSIVE, has two parts separated by UNION [ALL] or UNION DISTINCT.

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 RECURSIVE:

WITH RECURSIVE ctename AS (
   SELECT ...
)
SELECT ...
FROM ctename ...

Now Postgres will create a working table (instead of a materialized table)

• Introduction to recursive CTE’s
• article about recursive queries in Postgres

Connection Scalability

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.