Does sqlite3 read the entire DB into memory before it can be used?

Question

Does sqlite3 need to read the whole sqlite DB into memory before a query can run? IOW, am I limited in how big the DB can be based on available memory?

Stated another way, does SQLite3 work with the db file in the same way a DB like postgres would work with it's DB file(s).... searching specific files/pages for records, that sort of thing. Or does SQLite need to read the entire db file into memory before it can search for specific things?

I've always had the impression it was the later. But I may be wrong about that.

ihightower · Accepted Answer · 2023-11-13 06:33:54Z

3

Unless you're using an in-memory database, no.

Additional Info: it is from this page: https://sqlite.org/forum/info/23f41177ccabed1e

Below excerpt clearly shows the whole file is NOT read.

I suspect there must be more going on than is present in your description. If I run this query on a instrumented build of SQLite that logs all reads/writes and point it at a 16709362688 byte SQLite database, the process reads only 4712 bytes from the file. Indeed it finished very quickly, suggesting not all of the 16gb file was read.

Also, the OP in that page confirms this to be the case:

You are all right - there was indeed an intervening process (gunzipping the db file before it was opened) that was responsible for the delay. On an uncompressed db file the response time is very good. Apologies for the confusion, and thanks all for your help! Johan

So, in conclusion, the whole file is never read, unless it requires decompressing.

edited Nov 13, 2023 at 6:33

ihightower

3,3036 gold badges39 silver badges51 bronze badges

answered Jul 31, 2018 at 21:54

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

daveg Over a year ago

The link to "in-memory database" sounds like a DB that exists only in memory (there is no corresponding file which = the DB). I'm going to augment my original question to be more specific.

ihightower Over a year ago

i have added additional info to above answer, hope this helps @daveg

Collectives™ on Stack Overflow

Does sqlite3 read the entire DB into memory before it can be used?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related