|$ curl https://forge-ai.dev/api/markdown?path=docs/databases/sql

$cat docs/sql-fundamentals.md

updated Recently·45 min read·published

SQL Fundamentals

◆SQL◆Databases◆Backend◆Beginner to Advanced🎯Free Tools

Introduction

SQL (Structured Query Language) is the standard language for managing and querying relational databases. It has been the dominant database language for over four decades and remains the most widely used data manipulation language in the world.

Understanding SQL deeply is essential for any backend developer. Even if you use an ORM, knowing what happens under the hood helps you write better queries, debug performance issues, and make informed architectural decisions.

This guide covers SQL from the basics of SELECT through advanced topics like window functions, CTEs, and query optimization strategies.

Basic Queries

Every SQL query starts with a statement that tells the database what you want. The fundamental statements are SELECT, INSERT, UPDATE, and DELETE — known collectively as CRUD operations (Create, Read, Update, Delete).

basic-queries.sql

SQL

1	-- SELECT: Read data
2	SELECT id, name, email, created_at
3	FROM users
4	WHERE email LIKE '%@gmail.com'
5	ORDER BY created_at DESC
6	LIMIT 10 OFFSET 0;
7
8	-- SELECT with aliases and computed columns
9	SELECT
10	u.name AS user_name,
11	o.total AS order_total,
12	o.total * 0.1 AS tax,
13	CASE
14	WHEN o.total > 1000 THEN 'premium'
15	WHEN o.total > 100 THEN 'regular'
16	ELSE 'new'
17	END AS customer_tier
18	FROM users u
19	JOIN orders o ON o.user_id = u.id;
20
21	-- INSERT: Create data
22	INSERT INTO users (name, email, created_at)
23	VALUES
24	('Alice Johnson', 'alice@example.com', NOW()),
25	('Bob Smith', 'bob@example.com', NOW())
26	ON CONFLICT (email) DO UPDATE
27	SET name = EXCLUDED.name, updated_at = NOW();
28
29	-- UPDATE: Modify data
30	UPDATE users
31	SET name = 'Alice Chen', updated_at = NOW()
32	WHERE id = 'a1b2c3d4';
33
34	-- DELETE: Remove data
35	DELETE FROM users
36	WHERE created_at < NOW() - INTERVAL '1 year'
37	AND id NOT IN (SELECT user_id FROM orders);

ℹ

info

Always use parameterized queries or prepared statements in application code. Never concatenate user input into SQL strings — this opens you up to SQL injection attacks.

Filtering and Sorting

The WHERE clause filters rows before aggregation, while HAVING filters after aggregation. Understanding this distinction is critical for writing correct aggregate queries.

filtering.sql

SQL

1	-- WHERE: Filter rows before aggregation
2	SELECT user_id, COUNT(*) AS order_count
3	FROM orders
4	WHERE status = 'completed'
5	AND created_at >= '2025-01-01'
6	GROUP BY user_id
7	-- HAVING: Filter after aggregation
8	HAVING COUNT(*) >= 5
9	ORDER BY order_count DESC;
10
11	-- IN, BETWEEN, LIKE patterns
12	SELECT * FROM products
13	WHERE category IN ('electronics', 'books')
14	AND price BETWEEN 10.00 AND 99.99
15	AND (name ILIKE '%wireless%' OR description ILIKE '%bluetooth%');
16
17	-- NULL handling
18	SELECT * FROM users
19	WHERE deleted_at IS NULL
20	AND (phone IS NOT NULL OR email IS NOT NULL);
21
22	-- EXISTS (often faster than IN for large subqueries)
23	SELECT * FROM users u
24	WHERE EXISTS (
25	SELECT 1 FROM orders o
26	WHERE o.user_id = u.id
27	AND o.total > 500
28	);

JOINs

JOINs combine rows from two or more tables based on related columns. They are the cornerstone of relational database queries and one of the primary advantages of SQL over NoSQL databases.

joins.sql

SQL

