I am building a simple reporting app separate from our usual Firebird Database based app. Since the choice of database is open I though it would be good idea to compare my usual first choice of Firebird to some other open source databases. Since this is a very small app with a small database I did not consider some options that are more cloud/scale focused and many options lack good windows support.
Schema was simplified version of real use and data came from production backup.
Dataset: 3 386 544 rows total with 355 362 rows of interest (10% of total)
I tried variation that used decimal instead of double precision but the results were similar.
Benchmark was conducted using Python 3.8 using the timeit module and were run 20 times with the average being the result.
CREATE TABLE stdetail ( detailno integer, stockno integer, qty double precision, PRIMARY KEY (detailno) ) CREATE INDEX stocknoidx ON stdetail (stockno) SELECT sum(qty) FROM stdetail where stockno = 626
|Firebird 2.5 default config||0.294|
|Firebird 2.5 optimized config||0.176|
|Firebird 4.0 RC1 default config||0.272|
|Firebird 4.0 RC1 optimized config||0.146|
|PostgreSQL 13 default config||0.090|
The default Firebird config is terrible. The optimized config is just changing to DefaultDbCachePages = 100K; other settings with temp/sorting memory did not have measurable impact on this test case.
In absolute terms any of the databases is fast enough on this dataset.
DuckDB of course wins hands down which was expected with its column based storage and vectorized execution; the data loading however was by far also the slowest.
Firebird 4.0 is at least in the same ballpark as SQLite and PostgreSQL.