Topic
43 posts tagged “SQL”.
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.
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 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 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.
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 · · 4 min read DuckDB is an embedded, columnar SQL database built for fast analytical queries directly inside your application process. How it works.
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.
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 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 · · 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 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 · · 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 · · 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 · · 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.
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.
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.
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 · · 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 · · 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 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 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 · · 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 · · 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.
