Loading…
Databases
11 posts about Databases. Every summary links to the original.
Enabling Evolutionary Database Development: database branching with Lakebase
Evolutionary database development treats migrations as first-class CI/CD artifacts, but shared development databases still limit per-developer isolation and fast, realistic feedback. The post follows Jen as she adds location, batch, and serial fields to inventory, coordinating application changes, production-data preservation, schema migration, and tests for storing, reading, and displaying the new values. It contrasts shared databases and simplified local substitutes with Databricks Lakebase copy-on-write branches, created in one second with zero storage at creation, including for a terabyte-scale production database. Jen pairs code and database branches, while CI creates a temporary branch, validates an idempotent and reversible migration, runs application and database tests, and posts a schema diff, enabling combined review and shifting DBA attention toward design and maintainability.
Pramod Sadalage, Kevin Hartman10 trillion samples a day: Scaling beyond traditional monitoring infra at Databricks
Databricks’ monitoring infrastructure now tracks 5 billion active timeseries in real time and ingests more than 10 trillion samples daily, exposing scalability, reliability, cost, and operability limits in its older stack. The replacement, Pantheon, is a fork of CNCF Thanos deployed across more than 160 instances in about 70 regions and three cloud providers; tiered storage, differentiated memory retention, isolated replicated Receive groups, multitenancy, and a custom control plane support automated scaling and recovery. Pantheon’s largest instance holds about 300 million in-memory timeseries and handles nearly 1,000 PromQL queries per second, while migration reduced annual cloud costs by millions and monitoring downtime by roughly five times. For high-cardinality troubleshooting, Hydra preserves raw metrics in Delta tables, exposes them through Grafana and SQL, and unifies metric semantics across aggregated and raw paths, with freshness improvements planned.
David Yuan, Yi Jin, Karan Bavishi, HC Zhu, Joey BeydaDatabase modeling: a practical guide to techniques and best practices
Database modeling defines the structure, relationships and constraints that keep systems coherent across transactional, analytical and flexible workloads. The guide presents a three-phase design process: conceptual modeling captures entities and relationships, logical modeling adds attributes, keys, cardinality and normalization, and physical design translates that model into tables, indexes, constraints, partitioning and other platform-specific choices. It compares relational, document/NoSQL, dimensional, hierarchical and network models, linking each to data shape, workload and consistency requirements. It recommends explicit keys, consistent naming, appropriate normalization, query validation and version-controlled DDL, while warning against premature physical design, missing foreign keys, unclear NULL handling and poorly balanced schemas. It concludes that Databricks Lakebase combines a transactional database engine with the Databricks Lakehouse Architecture so one model can support operational and analytical workloads.
Databricks StaffAre LLM agents good at join order optimization?
Join ordering is difficult because relational optimizers choose among exponentially many plans and depend on potentially inaccurate cardinality estimates. Databricks and UPenn researchers built an LLM agent that uses structured outputs to generate valid join reorderings and executes candidates through a tool returning runtime and intermediate subplan sizes. It can test up to 50 iterations, while the evaluation tested 15 rollouts per query across 113 Join Order Benchmark queries after duplicating each dataset row ten times. With a frontier model, the best tested orders improved geometric-mean latency by 1.288 times and reduced P90 latency by 41% versus the standard Databricks optimizer. A five-way example links its gain to LIKE predicates that are exceptionally difficult for cardinality estimators.
Eric Liang, Ryan Marcus, Sid Taneja, Yuhao ZhangConcurrency Control in DBMS: How Locking, MVCC and Optimistic Strategies Keep Data Consistent
The guide explains how database management systems coordinate simultaneous transactions to preserve consistency and serializability, preventing dirty reads, lost updates, non-repeatable reads and phantom reads. It compares lock-based control, including shared and exclusive locks and two-phase locking, with MVCC, which gives transactions consistent snapshots and lets readers and writers proceed without blocking. The discussion covers pessimistic and optimistic strategies, isolation levels, deadlocks, retries, lock-granularity trade-offs and MVCC’s storage and garbage-collection costs, including PostgreSQL’s VACUUM. For lakehouse workloads, Delta Lake uses a read-validate-commit process with optimistic concurrency, snapshot isolation and conflict detection for concurrent writes. The source presents Databricks Lakebase as a managed implementation offering optimistic concurrency control, snapshot isolation for reads and write-serializable isolation for writes without lock or deadlock management.
Databricks StaffTake Control: Customer-Managed Keys for Lakebase Postgres
Lakebase Customer Managed Keys (CMK) gives regulated enterprises control of the root of trust through customer-owned keys from AWS KMS, Azure Key Vault, or Google Cloud KMS. Because Lakebase separates persistent storage from elastic Postgres compute that can scale to zero, CMK protects both layers and their caches rather than storage alone. Its hierarchical envelope-encryption model uses the CMK as the root, transient Key Encryption Keys (KEKs) to wrap Data Encryption Keys (DEKs), and unique DEKs for each data segment, while plaintext CMKs remain in the customer’s KMS. Account Admins bind a configured key to a workspace, after which new projects inherit the CMK; rotation needs no re-encryption or downtime, revocation terminates compute instances, and audit services record cryptographic operations.
Ben HaganWhat Is a Transactional Database?
Transactional databases handle large volumes of short, real-time read and write operations for systems such as orders, payments, account balances, and customer records. They support online transaction processing (OLTP) by treating related steps as one logical unit: successful work commits, while failures roll back the full transaction. ACID—atomicity, consistency, isolation, and durability—protects integrity under concurrency and failures, with row-oriented storage optimized for individual-record access, locking and isolation levels coordinating concurrent work, and write-ahead logging (WAL) supporting crash recovery. The source positions these databases as foundations for banking, e-commerce, healthcare, reservations, and telecommunications, while noting that row-oriented execution is less suitable for large analytical workloads and that horizontal scaling is more complex than in distributed NoSQL systems.
Databricks StaffRamp ·
Faster Pull Request Merges
Ramp’s growing Python pytest suite took over 12 minutes, while pull requests could wait up to one to two hours between merge queueing and production. Previously, Kodiak kept pull requests in a merge queue, requiring each PR to be up-to-date with master and rerun the full suite after every merge. Removing that requirement exposed a failure mode: parallel branches could introduce Alembic migrations with multiple heads, making the database migration history invalid. Ramp instead added migrations/migration-hash.txt, a hash of migration filenames that creates merge conflicts between migration-changing PRs while leaving other PRs free to merge; the deploy pipeline still runs tests on master before deployment. This reduced average merge time from more than an hour to 12 minutes.
Neal Wu & Young KimShopify ·
Five Common Data Stores and When to Use Them
The article compares five common data stores—relational databases, NoSQL databases, key-value stores, full-text search engines, and message queues—and connects each to appropriate design needs. Relational databases use schema-bound tables, primary and foreign keys, SQL, and ACID transactions for durable business-critical data, while NoSQL databases trade strict schemas for flexibility with document and wide-column models. In-memory stores such as Redis and Memcached provide fast access and configurable eviction for caching; Redis additionally supports richer data structures and disk persistence. Full-text search engines target word and substring searches, whereas Kafka uses a distributed append-only log and producer-consumer model to move data reliably between services. The article concludes that the right choice depends on whether data must be permanent, flexible, quickly accessed, searched, or transferred.
2023-10-18Shopify ·
Building Resilient GraphQL APIs Using Idempotency
Shopify’s Payment Service uses API-level idempotency to make GraphQL payment mutations resilient to timeouts, connection failures, and ambiguous outcomes that could otherwise leave merchants unpaid or double-charge buyers. The design places a UUID-based idempotency key directly in each mutation input, identifies requests by client plus key, and locks concurrent duplicates so retries receive a 409 response while the original call is processing. An IncomingRequest record stores request state and completed recovery points, allowing completed responses to be replayed and incomplete attempts to recover before continuing. Handlers divide work into no-side-effect, transactional local-side-effect, and remote-side-effect steps, each optionally providing run and recover functions. The approach adds database-write overhead and requires careful compatibility testing when recovery points change, but the team reports that its structured handlers are clear and maintainable.
2023-10-18Shopify ·
Pagination with Relative Cursors
Incremental page-number pagination becomes increasingly expensive for large offsets, because the database scans and discards earlier records before returning a page. Shopify describes relative cursor pagination, which remembers the last record seen and continues from that point, using an indexed id to keep query time nearly constant as pages increase. In tests on a shop with 14 million products, a 100,000 offset took 2,221.60 ms, compared with 5.24 ms using the last id, while offsets of 1,000,000 and above consistently timed out. Sorting by another field requires retaining both its last value and a unique secondary key such as id, with a matching compound index to avoid skipped records and maintain performance. Shopify introduced Link-header URLs for next and previous pages and began migrating REST endpoints from page numbers, reporting early /admin/products.json usage as about 11 times faster, though adoption remained low and full results were pending.
2023-10-18
