SQL

Keyset Pagination vs Offset

Efficient keyset (cursor) pagination pattern compared to traditional OFFSET for large datasets in PostgreSQL.

Best for: API list endpoints

#pagination#performance

UPSERT with ON CONFLICT

Insert or update rows atomically using PostgreSQL ON CONFLICT clause with partial indexes and conditions.

Best for: Sync external data sources

#upsert#postgresql

Recursive CTE for Tree Queries

Traverse hierarchical data like org charts and nested categories using PostgreSQL recursive common table expressions.

Best for: Org chart navigation

#cte#recursive

#full-text-search#postgresql

Full-Text Search with Ranking

PostgreSQL full-text search using tsvector, tsquery, and ts_rank with trigram similarity for fuzzy matching.

Best for: Site search engines

Window Functions with RANK and LAG

Use window functions to rank rows, calculate running totals, and compare with previous rows without self-joins.

Best for: Business reporting

#window-functions#rank

JSON Aggregation and Querying

Aggregate related rows into JSON arrays and query JSONB columns with PostgreSQL native JSON operators.

Best for: API response building

#json#jsonb

Soft Delete with Filtered Indexes

Implement soft deletes using a deleted_at column with partial indexes and views for transparent querying.

Best for: User account deletion

#soft-delete#pattern

Audit Log Trigger Function

Automatically record all INSERT, UPDATE, and DELETE operations into an audit log table via PostgreSQL triggers.

Best for: Compliance logging

#audit#trigger

#materialized-view#performance

Materialized View with Auto-Refresh

Create and maintain materialized views for expensive aggregate queries with concurrent refresh support.

Best for: Dashboard analytics

LATERAL Join for Top-N Per Group

Use LATERAL joins to efficiently fetch the top N related rows per group without window function subqueries.

Best for: Top-N per group queries

#lateral-join#top-n

Optimistic Locking with Version Column

Prevent lost updates in concurrent environments using a version column for optimistic concurrency control.

Best for: Multi-user editing

#concurrency#locking

Row-Level Security Policies

Enforce data access rules at the database level with PostgreSQL Row-Level Security policies.

Best for: Multi-tenant databases

#security#rls

#partitioning#performance

Table Partitioning by Range

Partition large tables by date range for faster queries and easier data lifecycle management.

Best for: Time-series data

#constraints#transactions

Deferred Foreign Key Constraints

Defer constraint checking to transaction commit for circular references and batch operations.

Best for: Circular references

SQL MERGE (Standard Upsert)

Use the SQL MERGE statement for atomic insert-or-update operations with matched/not-matched clauses.

Best for: Data warehouse loading

#merge#upsert

String Aggregation with GROUP BY

Concatenate grouped values into comma-separated strings using STRING_AGG with ordering and filtering.

Best for: Tag lists per item

#aggregation#string-agg

#generate-series#calendar

Generate Series Calendar Table

Create a date calendar using generate_series for gap-free time series reporting and joins.

Best for: Daily revenue reports

EXISTS vs IN Subquery Patterns

Choose between EXISTS and IN subqueries for optimal performance based on data distribution.

Best for: Filtering with related tables

#subquery#exists

Covering Index (INCLUDE Columns)

Create covering indexes with INCLUDE columns to satisfy queries entirely from the index.

Best for: Index-only scans

#indexing#performance

JSONB Query and Indexing Patterns

Query, filter, and index JSONB columns in PostgreSQL for flexible document-style data storage.

Best for: Flexible metadata storage

#jsonb#postgres

Recursive CTE for Hierarchical Data

Query hierarchical data like org charts, categories, and file trees using recursive CTEs.

Best for: Querying organizational hierarchies

#sql#cte

SQL Pivot and Crosstab Queries

Transform row data into columnar reports using CASE expressions, FILTER, and crosstab patterns.

Best for: Creating monthly revenue reports

#sql#pivot

SQL Upsert and Merge Patterns

Insert or update records atomically using ON CONFLICT, MERGE, and database-specific upsert syntax.