1	-- INNER JOIN: Only matching rows from both tables
2	SELECT u.name, o.id AS order_id, o.total
3	FROM users u
4	INNER JOIN orders o ON o.user_id = u.id;
5
6	-- LEFT JOIN: All rows from left table, matching from right
7	SELECT u.name, COUNT(o.id) AS order_count
8	FROM users u
9	LEFT JOIN orders o ON o.user_id = u.id
10	GROUP BY u.id, u.name;
11
12	-- RIGHT JOIN: All rows from right table, matching from left
13	SELECT u.name, o.id
14	FROM users u
15	RIGHT JOIN orders o ON o.user_id = u.id;
16
17	-- FULL OUTER JOIN: All rows from both tables
18	SELECT u.name, o.id
19	FROM users u
20	FULL OUTER JOIN orders o ON o.user_id = u.id;
21
22	-- CROSS JOIN: Cartesian product (every combination)
23	SELECT p.name AS product, c.name AS color
24	FROM products p
25	CROSS JOIN colors c;
26
27	-- SELF JOIN: Table joined with itself
28	SELECT
29	e.name AS employee,
30	m.name AS manager
31	FROM employees e
32	LEFT JOIN employees m ON e.manager_id = m.id;
33
34	-- JOIN with multiple conditions and aggregates
35	SELECT
36	u.name,
37	COUNT(DISTINCT o.id) AS order_count,
38	COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS lifetime_value
39	FROM users u
40	LEFT JOIN orders o ON o.user_id = u.id AND o.status != 'cancelled'
41	LEFT JOIN order_items oi ON oi.order_id = o.id
42	GROUP BY u.id, u.name
43	ORDER BY lifetime_value DESC
44	LIMIT 50;

⚠

warning

Be careful with CROSS JOINs on large tables — they produce the Cartesian product of both tables. A table with 10,000 rows crossed with another 10,000 rows produces 100 million rows.

Subqueries and CTEs

Subqueries are queries nested inside other queries. Common Table Expressions (CTEs) use the WITH clause to create named temporary result sets that improve readability and enable recursive queries.

subqueries-ctes.sql

SQL

1	-- Scalar subquery: returns a single value
2	SELECT
3	name,
4	email,
5	(SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS total_orders
6	FROM users;
7
8	-- Correlated subquery: references outer query
9	SELECT *
10	FROM products p
11	WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);
12
13	-- CTE: Named temporary result set
14	WITH monthly_revenue AS (
15	SELECT
16	DATE_TRUNC('month', created_at) AS month,
17	SUM(total) AS revenue,
18	COUNT(*) AS order_count
19	FROM orders
20	WHERE status = 'completed'
21	GROUP BY DATE_TRUNC('month', created_at)
22	),
23	growth AS (
24	SELECT
25	month,
26	revenue,
27	LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
28	ROUND(
29	(revenue - LAG(revenue) OVER (ORDER BY month))
30	/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
31	2
32	) AS growth_pct
33	FROM monthly_revenue
34	)
35	SELECT * FROM growth
36	WHERE growth_pct > 10
37	ORDER BY month DESC;
38
39	-- Recursive CTE: traverse hierarchical data
40	WITH RECURSIVE org_tree AS (
41	-- Base case: top-level employees (no manager)
42	SELECT id, name, manager_id, 1 AS depth
43	FROM employees
44	WHERE manager_id IS NULL
45
46	UNION ALL
47
48	-- Recursive case: employees with managers
49	SELECT e.id, e.name, e.manager_id, t.depth + 1
50	FROM employees e
51	INNER JOIN org_tree t ON e.manager_id = t.id
52	)
53	SELECT * FROM org_tree
54	ORDER BY depth, name;

ℹ

info

CTEs are generally more readable than deeply nested subqueries. Use them whenever a query has multiple logical steps. They also enable recursive queries, which are impossible with simple subqueries.

Aggregate Functions

Aggregate functions compute a single value from a set of rows. They are used with GROUP BY to summarize data across categories.

aggregates.sql

SQL

