Database stories from Lobsters

The author argues that SQLite should adopt a versioned 'editions' system similar to Rust. Currently, SQLite’s default settings—such as disabled foreign key constraints, loose type affinity, and lack of busy-timeouts—often lead to bugs and poor performance. An edition-based approach would allow for modernizing global defaults while maintaining backward compatibility.

The creator of Hister, a self-hosted web history management tool, details how they reduced their dependence on Google Search by 50% in just 1.5 months. The core insight is the distinction between Discovery Search (finding new content) and Recall Search (refinding previously visited content). While search engines like Google are plagued by excessive advertising, SEO manipulation, and privacy concerns, Hister allows users to index the full text of their own browsing history locally. This includes content behind authentication and private networks that global search engines cannot access. By prioritizing speed, privacy, and local full-text indexing, Hister serves as a personal knowledge base that improves over time. The project is open-source under the AGPLv3 license and aims to provide a faster, ad-free alternative for professionals who frequently revisit known documentation or resources.

A developer shares a cautionary tale about losing their PostgreSQL database twice due to exploitable configurations. The root causes were weak default credentials and Docker modifying host firewall rules to expose the database port publicly. The incident highlights the importance of securing docker-compose ports, using strong passwords, and properly configuring UFW on a VPS.

Gitgres is an experimental system that uses PostgreSQL as a storage backend for Git repositories via libgit2. By treating Git objects and refs as database rows, it simplifies DevOps by unifying application data and repository storage, enabling complex SQL queries across code and metadata while streamlining backups and scalability.

Jepsen tested MariaDB Galera Cluster by simulating network faults and crashes. Findings reveal significant data persistence issues, including the loss of committed writes during node recovery, as well as consistency anomalies like Lost Updates and Stale Reads in healthy clusters. The study concludes that the system's actual performance fails to meet documented consistency and availability claims.

Observability often struggles with the high volume and inconsistent nature of logs, which become increasingly difficult to manage as data grows. Most logging stacks mutate in complexity or cost at scale. ClickHouse stands out by offering a consistent, columnar architecture that scales linearly through sharding, providing significant performance and efficiency advantages as data volumes increase.

SQLite 3.53.0 introduces the Query Result Formatter (QRF) library to enhance CLI output, adds support for modifying constraints in ALTER TABLE, improves the query planner, and introduces new JSON functions. It also provides new C APIs and optimizations while addressing critical reliability fixes like the WAL-reset corruption bug.

SQLite provides limited support for the ALTER TABLE command, primarily allowing table and column renames, and the addition or deletion of columns. While simple renames and additions are fast because they only modify schema text, certain operations like adding constraints or dropping columns require rewriting table data. For complex schema changes not directly supported, a 12-step manual procedure involving temporary tables is recommended to maintain database integrity and cross-version compatibility.

PostgreSQL 18 introduces pg_restore_relation_stats and pg_restore_attribute_stats, allowing users to export and inject optimizer statistics as portable SQL artifacts. This feature enables developers to replicate production query plans in CI/CD or local environments without needing full production datasets, significantly improving performance debugging and preventing query regressions.

This article explores unconventional optimization techniques for PostgreSQL to improve performance and reduce storage costs. Key strategies include using constraint_exclusion to skip unnecessary table scans, leveraging function-based indexes on virtual generated columns for lower cardinality data, and enforcing uniqueness on large values using exclusion constraints with Hash indexes.

Eduardo Bellani argues that many engineering problems, often solved with complex distributed systems, can be effectively handled using vanilla PostgreSQL. By leveraging features like temporal tables, updatable views, and rigorous constraint triggers, teams can maintain data integrity, enforce business rules, and ensure high performance without unnecessary infrastructure complexity or architectural overhead.

Running SQLite in production requires managing database operations like query optimization via ANALYZE, handling long-running transactions to avoid timeouts, and implementing reliable backup strategies. While Django ORM simplifies development, developers must be mindful of query efficiency and write-heavy operations to maintain site stability, often leading to considerations for alternative solutions like Litestream or Postgres.