Engineering a Real psql Terminal: PTY, Reverse WebSockets, and Redis Streams

We built a real psql terminal in the browser. Here’s what made it unexpectedly hard.

Alex Zhdankov and his team developed a PTY-backed PostgreSQL console that runs real psql sessions within the browser using xterm.js. Unlike standard SQL editors, this system requires full terminal semantics, including bidirectional streaming and stateful process management.

Why This Matters

In ideal web models, stateless APIs are preferred, but real terminal sessions require persistent PTY processes that do not easily scale horizontally. This architecture addresses the technical reality where network backpressure in WebSockets can freeze synchronous PTY readers, leading to non-deterministic terminal behavior and stuck database sessions. By implementing a buffer layer, the system isolates PTY execution from network volatility, ensuring production stability.

Key Insights

• Reverse WebSockets allow agents behind NAT to initiate outbound connections to a control plane, bypassing the need for inbound port forwarding.
• Redis Streams act as a critical decoupling boundary between the PTY reader and the WebSocket writer, preventing network slowdowns from freezing the psql process.
• The system separates concerns into two independent loops: an input loop (Browser to PTY) and an output loop (PTY to Browser via Redis) to survive partial failures.
• Scaling is fundamentally bounded by the number of concurrent database sessions and OS processes (PTY, threads, psql) rather than typical web constraints like CPU or WebSocket limits.
• A BoundedSemaphore provides a simple but effective safety boundary against process explosions and database connection storms in production environments.

Working Examples

A simple safety boundary used to prevent memory exhaustion and database connection storms by limiting concurrent psql sessions.

BoundedSemaphore(max_sessions=10)

Practical Applications

• Use Case: A database management platform providing browser-based CLI access to instances behind private networks using reverse WebSockets. Pitfall: Using HTTP polling or SSE, which lack the bidirectional state required for real terminal interaction.
• Use Case: Implementing terminal resizing in xterm.js with an ioctl(TIOCSWINSZ) call on the agent side. Pitfall: Failing to debounce resize events, which causes a ‘resize storm’ that overwhelms the agent process.

References:

• https://dev.to/alex_zhdankov/we-built-a-real-psql-terminal-in-the-browser-heres-what-made-it-unexpectedly-hard-57a1