Skip to content
Tutorials Center
My learning

Topics

Lesson 1 of 90 ยท Getting Started with PostgreSQL

What Is PostgreSQL?

Meet PostgreSQL: where it came from, how MVCC and the SQL standard shape it, how it differs from MySQL and SQLite, and how this track runs its examples.

PostgreSQL (say "post-gres-Q-L", or just "Postgres") is a free, open-source relational database server. You run it as a background service, applications connect to it over the network, and it stores their data in tables that you query with SQL. It has a reputation for correctness, for a very rich set of data types, and for being easy to extend, and it is behind everything from weekend projects to some of the busiest sites on the web.

This track teaches PostgreSQL as a database in its own right: how it stores data, which types and features it gives you, how to keep it fast and safe, and how to run it in production. You do not need to know PostgreSQL already, but you should be comfortable with basic SQL. If SELECT, JOIN and GROUP BY are new to you, start with the SQL tutorial first; everything in this track builds on it.

In this first lesson you will learn where PostgreSQL came from, what makes its design different, when to pick it over other databases, and how the examples in this track work.

A short history

PostgreSQL started as the POSTGRES research project at the University of California, Berkeley, led by Michael Stonebraker in the mid-1980s. It was a successor to an earlier system called Ingres (hence "post-Ingres"). The research ideas were ambitious: user-defined types, rules, and an extensible design where the database could learn new tricks without being rewritten.

In 1995 two Berkeley students replaced the original query language with SQL and released the result as Postgres95. A volunteer team took it over in 1996 and renamed it PostgreSQL to show that it speaks SQL. Since then it has been developed in the open by a community and many companies, with no single owner. It is released under the PostgreSQL License, a permissive license similar to MIT and BSD: you can use it for anything, including commercial products, at no cost.

The result is a project that ships a new major version every year (in autumn), supports each major version for five years, and puts stability before fashion. A few recent highlights:

  • PostgreSQL 17 (2024) added incremental backups, JSON_TABLE, a RETURNING clause for MERGE and a much more memory-efficient VACUUM.
  • PostgreSQL 18 (2025) added asynchronous I/O for reads, virtual generated columns, built-in UUID version 7 generation, skip scans for B-tree indexes, temporal constraints and OLD/NEW values in RETURNING.

Since version 10, a release number has two parts: 18.2 is major version 18, second bug-fix ("minor") release. Minor releases never change the on-disk format, so upgrading within a major version is just a restart. Moving to a new major version takes more planning, which the lesson on high availability and upgrades covers.

Why people pick PostgreSQL

Correctness Strict types, real constraints, transactional DDL.
Rich features JSONB, arrays, ranges, full-text search, window functions.
Extensible Add types, indexes and languages with extensions.
Free forever Permissive license and a community, not a vendor.

You get a lot of database without paying for it or being locked in.

1 / 2

What makes it different: MVCC

The idea that shapes most of PostgreSQL's behavior is MVCC, multi-version concurrency control. When you update a row, PostgreSQL does not overwrite it in place. It writes a new version of the row and leaves the old version where it is, marked as replaced. Every transaction sees a consistent snapshot of the data, and each snapshot decides which row versions are visible to it.

The practical result is the famous rule of thumb: readers do not block writers, and writers do not block readers. A long report can scan a table while other sessions keep inserting and updating rows, and neither side waits for the other. The price is that old row versions pile up and must be cleaned out later by a background process called vacuum. You will meet both ideas in detail in MVCC: How PostgreSQL Keeps Versions and VACUUM and Autovacuum, and they explain a surprising number of everyday PostgreSQL facts, from why UPDATE is not free to why COUNT(*) on a big table is slow.

PostgreSQL is also fully ACID: transactions are atomic (all or nothing), consistent (constraints always hold), isolated (concurrent work does not interfere in the ways you forbid) and durable (committed data survives a crash). Even CREATE TABLE and ALTER TABLE are transactional, so a failed migration can roll back cleanly, which many other databases cannot do.

Standards and strictness

PostgreSQL implements a large part of the SQL standard, including features that are rare elsewhere: window functions, recursive CTEs, FILTER on aggregates, LATERAL joins, identity columns, MERGE and parts of SQL/JSON. Where it extends the standard (and it does so often), it tends to do it in a principled way.

It is also strict. If you store the text 'abc' in an integer column, you get an error, not a silently stored string. If you divide by zero, you get an error, not NULL. Dates must be valid dates. Constraints are checked every time. This strictness feels fussy for a day and then feels like a safety net for years: bugs surface where they happen instead of corrupting data quietly.

