When YSQL Wouldn't Answer: A Case Study in Systematic Debugging of Distributed Database Startup

The Message

[assistant] YCQL is working. Let me wait for YSQL to become ready:
[bash] for i in {1..10}; do echo "Attempt $i..."; timeout 5 docker exec test-cluster-yugabyte-1 bin/ysqlsh -h 127.0.0.1 -p 15433 -U yugabyte -d yugabyte -c 'select 1' 2>&1 && break; sleep 10; done
Attempt 1...
Attempt 2...
Attempt 3...
Attempt 4...
Attempt 5...
Attempt 6...
Attempt 7...
Attempt 8...
Attempt 9...


<bash_metadata>
bash tool terminated command after exceeding timeout 120000 ms
</bash_metadata>

At first glance, this message appears to be a routine wait loop—a developer checking whether a database service has finished starting. But in the context of the broader debugging session, it represents a critical diagnostic pivot. The assistant has just confirmed that YugabyteDB's YCQL interface (the Cassandra-compatible query layer) is operational, yet the YSQL interface (the PostgreSQL-compatible layer) remains stubbornly unresponsive. This single message captures the moment when a developer transitions from assuming "the database is still booting" to suspecting "something is specifically wrong with the PostgreSQL subsystem."

The Context: A Test Cluster Under Construction

To understand why this message matters, we need to step back. The assistant has been building a horizontally scalable S3 storage architecture—a complex system involving stateless S3 frontend proxies, Kuri storage nodes, and a shared YugabyteDB metadata store. The test cluster runs in Docker Compose, and the session leading up to this message has been a marathon of configuration debugging.

Earlier in this segment, the assistant had been fighting with Docker networking. An attempt to use host networking mode had created port conflicts with existing services on the development machine. After reverting to bridge networking and cleaning up stale YugabyteDB data directories, the assistant finally got the database container to start with custom ports mapped correctly. The ss -tlnp output confirmed that all four expected ports were bound: 15433 (YSQL), 19042 (YCQL), 19000 (tserver web), and 19100 (master web). The database health status showed "starting," which seemed consistent with normal boot behavior.

The Diagnostic Pivot

The critical moment comes in message 1266, immediately before our subject message. The assistant runs a YCQL query and gets a response:

system_auth  system_schema  system

This is the first clear signal that something is wrong. YCQL works. The Cassandra-compatible API is fully operational. But YSQL—the PostgreSQL-compatible API that the application actually needs for certain operations—has been timing out for over two minutes across multiple attempts.

The subject message is the assistant's response to this asymmetry. The reasoning is clear: "YCQL is working. Let me wait for YSQL to become ready." The assistant is giving YSQL the benefit of the doubt, assuming it might just be slower to initialize. The loop uses a 5-second timeout per attempt with a 10-second sleep between tries, which is a reasonable strategy for distinguishing between "still booting" and "permanently broken."

The Assumptions at Play

Several assumptions are embedded in this message, and they reveal the assistant's mental model of the system:

Assumption 1: YSQL startup is independent of YCQL startup. The assistant treats YCQL working as evidence that the database is fundamentally operational, and YSQL is simply lagging behind. This is a reasonable assumption—YugabyteDB does start its YCQL and YSQL subsystems somewhat independently, and YSQL (being PostgreSQL-based) can take longer to initialize.

Assumption 2: A retry loop with timeout is sufficient to detect readiness. The assistant uses timeout 5 to ensure each attempt doesn't hang indefinitely, and && break to exit the loop on success. This is a standard pattern, but it assumes that the failure mode is either "connection refused" (fast failure) or "connection accepted and query executed" (success). It doesn't account for the possibility of a connection that hangs without error.

Assumption 3: The database is in a clean state. The assistant had cleaned the YugabyteDB data directory and restarted from scratch. The assumption is that no residual processes or state from previous runs could interfere. This assumption would prove incorrect.

Assumption 4: The port being open implies the service is functional. The assistant had verified that port 15433 was listening. In normal operation, this would mean the PostgreSQL process is accepting connections. But the assistant hadn't yet checked whether that listening process was actually the right process, or a zombie from a previous run.

What Actually Happened

