SQLite does not function as a standalone server like Postgres or MySQL. SQLite is a library (written in C) that runs within the process of an application.

SQLite can be configured to run with Write-Ahead-Log (WAL) mode. With this mode enabled, SQLite creates and manages additional "semi-persistent" files: the shared memory file and the WAL file (-shm and -wal, respectively).

The capability that where SQLite files are replicated across a horizontally scaled cluster - exists in various products, including, but not limited to:

Turso a distributed database based on libSQL, an open-source fork of SQLite
rqlite lightweight, user-friendly, distributed relational database built on SQLite
SQLiteCloud a, distributed relational database system built on top of SQLite
LiteFS a distributed file system that transparently replicates SQLite databases.
LiteCluster replicated, leaderless, ACID compliant SQLite
sqlite-rsync - SQLite’s new add-on replication tool

SQLite supports one writer at a time. With WAL mode enabled, SQLite supports concurrency for reads—in other words, reads do not block other reads or writes, and writes do not block reads.

https://blog.wesleyac.com/posts/consider-sqlite - performance

SQLite dedicates a page in its docs to why many small queries are efficient in SQLite. In a nutshell, since SQLite doesn’t need to query data over a network connection, it doesn’t have the same latency concerns from making many small queries as is the case for Postgres and MySQL

SQLite supports a number of flags for modifying its behavior at compilation time.

Problems/disadventage of sqlite

single writes
SQLite natively supports a limited set of ALTER TABLE commands, namely "rename table", "rename column", "add column", and "drop column"". For other, SQLite describes a recipes which includes temporarily disabling referential integrity and copying data to and from a temporary table. But, since schema changes are subject to the same one-writer restriction as your live application, you can imagine, running write-heavy migrations on a large and/or critical table may be very slow and may possibly require planned downtime.
SQLite is weakly typed: You can insert a string into a column defined as an integer. datatypes work in SQLite.
SQLite is not considered fork-safe. The key point is that open database connections cannot be safely carried across a fork.

The list goes on: there’s a whole page in the SQLite docs dedicated to its quirks and gotchas.

Adventages

SQLite is portable - Move it from Windows to macOS, to a Linux CI/CD pipelines on Docker, to a Raspberry Pi, to a VPS, to a serverless function, it will work without any change.

SQLite stores data in a single file that is internally split into pages. By default, when you execute a query that changes data, SQLite will copy the page that is about to be modified. This copy is called a journal file.

This is done to ensure that if something goes wrong during the write operation, the database can be restored to its previous state, enforcing the ACID properties of the SQLite.

When your write query is fully executed, SQLite will delete the previously created journal file.

The journal mode can be enabled by a single PRAGMA instruction and will persist once set on a database:

$ sqlite3 mydb.sqlite "PRAGMA journal_mode = wal"