1	-- Core aggregate functions
2	SELECT
3	COUNT(*) AS total_orders,
4	COUNT(DISTINCT user_id) AS unique_customers,
5	SUM(total) AS total_revenue,
6	AVG(total) AS avg_order_value,
7	MIN(total) AS smallest_order,
8	MAX(total) AS largest_order,
9	ROUND(STDDEV(total), 2) AS stddev_order_value
10	FROM orders
11	WHERE status = 'completed';
12
13	-- GROUP BY with multiple dimensions
14	SELECT
15	DATE_TRUNC('week', o.created_at) AS week,
16	p.category,
17	COUNT(DISTINCT o.id) AS orders,
18	SUM(oi.quantity) AS units_sold,
19	SUM(oi.quantity * oi.unit_price) AS revenue
20	FROM orders o
21	JOIN order_items oi ON oi.order_id = o.id
22	JOIN products p ON p.id = oi.product_id
23	WHERE o.status = 'completed'
24	GROUP BY DATE_TRUNC('week', o.created_at), p.category
25	ORDER BY week DESC, revenue DESC;
26
27	-- GROUPING SETS: multiple aggregation levels in one query
28	SELECT
29	COALESCE(category, 'ALL') AS category,
30	COALESCE(status::text, 'ALL') AS status,
31	COUNT(*) AS order_count,
32	SUM(total) AS revenue
33	FROM orders o
34	JOIN products p ON p.id = o.product_id
35	GROUP BY GROUPING SETS (
36	(p.category, o.status),
37	(p.category),
38	(o.status),
39	()
40	)
41	ORDER BY GROUPING(p.category), GROUPING(o.status), revenue DESC;

Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing them (unlike GROUP BY). They are incredibly powerful for ranking, running totals, moving averages, and comparisons.

window-functions.sql

SQL

1	-- Ranking functions
2	SELECT
3	name,
4	department,
5	salary,
6	ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
7	RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
8	DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
9	NTILE(4) OVER (ORDER BY salary DESC) AS quartile
10	FROM employees;
11
12	-- Running totals and moving averages
13	SELECT
14	DATE_TRUNC('day', created_at) AS day,
15	revenue,
16	SUM(revenue) OVER (ORDER BY DATE_TRUNC('day', created_at)) AS cumulative_revenue,
17	AVG(revenue) OVER (
18	ORDER BY DATE_TRUNC('day', created_at)
19	ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
20	) AS moving_avg_7day
21	FROM daily_revenue;
22
23	-- LAG and LEAD for period-over-period comparisons
24	SELECT
25	DATE_TRUNC('month', created_at) AS month,
26	revenue,
27	LAG(revenue, 1) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS prev_month,
28	LEAD(revenue, 1) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS next_month,
29	ROUND(
30	(revenue - LAG(revenue, 1) OVER (ORDER BY DATE_TRUNC('month', created_at)))
31	/ NULLIF(LAG(revenue, 1) OVER (ORDER BY DATE_TRUNC('month', created_at)), 0)
32	* 100, 2
33	) AS growth_pct
34	FROM monthly_revenue;
35
36	-- First and last value in a partition
37	SELECT
38	user_id,
39	order_date,
40	total,
41	FIRST_VALUE(total) OVER (
42	PARTITION BY user_id ORDER BY order_date
43	) AS first_order_total,
44	LAST_VALUE(total) OVER (
45	PARTITION BY user_id ORDER BY order_date
46	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
47	) AS last_order_total
48	FROM orders;

🔥

pro tip

Window functions are evaluated AFTER WHERE and GROUP BY but can be used in SELECT and ORDER BY. They do not reduce the number of rows returned. If you need to filter on a window function result, use a CTE or subquery.

Indexes

Indexes are data structures that speed up data retrieval at the cost of additional storage and slower writes. Choosing the right indexes is one of the most impactful things you can do for database performance.

indexes.sql

SQL

