Understanding and Applying Common Table Expressions (CTE) in SQL

1. What Is a CTE?

CTE (Common Table Expressions) is a temporary, query‑scoped table created with the WITH keyword and supported by most mainstream databases. It can be written to (INSERT, UPDATE, DELETE), allowing complex logic to be broken into readable, ordered steps.

2. Beginner Techniques – Enhancing Readability and Performance

2.1 Abstract Logic to Improve Readability

SQL is powerful but often hard to read. By encapsulating logic in CTEs, developers can create reusable sub‑queries that simplify multi‑user development and shorten statements.

Logical Encapsulation for Parallel Development

Example: a student‑grade detail query rewritten with CTEs shows a much clearer structure.

Summary: CTEs can abstract independent logic units, enable parallel development, improve readability, and reduce statement length.

2.2 Simplify Operations, Avoid Deadlocks, Boost Execution Efficiency

When multiple related tables need to be modified atomically, wrapping all operations in a single transaction can cause lock contention and deadlocks, especially in MVCC databases like PostgreSQL.

2.2.1 Lock Issues

Long transactions become a source of mutual blocking and can lead to avalanche effects under high load.

2.2.2 Error‑Handling Pitfalls

Improper handling of success messages may leave a transaction open, complicating debugging.

2.2.3 Multi‑Table Parallel Modification Solution

Using a CTE to perform three independent INSERTs in a single statement ensures atomicity and often improves performance.

Benchmark results show that the CTE‑based approach can be faster than executing separate statements or wrapping them in a transaction.

Summary: CTEs enable multi‑table parallel modifications while preserving atomicity and often delivering performance gains.

3. Advanced Skill – Full CRUD with CTEs

A settlement scenario demonstrates how a CTE can insert aggregated data into a summary table, move processed rows to a completed table, and delete from the source—all in one statement.

3.1 Basic Data Structures

Tables test_a (pending settlements), test_b (completed settlements), and a summary table are defined.

3.2 SQL Simulation of a Settlement

The CTE‑based script processes the settlement and the resulting data is shown.

Summary: CTEs can slice data for complex business logic and serve as a foundation for batch processing.

4. High‑End Application – Implementing a Ring Buffer with SQL

CTEs can simulate sophisticated modules, such as a FIFO (ring buffer) table where rows are overwritten once a fixed capacity N is reached.

4.1 Design Concept

The buffer maintains a constant number of rows; new inserts replace the oldest rows when the limit is hit, aiming for O(1) operations.

4.2 Feasibility Assessment

Triggers work but are inefficient under high concurrency.

Sequences can control IDs but may create gaps and waste space.

Using WITH to split insertable and updatable rows enables batch processing.

4.3 Implementation

A helper function ring_buffer_insert(content text, delimiter text) inserts or updates rows based on the buffer logic.

Table definition:

create table ly_test(
  id integer CHECK (id<5),
  tag text,
  op_time timestamp default timeofday()::timestamp,
  u_end boolean default false
);
create unique index on ly_test (id);
create index on ly_test (id) where u_end;

Function body (illustrated with diagrams in the original article).

4.4 Performance Tests

4.4.1 Single Insert

Empty table single‑row insert takes milliseconds.

4.4.2 Simple Batch (1)

Inserts exceeding N correctly wrap around.

4.4.3 Simple Batch (2)

Continuous inserts maintain correct positions.

4.4.4 Batch Exceeding Threshold N

Performance remains stable when processing batches larger than the buffer size.

4.4.5 Large‑Capacity Batch Operations

-- Increase threshold to 1,000,004
alter table ly_test drop constraint ly_test_id_check;
alter table ly_test add constraint ly_test_id_check check(id<1000005);

-- Insert 1,000,000 test rows
INSERT INTO ly_test SELECT id, id::text, timeofday()::timestamp, false FROM generate_series(1,1000000) AS t(id);

-- Mark the last row as completed
UPDATE ly_test SET u_end=true WHERE id=1000000;

-- Perform batch insert using the ring buffer function
SELECT ring_buffer_insert('a,b,c,d,e,f,g',',');

4.4.6 Batch of 100 Inserts on a Full Buffer

Time increase is minimal compared to single inserts.

5. Potential Pitfalls – CTE “Dark Recipes”

Many ORMs cannot generate raw SQL with CTEs, breaking abstraction layers.

Writable CTEs add learning overhead and can easily produce dirty data if not handled correctly.

Long‑running CTEs under high concurrency may cause deadlocks and cascade failures.

6. Conclusion

CTEs significantly improve SQL readability and can combine multiple operations into a single, optimizable statement, benefiting offline data‑processing scenarios that require high accuracy. However, they have a steep learning curve and may perform poorly under high‑concurrency workloads.