Learn PostgreSQL

What Is PostgreSQL?

PostgreSQL (often called "Postgres") is a free, open-source relational database management system (RDBMS) that has been in active development for more than 30 years. It stores data in structured tables made of rows and columns, and it uses SQL (Structured Query Language) to create, read, update, and delete that data. Unlike purely relational databases, PostgreSQL is described as object-relational because it supports advanced features such as custom data types, table inheritance, and user-defined functions.

PostgreSQL runs on all major operating systems, including Linux, Windows, and macOS, and it powers applications ranging from small personal projects to massive systems used by companies like Instagram, Spotify, and Reddit.

Why Choose PostgreSQL?

PostgreSQL is popular because it combines the reliability of a mature relational database with modern features that many competitors lack. It strictly follows the SQL standard, supports full ACID (Atomicity, Consistency, Isolation, Durability) compliance, and includes advanced capabilities out of the box.

• Rich data types: arrays, JSON/JSONB, XML, UUID, geometric types, and more
• Full ACID compliance for safe, reliable transactions
• Powerful indexing including B-tree, Hash, GiST, GIN, and BRIN indexes
• Extensibility through custom functions, operators, and extensions like PostGIS
• Concurrency handled through MVCC (Multi-Version Concurrency Control)
• Strong community support and permissive open-source license

PostgreSQL vs MySQL

MySQL and PostgreSQL are both free, open-source relational databases, but they were built with different priorities. MySQL was historically optimized for speed and simplicity in read-heavy web applications, while PostgreSQL was built with strict standards compliance and advanced features as the priority. The table below highlights some key differences.

A First Look at SQL in PostgreSQL

Every interaction with PostgreSQL happens through SQL statements. Below are three simple examples that show how PostgreSQL handles table creation, data retrieval, and built-in functions.

SELECT version();

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

SELECT * FROM employees
ORDER BY name ASC;