Mapping Runtime Dependencies from Traffic and Query Logs

A component cannot be replaced until the set of things that depend on it is known, and that set is almost never what the source tree suggests. Static analysis follows syntactic calls. It cannot see a table name assembled from a string at run time, a stored procedure invoked by name from a configuration row, a report that reads a view created years ago by someone who has left, or a nightly job that writes into an interface table another team reads by file transfer. The dependencies that block decommissioning are exactly the ones that no compiler ever resolved.

The reliable record of a system’s dependencies is its own runtime behaviour. Every dependency that matters crosses a boundary (a socket, a file, a database connection, a queue), and boundaries can be observed. The work is to observe long enough, at the right layer, and to attribute what is observed back to something a human can act on.

What the boundaries reveal

Different boundaries need different instruments.

At the network layer, flow records and packet captures give the caller and callee addresses and ports, plus byte and connection counts. Flow records are cheap and continuous; packet capture is complete but expensive and short-lived. Connection-level observation answers “which hosts talk to which”, which is enough to find an unknown client of a soon-to-be-retired service, but not enough to say which endpoint or which query.

At the database layer, statement logging and server-side statistics answer the more important question: which tables, views, and procedures are actually touched. PostgreSQL’s pg_stat_statements extension aggregates normalized query text with call counts and timing, keyed on a normalized form in which literal values are replaced by parameters. That normalisation is what makes aggregation possible, and also what hides which row was read. Setting log_statement = 'all' produces a line per statement, which is exact but generates log volume proportional to statement rate; on a system executing thousands of statements per second the log becomes the dominant write load unless it is directed to a separate device or consumed immediately.

At the process layer, system-call tracing shows file opens, connect calls, and child processes. This catches the dependencies that never appear in the application’s own configuration: a library that reads a licence file on startup, a helper binary invoked through system(), a cron entry that the application does not know exists.

At the scheduling layer, the crontab, the job scheduler’s repository, and the file-transfer configuration hold the dependencies that fire on a schedule rather than in response to a request.

Building a graph an engineer can use

Raw observation is a list of edges. Making it useful requires two reductions.

The first is attribution: mapping each observed edge to an owner. A query against invoice_archive belongs to whichever module issues it, and the log line usually carries the connecting user, application name, or client address that identifies the module. Without that attribution the graph says “something reads this table” and cannot be acted on. Connection poolers obscure attribution because every client appears as the pooler’s address; the pooler’s own logs, or an application-set connection parameter, are needed to recover the caller.

The second is weighting. An edge that fires once a quarter matters differently from one that fires a thousand times a second. Counts also reveal paths that exist but are dead: a table with zero reads over a full business cycle is a candidate for removal, while a table with a single monthly reader is a blocker that appears in no test.

Attribution has a practical prerequisite: the source must label its connections. Where the client can set an application name on its connection, doing so converts an anonymous edge into a named one, and the cost is a configuration line per client. Where it cannot, the fallback is the source address plus the connection’s shape: a pool of ten connections from a known host, opened at startup and held open, is distinguishable from an interactive client that connects and disconnects per request. The weakest attribution is a single shared role used by everything, which produces a graph that says the database is used but not by whom.

Timing is data too. Grouping edges by hour of day and day of month separates interactive traffic from batch windows, and the separation is what allows a removal to be scoped to one class. A table read only between two and four in the morning is a batch dependency, and a replacement plan that covers interactive requests but not the batch has not covered it. Recording the hour of last access costs a histogram per edge and pays for itself the first time a monthly job is found three weeks after it was declared dead.

The result is a bipartite graph between components and resources (tables, endpoints, files, queues), with counts on the edges. Removal decisions are then graph queries: does this table have any inbound edges from components outside the replacement scope. The answer changes the plan more often than any code reading does.

Why the observation window is the hard part

The dominant failure in dependency mapping is a window that is too short. Systems built around monthly close, quarterly reporting, annual statements, or seasonal peaks have dependencies that do not execute in a two-week capture. A table read only by the year-end regulatory extract looks dead for eleven months.

The window must therefore be at least one full cycle of the longest periodic job, and the mapping must record when each edge was last observed rather than only whether it was observed. “Last seen 47 days ago” and “never seen” are different facts, and only the second justifies deletion. Where the cycle is annual and waiting is impractical, the scheduler’s configuration is the substitute: enumerate every scheduled job and check each against the observed set, treating unobserved scheduled jobs as unknowns rather than as absent.

A second, subtler problem is that replicated reads are invisible at the primary. Analytical queries are often routed to a replica, and statements executed there do not appear in the primary’s statistics. Capturing from one node produces a graph that is correct for that node and wrong for the system.

Reading dynamic SQL and indirection

Legacy data access layers frequently build SQL by concatenation, so the logged text contains the literal values and no stable shape. Normalisation by the database collapses the literals only when the statement is parsed as a parameterised query; text assembled in the application is logged as-is, and each execution looks unique. Two techniques recover structure: group statements by the set of identifiers they contain rather than by full text, and cross-reference the resulting table sets with the code that builds them. The first produces a stable signature; the second produces the owner.

Stored procedures add a layer that statement logs only partly expose. A call to a procedure appears as one statement; the tables it touches do not appear unless the procedure body is inspected or unless the server logs nested statements. Reading the procedure definitions is therefore unavoidable, and the log is used to determine which procedures are still called at all.

Indirection through configuration is the other common blind spot. A connection string in a database table, an endpoint in a properties file read at startup, a queue name composed from an environment prefix: each is a dependency whose target is decided at run time. Grepping the source finds the mechanism but not the value, so the observed connections must be reconciled against every configuration source to find targets that no current configuration points at.

Overhead, sampling, and the accuracy trade

Continuous full capture is rarely affordable. Statement logging at all costs I/O proportional to statement count and can be enabled for a bounded interval; pg_stat_statements costs memory bounded by its configured entry limit and gives approximate aggregates rather than individual executions. Flow sampling gives a statistically representative view of host-to-host traffic at a fraction of the storage, but a sampled edge with a low rate can be missed entirely, which is the wrong error direction for a removal decision.

The asymmetry matters: for finding unknown callers, false negatives are expensive and false positives are cheap, so the capture should err toward completeness and the analysis toward suspicion. A component that appears to have no callers should be confirmed by a second method, blocking its port or revoking its credentials in a controlled environment and observing what fails, rather than trusted from a single observation window.

That last technique, deliberate denial, is also the most direct way to catch dependencies that are dormant during observation but enabled by a flag or a date. Denial converts an absence of evidence into evidence of absence, and it is the transition point into differential testing between the old and new implementations and eventually into proving a legacy component is unused. Before either, the graph is what tells the team where a strangler facade can be inserted without stranding a caller nobody knew about.