Skip to content

Benchmarks

FirebirdREST ships a load tool in bench/ so you can measure it against your own hardware and data.

Running it

# 1. a database with the example schema
isql -user SYSDBA -password masterkey -i examples/bookstore.sql localhost:/data/bench.fdb

# 2. ~400k rows: 2,000 authors, 100,000 books, 200,000 book/tag links,
#    5,000 customers, 50,000 orders with 100,000 items (takes a few seconds)
go run ./bench seed -dsn SYSDBA:masterkey@localhost:3050/data/bench.fdb

# 3. start FirebirdREST against it, then
go run ./bench run -url http://localhost:3000 -c 16 -d 10s -token "$JWT_FOR_WEB_USER"

run prints a Markdown table with requests per second and p50/p95/p99 latency for each scenario. -only <text> runs the matching scenarios only. Scenarios marked "rollback" send Prefer: tx=rollback, so the database is unchanged afterwards.

Measure on a quiet machine

The client, FirebirdREST and Firebird all compete for CPU. With Docker, connect to the container's IP rather than a published port: Docker's userland port proxy cost about 30% of throughput in our runs.

Results

The current code compared with the code before the performance work (commit 712a7fb), on a 4-vCPU Intel Xeon 2.1 GHz VM with 16 GB RAM, Firebird 5.0.4 in Docker on the same machine, 16 concurrent clients, pool of 32 connections. The two versions ran side by side and each scenario alternated between them (median of two 4-second runs) so that drift in the shared VM affects both equally. Absolute numbers depend heavily on hardware; compare the change column.

Database reads the request context

The example database has a function that reads request.jwt.claim.sub, so the request context is set on every request.

Scenario before req/s after req/s change after p95
row by primary key 2118 3444 +63% 8.11 ms
page of 100 rows 1244 1731 +39% 14.42 ms
filter + order + limit 25 91 102 +12% 200.71 ms
ilike search, limit 25 1070 1468 +37% 17.82 ms
embed many-to-one (25) 1296 1896 +46% 14.88 ms
embed one-to-many (10) 413 540 +31% 43.98 ms
embed many-to-many (25) 566 666 +18% 39.26 ms
nested 3 levels (5) 431 685 +59% 31.50 ms
aggregate group by 108 116 +8% 200.73 ms
count=exact, 25 rows 157 148 -5% 153.16 ms
computed field + order 1592 2252 +41% 12.68 ms
rpc function 2465 3942 +60% 7.03 ms
insert 1 row (rollback) 1139 1334 +17% 17.54 ms
insert 100 rows (rollback) 62 98 +57% 208.06 ms

Nothing reads the request context

This is what db-request-context = auto does when no view, trigger or routine mentions request. (measured with never, which skips it unconditionally).

Scenario before req/s after req/s change after p95
row by primary key 2036 4377 +115% 6.45 ms
page of 100 rows 1214 1995 +64% 12.64 ms
filter + order + limit 25 94 97 +4% 228.31 ms
ilike search, limit 25 1072 1626 +52% 19.67 ms
embed many-to-one (25) 1234 2253 +83% 12.38 ms
embed one-to-many (10) 410 569 +39% 40.95 ms
embed many-to-many (25) 569 718 +26% 36.09 ms
nested 3 levels (5) 419 734 +75% 32.51 ms
aggregate group by 104 113 +9% 196.07 ms
count=exact, 25 rows 144 156 +8% 169.78 ms
computed field + order 1592 2557 +61% 11.88 ms
rpc function 2266 5108 +125% 5.58 ms
insert 1 row (rollback) 1146 1494 +30% 14.72 ms
insert 100 rows (rollback) 68 101 +50% 190.25 ms

The scenarios that barely changed are limited by Firebird itself: sorting 100,000 rows on an unindexed column (filter + order), grouping the whole table (aggregate) and counting it (count=exact). An index on books(price) or on the filtered columns helps them far more than anything in the API layer.

Compared with PostgREST

The same 14 scenarios against PostgREST 16.5 on PostgreSQL 17.11, with identical data (bench/postgrest/schema.sql and seed.sql create the same rows as bench seed), the same load tool, the same settings on both sides (pool of 32, anonymous role, aggregates enabled, Prefer: tx=rollback honoured) and 16 concurrent clients. FirebirdREST 0.3.0 with Firebird 5.0.4; everything in Docker on the 4-vCPU Xeon VM above, PostgreSQL and Firebird each with default memory settings except shared_buffers=512MB. Each side ran twice, alternating, 4 seconds per scenario; the table shows the median run. bench/postgrest/README.md has the commands.

Scenario FirebirdREST req/s PostgREST req/s FirebirdREST / PostgREST FirebirdREST p95 PostgREST p95
row by primary key 4458 4310 1.03 5.99 ms 6.92 ms
page of 100 rows 1980 2948 0.67 13.20 ms 10.29 ms
filter + order + limit 25 82 180 0.45 280.04 ms 150.48 ms
ilike search, limit 25 1436 1722 0.83 21.26 ms 20.03 ms
embed many-to-one (25) 2068 1300 1.59 12.92 ms 27.42 ms
embed one-to-many (10) 582 46 12.66 42.91 ms 655.88 ms
embed many-to-many (25) 826 706 1.17 32.25 ms 47.57 ms
nested 3 levels (5) 678 32 20.88 40.53 ms 855.70 ms
aggregate group by 96 302 0.32 236.88 ms 82.17 ms
count=exact, 25 rows 139 485 0.29 165.38 ms 53.21 ms
computed field + order 2424 3174 0.76 10.94 ms 8.95 ms
rpc function 4837 4523 1.07 5.65 ms 6.11 ms
insert 1 row (rollback) 1764 3679 0.48 13.26 ms 7.26 ms
insert 100 rows (rollback) 127 → 191 (see below) 2028 0.09 168.88 → 99.74 ms 14.72 ms

