Ed‑Fi API 8 Performance: How It Compares to the ODS API

note

This post reflects the upcoming Ed‑Fi API 8.1 compared against the ODS API v7.3.2.

Overview

Since the move to a relational backend, the most common question from the community has been simple: how does it perform against the ODS API? We ran a shared test suite against both APIs, on both PostgreSQL and SQL Server, using the same dataset and request mix.

The short version: on PostgreSQL, Ed‑Fi API 8.1 comes out ahead on every workload we tested. On SQL Server, DeepPaging and FilteredRead are close to a tie, and Volume (writes) is behind. The reason is narrow and well understood: the change-version stamp triggers, the database triggers Ed‑Fi API 8.1 uses to keep each record's _etag, last-modified timestamp, and change-tracking version current on every write. We can point to exactly how much they cost and the work already underway to reduce it.

What This Means for Implementers

• PostgreSQL: Ed‑Fi API 8.1 wins on writes, paging, and filtered reads against ODS API v7.3.2, on the same hardware and dataset.
• Where the win comes from: mostly a database CPU advantage on reads. The write path is faster too, but that doesn't mean it's cheaper to run across the board (more on that below).
• SQL Server: near ties on paging and filtered reads; behind on writes, traced to the change-version stamp triggers, a mechanism we've measured precisely and already scoped a fix for (see below).
• Read this as a snapshot, not a guarantee for your deployment. See the caveats in the "How We Tested" section below.

How We Tested

We used the Suite-3 performance-testing suite against the Northridge dataset, on fixed-performance (non-burstable) virtual machines, restoring the database before every run. Three workloads:

• Volume: 10 concurrent users issuing a mix of POST/PUT/DELETE (40% deletes, a higher share than most production traffic sees) for 30 minutes; measures write throughput.
• DeepPaging: sequential 500-row offset paging across the catalog; measures sustained read cost on large collections.
• FilteredRead: GET requests using query parameters, returning up to 100 matching records; measures per-request read cost.

Each application/configuration/workload combination is a single run, not an average of several. We call this out explicitly because run-to-run variation exists, and a handful of single-digit-percent results in this post should be read with that in mind. Our headline PostgreSQL numbers use a tuned PostgreSQL configuration (larger shared buffers, larger work_mem, longer checkpoint intervals) rather than out-of-the-box defaults, since that's closer to how the community actually runs PostgreSQL in production.

The numbers in this post reflect two database changes made since the v8.0 release. Removing a redundant DISTINCT from the authorization views used on every read is committed to the 8.1 codebase. The foreign-key change is still being finalized: these numbers were measured with the per-resource foreign keys to the internal document table removed entirely, but the team is now testing a narrower alternative that keeps the constraint and switches its delete behavior from CASCADE to NO ACTION, which early SQL Server testing shows captures nearly the same gain while leaving the database's own referential-integrity check in place; PostgreSQL testing of that alternative is underway. Either way, the application already deletes in the correct order, so the constraint's cascade behavior wasn't protecting against anything real traffic triggers.

PostgreSQL Results

For PostgreSQL, the results are straightforward: Ed‑Fi API 8.1 produced better overall results than ODS API across all three workloads in this test campaign.

Volume: Ed‑Fi API 8.1 sustains 1,378 successful requests/sec against ODS API's 1,305, about 5.6% higher throughput on the same write mix.

DeepPaging: Ed‑Fi API 8.1 averages 379 ms per page against ODS API's 573 ms, about 34% lower latency across the whole run.

FilteredRead: Ed‑Fi API 8.1 averages 17.0 ms per request against ODS API's 29.1 ms, about 1.7x faster.

These are whole-run averages, and they don't tell the whole story on every resource. On a handful of resources where ODS API's authorization cost is naturally low and flat regardless of page depth (courseTranscripts and studentSectionAssociations among them), its per-page cost can still beat Ed‑Fi API 8.1's at very deep offsets, even though Ed‑Fi API 8.1 wins the run overall.

