PostgreSQL, commonly called Postgres, is an open-source relational and general-purpose database that speaks SQL natively. It supports low-latency transactional workloads with consistency and integrity guarantees while extending into JSON, full-text search, geospatial, analytical, time-series, and generative AI applications. section 1.0 section 5.0
Use PostgreSQL across application and data-processing scenarios:
- Model applications with databases, schemas, tables, joins, transactions, constraints, functions, triggers, and granular roles. section 2.0 section 2.9
- Store and query semi-structured data with JSON and optimize access using GIN and B-tree indexes. section 5.0
- Adapt the database through extensions for specialized workloads such as vector search, time-series processing, and messaging. section 7.2 section 8.7
- Use PostgreSQL as a message queue when durable, SQL-backed asynchronous communication is sufficient, while recognizing that it is not purpose-built messaging infrastructure. section 11.1 section 11.8
Start a local PostgreSQL instance
Create a Docker volume, publish port 5432, and launch the PostgreSQL 17.2 image with persistent storage. Check the container status or logs to verify that the server is ready to accept connections. This provides a disposable, cross-platform development environment. section 1.3
Aggregate application data with SQL
SELECT symbol, count(*) AS total_volume FROM trades GROUP BY symbol ORDER BY total_volume DESC; returns grouped trade counts such as DASH 351, F 344, and AAPL 305. A filtered count(*) can count AAPL trades without retrieving column values. section 1.6
Listing 1.1 Starting a Postgres container on Unix
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.2
Inspect the complete Unix Docker command for launching PostgreSQL with credentials, port mapping, a named volume, and the 17.2 image. section 1.3