1	-- B-tree index (default, most common)
2	CREATE INDEX idx_users_email ON users (email);
3	CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
4
5	-- Composite index (multi-column)
6	-- Order matters! Put high-selectivity columns first
7	CREATE INDEX idx_orders_user_status ON orders (user_id, status);
8	CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
9
10	-- Partial index (index only a subset of rows)
11	CREATE INDEX idx_orders_pending ON orders (user_id, created_at)
12	WHERE status = 'pending';
13
14	-- Covering index (includes all columns needed by query)
15	CREATE INDEX idx_orders_covering ON orders (user_id, status, total)
16	INCLUDE (created_at);
17
18	-- GIN index (for arrays, JSONB, full-text search)
19	CREATE INDEX idx_products_tags ON products USING GIN (tags);
20	CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
21
22	-- GiST index (for geometric, range, full-text)
23	CREATE INDEX idx_events_daterange ON events USING GiST (during);
24
25	-- Expression index (index on computed value)
26	CREATE INDEX idx_users_lower_email ON users (LOWER(email));
27
28	-- Check index usage
29	SELECT
30	indexrelname AS index_name,
31	idx_scan AS times_used,
32	pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
33	FROM pg_stat_user_indexes
34	ORDER BY idx_scan DESC;
35
36	-- Drop unused indexes
37	DROP INDEX IF EXISTS idx_unused_index;

⚠

warning

More indexes are not always better. Each index slows down INSERT, UPDATE, and DELETE operations because the index must be updated too. Monitor your index usage with pg_stat_user_indexes and remove unused indexes.

Transactions

Transactions group multiple operations into a single atomic unit. Either all operations succeed, or all are rolled back. This is essential for data integrity in multi-step operations.

transactions.sql

SQL

1	-- Basic transaction
2	BEGIN;
3	INSERT INTO orders (user_id, status, total) VALUES ('u1', 'pending', 99.99);
4	INSERT INTO order_items (order_id, product_id, quantity, unit_price)
5	VALUES (currval('orders_id_seq'), 'p1', 2, 49.99);
6	UPDATE products SET stock = stock - 2 WHERE id = 'p1';
7	COMMIT;
8
9	-- Transaction with error handling (PostgreSQL)
10	DO $$
11	DECLARE
12	v_order_id UUID;
13	v_stock INTEGER;
14	BEGIN
15	-- Check stock first
16	SELECT stock INTO v_stock FROM products WHERE id = 'p1' FOR UPDATE;
17
18	IF v_stock < 2 THEN
19	RAISE EXCEPTION 'Insufficient stock: % available', v_stock;
20	END IF;
21
22	-- Create order
23	INSERT INTO orders (user_id, status, total)
24	VALUES ('u1', 'pending', 99.99)
25	RETURNING id INTO v_order_id;
26
27	-- Create order items
28	INSERT INTO order_items (order_id, product_id, quantity, unit_price)
29	VALUES (v_order_id, 'p1', 2, 49.99);
30
31	-- Decrement stock
32	UPDATE products SET stock = stock - 2 WHERE id = 'p1';
33
34	RAISE NOTICE 'Order % created successfully', v_order_id;
35	EXCEPTION
36	WHEN OTHERS THEN
37	RAISE NOTICE 'Transaction failed: %', SQLERRM;
38	-- Implicit ROLLBACK on exception
39	END $$;
40
41	-- Isolation levels
42	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
43	-- Levels: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE

ℹ

info

In PostgreSQL, the default isolation level is READ COMMITTED. Use SERIALIZABLE when you need the strongest guarantees — for example, when concurrent transactions could cause logical inconsistencies that simple row locking cannot prevent.

PostgreSQL vs MySQL

PostgreSQL and MySQL are the two most popular open-source relational databases. While they share the SQL foundation, they differ significantly in features, performance characteristics, and philosophy.

Feature	PostgreSQL	MySQL
ACID Compliance	Full ACID with MVCC	InnoDB: yes, MyISAM: no
JSON Support	JSONB (binary, indexable, queryable)	JSON (text, limited indexing)
Extensions	Rich ecosystem (PostGIS, pgvector, TimescaleDB)	Limited plugin system
Standards Compliance	Very high SQL standard compliance	Some non-standard syntax
Replication	Streaming, logical, synchronous	Group replication, InnoDB Cluster
Partitioning	Declarative (native)	Range, list, hash (InnoDB)
Full-Text Search	Built-in ts_vector/ts_query	Built-in FULLTEXT index
Concurrency	MVCC (no read locks)	MVCC (InnoDB) + gap locking
Best For	Complex queries, data integrity, GIS	Web apps, read-heavy, simple schemas

