Why I Stored File Hierarchies as Paths for Incremental Indexing

A filesystem is a tree, but that does not mean its database representation must optimize for recursive traversal.

I worked on a document indexing pipeline that scanned a large file hierarchy, stored file metadata in a relational database, and sent changed documents to Elasticsearch. The first modeling question looked obvious: folders have parents, so store each node with a parent_id and reconstruct the hierarchy recursively.

That model represented the domain neatly. It did not represent the main workload neatly.

The pipeline repeatedly needed to answer a different question: “Which stored entries belong to this root or folder so I can compare them with the current filesystem scan?” Recursive queries and joins added work to that hot path. I instead stored a normalized path with each entry and used path-prefix reads to load a subtree. A depth-first filesystem scan then classified entries as new, modified, unchanged, or deleted before a queue carried the required index and delete operations to Elasticsearch.

This article reconstructs that design from the parts of the original implementation that remain verifiable. I have deliberately omitted the old benchmark figures: the notes preserved timings and improvement percentages, but not enough detail about hardware, database version, cache state, repetitions, or the exact dataset to make those numbers reproducible.

TL;DR

• Model around the dominant query, not only the shape of the source domain.
• A normalized materialized path turns a subtree read into an equality or prefix predicate.
• A B-tree can support a left-anchored path pattern such as /docs/%; a leading wildcard such as %/docs removes that useful prefix.
• Scan the filesystem, compare it with the stored subtree, and emit explicit upsert and delete work instead of rebuilding the entire search index.
• Treat an incomplete scan as incomplete evidence. Never delete every unseen database row after permission or I/O failures.
• Path models make reads simple, but directory moves rewrite descendant paths and path normalization becomes part of data integrity.

The Real Operation Was a Subtree Diff

The pipeline had three responsibilities:

1. walk a configured filesystem root and collect metadata such as file name, normalized path, and modification time;
2. compare that snapshot with metadata already stored for the same scope;
3. update the database and enqueue the corresponding Elasticsearch operations.

The important unit was not one node and its immediate children. It was an entire indexing scope.

filesystem root
  -> depth-first scan
  -> current path/mtime snapshot
  -> compare with stored subtree
  -> new | modified | unchanged | deleted
  -> database changes
  -> queued Elasticsearch upserts and deletes

An adjacency-list table can represent the tree like this:

id | parent_id | name

That is attractive when moving a folder is common: changing one folder's parent can preserve all descendant rows. It is less attractive when every indexing run needs the complete descendant set. The application must issue a recursive query, maintain hierarchy reconstruction logic, or repeatedly join through parent relationships.

The path model stored the location directly:

root_id | normalized_path                  | modified_at
42      | /manuals                         | ...
42      | /manuals/install                 | ...
42      | /manuals/install/linux.pdf       | ...
42      | /manuals/install/windows.docx    | ...

For this workload, the duplicated ancestry was useful information. Every row carried enough context to answer the subtree question without rebuilding the tree.

Normalize Paths Before They Become Keys

A prefix query is only reliable if equivalent paths have one representation. Path normalization therefore belongs at the ingestion boundary, before comparison or persistence.

At minimum, the pipeline must decide:

• whether stored paths are absolute or relative to a registered root;
• which separator is canonical;
• whether trailing separators are allowed;
• how . and .. segments are handled;
• whether case differences are significant for the source filesystem;
• whether symbolic links are followed;
• how Unicode normalization is handled;
• how the root itself is represented.

I prefer a stable root_id plus a normalized relative path. It avoids exposing machine-specific mount points and allows two registered roots to contain the same relative name without colliding.

The following index is illustrative; column sizes and collations must be chosen for the actual filesystem, character set, and MySQL index-key limits:

CREATE INDEX idx_file_entry_root_path
    ON file_entry (root_id, normalized_path);

The subtree lookup binds a complete, escaped prefix rather than concatenating untrusted text into SQL:

SELECT normalized_path, modified_at
FROM file_entry
WHERE root_id = ?
  AND (
    normalized_path = ?
    OR normalized_path LIKE ? ESCAPE '='
  )
ORDER BY normalized_path;

For the folder /manuals/install, the parameters are:

exact path: /manuals/install
pattern:    /manuals/install/%

The separator before % matters. A raw /manuals/install% pattern would also match a sibling such as /manuals/installer.

Real file names can contain SQL pattern characters. Before adding the final /%, escape =, %, and _ because = is the declared escape character in this example:

static String escapeLike(String value) {
    return value
        .replace("=", "==")
        .replace("%", "=%")
        .replace("_", "=_");
}

