Talking to Postgres, MySQL and SQLite from Rust with SQLx and Diesel, and schemas that age well.
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.
pgvector adds a vector type and nearest-neighbor operators to Postgres. From Rust you store embeddings as arrays, index them with HNSW and query with an ORDER BY on distance.
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.
Set acquire, connect and statement timeouts, tell transient errors from permanent ones, retry only what is safe with backoff and jitter, and keep a retry from doubling a write. SQLx and PostgreSQL examples.
Change a live schema without an outage: expand, migrate, contract. Add columns safely, backfill in batches, create indexes concurrently, and order deploys so old and new code both work against the database.
The sqlx::test attribute creates a fresh database for each test, runs your migrations, hands you a pool and drops it afterwards. Learn fixtures, the DATABASE_URL requirement, parallelism and common errors.
Run tests against a real PostgreSQL in a disposable container with the testcontainers crate. Start one per test run, migrate, isolate tests with transactions or databases, and keep CI fast with reuse.
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.
Write the database change and the event it should publish in one transaction, then publish from an outbox table with a relay. Fixes the dual write problem in Rust with SQLx.
OFFSET pagination slows down and skips rows as data changes. Keyset pagination remembers the last key and stays fast. Build it with SQLx, including the tie-breaker and the cursor.
We send one email when a new post is published. No spam.