When Gramps was making the transition to Sqlite, we decided to use “pickled blobs” for the hierarchical data for a couple of reasons. First, the goal was to make as small a step as possible away from the previous database default (BSDDB). Also, pickled blobs are small, and it was thought that it would be good to keep the database small.
There are some serious downsides to keeping pickled blobs. The most important is that the pickle format changes with different versions of Python. Pickled object are not backwards compatible. This is bad for data that we want to keep for long periods of time, like genealogical data. Pickled blobs can also pose security issues.
Fast forward to 2024. Storing JSON data in Sqlite is now in common use, and Sqlite has adapted to have some very nice query properties.
- Current: JSON Functions And Operators
- In development: The SQLite JSONB Format
How to query pickled blobs? You can’t. But if we changed pickled blobs to JSON, you can. And we gain some nice future-proofing and security as well.
As to how to make Gramps faster with filters is a different question. It may be time to revisit that issue 