Topic
27 posts tagged “SQL”.
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.
