Relational database
SQL queries, schema design, EXPLAIN ANALYZE, indexing, and transactions for university database coursework. The top grading deduction on CS186 final projects is missing an index on a JOIN column that turns O(n log n) into O(n squared), the cost our tutors verify with real EXPLAIN ANALYZE output. Verified CS graduates from Georgia Tech, Purdue, and BITS Pilani, starting at $20 per task, 12-hour average turnaround.
About
PostgreSQL is an open-source relational database that implements the SQL standard with extensions for window functions, common table expressions, JSON and JSONB columns, full-text search, geographic data via PostGIS, and concurrent index builds. The 16.x release line ships with logical replication, parallel query execution, just-in-time compilation for expression evaluation, and SQL/JSON path expressions. Students meet PostgreSQL in CS186 (Berkeley Database Systems), CS145 (Stanford Intro to Databases), 6.830 (MIT Database Systems), CMU 15-445 (Database Systems), and any web development course that picks a real database over SQLite.
The query planner uses cost-based optimization with statistics gathered by ANALYZE, choosing between sequential scan, index scan, index-only scan, bitmap heap scan, nested loop join, hash join, and merge join based on table sizes, predicate selectivity, and available indexes. CSHH tutors deliver schema designs in third normal form (with denormalization explicit and justified), SQL queries that EXPLAIN ANALYZE confirms uses index scans rather than sequential scans, indexes (B-tree for equality and range, GIN for full-text and JSONB containment, GiST for geometric and exclusion constraints, BRIN for time-series tables), transactions with the correct isolation level (READ COMMITTED default, REPEATABLE READ for reports, SERIALIZABLE for financial logic), and migrations using flyway, liquibase, or framework-native tools (Django migrations, Sequelize migrations, Alembic for SQLAlchemy).
Coursework
Student, Course, Section, Enrollment, Instructor, Department tables with proper foreign keys and ON DELETE actions, unique constraints (student_id + section_id + term), check constraints (grade BETWEEN 0 AND 100), B-tree indexes on (student_id) and (section_id) for the enrollment table to keep grade-lookup queries under 10ms even with 1 million enrollment rows. Tutors include EXPLAIN ANALYZE output proving the indexes are used.
Account, Transaction, Transfer tables with SERIALIZABLE isolation level on transfer operations to prevent concurrent debits exceeding balance, explicit row-level locks via SELECT FOR UPDATE, transaction begin and commit blocks, and savepoints for partial rollback. Tutors include pgbench scripts to demonstrate concurrent execution with no negative balances.
Product table with a tsvector column generated from name and description, GIN index on the tsvector column, search query using to_tsquery with ranking via ts_rank_cd, faceted filters via WHERE clauses on indexed columns. EXPLAIN ANALYZE shows the GIN index providing sub-10ms search latency on 100,000 product rows.
Reading table partitioned by month using PostgreSQL native partitioning (PARTITION BY RANGE (timestamp)), BRIN indexes on the timestamp column (200x smaller than B-tree for monotonically increasing data), aggregation queries using window functions (LAG, LEAD, percentile_cont), and CTAS (CREATE TABLE AS SELECT) for materialized rollup tables refreshed nightly.
Geometry and geography columns for points, polygons, and lines, GiST index on the geometry column for spatial queries, ST_Distance for nearest-neighbor queries, ST_Contains for point-in-polygon, ST_DWithin for proximity searches. Tutors include CS186-style queries for "find restaurants within 2km of coordinates" with sub-50ms latency.
audit_log table capturing INSERT, UPDATE, DELETE events on tracked tables, PL/pgSQL trigger functions using OLD and NEW row variables, AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW triggers, and JSONB column storing the before-and-after diff. Tutors include a query that reconstructs any row state at any historical timestamp.
Employee table with manager_id self-referencing foreign key, WITH RECURSIVE CTE that walks from a root manager down to leaf reports computing the depth and full path, and a derived view that exposes the org chart for direct querying. Tutors include the recursive termination condition and a worked example with 5 levels of management.
Debugging
-- Seq Scan on orders, cost=0..50000, 4200 ms
SELECT * FROM orders
WHERE date_trunc('day', created_at) = '2026-05-27';CREATE INDEX ix_orders_created ON orders(created_at);
-- Index Scan using ix_orders_created, 12 ms
SELECT * FROM orders
WHERE created_at >= '2026-05-27'
AND created_at < '2026-05-28';-- Two writers both see no row, both INSERT,
-- second one raises IntegrityError on the unique constraint
SELECT id FROM users WHERE email = '[email protected]';
INSERT INTO users (email) VALUES ('[email protected]');INSERT INTO users (email)
VALUES ('[email protected]')
ON CONFLICT (email) DO NOTHING
RETURNING id;Transaction A updates row 1 then row 2; transaction B updates row 2 then row 1. PostgreSQL detects the cycle and aborts one with ERROR: deadlock detected. Fix by always acquiring locks in a consistent order across the codebase (always update rows in primary-key order), wrap the operation in a retry loop that handles serialization_failure, or use SELECT FOR UPDATE OF table NOWAIT to fail fast instead of waiting on lock.
A GROUP BY query allocates a hash table exceeding work_mem and spills to disk (or worse, OOMs the process). EXPLAIN ANALYZE shows "Sort Method: external merge Disk: 500MB". Either increase work_mem (SET work_mem = "256MB" for the session), rewrite the query to filter before the aggregate (push WHERE inside a subquery), or use approximate functions (percentile_disc instead of full sort, hyperloglog for distinct counts).
A query that ran in 50ms now takes 5 seconds. Most likely the table grew past a threshold and statistics are stale. Run ANALYZE table_name, then EXPLAIN ANALYZE again. If the plan changed, the planner re-evaluated the cost model with updated row estimates. For tables with rapid churn, configure autovacuum more aggressively (ALTER TABLE x SET (autovacuum_vacuum_scale_factor = 0.05)).
UPDATE creates a new row version (MVCC) and marks the old one dead. Without VACUUM, dead tuples accumulate and inflate both the table and its indexes. Symptoms: table size grows but row count stable, queries slower over time. Fix: VACUUM ANALYZE table_name (online, no lock), VACUUM FULL table_name (rewrites table, exclusive lock, expensive), or pg_repack extension (rewrites without exclusive lock).
SERIALIZABLE isolation aborts a transaction when the planner detects a possible serialization anomaly. The application should retry on SQLSTATE 40001. The retry loop reads the transaction state fresh, recomputes, and commits again. Code that does not implement retry treats serialization_failure as a fatal error, which it is not.
WHERE data->>"status" = "active" does a sequential scan because the function call prevents B-tree index use on data. Either create a GIN index on the JSONB column (CREATE INDEX ix_x_data_gin ON x USING GIN (data)), which supports containment queries (WHERE data @> {"status": "active"}), or a partial functional B-tree index for the specific path (CREATE INDEX ix_x_status ON x((data->>"status"))).
Code Examples
-- Without the (customer_id) index this Seq Scans 1M rows
EXPLAIN ANALYZE
SELECT c.name, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
AND o.created_at >= now() - interval '30 days'
GROUP BY c.name
ORDER BY order_count DESC
LIMIT 25;
-- Supporting indexes
CREATE INDEX ix_orders_customer ON orders(customer_id);
CREATE INDEX ix_orders_status_created ON orders(status, created_at DESC);SELECT customer_id,
order_id,
total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rk
FROM orders
WHERE created_at >= date_trunc('month', now())
ORDER BY customer_id, rk;Related
FAQ
Submit your PostgreSQL assignment and get a working, commented solution within 12 hours from a verified CS graduate. Plagiarism-free, line-by-line annotated, with a reproducible test suite where the rubric allows it.
Submit PostgreSQL Assignment