Where the Work Happens

The clearest, most consistent advantage is on the read path: Ed‑Fi API 8.1 uses significantly less database CPU per unit of read work than ODS API, on both DeepPaging and FilteredRead.

The same is true one layer up, on the web tier that serves each request. ODS API's aggregate serialization and materialization cost several times more CPU per unit of read work than Ed‑Fi API 8.1's, regardless of which database engine sits behind it.

The write path improved too. That's the Volume throughput gain above, but it's worth being precise about why, and where the gains don't (yet) show up. The throughput and latency improvement on writes comes primarily from eliminating redundant database work. Requests spend less time inside database transactions now that a set of redundant foreign-key checks has been removed. Those checks were a safety net rather than a correctness requirement: the application already deletes records in the correct order, so removing them did not change data integrity guarantees.

That did not translate into lower operating cost across the board. On Volume, Ed‑Fi API 8.1 uses slightly more database CPU than ODS API per 1,000 requests, writes more than twice as many write-ahead-log records per request, and its PostgreSQL database is meaningfully larger (7.19 GiB against 4.55 GiB in our test, about 58% larger). Most of that difference comes from the internal identity-management tables and their supporting indexes, storage that ODS API does not carry.

The write path is faster to respond to, but not cheaper to run. Reducing database size and write-ahead-log volume is part of what's being worked on next, described in What We Are Improving Next.

SQL Server Results: An Early Look

Our SQL Server results come from a more recent build than the PostgreSQL numbers above, but both database changes described in "How We Tested" apply here too.

One more environment note: the SQL Server host mix differs from PostgreSQL's, a slower database host and a larger web host, so comparing across engines is directional only. Comparing Ed‑Fi API 8.1 to ODS API within SQL Server stays sound, since both ran on the same hosts.

The results split by workload rather than pointing one direction:

Volume

ODS API leads by 17.4% (1,217.5 requests/sec against 1,037.2 for Ed‑Fi API 8.1). Most of that gap traces to the change-version stamp triggers described above: with them disabled, and nothing else changed, Ed‑Fi API 8.1 reaches 1,213.7 requests/sec, a dead tie with ODS API. That isn't a configuration anyone can actually run, since disabling them breaks change tracking, but it's a precise ceiling for what a fix along these lines is worth. Even at that ceiling, Ed‑Fi API 8.1 still uses about 1.5x ODS API's database CPU per request to hit the same throughput, so closing this gap means better latency and throughput, not a cheaper database.

We also saw a handful of brief, multi-second stalls under sustained concurrent writes, close to a worst-case delete pattern unlikely in typical production. SQL Server's READ_COMMITTED_SNAPSHOT setting measurably reduces this, and Ed‑Fi API 8.1's own provisioner already turns it on automatically. For balance: ODS API hit its own concurrency-related failures here too, from an unrelated cause in its own change-tracking triggers.

DeepPaging

The two applications are now close, within 3.5% (493 ms per page for Ed‑Fi API 8.1 against 477 ms for ODS API). The gap traces to Ed‑Fi API 8.1's paging plan growing with offset rather than staying flat, most relevant to deep bulk-export paging, not typical shallow reads.

FilteredRead

Still essentially tied with ODS API (89 ms per request for Ed‑Fi API 8.1 against 88 ms for ODS API). Both pay a SQL Server compilation cost here, each for a different reason, and each is several times slower than its own PostgreSQL number.

Efficiency, Even Where Latency Ties

Latency being close doesn't mean the database-CPU advantage seen on PostgreSQL disappears; it's just a smaller margin here: Ed‑Fi API 8.1 still uses meaningfully less database CPU per unit of read work on SQL Server too.

The web-tier gap is wider still, and it's the largest engine-independent advantage in this comparison: ODS API's web tier costs 5.2x as much CPU per 1,000 DeepPaging pages and 3.1x per 1,000 FilteredRead requests. That cost is ODS API's own serialization and materialization work, not the database engine, so it shows up almost identically on PostgreSQL.

