Best of Database — August 2025

  1. 1
    Article
    Avatar of jobsJobs·38w

    🚨How one tiny SQL query nearly killed our production system 🚨

    A production outage caused by a seemingly innocent `SELECT *` query demonstrates how database queries that work fine in development can fail catastrophically at scale. The query pulled 720MB of data from 60,000+ records, causing memory pressure, network bottlenecks, and connection timeouts. The post provides practical solutions including selecting specific columns, implementing pagination, caching hot data, using EXPLAIN plans, monitoring slow queries, and testing with production-scale data.

  2. 2
    Article
    Avatar of freecodecampfreeCodeCamp·40w

    How to Design Structured Database Systems Using SQL [Full Book]

    A comprehensive guide covering database fundamentals, from basic data concepts to designing structured database systems. Explores the DIKW pyramid (Data, Information, Knowledge, Wisdom), database management systems architecture, ACID properties, and various data models including relational databases, NoSQL systems, and Big Data approaches. Includes practical examples using PostgreSQL and covers essential topics like data types, domains, database design principles, and the differences between structured, semi-structured, and unstructured data.

  3. 3
    Article
    Avatar of hnHacker News·39w

    google/mangle

    Mangle is Google's open-source programming language that extends Datalog for deductive database programming. It enables querying data from multiple sources uniformly and supports recursive rules, aggregation, and function calls. The language is particularly useful for vulnerability detection, dependency analysis, and knowledge graph modeling. Implemented as a Go library, Mangle can be embedded into applications and offers practical extensions beyond traditional Datalog while maintaining accessibility for developers.

  4. 4
    Article
    Avatar of hnHacker News·38w

    Marco – All your emails, one place

    Marco's engineering team shares their journey through the offline-first landscape, evaluating tools like WatermelonDB, Triplit, InstantDB, and PowerSync for building a cross-platform email client. They encountered significant performance issues with large datasets (100MB+) and ultimately settled on Replicache paired with Orama for indexing. The post highlights the fundamental challenges of implementing offline-first architectures in web browsers, where all solutions must work on top of IndexedDB's key-value storage limitations.

  5. 5
    Article
    Avatar of laraveldevLaravel Dev·41w

    You should stop using booleans in your database

    Argues against using boolean fields in databases, suggesting datetime fields as a better alternative for tracking state changes. The post acknowledges this is a controversial stance and notes that some truly binary conditions may still require boolean representation, but emphasizes that many boolean use cases can be better modeled with temporal data that captures when changes occurred.

  6. 6
    Video
    Avatar of awesome-codingAwesome·40w

    The complete system designs crash course

    A comprehensive overview of system design fundamentals covering web protocols, load balancing, databases, caching strategies, messaging systems, scalability patterns, security measures, and fault tolerance. Explains key concepts like CAP theorem, microservices communication, horizontal vs vertical scaling, and practical applications through examples like URL shorteners and file storage systems.

  7. 7
    Video
    Avatar of awesome-codingAwesome·40w

    Postgres 18 just dropped. Now is the best time to learn it...

    PostgreSQL 18 introduces significant performance improvements through asynchronous I/O support, delivering 2-3x better performance in read-heavy applications. Key features include smarter query optimization for OR/IN clauses, enhanced hash joins, virtual generated columns as default, UUID version 7 support with timestamp ordering, improved RETURNING clause functionality, and temporal constraints with WITHOUT OVERLAPS. The upgrade process is now smoother with preserved statistics, eliminating the need to rebuild planner statistics after major version upgrades.

  8. 8
    Video
    Avatar of fireshipFireship·40w

    DuckDB in 100 Seconds

    DuckDB is an open-source, embeddable SQL database optimized for analytical workloads through columnar storage. Unlike SQLite's row-based approach, DuckDB stores data column-wise, enabling faster aggregations, filters, and joins on large datasets. It features vectorized query execution, multi-threading, and can directly query CSV and Parquet files. The database excels at time series analysis and is already used by major companies like Meta, Google, and Airbnb.

  9. 9
    Article
    Avatar of crunchydataCrunchy Data·40w

    Indexing JSONB in Postgres

    PostgreSQL's JSONB datatype offers powerful querying capabilities, but requires proper indexing for optimal performance. GIN (Generalized Inverted Index) indexes are ideal for containment queries, key existence checks, and JSONB operators like @>, ?, ?|, and ?&. However, GIN indexes don't help with path-based navigation, comparisons, or pattern matching within JSONB values. For these cases, expression indexes using B-tree structures work better. GIN indexes have higher write overhead and can suffer from bloat, requiring periodic maintenance with REINDEX CONCURRENTLY. Best practices include using GIN for containment-style lookups, expression indexes for specific key queries, and combining both approaches for comprehensive JSONB performance optimization.

  10. 10
    Article
    Avatar of lobstersLobsters·40w

    Release v2.0.0 · syncthing/syncthing

    Syncthing v2.0.0 introduces major architectural changes including a switch from LevelDB to SQLite database backend, structured logging with per-package log levels, automatic deletion of old database entries after six months, modernized command line options, removal of rolling hash detection, multiple device connections by default, and improved conflict resolution for deleted files. The release includes numerous bug fixes and performance improvements but drops prebuilt binaries for several platforms due to SQLite cross-compilation complexities.

  11. 11
    Article
    Avatar of bytebytegoByteByteGo·40w

    How Atlassian Migrated 4 Million Jira Databases to AWS Aurora

    Atlassian successfully migrated 4 million Jira databases from AWS RDS PostgreSQL to Aurora PostgreSQL across 3,000 server instances in 13 regions. The migration faced a critical challenge when Aurora's file count limitations prevented startup with 20+ million files per cluster. They solved this by developing a "drain-convert-refill" strategy that reduced tenant density during conversion, then repopulated clusters afterward. The project achieved 99.99% uptime SLA, better cost efficiency through auto-scaling, and maintained under 3 minutes downtime per tenant while processing up to 90,000 daily migrations at peak.

  12. 12
    Article
    Avatar of lonely_programmerLonely Programmer·38w

    SQL INNER JOIN – Connect What Matches!

  13. 13
    Article
    Avatar of crunchydataCrunchy Data·39w

    Postgres Logging for Performance Optimization

    PostgreSQL provides comprehensive logging capabilities for performance optimization and troubleshooting. Key configurations include setting up log collectors, choosing appropriate log formats (stderr, csvlog, jsonlog), and configuring severity levels. For performance monitoring, enable logging of slow queries with log_min_duration_statement, lock waits with log_lock_waits, and temporary file usage with log_temp_files. The auto_explain extension automatically logs query execution plans for analysis. Proper log rotation prevents disk space issues, while tools like pgBadger can parse logs into visual reports. Additional audit logging is available through the pgAudit extension for tracking user actions and data changes.

  14. 14
    Article
    Avatar of freekFREEK.DEV·42w

    Why I don't use down migrations

    Down migrations in Laravel are often untested code that creates a false sense of security. They become problematic when new data exists after deployment, as rolling back can cause data loss or leave orphaned records. The complexity increases with modern deployment strategies where code and database versions may be misaligned. A forward-only migration approach is recommended, where issues are resolved by creating new migrations that move the schema forward rather than attempting to reverse changes.

  15. 15
    Article
    Avatar of lobstersLobsters·38w

    Three years of building no-code software for grassroots political organizations

    A developer shares three years of experience building no-code solutions for political organizations, including volunteer management systems, content distribution automation, and vetting processes. The article demonstrates practical applications using tools like N8n, Notion, Baserow, and Grist to create custom workflows without traditional programming, emphasizing the empowering nature of no-code approaches for grassroots organizing.

See all Database archives