Hasty Briefsbeta

All tags

#database

200 stories total

Bilingual

Maybe the database got it right
4 months ago
- The article discusses the historical distancing of databases in software development, favoring in-memory models and object-oriented programming.
- It highlights how despite efforts to minimize database influence, databases indirectly shaped system designs.
- The rise of ORMs and NoSQL movements further distanced relational databases, yet applications remained CRUD-heavy and relational.
- Modern frontend development and the need for expressive data queries led to solutions like GraphQL, reintroducing database-like features.
- Relational databases evolved, incorporating features from systems meant to replace them, suggesting they might have been right all along.
- The article questions whether the reflexive hiding of databases still serves current needs and suggests reevaluating their role in system design.
Elasticsearch Was Never a Database
4 months ago
- Elasticsearch was designed as a search engine, not a primary database.
- Using Elasticsearch as a primary database leads to issues with transactions, schema migrations, and query limitations.
- Elasticsearch lacks atomic transactions, making it unreliable for operations requiring consistency.
- Schema changes in Elasticsearch often require reindexing, which can be risky when it's the primary store.
- Elasticsearch's query capabilities are limited compared to relational databases, especially for joins.
- Durability and recovery in Elasticsearch are not as robust as in traditional databases.
- Operating Elasticsearch at scale introduces operational challenges and instability.
- Misusing Elasticsearch as a database increases complexity, cost, and engineering effort.
- Elasticsearch should remain a search engine, with the primary database handling the system of record.
- ParadeDB is presented as an alternative combining OLTP and full-text search in one system.
The challenges of soft delete
3 months ago
- Soft delete implementations, like using a `deleted` boolean or `archived_at` timestamp, help recover accidentally deleted data but introduce complexity.
- Most archived data is never accessed, leading to bloated databases with dead rows, especially if retention policies are not enforced.
- Restoring from backups becomes slower with large amounts of archived data, complicating disaster recovery.
- Queries, indexes, and migrations become more complex due to the need to filter out archived data.
- Restoring archived records isn't always straightforward and may require validation against current business rules.
- Alternatives to `archived_at` include application-level event archiving, trigger-based archiving, and WAL-based change data capture (CDC).
- Application-level archiving simplifies the primary database but introduces risks of data loss and requires additional infrastructure.
- Trigger-based archiving moves deleted data to a separate table, keeping live tables clean and simplifying queries and migrations.
- WAL-based CDC (e.g., Debezium) captures all changes without application modifications but adds significant operational complexity.
- A novel idea is maintaining a PostgreSQL replica that ignores DELETE queries, though this may introduce schema migration challenges and higher costs.
- The trigger-based approach is recommended for new projects due to its simplicity and minimal infrastructure requirements.
Challenges in Join Optimization
3 months ago
- StarRocks optimizes join performance by keeping data normalized and making joins fast enough to run on the fly.
- The article explains StarRocks' cost-based optimizer in four parts: join fundamentals, logical join optimizations, join reordering, and distributed join planning.
- Common join types include Cross Join, Full/Left/Right Outer Join, Anti Join, Semi Join, and Inner Join, each with different performance characteristics.
- Join optimization challenges include multiple join implementation strategies, join order selection, difficulty in estimating join effectiveness, and distributed system complexities.
- StarRocks uses Hash Join as its primary join algorithm, with optimizations like predicate pushdown, predicate extraction, equivalence derivation, and limit pushdown.
- Join reordering strategies in StarRocks include exhaustive, greedy, and dynamic programming approaches to determine the optimal join order.
- Distributed join planning in StarRocks involves shuffle join, broadcast join, bucket shuffle join, colocate join, and replicate join to minimize network overhead.
- Global Runtime Filters in StarRocks help reduce join input size by filtering irrelevant rows early using Min/Max filters, IN predicates, and Bloom filters.
- Case studies from NAVER, Demandbase, and Shopee demonstrate significant performance improvements and cost reductions by leveraging StarRocks' efficient join execution.
Unveiling Firestore Pipeline operations – Firestore's powerful new query engine
4 months ago
- Firestore introduces a new query engine with over 100 new capabilities in the Enterprise edition.
- Pipeline operations allow for complex queries like aggregations, map operations, and string matching with regex.
- Enterprise edition offers more flexibility with optional indexes, improving write performance and reducing storage costs.
- New features include unnesting arrays, aggregating data, and sorting results within queries.
- Enterprise edition supports sparse, non-sparse, and unique indexes for better query performance.
- Developers can migrate from Standard to Enterprise edition using Firestore's import/export service.
- Enterprise edition is available for all developers with a free tier, similar to Standard edition.
- Standard edition remains supported, but Pipeline operations are exclusive to Enterprise edition.
- Firestore provides tools like Query Explain and Query Insights to optimize query performance.
- The update is available for Android, iOS, web, and admin SDKs, with Flutter, Unity, and C++ support coming soon.
Avoiding duplicate objects in Django querysets
3 months ago
- Filtering Django querysets across relationships can result in duplicate objects due to SQL JOIN operations.
- The issue occurs with both one-to-many and many-to-many relationships when a parent object has multiple related objects matching the filter.
- Using `distinct()` removes duplicates but can be inefficient, especially with large fields like `JSONField` or `TextField`.
- PostgreSQL's `distinct(*fields)` is more efficient but has limitations, such as ordering constraints.
- The best solution is using `Exists` subqueries, which are performant, clear in intent, and work across all databases.
Introduction to PostgreSQL Indexes
3 months ago
- PostgreSQL indexes are designed to speed up data access by reducing disk reads.
- Indexes enforce constraints like primary keys and unique keys but only help queries matching the index columns and data types.
- Indexes are beneficial when queries return less than 15-20% of the table; otherwise, a sequential scan might be preferred.
- PostgreSQL stores table data in heap files divided into 8KB pages, with rows (tuples) stored unordered.
- Indexes link key values to row locators (ctid) in the heap, enabling faster data retrieval.
- Creating an index on a column can drastically reduce query execution time by avoiding full table scans.
- Indexes come with costs: increased disk space, overhead on write operations (INSERT, UPDATE, DELETE), and memory usage.
- PostgreSQL offers six default index types (Btree, Hash, BRIN, GIN, GiST, SP-GiST) with more available via extensions.
- Btree is the most common index type, supporting primary/unique keys and efficient O(log n) searches.
- Multi-column indexes improve performance but require careful column ordering to maximize utility.
- Partial indexes reduce size and overhead by indexing only a subset of rows based on a condition.
- Covering indexes enable index-only scans by including all columns needed for a query.
- Expression indexes allow indexing transformed data (e.g., lowercased text) for efficient querying.
- Hash indexes are compact and fast for equality checks but lack support for ordering or multi-column use.
- BRIN indexes are space-efficient for large, append-only datasets with correlated data.
- GIN indexes excel at searching composite data (e.g., arrays, JSON, text).
- GiST and SP-GiST are frameworks for custom index types, useful for geometric and full-text search.
CedarDB: The fastest database you've never heard of
3 months ago
- CedarDB is a modern database designed from scratch to leverage contemporary hardware, unlike traditional databases like Postgres and MySQL which were built over 30 years ago.
- Originating from the Umbra research project at TUM, CedarDB focuses on performance optimizations for modern multi-core CPUs and large memory capacities.
- Key innovations include an advanced query optimizer capable of unnesting deeply nested SQL statements, reducing execution time from minutes to seconds.
- CedarDB employs code generation for SQL queries, converting them into machine code to eliminate interpretation overhead, significantly speeding up query execution.
- Morsel-driven parallelism ensures efficient CPU core utilization by dynamically distributing small data chunks ('morsels') among cores, keeping all cores busy.
- The database features a modern buffer manager designed for multi-threaded environments, using Pointer Swizzling to avoid global locks and maximize storage bandwidth.
- CedarDB's architecture is built to anticipate future hardware changes, with pluggable interfaces for new storage types and workloads, such as vector databases.
- Adaptive Query Execution allows CedarDB to start executing queries immediately with a basic version while optimizing more complex versions in the background.
- The database supports sophisticated statistics for the optimizer, enabling early data filtering and accurate estimates for operations like GROUP BY and MIN aggregations.
- CedarDB is positioned as a 'beyond main memory' system, optimizing for in-memory speeds but gracefully degrading performance when data exceeds RAM capacity.
SQLite Is a Self Contained System
3 months ago
- SQLite is a stand-alone, self-contained database with minimal dependencies.
- It runs on any operating system, including bare-bones embedded systems.
- SQLite uses no external libraries, encapsulated in a single source code file.
- Minimal build requires only standard C library routines.
- System memory allocation routines are optional and can be omitted.
- Default builds include VFS objects for OS interaction, with custom VFS support for embedded devices.
Joedb, the Journal-Only Embedded Database
3 months ago
- Joedb is a journal-only embedded database.
- The documentation is divided into three main sections: Introduction, User Guide, and Reference.
- Introduction covers pros and cons, an example, and concurrency examples.
- User Guide includes getting started, opening files, checkpoints, concurrency, remote procedure call, schema upgrade, vectors, indexes, and blobs.
- Reference section provides API reference, file format, network protocols, tools, testing, logging, TODO, links, release checklist, history, and license.
Bunny Database
3 months ago
- Bunny Database is a new SQLite-compatible managed service that spins down when idle, keeps latency low, and is cost-effective.
- Addresses the issue of DBaaS platforms becoming expensive and less accessible to smaller developers.
- Features include one-click deployment, language-specific SDKs, low latency worldwide, and affordable pay-as-you-go pricing.
- Offers 41 regions worldwide with options for automatic, single-region, or multi-region deployment.
- Includes tools like a database editor, metrics, and HTTP compatibility for easy integration.
- Benchmarks show up to 99% latency reduction by serving reads close to users.
- Pricing model charges only for reads, writes, and storage, with no costs when idle except for storage.
- Built on libSQL, an open-source fork of SQLite, with a focus on reliability and ease of use over strict upstream parity.
- Future roadmap includes automatic backups, database file import/export, and auto-generated, schema-aware APIs.
- Integrates with Bunny Edge Scripting and Bunny Magic Containers for seamless app development.
- Public preview is free, offering 50 databases per user account, each capped at 1 GB.
ClickHouse Release 26.1
3 months ago
- ClickHouse 26.1 introduces 25 new features, 43 performance optimizations, and 176 bug fixes.
- New system table `system.zookeeper_info` provides detailed status of all Keeper clusters.
- Function `reverseBySeparator` allows reversing delimited collections without converting to an array.
- New function `mergeTreeAnalyzeIndexes` aids in distributed index analysis with parallel replicas.
- Projections can now be defined with an INDEX clause for better query performance.
- Support for Nullable Tuple types enhances compatibility with external data formats.
- New data type `QBit` for vector embeddings with runtime precision tuning.
- Optimizations for GROUP BY and DISTINCT operations over LowCardinality columns.
- JIT compilation extended to more functions, including aggregates over Nullable, Decimal, and big integers.
- Memory reduction for inserts into wide tables, demonstrated with `system.metric_log`.
- New `concurrent_threads_scheduler` configuration improves latency distribution for highly concurrent queries.
- `sparseGrams` tokenizer enhances text search efficiency by using longer, more specific ngrams.
- Keeper API now supports HTTP control for better management.
- Asynchronous inserts now support exactly-once semantics for materialized views.
- ClickHouse Kubernetes operator automates cluster provisioning, scaling, and upgrades.
- Upcoming events include meetups in Brussels, Barcelona, London, and more.
Sqldef: Idempotent schema management tool for MySQL, PostgreSQL, SQLite
3 months ago
- sqldef is a CLI tool for comparing two SQL schemas.
- It helps manage database migrations using SQL DDLs.
- Supported databases include MySQL, MariaDB, TiDB, PostgreSQL, SQL Server, and SQLite3.
- An online demo uses WebAssembly to diff schemas and generate DDLs.
The Architecture of Open Source Applications (Volume 1) Berkeley DB
3 months ago
- Conway's Law states that a design reflects the structure of the organization that produced it.
- Berkeley DB is a software library providing fast, flexible, reliable, and scalable data management.
- Berkeley DB offers functionality similar to relational databases but is embedded directly into applications.
- The library supports keyed and sequential data access, transactions, and recovery from failure.
- Berkeley DB is modular, following the Unix 'do one thing well' philosophy.
- The project began as a replacement for hash packages with a freely redistributable implementation.
- Berkeley DB evolved from a hash library to include Btree support and transactional capabilities.
- Key versions include Berkeley DB 2.0 (transactions), 3.0 (abstraction layers), 4.0 (replication), and 5.0 (SQL support).
- The architecture includes access methods (Btree, Hash, Recno, Queue), buffer pool (Mpool), lock manager, log manager, and transaction manager.
- Design lessons emphasize modularity, consistent naming, respecting namespaces, and maintaining architectural integrity.
- Recovery in Berkeley DB uses a two-pass scheme to ensure database consistency after failure.
- The library is widely used in routers, browsers, mailers, and operating systems.
Lessons from BF-Tree: Building a Concurrent Larger-Than-Memory Index in Rust
3 months ago
- BF-Tree introduces variable-size mini-pages (64-4096 bytes) to reduce write amplification and memory waste compared to traditional 4KB page caching.
- The Rust implementation features a custom ring buffer allocator with a 6-state machine, RAII guards, optimistic locks, and deterministic concurrency testing.
- Mini-pages serve three functions: buffering writes, caching hot records, and growing for scan workloads, optimizing memory usage based on access patterns.
- The design decouples in-memory representation from on-disk format, allowing BF-Tree to optimize for the working set rather than physical page layout.
- BF-Tree's benchmarks show it is 2x faster than B-trees and LSM-trees for point lookups, 6x faster than B-trees for writes, and 2.5x faster than RocksDB for scans.
- The implementation uses RAII guards (CircularBufferPtr and TombstoneHandle) to manage lifecycle states, ensuring safe transitions and memory reclamation.
- Concurrency is handled with optimistic versioned locks for inner nodes and custom RwLocks with try_upgrade for page table entries, minimizing contention.
- Testing leverages Shuttle for systematic concurrency testing, exploring thread interleavings to uncover hard-to-find bugs.
- Key patterns include RAII guards for state machines, typed errors for retry control flow, and compile-time test infrastructure swaps.
GitHub - drawdb-io/drawdb: Free, simple, and intuitive online database diagram editor and SQL generator.
3 months ago
- DrawDB is a next-gen AI-powered intelligent terminal for all platforms.
- It is a robust and user-friendly database entity relationship (DBER) editor that works in your browser.
- Features include building diagrams with a few clicks, exporting SQL scripts, and customizing the editor without needing an account.
- Installation involves cloning the repository, installing dependencies, and running the development or build scripts.
- Docker support is available for containerized deployment.
- Optional server setup is required for enabling file sharing, following the .env.sample configuration.
MySQL Foreign Key Cascade Operations Hit the Binary Log
3 months ago
- MySQL 9.6 moves foreign key enforcement from InnoDB to the SQL layer, addressing a long-standing limitation.
- Previously, cascading operations were invisible to the binary log, causing issues with replication and CDC pipelines.
- The new approach ensures all cascaded operations are logged in the binary log, providing complete visibility.
- A new variable, innodb_native_foreign_keys, allows reverting to the old behavior for migration purposes.
- Performance benchmarks show no significant impact from the architectural change.
- Future enhancements may include support for triggers on cascaded changes and broader storage engine compatibility.
The mathematics of compression in database systems
3 months ago
- Compression in databases trades CPU cycles for reduced I/O bandwidth, optimizing between I/O, CPU, and memory resources.
- Breakeven analysis shows compression's worth depends on transfer bandwidth, with higher compression levels not always beneficial for latency.
- Logical bandwidth increases with compression, allowing higher throughput if CPU can handle the compression workload.
- Cost analysis reveals that optimal compression levels balance CPU costs against reduced data transfer fees in cloud environments.
- Semantic encoding (e.g., varint, delta encoding) and entropy compression (e.g., zstd) are combined for efficient data reduction.
- Techniques like dictionary encoding and bit-packing further optimize storage but require careful implementation to avoid inefficiencies.
- Lossy compression, while not covered in detail, is noted as valuable in specific domains like vector databases.
From 3 Minutes to 7.8 Seconds: Improving on RocksDB performance
3 months ago
- SereneDB improved RocksDB ingestion speed from 180 seconds to 7.8 seconds.
- Used flamegraphs to identify performance bottlenecks in RocksDB.
- Switched from rocksdb::Transaction Put to SST Writer for better performance.
- Optimized RocksDB settings by disabling filter computation and compression.
- Replaced sscanf with fast_float library for faster parsing.
- Changed std::string to std::vector<char> to reduce character writes.
- Removed redundant runtime checks and virtual function calls in hot paths.
- Pre-created keys for columns to eliminate redundant allocations.
- Achieved a 23x speedup through careful measurements and targeted changes.
How DSQL makes sure sequences scale
3 months ago
- DSQL introduces scalable sequence support, addressing the limitations of traditional Postgres sequences in distributed environments.
- Sequences in DSQL can be created with `CACHE` values to optimize performance, with recommendations for `CACHE=1` for low throughput and `CACHE>=65536` for high scalability.
- Experiments show significant performance improvements with high `CACHE` values, especially in bulk inserts and high-concurrency scenarios.
- UUIDs remain a recommended alternative for applications prioritizing simplicity and avoiding sequence bottlenecks.
- DSQL's architecture allows sequences to scale horizontally, leveraging distributed storage and compute resources to minimize contention.

first prev8next

About|Login

#database

Maybe the database got it right

Elasticsearch Was Never a Database

The challenges of soft delete

Challenges in Join Optimization

Unveiling Firestore Pipeline operations – Firestore's powerful new query engine

Avoiding duplicate objects in Django querysets

Introduction to PostgreSQL Indexes

CedarDB: The fastest database you've never heard of

SQLite Is a Self Contained System

Joedb, the Journal-Only Embedded Database

Bunny Database

ClickHouse Release 26.1

Sqldef: Idempotent schema management tool for MySQL, PostgreSQL, SQLite

The Architecture of Open Source Applications (Volume 1) Berkeley DB

Lessons from BF-Tree: Building a Concurrent Larger-Than-Memory Index in Rust

GitHub - drawdb-io/drawdb: Free, simple, and intuitive online database diagram editor and SQL generator.

MySQL Foreign Key Cascade Operations Hit the Binary Log

The mathematics of compression in database systems

From 3 Minutes to 7.8 Seconds: Improving on RocksDB performance

How DSQL makes sure sequences scale