Database Size

One place SQL Server flips the story from PostgreSQL: database size. Ed‑Fi API 8.1's database was larger than ODS API's on PostgreSQL (see "Where the Work Happens" above); on SQL Server it's about 42% smaller (12.5 GiB against 21.4 GiB in our test). That comes down to index width: SQL Server copies a table's full clustering key into every non-clustered index row, and ODS API clusters its tables on their natural keys, often several wide string columns, so that cost is duplicated across every index on the table. Ed‑Fi API 8.1 clusters on the narrower surrogate DocumentId instead, keeping its non-clustered indexes smaller. That advantage should widen further once the storage work described next lands, since it removes a table and its indexes rather than adding any back.

What We Are Improving Next

Targeted for 8.1

Five things worth knowing here: one is already reflected in the numbers above, though its final shipping form is still being decided, and four are still ahead, three from the SQL Server test campaign and one from the PostgreSQL storage story:

• Foreign-key change, still being finalized. These numbers were measured with the per-resource foreign keys removed entirely, as described in "How We Tested," but the team is now testing a narrower CASCADE-to-NO ACTION change that keeps the referential-integrity check in place and captures nearly the same gain on SQL Server, with PostgreSQL testing underway. Whichever form ships, it's expected in 8.1.
• Fixed-size SQL Server query parameters. The FilteredRead compile cost above is caused by SQL Server building a new query plan for nearly every request, because string parameters are currently sized to the exact length of each value instead of a fixed size. Pinning parameter sizes is expected to remove most of that cost and bring FilteredRead's SQL Server latency down substantially.
• A narrower write shape for unchanged identities. This one addresses a PUT-specific cost we haven't discussed above: some PUT requests currently ask SQL Server to compile an expensive cascade check even when a resource's identity hasn't actually changed. Skipping the identity columns in that case when nothing changed is expected to meaningfully cut PUT latency on the handful of resources affected, with little effect on overall throughput.
• Evaluating a query hint for deep paging. The plan behind Ed‑Fi API 8.1's DeepPaging cost on SQL Server, described above, has a candidate database hint that may help. An earlier, narrower attempt at this had mixed results, so we're evaluating it more rigorously across page depths before shipping anything, not treating it as a guaranteed fix.
• Reduced database size and write-ahead-log volume. This is the database size and write-ahead-log story called out in "Where the Work Happens": We're improving database size and write efficiency. It should help both engines: narrowing the PostgreSQL gap described above, and widening the SQL Server advantage described in "Database Size" above, since the same table and its indexes go away on both.

Targeted for 8.2

The largest opportunity is a redesign of the change-version stamp triggers themselves, the mechanism behind the Volume ceiling described above. It's a bigger, more architecturally involved change than the items above, so we're targeting the 8.2 release for it rather than 8.1.

Conclusion

On PostgreSQL, Ed‑Fi API 8.1 delivered better overall results than ODS API across all three workloads we tested, with the largest gains coming from a substantially more efficient read path. The write-path win is real but narrower than the read-path one, and it isn't yet a cost-efficiency win. That's worth knowing if you're capacity planning rather than just latency planning; we're targeting 8.1 to close it, though that work is earlier-stage than the SQL Server fixes below.

On SQL Server, two of three workloads are now close to a tie, and the one that isn't, writes, traces to the change-version stamp triggers, with a measured ceiling for fixing it. Three scoped efforts targeting these results are expected in 8.1; the largest opportunity is a bigger undertaking we're targeting for 8.2 rather than promising sooner than we can deliver it.

As with any benchmark, this one is a snapshot: one dataset, one hardware profile, one run per configuration. It's a reasonable guide, not a guarantee for your specific deployment, workload, or scale. We'll keep publishing as the SQL Server picture matures and as this work lands.