What is PostgreSQL?

What PostgreSQL is

PostgreSQL is an open source relational database known for reliability and standards support. It handles structured business data safely, supports complex queries, and scales from small apps to large systems.

PostgreSQL grew out of the POSTGRES research project at the University of California, Berkeley, which started in 1986 under Professor Michael Stonebraker. It was a follow-up to an earlier database called Ingres, which is where the name comes from. In the mid 1990s two students added support for the SQL language and released it as Postgres95.

The project was renamed PostgreSQL in 1996 and has been developed by a worldwide volunteer community ever since. No single company owns it. Over the years it gained features such as replication, full text search and, in 2014, the JSONB data type for storing JSON documents efficiently.

How PostgreSQL works

A relational database stores data in tables. Each table has columns with a fixed type, such as text, number or date, and each row is one record. Tables link to each other through keys: a primary key identifies a row, and a foreign key points to a row in another table. You read and change data using SQL, with statements such as SELECT, INSERT, UPDATE and DELETE.

PostgreSQL runs as a server program. Your app connects to it, sends SQL, and gets results back. Changes can be grouped in a transaction, which either fully succeeds or fully fails. This follows the ACID rules, which protect data when something goes wrong halfway through, for example during a payment.

To let many users work at once, PostgreSQL uses a method called MVCC, which means readers do not block writers. An index makes lookups fast, in the same way an index in a book helps you find a page. Extensions add extra abilities, such as PostGIS for maps and location data or pgvector for AI search.

Key features of PostgreSQL

• ACID transactions

  Groups of changes are saved together or not at all. This keeps money, orders and bookings consistent even if the server crashes.

• Rich data types

  Beyond text and numbers it supports JSON and JSONB, arrays, UUIDs, date ranges and more. You can also define your own types.

• Advanced SQL

  It supports joins, views, window functions, common table expressions and full text search. Complex reports can often be done in one query.

• Extensions

  Add-ons extend the database without changing its core. Well known examples are PostGIS for geographic data and pgvector for vector search.

• Row Level Security

  Rules inside the database can decide which rows each user is allowed to see or change. This adds a layer of protection below the app code.

• Replication and backups

  Built-in replication copies data to standby servers, and point in time recovery can restore the database to an earlier moment.

When to use PostgreSQL

PostgreSQL compared with alternatives

How to start learning PostgreSQL

1. 01Download the installer for your system from postgresql.org, or start a database with the official postgres image in Docker.
2. 02Open the psql command line tool or the pgAdmin app and connect to your local server.
3. 03Work through the tutorial at the start of the official PostgreSQL documentation to learn CREATE TABLE, INSERT and SELECT.
4. 04Practice joins, primary keys, foreign keys and indexes with a small sample schema.
5. 05Design a database for a simple project, such as a booking system with customers, services and appointments.

What PostgreSQL costs

PostgreSQL is free and open source, with a permissive license that allows commercial use without fees. Costs come from the server it runs on or from a managed database provider. For hosted services, check the provider's official pricing page.

How DATAL Projects uses PostgreSQL

PostgreSQL is part of our backend development work in the Philippines. Common uses:

• Customer, order, and booking records
• Reporting and analytics
• Backends for web and mobile apps

Common questions about PostgreSQL