Simple aggregate benchmark – Firebird vs DuckDB,SQLite,PostgreSQL

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

Results

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
SQLite 3.31.1	0.138
DuckDB 0.2.5	0.026
PostgreSQL 13 default config	0.090

times in seconds

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.

Conclusion

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.