pg-vs-mysql.sql

SQL

1	-- PostgreSQL-specific: JSONB querying
2	SELECT
3	name,
4	metadata->>'brand' AS brand,
5	(metadata->'specs'->>'weight')::numeric AS weight
6	FROM products
7	WHERE metadata @> '{"features": ["wireless"]}'
8	AND (metadata->'specs'->>'battery_life')::int > 24
9	ORDER BY metadata->>'brand';
10
11	-- MySQL equivalent: JSON (less powerful)
12	SELECT
13	name,
14	JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.brand')) AS brand,
15	CAST(JSON_EXTRACT(metadata, '$.specs.weight') AS DECIMAL) AS weight
16	FROM products
17	WHERE JSON_CONTAINS(metadata->'$.features', '"wireless"')
18	AND CAST(JSON_EXTRACT(metadata, '$.specs.battery_life') AS UNSIGNED) > 24
19	ORDER BY JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.brand'));

✓

best practice

For new projects in 2026, PostgreSQL is the default recommendation. It has superior JSONB support, richer indexing options, better standards compliance, and a more active extension ecosystem. MySQL is still excellent for simple web apps and has wider hosting support.

Essential Data Types

Choosing the right data types affects storage efficiency, query performance, and data integrity. Here are the most commonly used types across PostgreSQL and MySQL.

data-types.sql

SQL

1	-- Numeric types
2	CREATE TABLE numeric_examples (
3	small_val SMALLINT, -- 2 bytes, -32768 to 32767
4	int_val INTEGER, -- 4 bytes, ~2 billion
5	big_val BIGINT, -- 8 bytes, ~9 quintillion
6	price DECIMAL(10, 2), -- exact precision, money
7	rating REAL, -- 4 bytes, ~6 decimal digits
8	precise DOUBLE PRECISION -- 8 bytes, ~15 decimal digits
9	);
10
11	-- Text types
12	CREATE TABLE text_examples (
13	code CHAR(3), -- fixed length (ISO country codes)
14	name VARCHAR(255), -- variable length, max specified
15	bio TEXT, -- unlimited length
16	slug VARCHAR(100) UNIQUE
17	);
18
19	-- Date and time types
20	CREATE TABLE temporal_examples (
21	created DATE, -- date only
22	starts TIME, -- time only
23	meeting TIMESTAMP, -- date + time, no timezone
24	created_at TIMESTAMPTZ, -- date + time, with timezone
25	duration INTERVAL, -- '2 hours 30 minutes'
26	birthday DATE DEFAULT CURRENT_DATE
27	);
28
29	-- Boolean
30	CREATE TABLE flag_examples (
31	is_active BOOLEAN DEFAULT TRUE,
32	is_admin BOOLEAN NOT NULL DEFAULT FALSE
33	);
34
35	-- UUID (PostgreSQL native, MySQL uses CHAR(36) or BINARY(16))
36	CREATE TABLE uuid_examples (
37	id UUID PRIMARY KEY DEFAULT gen_random_uuid()
38	);

ℹ

info

Use TIMESTAMPTZ (with timezone) instead of TIMESTAMP in PostgreSQL. It stores the timestamp in UTC and converts to the session timezone on display. This avoids ambiguity during daylight saving time transitions.

Views and Materialized Views

Views are saved queries that act as virtual tables. Materialized views store the result of a query physically, providing faster reads at the cost of needing periodic refreshes.

views.sql

SQL

