Database SQL
20 items tagged with "database-sql"
Anti-Patterns5
SELECT * Everywhere
Querying all columns by default instead of naming the ones you need, wasting I/O and creating brittle coupling to schema order and shape.
Implicit Columns in INSERT
Writing INSERT statements without naming columns, relying on positional order so a schema change silently misaligns or corrupts data.
Missing Indexes
Querying large tables with no supporting index, forcing full scans that work in testing and collapse under production data volume.
Index Overuse
Adding an index for every column just in case, bloating storage and slowing every write while most indexes are never used by the planner.
Unbounded Result Sets
Querying without a LIMIT and loading entire growing tables into memory, so a query that was fine at launch crashes the app as data accumulates.
Comparisons5
Postgres vs CockroachDB
The versatile single-node-first relational database versus a distributed, Postgres-compatible SQL database built for global scale.
MySQL vs MariaDB
The widely deployed relational database now under Oracle versus its community-driven fork with diverging features.
SQLite vs Postgres
An embedded, serverless, single-file database versus a full-featured client-server relational database system.
MySQL vs MongoDB
A widely used relational database with structured schemas versus a flexible document database for evolving data.
TimescaleDB vs InfluxDB
A Postgres extension that turns SQL into a time-series database versus InfluxDB, a purpose-built time-series platform.
Glossaries9
ACID
ACID is a set of four properties — Atomicity, Consistency, Isolation, and Durability — that guarantee database transactions are processed reliably even in the presence of errors, crashes, or concurrent access.
Partitioning
Partitioning is the practice of dividing a large table or dataset into smaller, more manageable pieces called partitions, which can be queried and maintained independently while appearing as a single logical entity.
Database Index
A database index is an auxiliary data structure that improves the speed of data retrieval on a table at the cost of extra storage and slower writes, by letting the engine locate rows without scanning the entire table.
Normalization
Normalization is the process of organizing relational database tables to reduce data redundancy and improve integrity by decomposing them according to normal forms and linking related data with keys.
Denormalization
Denormalization is the deliberate introduction of redundancy into a database schema — by duplicating or precomputing data — to improve read performance, accepting reduced write efficiency and integrity guarantees.
OLTP
OLTP (Online Transaction Processing) refers to database systems optimized for large numbers of short, concurrent transactions — inserts, updates, and lookups — that support day-to-day operational applications.
Materialized View
A materialized view is a database object that stores the precomputed result of a query physically on disk, so reads return instantly without re-executing the underlying query, at the cost of keeping the result refreshed.
Primary Key
A primary key is a column or set of columns that uniquely identifies each row in a relational database table, enforcing uniqueness and non-null values and serving as the row's canonical identifier.
Foreign Key
A foreign key is a column or set of columns in one relational table that references the primary key of another table, enforcing referential integrity by ensuring referenced rows exist.