PRODUCT # Engineering Deep Dive: How We Made Transportial 3x Faster [Thomas Kolmans](https://www.linkedin.com/in/thomaskolmans/) · CTO, Transportial · July 29, 2026 ![Engineering Deep Dive: How We Made Transportial 3x Faster](/assets/blog/engineering-deep-dive-performance.webp) A planner opens the planning board hundreds of times a day. Every trip save, every drag on the Gantt, every refresh of the order list — the speed of those moments is the speed of your operation. This month we ran a performance program against the layers underneath those screens, and this post is the technical account of it: what we measured, what we found, and what we changed. ## The headline numbers * **Planning board:** server-side build time went from \~950ms to \~276ms for a full board of trips — about 3.4x faster. * **\~100 hidden database queries per board load eliminated.** * **Foreign-key lookups on our largest production table (75+ million event rows) went from 114 seconds to under a millisecond.** * **Saves now write only the columns that actually changed.** ## Interrogating the database: plans, not checklists Most index audits ask the catalog a question like "which foreign keys have no covering index?" — and ours passed that audit cleanly. The database was still slow. So we stopped trusting checklists and built a harness that walks the _entire foreign-key tree_ of a trip — trips to actions, actions to consignments, goods, equipment, events, and every edge between them — and runs a real `EXPLAIN (ANALYZE, BUFFERS)` for each edge against production-scale data, with a per-statement timeout so one pathological edge cannot stall the sweep. The output is a ranked list of relationship edges by actual execution time, not by what the schema claims. Three findings came out of that sweep, and each one is invisible to a conventional audit: ### 1\. The partial-index blind spot Our busiest edge — events by action — _had_ indexes on `action_id`. But they were partial indexes, built years ago with a `WHERE type = '...'` clause for one specific event subtype. A catalog query sees "column indexed" and moves on; the planner sees an index it cannot use for the general lookup and falls back to a sequential scan over 77.9 million rows: **114,639ms per lookup**. The fix is a partial index that matches how the queries actually run — `WHERE action_id IS NOT NULL`, which also keeps the index small on a column that is mostly null. Same lookup afterwards: **0.04ms**. We applied the same treatment to two sibling columns on the events table that had the identical blind spot. ### 2\. The migration that lied A years-old migration file contained hundreds of `CREATE INDEX` statements — commented out. Any text-level search for "does an index exist for X" matched those lines and reported success; the database never saw them. It is a small war story with a lasting lesson: **source files describe intent, only `pg_catalog` and live query plans describe reality.** Every conclusion in this program was verified against the running database, and every fix was captured in a versioned migration so it is a reproducible fact about the system, not a one-off tweak someone once ran. ### 3\. Join tables that only index one side Many-to-many join tables are generated with an index on the far side of the relationship — and nothing on the owner side, which is exactly the side every collection load filters on. The result: loading a trip's documents, actors or identifiers full-scanned the join table, every time, for every entity. Local development databases hide this completely (a full scan of a thousand rows is instant); at production scale it taxed every screen. One migration later, twelve join tables have proper owner-side indexes. The sweep also paid for itself in reverse: indexes with zero scans in the statistics counters were dropped — an unused index is pure write amplification — and autovacuum thresholds were tuned on the hottest tables, where the default "vacuum after 20% dead rows" policy means a 75-million-row table can accumulate fifteen million dead rows before maintenance ever runs. ## Teaching the data layer what "changed" means A transport order is a deep object graph: consignments, actions, goods, equipment, constraints, documents. Historically our ORM answered "what changed?" the expensive way — comparing every field of every loaded entity against a snapshot, on every save, and on most queries in between (a flush precedes any query that might depend on pending changes). On a board request that runs \~90 queries with hundreds of entities loaded, that comparison tax is paid ninety times. We moved to compile-time bytecode enhancement with inline dirty tracking: each entity records which fields were written as they are written, so a save issues an UPDATE for exactly those columns and a clean entity costs a flag-check. The hard part is correctness, not speed — inline tracking only sees writes that go through the entity, so every write path in the system had to be routed through tracked code: API request merging, integration imports, all of it. Our integration suite — 13,648 tests against a real database — was the referee. It caught every subtle case on the way there, including merge semantics and JSON edge cases that had been silently tolerated for years, and the program was only done when the suite was green with tracking enabled. The same enhancement made lazy loading genuinely lazy. One example: every vehicle on a board used to fire a hidden query for its chat thread — an inverse one-to-one relation that cannot be lazy without bytecode enhancement, because the ORM must otherwise query to learn whether the related row exists. That was roughly 100 queries per board load serving no screen. Gone. ## Why we work this way None of this changed a single screen, and there is no new button to announce. But the platform you rely on should get faster and more precise underneath you — continuously, guarded by tests, and verified against production-scale data, not just when something breaks. Measured, not assumed: that is the standard, and it compounds, because every feature we ship from here rides on a faster foundation. Curious what a faster TMS feels like in practice? [Get in touch](https://www.transportial.com/contact-us) — we will show you the board, and it will not keep you waiting. Share[LinkedIn](https://www.linkedin.com/shareArticle?mini=true&url=https://transportial.com/blog/engineering-deep-dive-making-transportial-3x-faster)[X / Twitter](https://twitter.com/share?url=https://transportial.com/blog/engineering-deep-dive-making-transportial-3x-faster) ## More articles [![What is a Transport Management System (TMS)? A Complete Guide](/assets/blog/transport-management-system.webp)GUIDEWhat is a Transport Management System (TMS)? A Complete GuideJune 1, 2026Read more →](/en/blog/what-is-a-transport-management-system)[![Introducing OpenMove: The Transportial Driver App, Now on iOS and Android](/assets/openmove/ipad/openmove-ipad-1.webp)PRODUCTIntroducing OpenMove: The Transportial Driver App, Now on iOS and AndroidMay 10, 2026Read more →](/en/blog/introducing-openmove-the-transportial-driver-app)[![Spizarnia and Transportial Join Forces to Transform Logistics Across Europe](https://cdn.prod.website-files.com/65aabb2dbeaeb88927636a2c/6808f64af892cccd6303e622_Spizarnia-and-Transportial-Join-Forces-to-Transform-Logistics-Across-Europe-featured-img-big.webp)BUSINESSSpizarnia and Transportial Join Forces to Transform Logistics Across EuropeApril 24, 2025Read more →](/en/blog/spizarnia-and-transportial-join-forces-to-transform-logistics-across-europe) ## Get started with Transportial See how Transportial TMS can help you plan, track, and manage your logistics operations. [Get Started Now](/en/contact) --- Canonical page: https://transportial.com/en/blog/engineering-deep-dive-making-transportial-3x-faster