The loop runs nine times. Each attempt produces only "Attempt N..." with no output from ysqlsh. The timeout 5 command is supposed to kill the ysqlsh process after 5 seconds if it hangs, but the entire bash command is eventually terminated by the tool after 120 seconds. This suggests that either the timeout command itself is hanging (perhaps because docker exec doesn't respond to SIGTERM from within the container), or the cumulative sleep time plus timeouts exceeded the tool's limit.

The key observation is that none of the nine attempts succeeded. After 90 seconds of waiting (9 attempts × 10-second sleeps) plus the timeout periods, YSQL still isn't responding. This is far beyond normal startup time for a database that has already initialized its YCQL layer.

The Reveal: What the Next Message Shows

The very next message (index 1268) provides the answer. The assistant checks the tserver logs and finds:

2026-01-31 15:51:53.887 UTC [9259] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
2026-01-31 15:51:53.887 UTC [9259] HINT:  Is another postmaster already running on port 15433?
2026-01-31 15:51:53.887 UTC [9259] FATAL:  could not create any TCP/IP sockets

A zombie PostgreSQL process from a previous container run was still holding port 15433. The new YugabyteDB instance couldn't bind its YSQL listener, so the port appeared open (because the old process was still there), but any connection attempt would either connect to the wrong process or hang. This explains the asymmetry: YCQL worked because it uses a different port (19042) that wasn't contested, while YSQL was blocked by a ghost process.

Input Knowledge Required

To fully understand this message, a reader needs:

YugabyteDB architecture knowledge: Understanding that YugabyteDB provides both a YCQL (Cassandra-compatible) and YSQL (PostgreSQL-compatible) API, and that these are separate subsystems with independent ports.
Docker networking concepts: Understanding how port mapping works, what bridge vs. host networking means, and how container lifecycle affects port availability.
Bash scripting patterns: Recognizing the retry loop pattern, the use of timeout to prevent hanging, and the && break short-circuit for early exit.
Debugging methodology: Understanding the significance of "YCQL works but YSQL doesn't" as a diagnostic signal that narrows the problem to the PostgreSQL layer specifically.

Output Knowledge Created

This message, combined with the subsequent investigation, creates several pieces of knowledge:

A confirmed failure mode: A zombie PostgreSQL process from a previous container can block YSQL startup while YCQL continues to function normally. This is a specific, reproducible failure pattern.
A diagnostic technique: Checking YCQL first as a "canary" to determine whether the database core is functional, then investigating YSQL separately if it fails.
A lesson in container lifecycle management: Docker containers can leave behind processes that survive container removal if the process was started with certain flags or if the filesystem wasn't properly cleaned.
The importance of log inspection: The tserver.err log contained the definitive answer. The assistant's decision to check logs after the retry loop failed was the correct next step.

The Thinking Process Visible in the Message

The subject message reveals the assistant's reasoning in real time. The opening statement—"YCQL is working. Let me wait for YSQL to become ready"—is a hypothesis being tested. The assistant has observed an asymmetry and is testing whether it's a timing issue or a fundamental problem.

The loop structure itself encodes several design decisions:

5-second timeout: Chosen to be long enough for a normal connection but short enough to not waste time if the service is down.
10-second sleep between attempts: Gives the database time to complete initialization between checks.
10 attempts maximum: Sets an upper bound of about 100 seconds of total waiting time.
&& break: Exits immediately on success, avoiding unnecessary waits. The fact that the assistant didn't check the logs before running this loop is itself an interesting decision. The logs were available and would have shown the bind error immediately. But the assistant chose to test the hypothesis "YSQL is just slow" first, which is the more efficient path if the hypothesis is correct. Only after the loop failed did the assistant pivot to log inspection. This is a classic debugging pattern: test the simplest hypothesis first, and escalate to deeper investigation only when the simple hypothesis is falsified. The message captures this moment of hypothesis testing in action.

Broader Significance

This message is a microcosm of the entire debugging session. The test cluster has been plagued by state leakage between runs—stale data directories, leftover containers, orphaned processes. The YSQL bind failure is just another manifestation of the same underlying challenge: distributed systems leave state everywhere, and cleaning that state thoroughly is essential for reliable testing.

The assistant's systematic approach—verify what works, isolate what doesn't, test hypotheses, escalate to logs—is the disciplined response to this chaos. The message may look like a simple wait loop, but it's actually a carefully structured diagnostic probe, designed to efficiently distinguish between two possible failure modes and guide the next step in the investigation.