1	-- Regular view: virtual table (re-executes query each time)
2	CREATE VIEW user_order_summary AS
3	SELECT
4	u.id,
5	u.name,
6	u.email,
7	COUNT(o.id) AS total_orders,
8	COALESCE(SUM(o.total), 0) AS lifetime_value,
9	MAX(o.created_at) AS last_order_date
10	FROM users u
11	LEFT JOIN orders o ON o.user_id = u.id
12	GROUP BY u.id, u.name, u.email;
13
14	-- Query the view like a normal table
15	SELECT * FROM user_order_summary
16	WHERE lifetime_value > 500
17	ORDER BY lifetime_value DESC;
18
19	-- Materialized view: stores results physically
20	CREATE MATERIALIZED VIEW monthly_revenue_report AS
21	SELECT
22	DATE_TRUNC('month', created_at) AS month,
23	COUNT(*) AS order_count,
24	SUM(total) AS revenue,
25	AVG(total) AS avg_order_value,
26	COUNT(DISTINCT user_id) AS unique_customers
27	FROM orders
28	WHERE status = 'completed'
29	GROUP BY DATE_TRUNC('month', created_at)
30	WITH DATA;
31
32	-- Refresh the materialized view (PostgreSQL)
33	REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_report;
34	-- CONCURRENTLY requires a UNIQUE index on the view
35
36	-- Check if materialized view needs refresh
37	SELECT
38	matviewname,
39	pg_size_pretty(pg_total_relation_size(schemaname \|\| '.' \|\| matviewname)) AS size
40	FROM pg_matviews;

Constraints and Data Integrity

Constraints enforce rules on your data at the database level, ensuring integrity regardless of which application writes to the database. They are your last line of defense against bad data.

constraints.sql

SQL

1	CREATE TABLE products (
2	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3	name VARCHAR(255) NOT NULL,
4	slug VARCHAR(255) NOT NULL UNIQUE,
5	price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
6	discount_price DECIMAL(10, 2) CHECK (discount_price >= 0),
7	stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
8	sku VARCHAR(50) UNIQUE,
9	status VARCHAR(20) DEFAULT 'draft'
10	CHECK (status IN ('draft', 'active', 'archived')),
11	created_at TIMESTAMPTZ DEFAULT NOW(),
12	updated_at TIMESTAMPTZ DEFAULT NOW(),
13
14	-- Ensure discount_price is less than price
15	CHECK (discount_price IS NULL OR discount_price < price),
16
17	-- Composite unique constraint
18	UNIQUE (name, status)
19	);
20
21	-- Foreign key with actions
22	CREATE TABLE order_items (
23	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
24	order_id UUID NOT NULL REFERENCES orders(id)
25	ON DELETE CASCADE ON UPDATE CASCADE,
26	product_id UUID NOT NULL REFERENCES products(id)
27	ON DELETE RESTRICT ON UPDATE CASCADE,
28	quantity INTEGER NOT NULL CHECK (quantity > 0),
29	unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0)
30	);
31
32	-- Deferrable constraint (can be deferred to end of transaction)
33	ALTER TABLE products
34	ADD CONSTRAINT check_discount_valid
35	CHECK (discount_price IS NULL OR discount_price < price)
36	DEFERRABLE INITIALLY DEFERRED;

✓

best practice

Always define constraints at the database level, not just in application code. Multiple applications, migrations, and manual edits can bypass application-level validation. Database constraints are the source of truth.

Common SQL Patterns

Here are battle-tested SQL patterns that solve common problems in web applications.

patterns.sql

SQL

1	-- Pagination with cursor (better than OFFSET for large datasets)
2	SELECT id, name, created_at
3	FROM users
4	WHERE created_at < $1 -- cursor: last seen timestamp
5	ORDER BY created_at DESC
6	LIMIT 20;
7
8	-- Upsert (insert or update)
9	INSERT INTO user_stats (user_id, login_count, last_login)
10	VALUES ($1, 1, NOW())
11	ON CONFLICT (user_id) DO UPDATE
12	SET
13	login_count = user_stats.login_count + 1,
14	last_login = NOW();
15
16	-- Soft delete pattern
17	UPDATE users SET deleted_at = NOW() WHERE id = $1;
18	-- Query only non-deleted:
19	SELECT * FROM users WHERE deleted_at IS NULL;
20
21	-- Slow query log (PostgreSQL)
22	ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1 second
23	SELECT pg_reload_conf();
24
25	-- Find slow queries
26	SELECT
27	query,
28	calls,
29	ROUND(total_exec_time::numeric, 2) AS total_ms,
30	ROUND(mean_exec_time::numeric, 2) AS avg_ms,
31	rows
32	FROM pg_stat_statements
33	ORDER BY total_exec_time DESC
34	LIMIT 10;

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor