What is PostgreSQL?

An open-source object-relational database known for standards compliance, extensibility, and advanced features.

Overview

PostgreSQL (often called "Postgres") is a powerful, open-source object-relational database system with over 35 years of active development. It has earned a strong reputation for reliability, feature robustness, and performance. Unlike simpler databases, PostgreSQL fully supports ACID (Atomicity, Consistency, Isolation, Durability) transactions, ensuring your data remains consistent even in the event of failures. PostgreSQL supports both SQL (relational) and JSON (non-relational) querying, making it one of the most flexible databases available. It runs on all major operating systems and is the default database for many cloud platforms including AWS RDS, Google Cloud SQL, Azure Database, and Supabase. Many major companies Apple, Instagram, Reddit, and Spotify rely on PostgreSQL in production. Being open-source means PostgreSQL is completely free to use, even for commercial applications. Its active community continuously adds new features, and the extension ecosystem (PostGIS for geography, TimescaleDB for time series, pgvector for AI embeddings) makes it adaptable to almost any use case.

Key Points

•Open-source and free under the PostgreSQL License use it commercially with no restrictions
•ACID-compliant: every transaction either fully commits or fully rolls back
•Supports advanced SQL features: window functions, CTEs, full-text search, JSONB
•Extensible: add custom data types, functions, operators, and index types
•Cross-platform: runs on Linux, macOS, Windows, and major cloud providers
•Follows the SQL standard more closely than MySQL or SQLite

Examples

Check your PostgreSQL server version

SELECT version();

Run this after connecting with psql to confirm PostgreSQL is working.

List all databases on the server

SELECT datname FROM pg_database;

Show server uptime and connections

SELECT now() - pg_postmaster_start_time() AS uptime,
       count(*) AS active_connections
FROM pg_stat_activity;

Common Mistakes

Confusing PostgreSQL with MySQL they have different syntax for things like AUTO_INCREMENT (use SERIAL or IDENTITY in Postgres)
Forgetting that PostgreSQL is case-insensitive for identifiers by default "UserName" and "username" refer to the same column unless you use double quotes

PostgreSQL Tip

PostgreSQL uses the term "database cluster" to mean a collection of databases managed by a single server instance not a group of servers as in other systems.

What is PostgreSQL?

Overview

Key Points

Examples

Common Mistakes

PostgreSQL Tip

Exercises

Knowledge Check

What does ACID stand for in database terminology?