1 Meeting Postgres
PostgreSQL, commonly called Postgres, is introduced as a robust relational database that natively speaks SQL for OLTP workloads and has steadily evolved into a general-purpose platform. Thanks to a rich ecosystem of extensions, it capably handles full-text search, time-series, geospatial, analytics, and vector similarity use cases that power modern applications, from financial analysis to location services and AI assistants. Its popularity surge is attributed to three pillars: open-source, community-led governance; enterprise-grade reliability with conservative, incremental releases and broad commercial support; and extensibility by design, enabling innovations like JSON querying, advanced indexing, and specialized workloads without bloating the core.
The chapter clarifies the community motto “Just use Postgres!” as practical guidance rather than dogma. It encourages teams already using Postgres to evaluate whether new requirements—such as geospatial queries, time-series, or generative AI—can be met with Postgres before introducing another database, thereby reducing operational complexity and learning overhead. If Postgres fits, you gain simplicity; if it doesn’t, you choose the more suitable tool. The book’s goal is to equip readers to make these decisions confidently by understanding the breadth and depth of Postgres capabilities.
Readers then get hands-on quickly: running Postgres in a Docker container with a persistent volume, verifying the instance, and connecting via the psql command-line tool bundled in the image. After learning useful psql meta-commands for inspecting connections and schema, the chapter demonstrates generating mock data entirely in SQL by creating a trades table and populating it with 1,000 rows using generate_series, random values, arrays, rounding, and timestamps. It closes with foundational queries—counting rows efficiently with count(*), grouping to find most-traded symbols, and aggregating spend per buyer—to emphasize that SQL remains Postgres’s native interface, even when applications use ORMs, and to set the stage for deeper exploration in later chapters.
DB-Engines ranking showing Postgres trending up in popularity
How Postgres is deployed and used throughout the book
Summary
- Postgres is one of the most popular and fastest-growing databases.
- Postgres’s open source nature, enterprise readiness, and extendibility are key factors contributing to its popularity and growth.
- The phrase “Just use Postgres” implies that Postgres offers a wide range of capabilities, allowing it to handle use cases far beyond traditional transactional workloads.
- Postgres is written in C and can be installed on Windows and a wide range of Unix-based operating systems.
- The database can be started as a container in under a minute on any operating system that supports Docker.
- Postgres comes with the generate_series function, which can be used to generate mock data of varying complexity.
- Postgres “speaks” SQL natively, allowing you to solve various business tasks by crafting simple and elegant SQL queries.
FAQ
What does “Just use Postgres!” actually mean?
It’s a practical recommendation: if you already use (or plan to use) Postgres and a new use case arises (for example, geospatial, time series, or generative AI), first check whether Postgres can handle it before adding another database. It’s not a claim that Postgres is the only database you’ll ever need—use another system if it fits the use case better.Why is Postgres so popular?
Three primary reasons: (1) Open source and community-governed since the 1990s, with no single vendor control. (2) Enterprise-ready, with annual major releases and a conservative, incremental approach that keeps stability high and regressions low, plus strong vendor/cloud support. (3) Extendible by design—extensions add capabilities like JSON querying, time-series, full-text search, vector similarity, geospatial, and analytics. Popularity is reflected by DB-Engines (trending up, ranked around 4th) and Stack Overflow surveys (most popular/admired/desired in 2023–2024).What kinds of workloads can Postgres handle beyond OLTP?
Postgres has evolved into a general-purpose database. With its ecosystem and extensions, it supports full-text search, time-series, generative AI (vectors), geospatial, and analytical workloads—on top of its core OLTP strengths (low-latency, consistent, and durable transactions).How do I start Postgres quickly with Docker?
Install Docker, then create a volume and run a container. Example (Unix/macOS/Linux):docker volume create postgres-volume docker run --name postgres \ -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password \ -p 5432:5432 \ -v postgres-volume:/var/lib/postgresql/data \ -d postgres:17.2On Windows PowerShell, use backticks for line breaks. This spins up Postgres 17.2 in under a minute.
What do the key Docker options mean in this setup?
- POSTGRES_USER/PASSWORD: default credentials (postgres/password). -p 5432:5432: exposes the Postgres port to the host. -v postgres-volume:/var/lib/postgresql/data: persists data on the host via a Docker volume, so data survives container restarts/removals. -d: runs the container in the background. Image tag postgres:17.2 ensures consistent behavior with the book’s examples.How can I verify the container is running and ready?
Check status:docker container ls -f name=postgres. View logs: docker logs postgres. When you see “database system is ready to accept connections” and the port mapping (e.g., 0.0.0.0:5432->5432/tcp), the instance is ready.How do I connect with psql, and why am I not asked for a password?
Use:docker exec -it postgres psql -U postgres. Inside the container, psql connects via a local Unix socket that’s configured as trusted in the official image, so no password prompt appears. Use \q to quit.Which psql meta-commands are handy to start with?
-\?: list all meta-commands (press q to exit the pager). - \conninfo: show current connection details. - \d: list tables, views, and sequences in the current database.How can I generate mock data entirely inside Postgres?
Create a table, then usegenerate_series, random, arrays, and functions like now(). Example pattern: generate IDs with generate_series, random integers with random(min,max) (Postgres 17+) or floor(random()*(max-min+1)+min) for earlier versions, pick random symbols from an array, round prices, and insert rows with a single INSERT…SELECT. Results will differ run-to-run due to randomness and current timestamps.What basic queries should I try, and any best practices?
- Counting with a filter:SELECT count(*) FROM trades WHERE symbol = 'AAPL'; Postgres optimizes count(*) to just count rows. - Aggregations: most-traded stocks by volume via GROUP BY and ORDER BY. - Top-N: largest buyers using sum(bid_price * order_quantity) with GROUP BY and LIMIT. Best practice: avoid SELECT * in application queries—explicitly select needed columns to save resources.
Just Use Postgres! ebook for free
