Tag: sql
Browse 36 practical software engineering resources tagged with "sql". Discover code recipes, design patterns, documentation templates, and in-depth guides to help you build, deploy, and maintain production-ready solutions involving sql.
Transform Data in the Warehouse with dbt
How to use dbt for SQL-based data transformations with models, tests, materializations, macros, and incremental loading in a data warehouse.
Recursive CTEs for Hierarchical Data Queries
How to query hierarchical data with recursive Common Table Expressions in SQL, covering tree traversal, org charts, category trees, and cycle detection.
Caching with Redis
How to implement application caching using Redis for performance and scalability.
Handle Database Deadlocks and Retries
Detect, prevent, and recover from database deadlocks with automatic retry logic, isolation levels, and query ordering strategies.
Manage Database Migrations Safely
How to version, apply, and rollback database schema changes using migration tools like Flyway, Alembic, and Liquibase in production environments.
Set Up Database Read Replicas for Scaling
Scale read-heavy workloads with database read replicas, replication lag monitoring, and read/write splitting across primary and replica instances.
Database Replication
Set up and manage database replication for high availability, read scaling, and disaster recovery with primary-replica architectures.
Database Transactions
How to use ACID transactions to ensure data integrity across Python, JavaScript, and Java with SQL examples.
Create and Use Database Views and Materialized Views
How to create and use database views and materialized views to simplify queries and improve read performance
Prevent and Resolve Deadlocks in SQL Transactions
Identify deadlock patterns in SQL databases, apply consistent lock ordering, use appropriate isolation levels, and implement retry logic for resilient concurrent transactions
Elasticsearch Aggregations for Analytics and Search
How to use Elasticsearch aggregations to build faceted search, analytics dashboards, and real-time metrics from indexed data
Implement Event Sourcing in a Relational Database
Build event sourcing systems using relational databases with event stores, projections, and snapshotting for audit and temporal querying.
CRUD Operations with MongoDB and Mongoose
How to perform Create, Read, Update, and Delete operations in MongoDB using Mongoose ODM with Node.js and Express
Implement Optimistic Locking with Versioning
How to implement optimistic locking with versioning to prevent lost updates in concurrent database access
PostgreSQL Query Optimization and Indexing Strategies
Analyze and optimize slow PostgreSQL queries using EXPLAIN, proper indexing, partial indexes, and query rewriting to reduce execution time from seconds to milliseconds
Database Schema Evolution
Evolve database schemas safely with backward-compatible changes, versioned migrations, and online DDL operations in production environments.
Soft Deletes
How to implement soft deletes to preserve data while hiding records from normal queries.
Find and Remove Duplicate Rows in SQL
Detect duplicate records in SQL tables using GROUP BY and HAVING, then remove them safely while keeping the canonical row.
Set Up Full-Text Search Indexes
Configure full-text search indexes in PostgreSQL to query large text columns with ranking, stemming, and highlighting.
Analyze and Optimize SQL Indexes with EXPLAIN
Identify missing, unused, and inefficient indexes by reading execution plans and measuring query cost with EXPLAIN.
SQL Joins
Practical examples of INNER, LEFT, RIGHT, and FULL OUTER JOINs with real-world query patterns.
Zero-Downtime Column Rename Migration
Rename columns or change data types without locking tables by using views, triggers, and backfill strategies.
Partition Large Tables by Date or Range
Split huge SQL tables into smaller partitions by date, range, or list to improve query performance and maintenance.
Traverse Hierarchical Data with Recursive CTEs
Query tree-like or graph-like structures in SQL using recursive common table expressions to walk parent-child relationships.
Rank Rows and Calculate Running Totals with Window Functions
Use SQL window functions to rank rows, compute running totals, and compare values within partitions without self-joins.
Database Query Tuning Checklist
Checklist for systematic SQL query optimization: EXPLAIN plan analysis, index strategy, N+1 query detection, join optimization, pagination patterns, connection pooling, query caching, and slow query log triage with examples for PostgreSQL and MySQL.
Database Schema Documentation Template
A template for documenting database schemas with entity relationships, field definitions, and migration history.
dbt: Models, Tests, Macros, Materializations
Master dbt for data transformations: models, tests, macros, materializations, seeds, snapshots, Jinja templating, and production patterns for analytics engineering.
Complete Guide to SQL Query Optimization
Optimize SQL queries. Covers EXPLAIN plan analysis, index strategies, join optimization, N+1 query detection, query rewriting, materialized views, partitioning, connection pooling, and query caching with practical PostgreSQL and MySQL examples.
Database Design Guide
A practical guide to designing relational databases with normalization, indexing, and relationship modeling.
SQL CTEs — Common Table Expressions Explained
A practical guide to SQL Common Table Expressions (CTEs): non-recursive and recursive CTEs, readability, performance, and when to use them over subqueries.
SQL Performance Tuning — Indexes, Queries, and Explain Plans
A practical guide to optimizing SQL queries: indexing strategies, query rewriting, EXPLAIN plan analysis, and common anti-patterns to avoid.
SQL Window Functions — Complete Guide
A practical guide to SQL window functions: ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, SUM, AVG over partitions, and real-world analytics use cases.