Are SQLite reads always hitting disk?

I have a Pylons application using SQLAlchemy with SQLite as backend. I would like to know if every read operation going to SQLite will always lead to a hard disk read (which is very slow compared to RAM) or some caching mechanisms are already involved.

• does SQLite maintain a subset of the database in RAM for faster access ?
• Can the OS (Linux) do that automatically ?
• How much s开发者_如何学Cpeedup could I expect by using a production database (MySQL or PostgreSQL) instead of SQLite?

---

1. Yes, SQLite has its own memory cache. Check PRAGMA cache_size for instance. Also, if you're looking for speedups, check PRAGMA temp_store. There is also API for implementing your own cache.

2. The SQLite database is just a file to the OS. Nothing is 'automatically' done for it. To ensure caching does happen, there are sqlite.h defines and runtime pragma settings.

3. It depends, there are a lot of cases when you'll get a slowdown instead.

---

How much speedup could I expect by using a production database (Mysql or postgres) instead of sqlite?

Are you using sqlite in a production server environment? You probably shouldn't be:

From Appropriate Uses for Sqlite:

SQLite will normally work fine as the database backend to a website. But if you website is so busy that you are thinking of splitting the database component off onto a separate machine, then you should definitely consider using an enterprise-class client/server database engine instead of SQLite.

SQLite is not designed well for, and was never intended to scale well; SQLite trades convenience for performance; if performance is a concern, you should consider another DBMS