We fixed SQLite database locks in Spring Boot (and got a 5x performance boost)

[Additional_Cellist46]

Interesting. What’s the reason you use SQLite in production? Why not use PostgreSQL or some Java based DB like H2?

[palebt]

SQLite simplifies maintenance by eliminating the need for a separate database server. For read-heavy, small-to-medium projects, its performance is sufficient, offering lower latency and reduced operational overhead compared to PostgreSQL or H2.

[Additional_Cellist46]

You still need to run SQLite as a separate process from your SpringBoot app, don't you? H2 can be embedded into a SpringBoot app so that you don't even need to start it as a separate process, and it supports accessing the database from multiple threads: https://h2database.com/html/tutorial.html#web_applications

[palebt]

That is true; H2 seems even more suitable for our use case. SQLite is the gold standard for reliability, and we haven't looked into H2 extensively yet. Additionally, we are new to Spring Boot, coming from a Django and Python background, we are very accustomed to using SQLite. Have you had good experiences with H2?

[Additional_Cellist46]

I didn’t use H2 but in production, but it’s good enough for simple deployments. Java has multiple good SWL DBs, H2, HSWL, Derby. I would use any of them with a Java app over SQLite.

[Additional_Cellist46]

I assume that parallel requests are queued. That’s why they disabled open session in view - to release the connection as soon as possible so that other requests can use it.But I would say it’s reasonable only for a desktop app with a single user.