48 docs tagged with "database"

Window functions, CTEs, subqueries, recursive queries, lateral joins, pivot/unpivot, pagination patterns, EXPLAIN, and advanced SQL for senior engineers.

Advanced database query optimization when indexes are already optimal: Hash Join memory spills to disk, the art of application-side joins without N+1, deterministic SELECT-then-write ID ordering, and 4 dangerous traps of ON DUPLICATE KEY UPDATE.

DynamoDB mastery for DVA-C02. Covers partition keys, sort keys, GSI vs LSI, read/write capacity modes, DynamoDB Streams, DAX, TTL, transactions, and the most common exam scenarios and anti-patterns.

Amazon RDS and Aurora for DVA-C02. Multi-AZ vs Read Replicas, Aurora Serverless, RDS Proxy, IAM authentication, connection pooling, encryption, and common developer patterns with Java/Spring Boot.

RPO, RTO, backup strategies, point-in-time recovery, logical vs physical backups, and disaster recovery planning.

Detailed guide and senior deep dive into JPA/Hibernate best practices for @ManyToOne and @OneToMany mappings based on Thorben Janssen's tutorial.

Full-depth guide to CAP theorem and PACELC — the GitHub 2018 incident, why 'choose 2 of 3' is misleading, PACELC's EL trade-off (latency vs consistency during normal operation), database classification matrix, conflict resolution costs, and Brewer's 12-year correction.

Comprehensive guide on Change Data Capture (CDC), detailing how it works, alternatives comparison, implementation patterns with Debezium and Spring, and deep dives for senior engineers.

Databases, web frameworks, and all third-party frameworks are details — implementation choices that should be deferred and hidden behind boundaries. Learn why treating them as the center of your architecture leads to rigidity and how to protect your business rules from them.

A comprehensive deep dive into consistent hashing, addressing modulo scaling bottlenecks, hash rings, virtual nodes, data replication, interview questions, and real-world implementations.

OLTP vs OLAP, dimensional modeling, star and snowflake schemas, ETL/ELT, materialized views, and modern data warehouse tools.

A deep-dive guide to ACID properties — Atomicity, Consistency, Isolation, Durability — covering isolation levels, MVCC, 2PL, WAL, write skew, distributed ACID, and practical interview questions. Includes the real priority order (I → C → D → A), the AI PR story, and the link to the full isolation levels deep-dive.

A complete guide to database connection pooling — how connections work, pool mechanics, HikariCP tuning, pool sizing formulas, failure modes, PgBouncer, RDS Proxy, and production observability. Beginner through senior depth.

Entity-Relationship modeling, normal forms (1NF through BCNF), denormalization trade-offs, and schema design patterns.

Deep-dive into all 5 isolation anomalies (dirty read, non-repeatable, phantom, lost update, write skew), the 4 standard isolation levels, PostgreSQL vs MySQL vs Oracle implementation differences, snapshot scope (per-statement vs per-transaction), practical fix strategies without raising isolation, and the spec vs implementation distinction.

A comprehensive reference covering relational fundamentals, indexing, transactions, distributed systems, caching, and more — with common interview questions.

Transactional Outbox, Saga, CQRS, Event Sourcing, database-per-service, and data consistency patterns in distributed architectures.

Authentication, authorization, SQL injection, encryption at rest and in transit, auditing, and security best practices.

A complete guide to horizontal scaling — sharding strategies, consistent hashing, cross-shard complexities, rebalancing, distributed ID generation, and real-world database comparisons.

Physical engine mechanics of large-scale database exports: why LIMIT OFFSET degrades linearly, keyset pagination pitfalls, Go streaming vs Java JDBC OutOfMemoryError, and an anatomy of the net_write_timeout socket failure.

Full-text search concepts, inverted indexes, relevance scoring, MySQL FULLTEXT, PostgreSQL tsvector, and Elasticsearch for advanced search.

Physical mechanisms of long-running transaction failures: Undo tablespace and History List Length (HLL) bloat, the single-threaded rollback trap, the critical difference between KILL QUERY and KILL CONNECTION, and the Metadata Lock (MDL) queue cascade exhausting connection pools.

Deep dive into database indexing mechanisms — covering disk I/O, B-Trees, hash indexes, composite index design, geospatial indexing, inverted indexes, EXPLAIN analysis, and Spring/JPA performance.

A comprehensive list of technical interview questions and detailed answers from a real LTIMindtree Java Developer interview for a candidate with 2 to 7 years of experience.

