1 Meeting Postgres
PostgreSQL is introduced as a reliable, community-governed relational database that has evolved into a versatile general-purpose platform. Beyond powering OLTP workloads with strong consistency and integrity, it now supports full-text search, time-series, geospatial, analytics, and generative AI through a thriving ecosystem of extensions. Its rising popularity stems from open-source stewardship, enterprise-grade stability with incremental releases, and extensibility by design. The chapter frames the community mantra “Just use Postgres!” as pragmatic guidance: consider Postgres first for new use cases to reduce operational complexity—while acknowledging it isn’t a universal solution for every scenario.
The chapter then gets hands-on by showing how to run Postgres in a Docker container for a quick, cross-platform development setup. You interact with the database via psql inside the container, avoiding extra host installations, and connect through a trusted local socket. Basic psql meta-commands help you inspect connections and objects, providing a lightweight workflow for experimenting with SQL from the terminal.
To make experimentation concrete, the chapter demonstrates generating mock data entirely in SQL: creating a trades table, using generate_series to produce IDs, random to synthesize integers and prices, and arrays to pick stock symbols, then inserting 1,000 rows. With that dataset, you run foundational queries—counts with filters, aggregations with GROUP BY, ordering, limits, and simple calculations—to find trade volumes by symbol and identify top buyers by total spend. The takeaway is that SQL is the native interface to Postgres (even when using ORMs), and with a minimal setup you can quickly explore core capabilities and build intuition for effective querying.
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 guideline: if you already use (or plan to use) Postgres and a new need arises (geospatial, time series, generative AI, analytics, etc.), check whether Postgres can handle it before adding another database. It’s not a claim that Postgres is the only tool you’ll ever need—use another database if it serves the use case better.Why is Postgres so popular among developers?
Three main reasons: it’s open source and community-governed (no single vendor lock-in), it’s enterprise-ready with stable, incremental annual releases, and it’s highly extensible (rich ecosystem of extensions). Popularity is reflected in DB-Engines rankings and Stack Overflow Developer Surveys.Can Postgres handle workloads beyond traditional OLTP?
Yes. Postgres has evolved into a general-purpose database. With extensions and related projects, it supports full-text search, time-series, geospatial, analytics, and vector similarity for generative AI use cases, among others.How do I start Postgres quickly using Docker?
1) Install Docker and verify withdocker version.2) Create a volume:
docker volume create postgres-volume.3) Run the container:
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.2.This maps port 5432, sets credentials, and persists data on a Docker-managed volume.
How can I verify the container is running and inspect logs?
Usedocker container ls -f name=postgres to see status and ports. View logs with docker logs postgres. You’re ready when you see “database system is ready to accept connections.”How do I connect to Postgres in the container with psql?
Rundocker exec -it postgres psql -U postgres. Inside the container, psql uses a trusted local Unix socket, so no password prompt. Exit psql with \q.Which psql meta-commands are most useful to start with?
-\? lists all meta-commands (press q to close).-
\conninfo shows current connection details.-
\d lists tables, views, and sequences in the current database.How do I generate mock data entirely inside Postgres?
Create a table, then combinegenerate_series, arrays, and random values. Example pattern: use generate_series(1,1000) for IDs, array indexing for symbols (e.g., (array['AAPL','F','DASH'])[random(1,3)]), and random(min,max) (Postgres 17+) or floor(random()*(max-min+1)+min) on older versions. Insert with a single INSERT ... SELECT.Is using COUNT(*) considered bad practice in Postgres?
Not for counting. In Postgres,COUNT(*) is optimized to count rows without fetching column data. As a general rule, avoid * in regular SELECT lists; prefer explicit columns to reduce I/O and bandwidth.What basic queries can I run to explore the sample trades?
- Count trades for a symbol:SELECT count(*) FROM trades WHERE symbol = 'AAPL';- Most-traded stocks by volume: group and order by count.
- Top buyers by spend:
GROUP BY buyer_id, sum bid_price * order_quantity, order desc, and LIMIT for the top-N.
Just Use Postgres! ebook for free