PostgreSQL, MySQL and SQLite

All three are popular, and each fits a different job.

PostgreSQL MySQL / MariaDB SQLite
Architecture Server process per connection Server, thread per connection A library inside your app, one file
Typing Strict Moderately strict Flexible ("type affinity")
Concurrency MVCC, many writers MVCC (InnoDB), many writers One writer at a time
Standout features Types, extensions, JSONB, rich SQL Simplicity, huge hosting support Zero setup, embedded
Typical use Complex apps, analytics, geo, anything serious Web apps, CMSs Mobile, desktop, tests, small sites

Choose SQLite when the database lives with one application and you want zero administration. Choose MySQL or MariaDB when your hosting or framework is built around them. Choose PostgreSQL when you want the most capable general-purpose open-source server: complicated queries, strong integrity rules, JSON next to relational data, geospatial or vector search, or room to grow. The SQL course has a PostgreSQL essentials page and a MySQL page that compare the dialects side by side.

How this track runs its examples

Your browser can run SQLite (through the sql.js library) but it cannot run a PostgreSQL server. So this track is honest about what is live and what is only shown:

  • Live examples run in your browser against SQLite. They use standard SQL that works the same in PostgreSQL, or they show the SQLite counterpart of a PostgreSQL feature so you can experiment with the idea.
  • Read-only blocks, labeled -- PostgreSQL, show features that only a real server has: its own commands, types, extensions, replication and so on. Every lesson says which parts are which. Compare what you see on your own server with the outputs shown.

The best way to learn is to do both: run the live examples here, and install a real server (the next lesson shows how) so you can paste the read-only blocks into psql.

Here is a standard query that behaves identically on both systems, run against the bookstore sample data:

SQL The same SQL everywhere ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Every character of that query is valid PostgreSQL. The only difference is that PostgreSQL would need the bookstore tables created first.

Now a place where the two engines differ sharply. SQLite is happy to store any value in any column, so this table accepts both integers and words in an INTEGER column:

SQL SQLite is flexible, PostgreSQL is strict ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The text '42' is converted to the integer 42, but 'forty-two' stays text and 4.5 stays a real number, with no error. In PostgreSQL the word insert fails and the fraction is rounded to fit:

SQL
-- PostgreSQL
INSERT INTO readings VALUES ('forty-two', 'words');
-- ERROR:  invalid input syntax for type integer: "forty-two"
INSERT INTO readings VALUES (4.5, 'a fraction');
-- succeeds, but stores 5: the value is rounded to fit an integer column

So PostgreSQL rejects what cannot be an integer, and converts what can, following fixed rules. That predictability is a big part of why teams trust it.

What you will learn in this track

The 81 lessons are grouped into fourteen chapters. First you install a server, learn psql and tour the SQL. Then come data types (numbers, text, dates, JSON, arrays, ranges), PostgreSQL-specific query features, schema design and constraints, indexes, and the transaction and concurrency model. After that: functions, triggers and extensions, performance tuning, partitioning and replication, security and administration, and using PostgreSQL from application code. Five projects and a practice chapter finish the track.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does MVCC let PostgreSQL do?

    MVCC keeps several versions of a row, so each transaction reads a consistent snapshot while others keep writing. Old versions are cleaned up later by vacuum.
  2. 2. What happens in PostgreSQL if you insert the text 'forty-two' into an integer column?

    PostgreSQL is strictly typed. A value that cannot be converted to the column type is rejected instead of being stored silently.
  3. 3. Which license is PostgreSQL released under?

    The PostgreSQL License is permissive, so you can use it freely, including in commercial products, and no company owns the project.
  4. 4. Why do the live examples in this track run on SQLite instead of PostgreSQL?

    The in-browser engine is SQLite compiled to WebAssembly. Features that need a real PostgreSQL server are shown as read-only blocks and clearly labeled.

Summary

  • PostgreSQL is a free, open-source relational database server that grew out of the POSTGRES research project at Berkeley and is community-owned under a permissive license.
  • A new major version appears every year and each one is supported for five years; minor releases are safe bug-fix updates.
  • MVCC keeps multiple row versions so readers and writers do not block each other; vacuum cleans up old versions.
  • PostgreSQL is strict about types and constraints, fully ACID, and even supports transactional DDL.
  • Pick SQLite for embedded use, MySQL when your platform is built around it, and PostgreSQL for the most capable general-purpose open-source server.
  • In this track, live examples run on SQLite and PostgreSQL-only features appear as read-only -- PostgreSQL blocks.

Next: Installing PostgreSQL and Connecting, where you set up a real server of your own.