PostgreSQL is the most feature-rich open-source SQL database. Advanced features rival commercial databases.
Window functions perform calculations across related rows: RANK(), ROW_NUMBER(), LAG(), LEAD(). They compute running totals, moving averages, and percentiles without subqueries.
CTEs (Common Table Expressions) with WITH clause create readable complex queries. Recursive CTEs traverse hierarchical data like organizational charts.
JSON support: -> operators extract fields, jsonb stores binary JSON with indexing, jsonb_array_elements expands arrays. PostgreSQL handles JSON as well as dedicated document databases.
Full-text search with tsvector and tsquery. GIN indexes speed up text search. ts_rank ranks results by relevance.
Other features: arrays, hstore for key-value, range types, lateral joins, and materialized views for cached query results.