database optimization

Bilingual

SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
2 months ago
- SQLite can be used in production for low-latency app servers by eliminating network overhead through local storage.
- Enable Write-Ahead Logging (WAL) mode with PRAGMA journal_mode=WAL to allow concurrent reads and writes.
- Manage checkpointing explicitly (e.g., PASSIVE or RESTART) to prevent WAL file growth and latency spikes.
- Use PRAGMA busy_timeout (e.g., 5000ms) to handle SQLITE_BUSY errors gracefully with exponential backoff.
- Begin write transactions with BEGIN IMMEDIATE to avoid deadlocks and lock escalation issues.
- Optimize memory with cache_size (e.g., -64000 for 64MB) and mmap_size (e.g., 2GB) for memory-mapped I/O.
- Custom VFS layers (e.g., Litestream, LiteFS) enable replication and durability in cloud environments.
- A production-ready configuration includes WAL, NORMAL synchronous, busy timeout, cache size, mmap, foreign keys, journal size limit, and incremental auto-vacuum.
- SQLite is suitable for read-heavy workloads under a few hundred GB, offering high performance and simplicity.
SQLAlchemy 2 In Practice - Chapter 7: Asynchronous SQLAlchemy
2 months ago
- SQLAlchemy 1.4+ supports asynchronous programming via asyncio for both Core and ORM, requiring async-compatible database drivers (e.g., aiosqlite, aiomysql, asyncpg).
- Asynchronous code must avoid implicit lazy loading; relationships should use eager loaders (like 'joined' or 'selectin') or explicit loaders (like 'write_only') to prevent function coloring issues.
- Sessions and engines are replaced with asynchronous equivalents (create_async_engine, async_sessionmaker), and 'expire_on_commit' is disabled to avoid implicit database refreshes.
- Alembic migrations require an async template ('-t async') and configuration of batch mode for compatibility.
- After a session flush, uninitialized list-style relationships can trigger lazy loading; solutions include initializing relationships explicitly or using the WriteOnlyMapped loader.
- Query execution methods (session.execute, session.scalar, etc.) must be awaited, and stream()/stream_scalars() are recommended for efficient async iteration over large result sets.
The startup's Postgres survival guide
2 months ago
- Start with good schema design using identity columns, primary keys, timestamptz, and foreign keys with cascading deletes for low-volume tables.
- Write performant queries by using indexes (primary keys, unique constraints) to avoid sequential scans; use compound indexes with ORDER BY columns last.
- Keep transactions short, lock only necessary rows, and use CREATE INDEX CONCURRENTLY to avoid blocking writes.
- Use connection pooling (pgbouncer or in-memory pools like pgxpool) to manage connections efficiently.
- Understand the query planner: use EXPLAIN ANALYZE to debug slow queries, keep table statistics updated via autovacuum.
- Batch writes to improve throughput; tune autovacuum settings to prevent dead tuple bloat and transaction ID wraparound.
- Use advanced features: FOR UPDATE SKIP LOCKED for queue/lease operations, partitioning for time-series data, and triggers for large table migrations without long transactions.
Snowflake's string min/max is truncated to ~40 chars, and it's undocumented
2 months ago
- Snowflake prunes micro-partitions based on per-column min/max metadata, but for VARCHAR values, only the first ~40–41 characters are tracked, leading to potential full table scans if distinguishing bytes lie beyond this limit.
- The undocumented 40-41 character limit for VARCHAR min/max pruning was measured via black-box testing in Snowflake 10.25.101, distinct from the documented 5-6 byte limit for cluster-key metadata.
- Pruning effectiveness degrades when the distinguishing part of a string starts past ~41 characters, causing point lookups to silently become full table scans.
- Workarounds include clustering on a substring that includes the distinguishing bytes, storing the unique part first, or using alternative encoding strategies.
- Snowflake's approach contrasts with Databend, which allows users to configure the truncation length per-column via STATS_TRUNCATE_LEN.
The four horsemen behind Postgres outages
2 months ago
- Common PostgreSQL outages are caused by VACUUM and transaction ID wraparound, where deleted rows accumulate and 32-bit IDs lead to database shutdown if not reclaimed.
- Connection limits and process-based parallelism create scaling issues, requiring restarts or tools like PgBouncer, while a thread-based model in pgrust offers a safer alternative.
- Bad query plans can drastically slow queries, with limited control over the planner, unlike databases that allow hints; pgrust aims to build an adaptive query planner.
- JSON in PostgreSQL lacks statistics and compression, leading to inefficient query plans and storage; pgrust plans to add real statistics and compression to address this.
Divide and Compact: Segment-Oriented Compaction in SlateDB
2 months ago
- LSMs use a single tree for data, causing challenges in managing read/write amplification for mixed data structures with different patterns or lifetimes.
- Segment-oriented compaction in SlateDB splits the dataset into separate trees, allowing tailored compaction strategies based on data structure, frequency, retention, or other dimensions.
- Compaction in SlateDB involves L0 tables and sorted runs, with a scheduler like size-tiered compaction selecting similarly sized runs, though it struggles with heterogeneous data or time-based structures like timeseries.
- Segment-oriented compaction, introduced in RFC 24, partitions the keyspace by prefix, creating isolated LSM trees per segment for customized compaction and retention, useful for index structures or timeseries data.
- Benefits include bounded compaction work, steady throughput, improved write performance, and efficient retention, but tradeoffs include potential L0 write costs, manifest overhead, and the need for proper segment sizing for query efficiency.
Orasort: 5x faster column-sorting with an expired patent from Oracle
3 months ago
- Oracle's proprietary Orasort algorithm patent expired in 2024, entering the public domain after a 20-year term.
- Orasort enhances sorting speed by using 8-byte CPU register comparisons instead of traditional character-by-character (1-byte) methods, reducing CPU cycles.
- The algorithm benefits open-source databases like MySQL and PostgreSQL and cuts cloud computing operational costs through efficiency gains.
- Orasort operates by extracting and normalizing keys into 64-bit integers for comparison, handling large datasets via disk partitioning and asynchronous writing.
Do you really need separate systems when you already have Postgres?
3 months ago
- Postgres can often replace separate systems like Redis, Elasticsearch, MongoDB, and others for caching, search, queues, and more, reducing operational overhead.
- Running multiple databases and microservices leads to increased maintenance, monitoring complexity, costs, and debugging challenges, often prematurely.
- Most projects don't reach 'webscale' and can rely on 'boring' tech like Postgres, as used by large companies such as Notion and Netflix.
- Postgres offers built-in or extension-based solutions for caching (UNLOGGED tables), job queues (SKIP LOCKED), full-text search (tsvector), document storage (JSONB), vector search (pgvector), and other needs.
- Specialized infrastructure should only be added after proving Postgres insufficient and accepting the operational costs, to avoid unnecessary complexity.

database optimization

SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

SQLAlchemy 2 In Practice - Chapter 7: Asynchronous SQLAlchemy

The startup's Postgres survival guide

Snowflake's string min/max is truncated to ~40 chars, and it's undocumented

The four horsemen behind Postgres outages

Divide and Compact: Segment-Oriented Compaction in SlateDB

Orasort: 5x faster column-sorting with an expired patent from Oracle

Do you really need separate systems when you already have Postgres?