Reading the numbers

Requests that cost about the same in both engines (a row by key, a function call, a 25-row embed) land within 10% of each other: the HTTP and SQL-building layers of the two servers cost about the same, and the rest is the database. The differences come from how each server turns a request into SQL, and from the engines:

  • Embedding with paging (one-to-many, nested, 12–21× faster on FirebirdREST). PostgREST embeds in one statement with a lateral join per level, and PostgreSQL evaluates that join for every parent row the OFFSET skips: at offset=1900 the per-author aggregation runs 1,910 times for 10 result rows (178 ms in EXPLAIN ANALYZE; 560 ms for the three-level nest). FirebirdREST pages the parent rows first and fetches each level's children for those rows only, so the cost doesn't grow with the offset. Page 1 of the same request is fast on both; the advantage is in paging through embedded data, and in to-many embeds of parents with many children.
  • Bulk inserts (100 rows, 11× faster on PostgREST). PostgREST inserts the whole body in one statement (INSERT ... SELECT FROM json_populate_recordset). FirebirdREST used to run one INSERT per row, 100 round trips; since this measurement it sends the rows in EXECUTE BLOCKs (two for this body), which cut a single client's request from 20 ms to 6 ms. Under 16 concurrent clients the gain is only 127 → 191 req/s, because Firebird serializes concurrent inserts into the same table: the time spent in the database grows from 5 ms alone to 75 ms with 16 clients, for the old and the new code alike (a plain PSQL loop inserts 200,000 rows/s on this machine when nothing else writes). That ceiling is the engine's, and the benchmark, with every client inserting into one table, measures it. Single-row inserts are 2× slower than PostgREST's mainly through Firebird's commit cost.
  • Counting and aggregating 100,000 rows (count=exact, group by, 3× faster on PostgREST) and sorting 100,000 rows without an index (filter + order, 2×). These requests scan the whole table on both engines; PostgreSQL scans faster. FirebirdREST's count=planned/estimated avoid the scan when an estimate will do.
  • Pages of 100 rows and computed fields (0.7–0.8× at the time of this table). Counting the wire packets showed why: a read cost 7 round trips to Firebird, of which only two were the query (transaction start, the request context block plus the row-count request the driver added to it, execute, fetch, cursor close, commit). Since this measurement, reads reuse a read-only transaction per connection and the context block runs without the row-count request, which leaves 4 round trips. On the same machine and data, FirebirdREST went from 3,900 to 5,100 req/s on a row by key, 4,200 to 6,600 on the function call, 2,340 to 2,900 on the computed field (parity with PostgREST), 1,800 to 2,460 on the 25-row embed and 1,800 to 2,300 on the 100-row page (0.78× of PostgREST: the rest is the driver's per-row decoding). The next step is in the driver: sending the cursor close together with the following request, as fbclient does, would make it 3.

The absolute numbers are for this machine; the ratios are what to take away. Firebird's engine is generally slower than PostgreSQL's at scanning and aggregating large tables, so an API that does mostly keyed reads, embeds and small writes compares well, and one that counts or groups large tables on every request does not, whichever REST layer is in front.

What was optimized

Profiling (admin-pprof = true) showed that time went into database round trips and Firebird compiling statements, not into Go code:

  1. Prepared statement cache. Firebird compiles every statement on prepare, which costs about as much as running a small query. FirebirdREST now keeps up to db-prepared-statements (default 256) compiled statements per role and reuses them across requests. A cache miss runs the query directly and prepares it in the background, so a busy pool can't deadlock.
  2. Request context only when used. Setting the request.* variables was a separate round trip on every request, taking 27% of CPU in the profile. At schema load FirebirdREST checks whether any view, trigger, procedure, function, package or default expression mentions request.. If none does, the step is skipped. When it is needed, all variables (and the db-pre-request procedure) go in one EXECUTE BLOCK.
  3. Faster JSON encoding. Responses are written by an encoder specialised for result rows (no reflection, 2 allocations for a 100-row page) instead of encoding/json, which was 25% of CPU on embed-heavy requests.
  4. Bulk inserts reuse one prepared INSERT for all rows with the same columns.

Prepared statements and DDL

Firebird keeps a lock on every table a prepared statement uses. ALTER TABLE and CREATE OR ALTER VIEW work while statements are cached, but DROP TABLE (or dropping a view, procedure, ...) waits until the statements using it are closed. Cached statements unused for db-prepared-statements-idle seconds (default 30) are closed automatically, and a schema reload closes all of them. Before dropping an object that is in active use, reload first:

curl -X POST localhost:3001/reload     # admin server, or: kill -USR1 <pid>

or turn the cache off with db-prepared-statements = 0.