Topic
69 posts tagged “Databases”.
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.