Chi tiết cơ chế đánh index MySQL nâng cao: phân biệt Seek vs Filter, quy tắc Leftmost Prefix, ESR rule, Index Condition Pushdown (ICP), Skip Scan, và kỹ thuật Deferred Join tăng tốc 148 lần.

From first principles to production — key-value, document, wide-column, and graph databases with deep dives into MongoDB and Cassandra for Java/Spring engineers.

What MongoDB is, why to use it, core concepts, data modelling, CRUD, aggregation, indexes, advanced architecture, and schema patterns. Covers beginner through senior depth.

Online Schema Change mechanics in MySQL and PostgreSQL: why staging finishes in 40s while production exhausts connection pools, ALGORITHM & LOCK matrix, innodb_online_alter_log_max_size overflow, gh-ost vs pt-osc, and PostgreSQL's INVALID index trap.

Exhaustive analysis of the 5 lock types: version column, SELECT FOR UPDATE, advisory locks, lock columns, and Redis locks; physical lifespans, failure modes, and why optimistic retry storms crush high-contention ticketing systems.

Identifying slow queries, profiling tools, key metrics, connection pooling, and practical optimization workflow.

Comprehensive guide to PostgreSQL BRIN Index (Block Range Index) — mechanics, 99% RAM and disk space savings vs B-Tree, physical correlation pre-requisite, pages_per_range tuning, and Spring Data JPA integration.

Comprehensive guide to PostgreSQL Checkpoint mechanics, Write-Ahead Logging (WAL), checkpoint_completion_target I/O smoothing, pg_stat_bgwriter monitoring, Full-Page Writes (FPW), and Crash Recovery RTO optimization.

Comprehensive deep dive into PostgreSQL internal storage mechanics — 8KB slotted pages, HeapTupleHeader, CTID double-hop lookup, the UPDATE dilemma, HOT optimization, MVCC visibility, and VACUUM mechanics based on Hussein Nasser's database engineering architecture.

PostgreSQL Write-Ahead Logging and replication mechanics: why a no-op UPDATE generates 380MB of WAL, replication lag causes, orphan replication slots exhausting disk, silent archive_command failures, and the 2 AM 95% disk emergency playbook.

Deep dive into the 7 universal database tables that organically emerge in production systems, technical debt blast radius, and zero-downtime schema hygiene runbooks.

How databases parse, plan, and optimize SQL queries — cost-based optimization, statistics, plan hints, and common planner pitfalls.

Comprehensive Staff-level architectural breakdown of 8 landmark petabyte storage engines and zero-downtime database migrations — Dropbox Magic Pocket, Discord ScyllaDB, YouTube Vitess, GitHub MySQL 8, Pinterest 64-bit sharding, Instagram Rocksandra, Figma multiplayer, and WhatsApp Erlang.

Redis as a primary in-memory database — persistence mechanisms (RDB, AOF), durability guarantees, data modeling patterns, and when to use Redis as your primary store vs cache.

Core concepts of relational databases — tables, keys, joins, SQL basics, and the relational model.

Database replication strategies, sharding patterns, the CAP theorem, and horizontal scaling techniques.

Managing database schema changes safely in production — Flyway, Liquibase, zero-downtime migration patterns, and rollback strategies.

Essential SQL interview questions covering window ranking functions, B-Tree vs Hash indexing, composite index left-prefix rules, and EXPLAIN execution plan analysis.

How databases store and retrieve data — B+ trees, LSM trees, heap files, InnoDB vs MyISAM, WAL, and buffer pools.

Time-series data characteristics, storage optimizations, InfluxDB, TimescaleDB, Prometheus, and common time-series patterns.

A complete guide to the Transactional Outbox Pattern — from the Dual-Write problem for beginners to CDC vs polling internals, at-least-once guarantees, ordering semantics, and production monitoring for senior engineers.

ACID properties, isolation levels, locking mechanisms, MVCC, deadlocks, and optimistic vs pessimistic concurrency.

Physical engine mechanics of PostgreSQL row locking: how xmax writes locks directly into tuple headers without a lock table, the 4 row lock modes matrix, foreign key collisions, EvalPlanQual rechecks, and why SSI serializable aborts are by design.

Deep dive into MySQL InnoDB storage engine locking mechanics: why two users updating distinct draft carts trigger Deadlock 1213, decoding the 3 access paths of a WHERE clause, and why READ COMMITTED won't save you.