Getting Started

The database was originally called Ingres, name Postgres is coming from "Post-Ingres". Read more: A Brief History of PostgreSQL.

As of 2020 Dec 25th: PostgreSQL 13.1, 12.5, 11.10, 10.15, 9.6.20, and 9.5.24 released. Read more: PostgreSQL-releases

The Can I use X for PostgreSQL: Feature Matrix.

List of PostgreSQL glossary

Features

Extensions

Terms

They roughly mean the same thing:

Client programs come with Postgres

Their names are generic but actually from Postgres. Postgres called them "app", Postgres Client Applications.

  • createdb
  • dropdb
  • createuser
  • dropuser
  • clusterdb
  • reindexdb
  • vacuumdb
  • vacuumlo
  • pg_dump
  • pg_dumpall
  • pg_basebackup
  • pgbench
  • pg_config

Basics of PSQL

The interface to use SQL to interact with Postgres database.

psql [DBNAME [USERNAME]]
psql -h -p  connect to specific host and port
psql -c "SELECT 1 FROM users;"
\q to quit
\l
\db
\d table
\dt+ table
\sf func  Show function definition
\x, \x auto
\?
\h CREATE TABLE
\timing
\watch

CREATE ROLE
CREATE TABLESPACE
CREATE DATABASE

Boolean

True is one of TRUE, t, true, y, yes, on, 1.
False is one of FALSE, f, false, n, no, off, 0.

Topics to Learn

Worker

Parallel Query

Added since PostgreSQL 9.6+, thriving since 10+. This is not silver bullet.
Parallel Query does use more CPU, IO, Memory. If you have spare CPU, IO, Memory to splurge, go go go!

Configs we can change for Parallel Query.

max_parallel_workers_per_gather

0 means disabled Parallel Query; defaults to 2 (enabled).

max_worker_processes

defaults to 8. Max number of workers for System.

max_parallel_workers

defaults to 8. Max number of workers to use for Parallel Query.

If max_parallel_workers set to 2, but you have max_parallel_workers_per_gather set to 6, then you still only get 2 workers.

parallel_setup_cost

defaults to 1000. Cost to start parallel process.

parallel_tuple_cost

defaults to 0.1. Cost to process a row.

min_parallel_table_scan_size

defaults to 8MB. How big a table is to start using Parallel Query (bigger than 8MB).

min_parallel_index_scan_size

defaults to 512kb.

force_parallel_mode

Don’t enable this. This hurts OLTP.

Configs guideline

max_worker_processes > max_parallel_workers > max_parallel_workers_per_gather

PostgreSQL query table in these ways and its parallel equivalent you can benefit when you enable Parallel Query:

Aggregate functions can also run in parallel (COUNT, SUM, MIN, MAX).

Extensions

Tips

Further

PostgreSQL 12

People

PostgreSQL Person of the Week

Consultancy / Commercial Companies

Commercial Postgres solutions

Please see PostgreSQL derived databases for more.