Bilingual

Do you really need separate systems when you already have Postgres?
16 days 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.
Orasort: 5x faster column-sorting with an expired patent from Oracle
16 days 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.
Divide and Compact: Segment-Oriented Compaction in SlateDB
13 days 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.
The four horsemen behind Postgres outages
13 days 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.
Snowflake's string min/max is truncated to ~40 chars, and it's undocumented
19 hours 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 startup's Postgres survival guide
8 hours 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.

Hasty Briefsbeta

#database optimization

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

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

Divide and Compact: Segment-Oriented Compaction in SlateDB

The four horsemen behind Postgres outages

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

The startup's Postgres survival guide