Database stories from Lobsters

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.

This technical overview explains SQLite's Write-Ahead Log (WAL) mode, detailing its mechanism for atomic commits and rollbacks. It covers performance trade-offs between readers and writers, the role of checkpointing, and shared-memory requirements. Additionally, it addresses operational constraints, recovery processes, and a specific historical data race bug affecting database integrity.

PostgreSQL 18 introduces significant enhancements to the RETURNING clause, most notably the addition of OLD and NEW aliases for DML operations. These improvements allow developers to capture both the pre-modification and post-modification states of data within a single atomic statement, including INSERT, UPDATE, DELETE, and the recently refined MERGE operations. This capability eliminates the need for redundant SELECT queries or complex trigger functions to track data changes, thereby reducing database round trips and streamlining application architecture. The update is particularly impactful for synchronization tasks and building comprehensive audit trails. By providing explicit access to previous and current values, PostgreSQL 18 simplifies conditional logic and enhances data tracking performance, reinforcing its status as a highly advanced open-source database for modern application requirements.