Pre-compute recurring answers
Move repeated file reading out of the request path and reuse stored statistics across later investigations.
Engineering data architecture · technical ownership
Flight-Test
Intelligence
A PostgreSQL analytical platform that turns recurring questions about complete helicopter flight histories into indexed queries, backed by automated ingestion and a shared Python access package.
Late 2022first version delivered
400–500flights behind the initial bottleneck
~30,000Event Markers at initial scale
~800channels in the initial design
01 — The engineering bottleneck
The flights were indexed. Their signal history was not.
When I joined Flight Test Data Analysis in June 2022, a small SQLite database already linked flight numbers to recorded files and basic metadata. It was a useful catalogue, but it could not answer questions such as where a signal reached its historical maximum or whether a threshold had ever been exceeded.
Each request still required opening every relevant flight file, loading the requested channels, calculating the result and repeating the process across hundreds of flights. The runtime grew sharply with the number of signals: substantial Python extractions often took an estimated four to five hours, particularly large ones could approach ten, and DIAdem analyses were sometimes left running overnight.
Those are retrospective operating estimates, not benchmark measurements. The important constraint was that every new question rescanned the same raw history.
02 — Self-initiated solution
Store reusable engineering statistics instead of reopening every file.
Around September–October 2022, only a few months after joining as a full-time engineer, I independently began designing an analytical database. It was not a formally assigned IT or management project; it came from repeatedly seeing the same engineering bottleneck. The first version was implemented by late 2022.
Automated ingestion pre-computes signal statistics and stores their flight and Event Marker context in PostgreSQL. Historical extrema, threshold exceedances and their exact locations can then be retrieved without rereading the complete campaign.
Engineering modelFlight → Event Marker → ATA-organised system data
Event Markers come from flight-test operations: the crew marks the start and end of test conditions in the recorded data. The database uses that context without limiting historical searches to stabilised test points.
03 — Architecture
Why PostgreSQL, and why this data model.
I first considered extending SQLite. At the time, however, one prototype already represented roughly 400–500 flights, about 30,000 Event Markers and around 800 channels. Storing pre-computed statistics at that scale called for a larger, long-lived relational system.
After researching the options, I selected PostgreSQL as an open-source, mature platform suited to the expected volume without adding unnecessary complexity. This was my technical decision, not the outcome of a formal architecture-board process.
The schema follows the engineering domain. Flights contain operational Event Markers; below them, indexed tables are divided by aircraft system and ATA chapter. This avoids a single monolithic table and keeps data aligned with the subsystem boundaries engineers use in analysis.
04 — Analytical workflow
From processed flight to reusable history.
- 01
Processed flight and recorded Event Markers become available
- 02
Ingestion reads the available engineering channels
- 03
Signal statistics are calculated with flight and Event Marker context
- 04
Results are inserted into indexed, ATA-organised PostgreSQL tables
- 05
Rule-based criteria can label hover, climb, descent and forward-flight conditions
- 06
Engineers retrieve selected flights and signals through Python or SQL
The ingestion runs within the broader Flight-Test Data Operations infrastructure. This database is the analytical history built from that processed data.
05 — Key engineering decisions
Preserve operational context
Use crew-recorded Event Markers to locate test conditions while retaining the complete signal history beyond stabilised points.
Split data by engineering domain
Organise subsystem data by ATA chapter so the relational model remains navigable as campaigns accumulate.
Classify with explicit rules
A decision-tree / rule-based layer uses stability, parameter and flight-state criteria; it is engineering classification, not a machine-learning claim.
06 — Scale and performance
Verified scopes, not inflated totals.
The figures below describe the initial design context and one verified later analysis. They are not presented as current database-wide totals.
- 400–500 flights
- ~30,000 Event Markers
- ~800 channels
- 50–100 typical
- ~30 on some short flights
- 150–200 on some complex flights
Observed workflow
- Beforehours or overnight
- Historical locationsreturned in seconds
- Temperature study~10–15 s per sensor
Measurement boundary: there is no universal before/after benchmark. Previous runtimes varied with the number of flights and channels, and the historical values are retrospective estimates.
For a certification-related fuselage-temperature study covering roughly 900–1,000 flights across two prototypes, one sensor could be retrieved in about 10–15 seconds. Reproducing that specific analysis through the former DIAdem-style workflow was estimated to require roughly a four-day long weekend; this was not a formal benchmark.
07 — Operational stewardship
Built to remain usable beyond its author.
Long-term ownership covers the data model, ingestion, access, recovery and the anomalies that appear in real flight-test histories.
Access
Separate read and write credentials are defined; application credentials live in uncommitted .env files.
Write control
Python write operations are further restricted according to the authenticated workstation and user context.
Recovery
Automatic evening backups are part of the wider data-operations infrastructure, with approximately the 20 most recent backups retained.
Maintenance
Schema evolution, ingestion issues, access configuration, PostgreSQL maintenance and upgrades remain part of technical ownership.
The controls are deliberately described at their implemented level: database credentials, application checks and operational backups—not enterprise SSO or a separate IAM platform.
08 — Python access and adoption
A personal tool became shared engineering infrastructure.
For roughly the first year I was the primary user, and faster historical retrieval caused more extraction requests to be routed directly to me. That success created a new bottleneck. From early 2024, other Flight Test Data Analysis engineers increasingly began querying the system themselves.
I turned the access tooling into a documented internal Python package in the team Git repository. It is installed with editable pip, follows the shared repository update workflow, and provides simple functions for selecting signals and flights and returning analytical data directly to Python. I also provided SQL examples, PostgreSQL / pgAdmin guidance and access support.
Direct users are members of Flight Test Data Analysis. Design Office and other engineering teams usually request an extraction and receive CSV data, plots, extreme-value tables or supporting time histories rather than querying PostgreSQL themselves.
09 — Engineering use
Historical questions became routine engineering work.
Engineers use the database to find extrema, threshold exceedances, outliers and the exact flight and Event Marker where a condition occurred. It can also locate flights from a remembered signal value and provide the detailed time history needed to investigate an unusual point.
In the fuselage-temperature example, rule-based manoeuvre labels were used to compare approximately 10–15 sensors by manoeuvre type across roughly 900–1,000 flights, identify extrema and outliers, and retrieve time histories for points needing explanation. The results supported evaluation of thermal exposure and how representative a condition was in the aircraft usage spectrum.
The database is an analytical source for certification-related investigations. Flight Test Data Analysis supplies datasets, plots and outlier investigations; the Design Office or system engineers remain responsible for the aircraft systems, engineering conclusions and certification reports.
10 — My role
Technical owner from the first design onward.
I identified the bottleneck, researched the architecture and implemented the first PostgreSQL version in late 2022. I designed the schema and indexing approach, built the ingestion logic and Python access package, and introduced the system to colleagues through documentation, examples and hands-on support.
I continue to own database architecture, schema evolution, ingestion, maintenance, access configuration, backups and restore readiness, PostgreSQL upgrades, user support and investigation of data anomalies. The system has evolved under that ownership from an individual solution into recurring team infrastructure.
The boundary matters: this was a self-initiated engineering and data-architecture project, not a project-management assignment. I own the analytical platform; Design Office and system engineers own their certification reports and the conclusions they draw from the supplied analysis.
11 — Engineering lessons
Remove repeated work from the request path
Pre-computation changed the access pattern that caused the delay instead of repeatedly optimising the same file scan.
Model the engineering domain
Flights, operational Event Markers and ATA systems provide more useful structure than a generic table of detached measurements.
Adoption needs an access layer
The shared Python package, documentation and direct support turned a technically useful database into team infrastructure.