Counting Services Across Many Compose Files With DuckDB

A repository with two dozen Compose files is a small fleet. Which images does it run, how many services per file, which databases have no healthcheck, which versions disagree? Reading the files one at a time does not answer those questions, and grep answers them badly, because YAML is structure and text search does not see structure. The method that works is to turn the files into rows once and ask SQL. This post builds the table from 24 generated Compose files and runs five queries. It belongs to the cluster on analyzing configuration and metadata held in YAML, and the single-file Compose basics are in Docker Compose YAML for a database stack.

Files to rows

Each file has a services mapping. The loop emits one row per service:

rows = []
for p in sorted(Path("repo/compose").glob("*.yaml")):
    doc = yaml.safe_load(p.read_text())
    for name, svc in doc["services"].items():
        image, _, tag = svc["image"].partition(":")
        rows.append((p.name, name, image, tag, len(svc.get("ports", [])),
                     "healthcheck" in svc, svc.get("restart")))

and DuckDB holds them:

con = duckdb.connect()
con.execute("create table svc (file varchar, service varchar, image varchar, tag varchar, nports int, has_health boolean, restart varchar)")
con.executemany("insert into svc values (?,?,?,?,?,?,?)", rows)

Three notes on the loop. partition(":") splits the image reference at the first colon, which is correct for postgres:18 and wrong for an image with a registry port such as registry.example.com:5000/app:1.2; use a real image-reference parser for those. svc.get("ports", []) supplies a default for a service with no ports, because a missing key is data and not an error. And the table is flat: one fact per row, columns for the things you will filter on. If you later need environment variables or volumes, add a second table keyed by file and service, and join; a column of lists makes SQL harder. The flattening technique for arbitrary structure is in flattening nested YAML into rows.

Five questions

How big is the fleet?

files: 24 | services: 66
services per file (min/avg/max): (2, 2.8, 4)

Sixty-six services in 24 files, with two to four per file. Which images does it run, and how often?

select image, count(*) n from svc group by image order by n desc, image;

[('mysql', 17), ('redis', 15), ('app', 14), ('postgres', 14), ('nginx', 6)]

MySQL is the most common service, then Redis, the application image and PostgreSQL. A reader of the files one by one would have a sense of this; the table gives the exact counts and updates when a file changes. Which databases lack a healthcheck?

select count(*) from svc
where image in ('postgres','mysql','redis') and not has_health;

databases without healthcheck: 16 of 46

Sixteen of 46 database services have no healthcheck, which means anything that depends on them in Compose cannot wait for readiness (healthchecks and depends_on in Compose for databases). That is a finding worth a ticket, and the query lists the offenders if you select file, service instead of the count. How many set restart: always?

restart always: 20

Twenty. Whether that is right depends on policy; the point is that the question has an answer in one line. Where do versions disagree?

select image, count(distinct tag) as tags, string_agg(distinct tag, ', ' order by tag) as list
from svc group by image having tags > 1 order by tags desc;

[('postgres', 4, '15, 16, 18, 18.6'), ('app', 3, '1.4.2, 1.5.0, latest'), ('redis', 3, '6-alpine, 7-alpine, 7.4'), ('mysql', 2, '8.0, 8.4')]

Every image appears under more than one tag. PostgreSQL is run as 15, 16, 18 and 18.6 across these files, the application as three versions including latest, Redis as three. For a fleet that is supposed to be consistent, this is the headline result. The follow-up is to find which files use which, which finding inconsistent versions across YAML configs covers with a stricter extraction.

Why not just query the YAML directly

DuckDB's YAML extension reads YAML files as tables (using DuckDB to query data stored as YAML), and for a file whose records are a list it is the shortest route. Compose is awkward for it, because services is a mapping whose keys are service names: the extension infers a struct with one field per distinct name across the files, a wide and sparse shape that does not group well. The Python loop turns the mapping into rows, which is what SQL wants. The same pattern, a few lines of Python to normalize and a query engine to ask, applies to Kubernetes, CI files and Helm values. If you already have yq, a shell version is yq '.services | to_entries | .[] | [.key, .value.image] | @csv' *.yaml, which produces rows you can pipe into DuckDB's read_csv.

Making it repeatable

Run the loop in CI or a scheduled job, write the table to a file (copy svc to 'services.parquet') and keep the queries in a script. Three habits make the report useful over time:

• Store the result with a date and compare. A diff of "postgres tags: 4, now 3" shows progress.

• Fail on thresholds. A job that exits nonzero when databases without healthchecks exceed zero turns a report into a gate.

• Report by owner. If files live in team directories, add the directory as a column and group by it.

Check the inputs with a validator first (validating YAML before it breaks a deploy), so the analysis is not working from files that Compose itself would reject.

A second table for ports and environment

The svc table answers questions about images and flags. Questions about published ports or environment variables need a second, narrower table, because each service has several ports and many variables. The pattern is always the same: a child table with the file and the service as a key, one row per port or variable, joined back to svc when you need both. With a port table you can ask which host ports are published by more than one service in the same file (a conflict Compose would reject at start), which services publish on all interfaces instead of loopback, and which container ports are not the image's default. With an env table you can find services that set a password literally, which is a security finding, and variables that are set in some files and absent in others. Resist putting the lists into columns of svc; DuckDB can store lists, and queries over lists are harder to read than joins over rows. Keep each table tall and narrow, and add a table when a question needs one.

Check the loader against the question too. A count of services is only as good as the files' structure: a file with an include: of another Compose file, or extends: for a service, holds services you cannot see in its own text. Run docker compose config on each file and parse that output when your Compose files use merging features, and count from the effective configuration.

When counting services in YAML files is the wrong analysis

The files are not what runs. Compose files are intent. What runs is in the container runtime, and the two drift. Query the runtime (docker ps --format) for that.

Interpolation changes the meaning. image: postgres:${PG_VERSION:-18} is not a tag until the variable is resolved; parse with docker compose config to get the effective file.

Overrides and includes. A file merged with an override or included from another is not one file. Resolve before you count (Docker Compose YAML for a database stack shows the merge rules).

There are only a few files. With three files, reading is faster than building a table.

The query engine is described in the DuckDB documentation, and the Compose service keys in the Compose specification. Inspect a file in the YAML formatter.