PostgreSQL Introduction

What PostgreSQL is#

PostgreSQL ("Postgres") is an open-source, object-relational database with a reputation for strict standards compliance, extensibility, and advanced data types. Where MySQL prioritized ease of use and raw speed for common web workloads historically, PostgreSQL has generally prioritized correctness and features — a distinction that's narrowed over the years as both databases matured, but still shapes when each is chosen.

Why teams often reach for PostgreSQL over MySQL#

• Standards compliance — PostgreSQL's SQL implementation tracks the SQL standard more closely, including full support for window functions, recursive CTEs, and complex constraint types, some of which historically arrived in MySQL later or with more limitations.
• Advanced data types — native JSONB (indexed, binary JSON), array columns, range types, geometric types, and UUID, covered in depth on the next page. MySQL supports some overlapping features (a JSON type) but with different indexing characteristics.
• Extensibility — PostgreSQL supports custom types, operators, and extensions loaded directly into the database. PostGIS (geospatial queries) and pg_trgm (fuzzy text search) are widely used real-world examples that turn Postgres into a specialized engine without leaving SQL.
• Concurrency model — PostgreSQL's MVCC implementation (detailed on the transactions page in this section) is often cited as more consistent under mixed read/write workloads, though modern InnoDB has closed much of this gap.

None of this makes PostgreSQL strictly "better" — MySQL remains extremely well-suited to simpler, high-throughput read-heavy web workloads and has an enormous ecosystem of tooling and hosting options. The interview-questions page in this section covers the trade-off in more depth. In practice, teams building applications with complex querying needs, heavy use of JSON-shaped data, or geospatial requirements often lean toward Postgres; teams wanting the simplest possible setup for a standard CRUD web app often reach for MySQL. Both are excellent, production-proven choices.

Installing and connecting#

On Ubuntu/Debian:

sudo apt install postgresql
sudo systemctl start postgresql

Connect with psql, PostgreSQL's command-line client:

psql -U postgres -d postgres

Once connected, some psql-specific navigation commands (note the backslash prefix — these are client commands, not SQL):

\l              -- list databases
\c shop         -- connect to the "shop" database
\dt             -- list tables in the current database
\d users        -- describe the "users" table's columns and indexes
\q              -- quit

Creating a database and a simple table:

CREATE DATABASE shop;

\c shop

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now()
);

Two PostgreSQL-specific things already visible here: SERIAL is Postgres's auto-incrementing integer type (roughly equivalent to MySQL's AUTO_INCREMENT, implemented under the hood as an integer column backed by a sequence), and TIMESTAMPTZ (timestamp with time zone) stores an instant in time normalized to UTC and is almost always the right choice over plain TIMESTAMP for anything user-facing across time zones.

Common mistakes#

• Using plain TIMESTAMP (without time zone) for created_at/updated_at columns — it stores the literal value given with no timezone context, which becomes ambiguous the moment your application or its users span more than one timezone. TIMESTAMPTZ is the safer default.
• Expecting psql's backslash commands (\dt, \d) to work inside application code or a plain SQL script — they're client-side conveniences specific to the psql tool, not SQL syntax.
• Assuming PostgreSQL and MySQL are interchangeable at the syntax level — plenty of everyday SQL is identical, but auto-increment syntax, string concatenation, LIMIT/pagination edge cases, and quoting rules for identifiers all differ in ways that bite during a migration.