String subtreePattern = escapeLike(normalizedFolder) + "/%";

MySQL documents that B-tree indexes support range lookups. A left-anchored LIKE predicate gives the optimizer a usable leading range, while the exact plan still depends on statistics, selectivity, collation, index shape, and server version. The practical check is EXPLAIN ANALYZE, not the presence of an index in the schema.

Index Condition Pushdown is also an optimizer behavior, not a performance switch that guarantees a particular result. When MySQL can evaluate part of a condition from indexed columns, it can test that condition in the storage engine before reading the full base row. I checked for an index range scan and measured rows examined; I did not treat index_condition_pushdown=on alone as evidence that the query was fast.

Walk the Filesystem Once, Then Compare States

The original scanner used depth-first traversal. Java's Files.walkFileTree expresses that control flow directly and exposes both successful visits and failures.

record ScannedFile(String relativePath, long modifiedAtMillis) {}

final class CollectingVisitor extends SimpleFileVisitor<Path> {
    private final Path root;
    private final Consumer<ScannedFile> sink;
    private final List<Path> failedPaths;

    CollectingVisitor(
        Path root,
        Consumer<ScannedFile> sink,
        List<Path> failedPaths
    ) {
        this.root = root;
        this.sink = sink;
        this.failedPaths = failedPaths;
    }

    @Override
    public FileVisitResult visitFile(
        Path file,
        BasicFileAttributes attributes
    ) {
        String relativePath = normalize(root.relativize(file));
        sink.accept(new ScannedFile(
            relativePath,
            attributes.lastModifiedTime().toMillis()
        ));
        return FileVisitResult.CONTINUE;
    }

    @Override
    public FileVisitResult visitFileFailed(Path file, IOException error) {
        failedPaths.add(file);
        return FileVisitResult.CONTINUE;
    }
}

The code is a generic reconstruction, not a copy of private production code. The important boundary is that traversal errors remain part of the scan result. Silently swallowing them would make a partially visible directory indistinguishable from a directory whose files were intentionally deleted.

The simplest comparison loads the stored subtree into a map:

Map<String, StoredFile> stored = repository.loadSubtree(rootId, folder);
Set<String> seen = new HashSet<>();

for (ScannedFile current : scannedFiles) {
    seen.add(current.relativePath());
    StoredFile previous = stored.get(current.relativePath());

    if (previous == null) {
        changes.add(Change.upsert(current, Reason.NEW));
    } else if (previous.modifiedAtMillis() != current.modifiedAtMillis()) {
        changes.add(Change.upsert(current, Reason.MODIFIED));
    }
}

for (StoredFile previous : stored.values()) {
    if (!seen.contains(previous.relativePath())) {
        changes.add(Change.delete(previous.relativePath()));
    }
}

This makes the four states explicit:

For a bounded folder, a map is easy to reason about. For a very large scope, loading every row and every scanned entry at once can become the next bottleneck. Two common extensions are a merge of both path-sorted streams or a staging table followed by set-based inserts, updates, and anti-joins. The correct choice depends on memory limits, database write cost, and how scan results arrive.

Modification Time Is a Hint, Not Content Identity

The original pipeline used modification time as part of change detection. That avoids reading and hashing every unchanged file, but it has limits:

• filesystem timestamp resolution varies;
• a copy operation can preserve timestamps;
• clock behavior and network filesystems can produce surprising metadata;
• a file may be rewritten with the same length and timestamp;
• metadata can change while a file is being read.

A practical policy is layered:

1. use normalized path and modification time for the cheap comparison;
2. add file size when it is available and meaningful;
3. hash content only when stronger identity is required or metadata is ambiguous;
4. re-check metadata after extraction if files can change during processing.

That policy should be named in tests and operations documentation. Calling an mtime comparison “content equality” would overstate what it proves.

Deletion Requires a Complete Scan

New and modified files provide positive evidence: the scanner observed them. Deletion is different. It is inferred from absence.

That inference is unsafe when the scan encountered:

• permission failures;
• an unavailable network mount;
• I/O exceptions;
• a traversal cancellation;
• a process crash;
• a symlink policy mismatch.

The safe invariant is:

Only a successfully completed scan may turn unseen rows into deletes.

One implementation is to assign a scan ID, mark every observed row with it, and run the deletion phase only after the root scan reaches a successful terminal state. Another is to stage the complete snapshot and promote it atomically. If any path failed, retain existing rows for the affected scope and surface the scan as incomplete.

This rule matters more than shaving time from the prefix query. A fast scan that converts a temporary mount failure into thousands of search-index deletions is operationally wrong.

Keep Database Diffing Separate from Elasticsearch Writes

