PostgreSQL is not a fixed product but an extensible platform. Its extension mechanism lets third parties add new data types, operators, index methods, and functions that integrate as deeply as built-in features — sharing the same transactions, indexing, and SQL. This is why PostgreSQL can serve as a spatial database, a time-series database, and a vector database, roles that would otherwise require separate specialised systems.
Three extensions exemplify this reach. PostGIS turns PostgreSQL into a leading geospatial database with geometry types and spatial indexing. TimescaleDB optimises it for time-series data with automatic partitioning and compression. pgvector adds vector similarity search, making PostgreSQL a capable backend for AI embeddings and retrieval-augmented generation. Each extends the database into a domain with its own dedicated competitors.
Installing an extension is a single CREATE EXTENSION command (where the extension is available), after which its types and functions are first-class SQL. The strategic value is consolidation: keeping spatial, time-series, or vector data alongside your relational data means one system to operate, one set of backups and transactions, and the ability to join across domains — often avoiding a second database entirely.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.