PostgreSQL is the most advanced open-source database. JSONB, window functions, partitioning — features you won’t find in MySQL.
JSONB¶
– Storage and querying CREATE TABLE events (id SERIAL, data JSONB); INSERT INTO events (data) VALUES (‘{“type”: “click”, “page”: “/home”}’); SELECT data->>’type’ as event_type FROM events; SELECT * FROM events WHERE data @> ‘{“type”: “click”}’; CREATE INDEX idx_events_data ON events USING GIN(data);
Window Functions¶
– Ranking SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank, AVG(salary) OVER (PARTITION BY department) as dept_avg FROM employees; – Running total SELECT date, amount, SUM(amount) OVER (ORDER BY date) as running_total FROM transactions;
CTE (Common Table Expression)¶
WITH monthly_stats AS ( SELECT DATE_TRUNC(‘month’, created_at) as month, COUNT(*) as total, SUM(amount) as revenue FROM orders GROUP BY 1 ) SELECT month, total, revenue, LAG(revenue) OVER (ORDER BY month) as prev_revenue FROM monthly_stats;
Key Takeaway¶
PostgreSQL JSONB replaces MongoDB for most use cases. Window functions for analytics. CTEs for readability.