Topic
107 posts tagged “Databases”.
Chisato · · 5 min read Raft and Paxos both let a distributed cluster agree on a value despite failures — Raft trades some flexibility for a design built to be understood.
Chisato · · 4 min read The outbox pattern writes an event to the same database transaction as a state change, then publishes it reliably — solving the dual-write problem.
The Lycoris Team · · 4 min read A roaring bitmap is a compressed bitmap format that splits data into chunks and picks the best internal representation for each — fast and space-efficient.
The Lycoris Team · · 4 min read PgBouncer sits between clients and Postgres, reusing a small pool of real connections. Session, transaction, and statement modes trade features for scale.
Chisato · · 4 min read Logical replication streams row-level changes between Postgres databases instead of copying raw disk blocks — enabling selective sync, upgrades, and CDC.
The Lycoris Team · · 4 min read Cardinality is how many distinct values a column has. Low-cardinality columns make poor index candidates because the database still scans most of the table.
Chisato · · 4 min read A vector clock is a per-node counter array that lets distributed systems tell whether one event happened before another, without a shared clock.
The Lycoris Team · · 4 min read Quorum consensus lets distributed databases tune consistency and availability by requiring reads and writes to touch overlapping subsets of replicas.
Chisato · · 4 min read Replication lag is the delay between a write on the primary database and its arrival on a replica. What causes it, how to measure it, and how to reduce it.
The Lycoris Team · · 5 min read PostgreSQL's built-in full-text search uses tsvector documents and tsquery queries matched with @@, indexed with GIN for speed at scale.
The Lycoris Team · · 4 min read A database view is a saved SQL query that behaves like a table — simplifying complex joins and restricting access, without storing the data twice.
The Lycoris Team · · 5 min read A clustered index determines the physical row order on disk; a non-clustered index is a separate lookup structure. How they differ and when to use each.
The Lycoris Team · · 4 min read Read-your-writes consistency guarantees a client sees its own writes immediately, even when other clients might not yet. How it's implemented.
The Lycoris Team · · 5 min read Two-phase commit locks resources until every node agrees to a transaction; the saga pattern trades that guarantee for availability using compensating steps.
Chisato · · 5 min read A wide-column database stores data in column families keyed by row, built for massive write throughput and horizontal scale across many machines.
The Lycoris Team · · 4 min read Common table expressions and subqueries both let you build a query from smaller pieces, but they differ in readability, reuse, and optimizer behavior.
The Lycoris Team · · 4 min read B-trees update data in place for fast, predictable reads; LSM-trees batch writes sequentially for higher write throughput. How databases pick.
The Lycoris Team · · 5 min read A distributed lock coordinates exclusive access to a shared resource across multiple processes or machines, preventing race conditions in distributed systems.
The Lycoris Team · · 4 min read Natural keys use real-world data as a primary key; surrogate keys use a generated ID. Here's how to choose, with the trade-offs of each.
Chisato · · 4 min read PACELC extends CAP theorem by adding a tradeoff that applies even when there's no partition: latency versus consistency. Here's how it works.
Chisato · · 5 min read Synchronous replication waits for a replica to confirm a write before committing; asynchronous doesn't. The choice trades latency against durability.
The Lycoris Team · · 4 min read JSONB stores JSON in PostgreSQL as a parsed, indexable binary format instead of raw text. How it works, and when to reach for it.
The Lycoris Team · · 4 min read The N+1 query problem fires one query per row instead of one query total, quietly turning a fast page into hundreds of round trips. How to spot and fix it.
The Lycoris Team · · 5 min read Hash joins, nested loop joins, and merge joins are the three ways a database executes a JOIN — here's when the query planner picks each one.
The Lycoris Team · · 4 min read A hash index gives O(1) equality lookups but no range scans; a B-tree supports both. Here's how the two database index types actually differ.
Chisato · · 5 min read IVF clusters vectors into partitions to narrow a search; HNSW builds a navigable graph. Both trade recall for speed differently at scale.
Chisato · · 4 min read MongoDB vs PostgreSQL compared: document vs relational modeling, schema flexibility, joins, and transactions — how to pick the right one.
Chisato · · 4 min read DuckDB is an embedded, columnar SQL database built for fast analytical queries directly inside your application process. How it works.
Chisato · · 4 min read ACID guarantees strict consistency after every transaction; BASE trades that for availability and scale. How each model works and when to pick one.
The Lycoris Team · · 4 min read A composite index spans multiple columns in a fixed order, speeding up queries that filter or sort on that combination. How column order changes everything.
The Lycoris Team · · 5 min read Denormalization trades write-time consistency for read-time speed by duplicating data — the right call once joins become your bottleneck.
Chisato · · 4 min read Reverse ETL moves data out of the warehouse and into operational tools like a CRM, flipping the direction of a traditional ETL pipeline. How it works.
The Lycoris Team · · 4 min read Cache-aside puts the application in charge of loading cache misses; read-through delegates that job to the cache itself. How each pattern works.
The Lycoris Team · · 4 min read A database cursor is a pointer that lets you fetch a query's result set one row at a time instead of all at once. How cursors work and when to use them.
The Lycoris Team · · 4 min read Point-in-time recovery restores a database to any moment between backups by replaying transaction logs, undoing bad deploys and accidental deletes.
Chisato · · 4 min read Prisma generates a type-safe client from a schema file and runs through its own query engine; Drizzle is a thinner, SQL-like query builder with no engine.
The Lycoris Team · · 4 min read GROUP BY collapses rows into groups for aggregation; HAVING filters those groups afterward. How the two clauses differ and where each runs in query order.
The Lycoris Team · · 4 min read A bitmap index uses a bit array per distinct value to speed up queries on low-cardinality columns, and combines multiple filters with fast bitwise AND/OR.
The Lycoris Team · · 4 min read Write amplification is when a system writes more data physically than the logical write requested, wearing out storage faster and hurting throughput.
The Lycoris Team · · 4 min read Redis is in-memory, so RDB snapshots and the AOF log are how it survives a restart — each trades durability against performance differently.
The Lycoris Team · · 5 min read A stored procedure is precompiled SQL saved inside the database and invoked by name, cutting network round trips and centralizing business logic.
The Lycoris Team · · 6 min read A step-by-step guide to running EXPLAIN ANALYZE in PostgreSQL and reading the query plan it returns — node types, costs, and where the real time went.
The Lycoris Team · · 5 min read A database trigger is a procedure that runs automatically on an insert, update, or delete — enforcing rules the application layer can't guarantee.
The Lycoris Team · · 5 min read A SQL view is a saved query re-run on every read; a materialized view stores the result physically and needs refreshing. Here's when to use each.
The Lycoris Team · · 5 min read Postgres offers several index types beyond the default B-tree. When GIN and GiST outperform it for arrays, JSONB, full-text search, and ranges.
The Lycoris Team · · 5 min read A covering index holds every column a query needs, letting the database answer from the index alone without a lookup back to the table.
The Lycoris Team · · 4 min read A data warehouse stores structured, pre-modeled data optimized for queries; a data lake stores raw data of any shape. When each one fits.
The Lycoris Team · · 4 min read Primary keys identify a row, foreign keys link one table to another, and unique constraints just prevent duplicates. How the three differ in SQL.
The Lycoris Team · · 4 min read A database deadlock happens when two transactions each wait on a lock the other holds. Why deadlocks occur, how databases detect them, and how to avoid them.
The Lycoris Team · · 5 min read Raft is a consensus algorithm that lets a cluster of servers agree on a shared state even when some nodes fail. How leader election and log replication work.
The Lycoris Team · · 5 min read Write-through writes to cache and store together, write-back delays the store write, write-around skips the cache on writes entirely. When to use each.
The Lycoris Team · · 4 min read Star schema denormalizes dimensions into flat tables for fast queries; snowflake schema normalizes them to save space. How to choose for your warehouse.
The Lycoris Team · · 4 min read Vacuuming reclaims space left by deleted and updated rows in databases like PostgreSQL, preventing bloat and transaction ID wraparound.
Chisato · · 4 min read A time-series database is optimized for timestamped data — metrics, sensor readings, prices. How it differs from general-purpose databases.
The Lycoris Team · · 4 min read MVCC lets readers and writers work on a database concurrently without blocking each other, by keeping multiple versions of each row instead of locking it.
The Lycoris Team · · 4 min read The saga pattern coordinates a multi-step transaction across services using local commits and compensating actions instead of a distributed lock.
The Lycoris Team · · 4 min read A query optimizer turns declarative SQL into an execution plan by estimating the cost of alternative strategies. How that estimation works and how to read a plan.
The Lycoris Team · · 5 min read A foreign key constraint ties a column to a row in another table and blocks changes that would break that link. How referential integrity works in SQL.
The Lycoris Team · · 5 min read SQLite is a serverless, file-based SQL database compiled directly into an application. How it works, why it's everywhere, and when to reach for it.
The Lycoris Team · · 4 min read An LSM tree batches writes in memory and flushes them as sorted files on disk, trading read complexity for the fast, sequential writes many databases rely on.
The Lycoris Team · · 5 min read Two-phase commit coordinates a transaction across multiple databases with a prepare phase and a commit phase, trading availability for strong consistency.
Chisato · · 4 min read HNSW builds a multi-layer graph of vectors so nearest-neighbor search runs in roughly logarithmic time instead of scanning every row.
The Lycoris Team · · 5 min read A read replica is a synced copy of a database that serves read queries, taking load off the primary. How replication lag and failover actually work.
The Lycoris Team · · 4 min read Partitioning splits a table within one database; sharding splits data across separate database instances entirely. Here's how each works and when to use them.
The Lycoris Team · · 5 min read A graph database stores data as nodes and relationships instead of tables, making deeply connected queries fast instead of a chain of costly joins.
Chisato · · 4 min read A CRDT is a data structure that merges concurrent edits from multiple replicas automatically, without coordination or conflicts, using math instead of locks.
The Lycoris Team · · 4 min read A Merkle tree hashes data in pairs up to a single root hash, letting huge datasets be verified for integrity without downloading all of them.
Chisato · · 4 min read Eventual consistency guarantees that replicas converge over time, not instantly. How it differs from strong consistency and when it's acceptable.
The Lycoris Team · · 4 min read A CTE is a named, temporary result set defined with WITH that you can reference elsewhere in a SQL query. How they work and when to use one.
The Lycoris Team · · 4 min read Optimistic locking checks for conflicts at write time; pessimistic locking blocks other writers up front. How each works and when to pick one.
Chisato · · 5 min read PostgreSQL vs MySQL compared: SQL standards, JSON, replication, performance, and licensing — and a clear rule of thumb for choosing between them.
The Lycoris Team · · 5 min read A SQL join combines rows from two tables based on a related column. How inner, left, right, and full outer joins differ, with examples.
Chisato · · 4 min read ETL transforms data before loading it into a warehouse; ELT loads raw data first and transforms it inside the destination. How the two approaches differ.
The Lycoris Team · · 4 min read A data lakehouse combines a data lake's cheap object storage with a data warehouse's transactional guarantees and schema. How the architecture works.
The Lycoris Team · · 4 min read Write-ahead logging records changes to a log before applying them to a database, making crash recovery and replication possible. Here's how it works.
Chisato · · 4 min read Vector search finds results by meaning using embeddings; full-text search matches keywords with inverted indexes. When to use each, and when to combine them.
The Lycoris Team · · 4 min read Row-oriented databases store each record together on disk; columnar databases store each column together. The layout decides which workloads are fast.
Chisato · · 5 min read A database migration is a version-controlled script that changes a schema incrementally. How migration tools track state and apply changes safely.
Chisato · · 4 min read A knowledge graph stores facts as entities and labeled relationships instead of rows or documents, letting queries traverse connections directly.
The Lycoris Team · · 4 min read Isolation levels control how much of a concurrent transaction's uncommitted work another transaction can see, trading consistency for concurrency.
The Lycoris Team · · 4 min read Change data capture streams row-level inserts, updates, and deletes out of a database in real time, powering sync pipelines, caches, and event-driven systems.
The Lycoris Team · · 4 min read A B-tree is a self-balancing tree that keeps data sorted with logarithmic search, insert, and delete time — the structure behind most database indexes.
Chisato · · 4 min read SQL injection lets attackers run arbitrary database queries by smuggling SQL into user input. Parameterized queries close the hole. Here's how it works.
The Lycoris Team · · 4 min read Consistent hashing maps keys and nodes onto the same ring so adding or removing a server only reshuffles a small fraction of keys, not all of them.
The Lycoris Team · · 5 min read The N+1 query problem turns one database request into hundreds by issuing a separate query per row. Here's how to spot it and fix it.
The Lycoris Team · · 5 min read Connection pooling reuses a fixed set of open database connections instead of opening a new one per request. How pools work and why they prevent overload.
The Lycoris Team · · 4 min read OLTP systems handle many small, fast transactions like orders and logins; OLAP systems run large analytical queries across historical data for reporting.
The Lycoris Team · · 4 min read A materialized view stores a query's result as physical data instead of recomputing it on every read. How it differs from a view, and when to use one.
Chisato · · 4 min read SQL window functions compute values across a set of rows without collapsing them, unlike GROUP BY. How OVER, PARTITION BY, and ranking work.
The Lycoris Team · · 4 min read ACID — atomicity, consistency, isolation, durability — defines the guarantees a database transaction makes so concurrent, failure-prone operations stay correct.
The Lycoris Team · · 4 min read An ORM lets you query a database using your programming language's objects instead of raw SQL. How they work, what they trade off, and when to skip one.
Chisato · · 4 min read CAP theorem says a distributed system can't guarantee consistency, availability, and partition tolerance all at once. What the trade-off means in practice.
The Lycoris Team · · 4 min read Database normalization organizes tables to eliminate redundant data and update anomalies. The normal forms explained with a worked example.
The Lycoris Team · · 4 min read Database replication keeps copies of data on multiple servers for redundancy and read scaling, at the cost of consistency and lag tradeoffs.
The Lycoris Team · · 5 min read Database sharding splits one dataset across many servers so no single machine holds it all. How sharding works, how to pick a shard key, and the trade-offs.
The Lycoris Team · · 4 min read A database index is a sorted data structure that lets the engine find rows without scanning the whole table. How indexes work, and when they help or hurt.
Chisato · · 5 min read Build a Redis clone in Python that the real redis-cli can talk to — a TCP server, the RESP protocol, key expiry, and an in-memory store in under 200 lines.
Chisato · · 6 min read Caching keeps a copy of expensive data somewhere faster. How cache-aside, write-through, and TTLs work — and why invalidation is the hard part.
Chisato · · 3 min read A vector embedding turns text, images, or audio into numbers where similar meanings land close together — the foundation of semantic search and RAG.
Chisato · · 5 min read Redis and Memcached are both in-memory caches, but they differ on data types, persistence, and threading. How to choose — and when each one wins.
The Lycoris Team · · 4 min read Apache Kafka is a distributed event-streaming platform built on a durable, append-only log. How topics, partitions, and consumers power real-time pipelines.
Chisato · · 3 min read SQL is the standard language for querying and managing relational databases. Learn the core statements, how joins work, and when SQL is the right tool.
Chisato · · 6 min read Redis is an in-memory key-value store used as a cache, database, and message broker. How it works, why it's sub-millisecond fast, and when to use it.
The Lycoris Team · · 4 min read SQL and NoSQL aren't rivals — they suit different shapes of data. How relational and non-relational databases compare, and how to pick.
Chisato · · 5 min read PostgreSQL is a powerful, open-source relational database known for reliability and extensibility. Learn how Postgres works and why developers love it.
Chisato · · 3 min read A vector database stores embeddings and finds information by meaning, not keywords — the backbone of AI search and RAG. Here's how vector databases work.
The Lycoris Team · · 5 min read Putting compute at the edge is old news — now the data is moving there too. Edge databases promise low latency everywhere, with some real trade-offs.
