sql-proMasters modern SQL across PostgreSQL, BigQuery, Snowflake, and hybrid OLTP/OLAP systems — covering advanced query techniques, dimensional modeling, time-series SQL, and data warehouse patterns. Use when writing complex analytics SQL, designing cloud database schemas, or optimizing cross-platform SQL workloads.
Independent GitHub snapshot · Automated classification · Original documentation governs
NEVER WRITE SQL WITHOUT KNOWING THE QUERY PLAN — USE EXPLAIN ANALYZE BEFORE CLAIMING A QUERY IS OPTIMIZED
python-dev, nestjs-api, or java-spring-api)database-schema-designer for Firestore patterns)sql-optimization-patterns)| Workload | Platform | Key Consideration |
|---|---|---|
| OLTP (transactional) | PostgreSQL | Normalize to 3NF, index FKs and WHERE cols |
| OLAP (analytics) | BigQuery / Snowflake / Redshift | Denormalize, columnar storage, partition by date |
| HTAP (both) | CockroachDB / TiDB | Read replicas for analytics, avoid read-your-own-writes latency |
| Time-series | TimescaleDB / InfluxDB | Hypertables, continuous aggregates, retention policies |
| Data warehouse | Redshift / Databricks | Star schema, materialized views, query concurrency |
| Graph + SQL | Neo4j / Amazon Neptune | Cypher for traversal, SQL for aggregation |
EXPLAIN ANALYZE (PostgreSQL) or query profile (BigQuery/Snowflake)OVER (PARTITION BY ... ORDER BY ...)@>, #>>, jsonb_path_queryto_tsvector, ts_rank, GIN indexestimestamptz, AT TIME ZONE, generate_series for gapspg_stat_statements, pg_trgm, uuid-ossp, pgcryptoINFORMATION_SCHEMAMERGE INTO, streaming + batch unification-- Running total + rank within partition
SELECT
user_id,
order_date,
total,
SUM(total) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total,
RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank_in_user
FROM orders;
WITH RECURSIVE org_tree AS (
-- Base case: root nodes
SELECT id, name, parent_id, 0 AS depth
FROM employees WHERE parent_id IS NULL
UNION ALL
-- Recursive case
SELECT e.id, e.name, e.parent_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;
-- Write path: OLTP primary (PostgreSQL)
INSERT INTO orders (user_id, total, created_at) VALUES ($1, $2, NOW());
-- Read path: replica or analytics DB
-- Use logical replication to BigQuery/Snowflake for heavy aggregations
SELECT DATE_TRUNC('month', created_at), SUM(total)
FROM orders
GROUP BY 1 ORDER BY 1;
-- Route this to read replica, not primary
-- Invalidate current record, insert new version
UPDATE dim_customers
SET valid_to = NOW(), is_current = FALSE
WHERE customer_id = $1 AND is_current = TRUE;
INSERT INTO dim_customers (customer_id, name, email, valid_from, valid_to, is_current)
VALUES ($1, $2, $3, NOW(), '9999-12-31', TRUE);
SELECT * on wide fact tables in analytics workloads — columns are stored separately in columnar DBs/postgresql/postgresql/googleapis/google-cloud-bigqueryWebFetch from Snowflake documentation(None yet — patterns are inline above. Add reference/cloud-platform-sql.md when cloud-specific patterns grow beyond this file.)
sql-optimization-patterns — PostgreSQL OLTP query tuning, EXPLAIN analysis, N+1 fixes, cursor paginationdatabase-schema-designer — Schema design, Flyway migrations, normalization, FK/index rulesvector-database — pgvector, HNSW/IVFFlat, RAG pipelinespython-dev — SQLAlchemy async, asyncpg, FastAPI integrationjava-spring-api — R2DBC reactive PostgreSQLnestjs-api — Prisma ORM, raw SQL via Prisma