Best for: Syncing data from external sources

#sql#upsert

SQL Index Strategy Patterns

Create effective indexes including composite, partial, covering, and expression-based indexes.

Best for: Optimizing slow database queries

#sql#indexes

SQL Date and Time Functions

Essential date/time operations for filtering, formatting, calculating intervals, and time zones.

Best for: Filtering records by relative date ranges

#sql#dates

SQL String Manipulation Functions

Common string operations for cleaning, formatting, searching, and transforming text data in SQL.

Best for: Data cleaning and normalization

#sql#strings

SQL Materialized View Pattern

Create and manage materialized views for caching expensive queries with refresh strategies.

Best for: Caching expensive analytics queries

#sql#materialized-view

SQL LATERAL Join Examples

Use LATERAL joins to run correlated subqueries for top-N per group and row-dependent lookups.

Best for: Top-N per group queries efficiently

#sql#lateral

SQL Transaction Isolation Levels

Understand and use transaction isolation levels to control concurrency and data consistency.

Best for: Preventing race conditions in financial transactions

#sql#transactions

SQL EXPLAIN ANALYZE for Query Tuning

Use EXPLAIN ANALYZE to understand query plans, identify bottlenecks, and optimize slow queries.

Best for: Diagnosing and fixing slow database queries

#sql#explain

Handle NULLs with COALESCE

Use COALESCE to replace NULL values with a default in SQL queries.

Best for: Replacing NULL values in reports

#sql#coalesce

Self Join for Hierarchical Data

Use a self join to query hierarchical relationships like org charts.

Best for: Org chart queries

#sql#self-join

LATERAL Join for Correlated Subqueries

Use LATERAL join to reference columns from preceding tables in subqueries.

Best for: Top-N per group queries

#sql#lateral

MERGE / UPSERT Statement

Use MERGE or INSERT ON CONFLICT to upsert rows in a single statement.

Best for: Syncing data from staging tables

#sql#merge

GROUPING SETS, CUBE, and ROLLUP

Generate multiple levels of aggregation in a single query with grouping sets.

Best for: Report subtotals

#sql#grouping-sets

Query JSON Columns in SQL

Extract and filter data stored in JSON columns using SQL JSON functions.

Best for: Querying semi-structured data

#sql#json

Create and Refresh Materialized Views

Use materialized views to cache expensive query results for fast reads.

Best for: Dashboard caching

#sql#materialized-view

NTILE and Percentile Window Functions

Distribute rows into buckets and compute percentiles with window functions.

Best for: Customer segmentation

#sql#ntile

Generate a Date Series

Create a continuous date range to fill gaps in time-series queries.

Best for: Filling gaps in time-series data

#sql#date-series

Read EXPLAIN ANALYZE Output

Use EXPLAIN ANALYZE to understand and optimize query execution plans.

Best for: Query performance tuning

#sql#explain

Aggregate Strings with STRING_AGG / GROUP_CONCAT

Concatenate values from multiple rows into a single string per group.

Best for: Comma-separated lists in reports

#sql#string-agg

Temporal Tables for Data Versioning

Track historical changes to rows using system-versioned temporal tables.

Best for: Audit trail

#sql#temporal-tables

Conditional Aggregation with FILTER / CASE

Compute multiple conditional aggregates in a single query using FILTER or CASE.

Best for: Pivot-style reports

#sql#conditional

Window Frame ROWS vs RANGE Clauses

Control exactly which rows a window function considers using frame specifications.

Best for: Moving averages

#sql#window-frame

Common Table Expression (CTE) Patterns

Use CTEs to write readable, composable SQL queries with WITH clauses for complex logic.

Best for: complex queries

#sql#cte

INSERT ... RETURNING for Immediate Results

Use RETURNING clause to get inserted rows immediately without a separate SELECT query.

Best for: getting generated IDs

#sql#insert

CASE WHEN Conditional Expressions

