</>
🗄️

SQL

Advanced SQL patterns for PostgreSQL including CTEs, window functions, and performance optimizations.

20 snippets

Showing 20 of 20 snippets

sqlintermediate

Keyset Pagination vs Offset

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

#pagination#performance
sqlintermediate

UPSERT with ON CONFLICT

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

#upsert#postgresql
sqladvanced

Recursive CTE for Tree Queries

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

#cte#recursive
sqladvanced

Full-Text Search with Ranking

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

#full-text-search#postgresql
sqlintermediate

Window Functions with RANK and LAG

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

#window-functions#rank
sqlintermediate

JSON Aggregation and Querying

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

#json#jsonb
sqlintermediate

Soft Delete with Filtered Indexes

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

#soft-delete#pattern
sqladvanced

Audit Log Trigger Function

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

#audit#trigger
sqladvanced

Materialized View with Auto-Refresh

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

#materialized-view#performance
sqladvanced

LATERAL Join for Top-N Per Group

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

#lateral-join#top-n
sqlintermediate

Optimistic Locking with Version Column

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

#concurrency#locking
sqladvanced

Row-Level Security Policies

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

#security#rls
sqladvanced

Table Partitioning by Range

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

#partitioning#performance
sqlintermediate

Deferred Foreign Key Constraints

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

#constraints#transactions
sqlintermediate

SQL MERGE (Standard Upsert)

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

#merge#upsert
sqlbeginner

String Aggregation with GROUP BY

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

#aggregation#string-agg
sqlbeginner

Generate Series Calendar Table

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

#generate-series#calendar
sqlbeginner

EXISTS vs IN Subquery Patterns

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

#subquery#exists
sqlintermediate

Covering Index (INCLUDE Columns)

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

#indexing#performance
sqlintermediate

JSONB Query and Indexing Patterns

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

#jsonb#postgres