https://news.ycombinator.com/item?id=47616704

Working with JSON data

Full-text search with FTS5

Strict tables and better typing

Write-ahead logging and concurrency

From Axlerod:
While Write-Ahead Logging (WAL) is generally recommended for most SQLite applications because it significantly improves concurrency (allowing simultaneous readers and writers), there are a few specific scenarios where you might not want to use it:

Network File Systems: WAL mode relies heavily on shared-memory primitives (mmap) to coordinate access between different database connections. These primitives often do not work correctly over network file systems like NFS, SMB, or CIFS.

Single-File Distribution: One of SQLite's main appeals is that a database is a single file. When you enable WAL mode, SQLite creates two additional temporary files alongside your main database: a -wal file and a -shm (shared memory) file. If you need to easily copy, email, or distribute the database as a single file, these extra files can complicate things (though you can mitigate this by forcing a full checkpoint before copying).

Read-Only Databases: If your database is purely read-only (e.g., a static lookup table distributed with an application), WAL mode provides no benefits and adds a tiny bit of overhead. The default rollback journal is slightly more efficient for read-only operations.

Checkpointing Overhead: In WAL mode, writes are appended to the -wal file. Eventually, this data must be transferred back to the main database file in a process called "checkpointing." If your application has a massive, continuous stream of writes, the WAL file can grow very large, and the checkpointing process can cause occasional latency spikes or performance hiccups.

Until I came to read Schwern’s little gem, hinting to turn AutoCommit off. That hit the nail so good that one single hit sufficed.

Text::CSV_XS is extremely fast, using that to handle the CSV should take care of that side of the performance problem.

There should be no need for special bulk insert code to make DBD::SQLite performant. An insert statement with bind parameters is very fast. The main trick is to turn off AutoCommit in DBI and do all the inserts in a single transaction.

https://stackoverflow.com/questions/15331791/dbicsv-implementation-based-on-sqlite/15337369#15337369

The only time you need to consider a client-server setup is:

• Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients.
• If your machine is extremely write busy, like accepting thousand upon thousands of simultaneous write requests every second, then you also need a client-server setup because a client-server database is specifically build to handle that.
• If you're working with very big datasets, like in the terabytes size. A client-server approach is better suited for large datasets because the database will split files up into smaller files whereas SQLite only works with a single file.

Litestream is an open-source project that makes SQLite tenable for full-stack applications through the power of ✨replication✨. If you can set up a SQLite database, you can get Litestream working in less than 10 minutes.