Why are my read speeds so bad using sqlite?

Question

I know very little about databases and even less about how to optimize them, but I have a problem which calls for a database so here I am...

I created a sqlite3 database using the following script:

sqlite3 my.db <<EOF
create table bpe_lookup (id integer primary key, bpe text);
create unique index idx_id on bpe_lookup(id);
.separator "\t"
.import my_data.tsv bpe_lookup
EOF

my_data.tsv contains about 34M records and is fairly small -- approximately 11GB and the resulting DB file is 12GB. My machine has hundreds of GBs of RAM and 40 CPU cores, so it should fit comfortably in memory when indexed.

I have millions of ids corresponding to the PK in a text file that I want to throw against the database, returning bpe if the record exists and returning "default" otherwise. My file for doing this is:

import sqlite3

def execute_query(cur, ids):
    res = cur.execute("select id, bpe from bpe_lookup where ids in (%s)" % ("?," * len(ids))[:-1], ids)
    # create mapping of found ids to bpe and print for each id,
    # print "default" if it wasn't in the db
    lookup = dict(res)
    for id_ in ids:
        print(lookup.get(id_, "default"))

if __name__ == "__main__":
    conn = sqlite3.connect("my.db")
    cur = conn.cursor()

    # execute queries with 1k ids at a time
    batch_size = 1024

    with open("ids.txt") as ids:
        lines = []
        for line in ids:
            lines.append(line)
            if len(lines) >= batch_size:
                execute_query(cur, lines)
                lines = []
        if len(lines) > 0:
            execute_query(cur, lines)

My thought is that batching reads will probably help, but I'm not sure. There are also very likely other optimizations that I'm missing. I'm not particularly interested in style advice unless it impacts performance. I'm only getting hundreds of KBs per second of writes from stdout so clearly I'm doing something very wrong on the read side. Please help. :-)

Answer 1

At this scale I would not trust sqlite to perform reasonably, and would be motivated to switch to PostgreSQL. But in the more immediate term:

Rather than load your IDs from a file into Python's memory and run a post-query filtration step, I would sooner load those IDs into a temporary table where they will be in SQLite's memory and then do a join. This is what joins are for, and the database should be better at it than Python is.