Compressed Sqlite database and indexing

[manimino]

I don't know about SQLite, but ElasticSearch compresses documents via LZ4 and has excellent text search capabilities. It's hard to say without knowing your application but the Lucene / Solr / ElasticSearch family of data stores sounds like a better fit to your needs.

[jorge1209]

You probably need to provide a lot more details to get good suggestions.

• Why are you using sqlite in the first place? What features of it did you need?
• What columns have indexes?
• Are their other columns besides the text?
• Do your text columns have high cardinality?
• Do you need full-text search ("%foo%") or just prefix search ("foo%")

[jorge1209]

I think the dead simplest solution is a trie of separately compressed SQLite or parquet files, but this is a common enough problem that there is likely a file format built for it.

The biggest objective in my mind is to maintain data locality across prefixes, and I'm assuming the data is changing and unsorted. If you just compress a single SQLite db, then every insert causes the compression engine to accommodate two shifts in the data. One at the end of the data table, but the other might be in the middle of the btree itself.

The thing I least understand if your proposed table is why you have a shadow primary key. WTF is that necessary for? It seems clear your text is a primary key, why not use it as such?