Adding DuckDB as the OLAP DB/ Read-only Replica

[tomtom215]

Hey all,

First off, thank you for this fork and keeping the project alive!

I know new features aren't really being added, but since more and more people have SQLite DB's that are growing to over 100k detections, the Ui can struggle with some of the queries. This is why I wanted to propose added DuckDB.

DuckDb is essentially the analytical DB counterpart to SQLite which is used for transactions.

My idea would be to keep everything as is as far as recording and analysis, but to have DuckDb with the SQLite extension reading from the birds.db SQLite DB for all of the Ui queries.

Ideally this wouldn't require too much rework other than an addition to the the installation script to include DuckDb and then adjusting the Ui to read from DuckDb instead of SQLite directly.

Unfortunately I lack the coding skills to submit a PR for this, but I have colleagues that have had great success with DuckDb in other projects, and after dealing with slower queries on my nearly 200k detection dataset while analysis is running, I thought this was at least worth proposing in the discussions.

Thanks again!

[tomtom215]

Would be curious if @lloydbayley has any opinions as I saw he is running a larger DB

[lloydbayley]

My apologies. I did read this a week ago and was digesting it. Seems it got stuck on the way down. :) I have approx 382k rows in my database and haven't really noticed any remarkable speed differential. I had a bit of a look in to DuckDB and it says it could be better but I'm one of those "if it ain't broke, don't fix it" types.

You mentioned you weren't up to coding it into a PR, however, have you been doing trials of this manually on your own?
If so, how are the statistics coming out?

[tomtom215]

No need for apologies! I just saw you've been active here and seem to push to optimize things where possible. I mainly got inspired from seeing colleagues use DuckDb in my day job in places where they used to get a bottleneck with SQLITE or over complexities with Pandas operations in data pipelines.

As far as stats, I'm coming up short on an easy way to profile SQLite queries, but I did dump by database via the webUi and exported it to a CSV file. From there I loaded it into a local DuckDB instance which has EXPLAIN ANALYZE making it easy to see how long queries run.

My DB has 200,872 detections, so I ran two basic queries to get some timing. Just basic feeling, it felt "snappier" but I'm still looking for an easy way to get the same query time from the SQLite query.

EXPLAIN ANALYZE SELECT * from detections;
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT * from detections;
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││        Total Time: 0.0127s        ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│      RESULT_COLLECTOR     │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         SEQ_SCAN          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         detections        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            Date           │
│            Time           │
│          Sci_Name         │
│          Com_Name         │
│         Confidence        │
│            Lat            │
│            Lon            │
│           Cutoff          │
│            Week           │
│            Sens           │
│          Overlap          │
│         File_Name         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         EC: 200872        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           200872          │
│          (0.02s)          │
└───────────────────────────┘

And Slightly More complex:

EXPLAIN ANALYZE SELECT DISTINCT(Com_Name), COUNT(*) FROM detections GROUP By Com_Name ORDER BY COUNT(*) DESC;
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT DISTINCT(Com_Name), COUNT(*) FROM detections GROUP By Com_Name ORDER BY COUNT(*) DESC;
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││        Total Time: 0.0098s        ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│      RESULT_COLLECTOR     │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             0             │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│          ORDER_BY         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          ORDERS:          │
│     count_star() DESC     │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            168            │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             #0            │
│             #1            │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            168            │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          Com_Name         │
│        count_star()       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            168            │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             #0            │
│        count_star()       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            168            │
│          (0.01s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          Com_Name         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           200872          │
│          (0.00s)          │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         SEQ_SCAN          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         detections        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          Com_Name         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         EC: 200872        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           200872          │
│          (0.00s)          │
└───────────────────────────┘ 

However I did notice that there seemed to be an issue with the date time format for the current SQLite setup that prevented me from just reading the SQLite file directly from DuckDB instead of doing an export and import:

SELECT * FROM sqlite_scan('birds-may26.db', 'detections');
Conversion Error: timestamp field value out of range: "10:25:53", expected format is (YYYY-MM-DD HH:MM:SS[.US][±HH:MM| ZONE])

I even tried loading the ICU extension for DuckDB that was mentioned in one forum, but I still faced errors, so I decided to export and import.

Please let me know if there's a more complex query you want me to profile on this dataset in DuckDB or if you have any good methods of getting similar timing profiles on queries in SQLite

[Nachtzuster]

@tomtom215 Just a thought, but it could very well be that since BirdNET's DB is so simple, as are the queries, that DuckDB would not have the opportunity to really stretch it legs

[lloydbayley]

That's kind of where I was leaning as well. I have given it a good chew-over as I don't like to dampen spirits of a good idea without thinking about it properly.

[tomtom215]

I think it's a fair deduction @Nachtzuster and @lloydbayley thanks for giving it a thought. I also agree with keeping things simple if they are working, but thought this was at least worth a discussion with people that have been using this project and have continuously growing DB's.

I'll close this out as I think we've reached a conclusion, but I wanted to say thanks again for the discussion and the on-going efforts with this project!

[lloydbayley]

It was absolutely worth discussing. In reality, it could still have a place in the future, depending on database growth and other things...