Tag
7 posts
Both are excellent, and the difference is architecture. SQLite is a library inside your process, and Postgres is a server your process talks to. Pick by how many writers and machines you need.
Higher isolation levels prevent anomalies by aborting transactions that conflict. Your code must expect those aborts and retry the whole transaction.
Take a slow SQLx query, run EXPLAIN ANALYZE, and read the plan: sequential scan versus index scan, row estimates, sort and join nodes. Add the right index and confirm it is used, including partial and composite ones.
Build a reliable job queue in a single table: claim rows with FOR UPDATE SKIP LOCKED, mark them done in the same transaction, retry failures with backoff, and know where this stops scaling.
Choose between PostgreSQL full text search (tsvector, GIN, ts_rank) and an embedded Tantivy index. See working queries from SQLx and a Tantivy schema, and when each is the right size of tool.
Use PostgreSQL LISTEN and NOTIFY from Rust with SQLx PgListener: send notifications from SQL or triggers, receive them in a task, handle reconnects and know the limits.
Store and query JSON in PostgreSQL JSONB from Rust: sqlx Json<T> typed columns, containment and path operators, partial updates, GIN indexes and when a column beats JSON.