After the comparison, the pipeline updated relational metadata and placed search operations on a queue for a later worker. The queue separated filesystem and database work from Elasticsearch latency.

scan and compare
  -> persist metadata changes
  -> enqueue UPSERT(path) or DELETE(path)
  -> worker extracts/indexes or deletes document

The worker can group independent operations with Elasticsearch's Bulk API:

{ "index":  { "_index": "documents", "_id": "<stable-id>" } }
{ "path": "/manuals/install/linux.pdf", "modifiedAt": "..." }
{ "delete": { "_index": "documents", "_id": "<stable-id>" } }

The Bulk API supports mixed index, create, update, and delete actions in one request. It reduces request overhead, but a successful HTTP response is not enough: each item has its own result and failure information. Batch size and worker concurrency also require workload-specific tests; Elastic explicitly recommends benchmarking rather than copying a universal bulk size.

A stable document ID makes retries easier. It can be derived from root_id and normalized relative path without placing an absolute machine path in the public document identifier. A rename then appears as one delete and one index unless the system stores an additional stable file identity.

The queue boundary does not automatically make the database and Elasticsearch transactionally consistent. If metadata commits and an in-memory enqueue is lost during a crash, the search index can drift. When that loss is unacceptable, use a durable work table or transactional outbox and keep a reconciliation job that can regenerate missing work from the database. That reliability problem deserves its own design; it should not be hidden behind the word “queue.”

Verification Focused on Plans, Diffs, and Failure Boundaries

I would not validate this design with one happy-path directory and a wall-clock number. The useful checks cover each boundary.

Prove the path query plan

Run the same subtree query against representative distributions:

EXPLAIN ANALYZE
SELECT normalized_path, modified_at
FROM file_entry
WHERE root_id = 42
  AND normalized_path LIKE '/manuals/install/%';

Record:

• the selected index and access type;
• estimated versus actual rows;
• rows examined and rows returned;
• cold and warm cache conditions;
• folder selectivity and path depth;
• database version, collation, schema, and hardware.

Those details are what the old benchmark notes lacked, so I do not reuse their exact figures here.

Exercise every diff state

Start with a known snapshot and then perform one controlled change at a time:

• add a file;
• modify a file;
• leave a file unchanged;
• delete a file;
• rename a file;
• move a directory with descendants;
• create names containing %, _, mixed case, and Unicode;
• retry the same change batch;
• interrupt the scan;
• deny access to one subtree;
• disconnect the indexed root.

For every case, assert the database action, queued action, Elasticsearch document state, and retry result. A permission failure must not produce the same deletes as a successful empty scan.

Reconcile the final state

After the queue drains, compare stable IDs from the database and Elasticsearch for the scanned scope. Count equality is useful but insufficient: two sets can have the same size and different members. Compare IDs, sample stored metadata, and verify that deleted paths are absent.

Also inject failures between boundaries:

1. after scanning but before the database commit;
2. after the database commit but before enqueue;
3. after enqueue but before Elasticsearch accepts the item;
4. after Elasticsearch accepts the item but before the worker records success.

The recovery behavior at those four points reveals whether the pipeline is actually retryable.

The Trade-Off I Accepted

Path-based hierarchy storage fit because subtree reads and incremental comparisons were frequent, while hierarchy moves were comparatively less important.

It was a poor fit for free:

• moving a directory requires rewriting every descendant path;
• path normalization and collation become correctness rules;
• long paths must fit the chosen column and index design;
• a rename changes a path-derived document ID;
• path prefix escaping must be tested;
• a path alone does not preserve file identity across moves;
• database and search-index consistency still need an explicit recovery strategy.

I would choose this model again when the dominant operations are:

• load a complete subtree;
• compare a filesystem snapshot with stored metadata;
• enqueue only changed search documents;
• rebuild derived search state from a relational source of truth.

I would reconsider it when directory moves are frequent, stable node identity matters more than subtree reads, or the application needs many arbitrary graph-like relationships. An adjacency list, closure table, database-specific hierarchy type, or hybrid model may serve those workloads better.

The broader lesson is simple: “the domain is a tree” is not yet a database access pattern. Once the hot operation was stated as a subtree diff, storing ancestry in a normalized path became a deliberate trade rather than a shortcut—and incremental indexing became a comparison problem instead of a full-rebuild habit.

Official references

• Java Files.walkFileTree
• MySQL B-tree column indexes
• MySQL Index Condition Pushdown
• MySQL EXPLAIN and EXPLAIN ANALYZE
• Elasticsearch Bulk API
• Elastic guidance for tuning indexing speed