Resource Usage & Limitations

Running a real-time game engine inside a relational database engine designed for ACID compliance comes with fascinating performance characteristics and edge cases.

CPU Utilization

Despite the mathematical complexity of the array slicing, LATERAL joins, and terminal string formatting happening 60 times a second, PostgreSQL handles the workload with remarkable efficiency.

When running on a standard mid-range processor (e.g., AMD Ryzen 5 3600) with PostgreSQL 16, the query consumes approximately 4% of a single CPU thread.

The PostgreSQL execution engine is highly optimized for filtering and aggregating data. Because our "tables" (the CTEs) only contain a single row per frame, the actual data payload is microscopic, allowing the CPU to easily calculate the frame delta within the ~16.6ms window required for 60 FPS.

The Memory & Disk Leak Problem

The fundamental limitation of using WITH RECURSIVE as an infinite loop is how SQL databases handle memory management for CTEs.

PostgreSQL does not conceptually understand that this query is an infinite game loop. To the database, this is a standard data traversal query (like walking a billion-node graph). Therefore, it assumes that every row generated by the recursive CTE might theoretically be needed for the final output.

Postgres does not aggressively discard previous rows (frames). Over time, the CTE accumulates the entire history of the game state in memory.

Disk Spilling (work_mem)

PostgreSQL allocates a specific amount of RAM per operation, defined by the work_mem configuration parameter (which defaults to a conservative 4MB).

As you play Tetris-SQL, the accumulated frame history quickly exceeds 4MB. When this happens, Postgres gracefully falls back to disk, writing the intermediate CTE rows to temporary files.

If you run docker stats and monitor the disk usage over a 30-minute play session, you will see a slow, linear growth in both memory and temporary disk space consumption.

The "OOM Kill Screen"

In a theoretical infinite game, the query would eventually consume all available disk space or hit an Out-Of-Memory (OOM) error, crashing the database connection.

However, because the game implements dynamic difficulty (gravity increases as you clear lines), the vast majority of human players will top out and trigger a Game Over long before resource exhaustion becomes a problem.

Much like the original NES Tetris "Kill Screen" (where the game famously crashes at level 155 due to an integer overflow in the color palette rendering), reaching an OOM crash in Tetris-SQL should be considered a badge of honor!

Theoretical Fixes

In theory, since the final SELECT statement of the query only projects the maximum score (SELECT max(score)), the database engine could optimize this by maintaining only the maximum score and the current row, discarding the rest of the CTE history.

Currently, PostgreSQL's optimizer is not quite smart enough to deduce this optimization for recursive CTEs. Future versions of database engines might implement this, making infinite SQL loops completely viable for production workloads (such as continuous stream processing).