Use CASE expressions for conditional logic in SELECT, WHERE, ORDER BY, and aggregations.

Best for: data categorization

#sql#case-when

Subquery Patterns — Scalar, Correlated, Derived

Common subquery patterns including scalar, correlated, and derived table subqueries.

Best for: complex filtering

#sql#subquery

Array and UNNEST Operations

Work with array columns using UNNEST, ARRAY_AGG, and array operators in PostgreSQL.

Best for: tag systems

#sql#arrays

GRANT and REVOKE Permission Management

Manage database permissions with GRANT and REVOKE for roles, schemas, and tables.

Best for: database security

#sql#permissions

View - Technique 1

Create and manage database views

Best for: database operations

Index - Technique 2

Create and optimize database indexes

Best for: database operations

Trigger - Technique 3

Create triggers for automatic actions

Best for: database operations

Procedure - Technique 4

Create stored procedures

Best for: database operations

Function - Technique 5

Create user-defined functions

Best for: database operations

Cursor - Technique 6

Work with database cursors

Best for: database operations

Transaction - Technique 7

Manage database transactions

Best for: database operations

Lock - Technique 8

Handle database locking

Best for: database operations

Constraint - Technique 9

Add database constraints

Best for: database operations

Sequence - Technique 10

Create and use sequences

Best for: database operations

Partition - Technique 11

Partition large tables

Best for: database operations

JSON - Technique 12

Work with JSON data

Best for: database operations

XML - Technique 13

Parse XML data

Best for: database operations

Date - Technique 14

Handle date functions

Best for: database operations

String - Technique 15

String manipulation functions

Best for: database operations

Math - Technique 16

Mathematical functions

Best for: database operations

Window - Technique 17

Advanced window functions

Best for: database operations

Subquery - Technique 18

Use subqueries

Best for: database operations

Join - Technique 19

Advanced join techniques

Best for: database operations

Aggregate - Technique 20

Aggregate functions

Best for: database operations

Window Analytics - Technique 21

Advanced analytics

Best for: database operations

Materialized View - Technique 22

Materialized views

Best for: database operations

Composite Index - Technique 23

Multi-column indexes

Best for: database operations

Full Text Search - Technique 24

Full text indexing

Best for: database operations

Explain Plan - Technique 25

Query execution plans

Best for: database operations

Deadlock - Technique 26

Handle deadlocks

Best for: database operations

Replication - Technique 27

Database replication

Best for: database operations

Backup - Technique 28

Database backups

Best for: database operations

Restore - Technique 29

Database restoration

Best for: database operations

Archive - Technique 30

Data archiving

Best for: database operations

Vacuum - Technique 31

Database maintenance

Best for: database operations

Analyze - Technique 32

Query analysis

Best for: database operations

Statistics - Technique 33

Table statistics

Best for: database operations

Cluster - Technique 34

Data clustering

Best for: database operations

Sharding - Technique 35

Database sharding

Best for: database operations

Partitioning Strategy - Technique 36

Partitioning patterns

Best for: database operations

Denormalization - Technique 37

Denormalization patterns

Best for: database operations

Normalization - Technique 38

Database normalization

Best for: database operations

ETL Pipeline - Technique 39

Extract Transform Load

Best for: database operations

Data Warehouse - Technique 40

Data warehouse design

Best for: database operations

Recursive CTE - Technique 41

Recursive query traversal

Best for: database operations

Upsert - Technique 42

Insert or update rows

Best for: database operations

Merge - Technique 43

Merge source into target

Best for: database operations

Pivot - Technique 44

Pivot row values into columns

Best for: database operations

Unpivot - Technique 45

Unpivot columns into rows

Best for: database operations

Query Hint - Technique 46

Use optimizer hints

Best for: database operations

GRANT - Technique 47

Grant roles and privileges

Best for: database operations

RLS - Technique 48

Row level security policies

Best for: database operations

Audit - Technique 49

Track data mutations

Best for: database operations

CTE - Technique 50

Reusable query blocks

Best for: database operations