MODULE 05 · DATABASE & SQL

PostgreSQL

PostgreSQL is an open-source relational database management system used to store, query, and manage application data.

01 · What is it?

PostgreSQL is an open-source relational database management system used to store, query, and manage application data.

It organizes data into related tables and provides SQL plus other features for reliable application workloads.

02 · Think of it like…

Think of PostgreSQL as a carefully managed warehouse with labeled shelves, rules, and a fast lookup desk.

Tables are shelves, rows are stored records, and SQL is the language used to find or change what is inside.

Application Request

SQL Query

PostgreSQL

Stored Records

03 · What does it do?

PostgreSQL gives an application a structured place to work with durable, related data.

Store Data

Save records that remain available after a request ends.

Query Data

Find, filter, join, sort, and summarize records with SQL.

Enforce Rules

Use constraints to keep invalid data out.

Manage Relationships

Connect records across tables with keys.

Handle Transactions

Group changes so related operations succeed together.

Support Extensions

Add specialized capabilities when needed.

04 · Where is it used?

PostgreSQL is common wherever an application needs reliable relational data and expressive queries.

Web Applications

Users, orders, content, settings, and product data.

SaaS Products

Tenant records, billing data, and activity history.

Internal Tools

Workflows, approvals, reporting, and operational records.

Analytics

Joins and summaries for dashboards and business questions.

Geospatial Systems

Location-aware applications with extensions.

Data Platforms

A dependable system of record behind APIs.

05 · Example

Save and read an order

CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL,
  total numeric(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, total)
VALUES (42, 7, 89.50);

SELECT id, total
FROM orders
WHERE customer_id = 7;

What happens

The application sends SQL to PostgreSQL. PostgreSQL validates the table rules, stores the new row, and returns matching data later.

PostgreSQL keeps the order durable and queryable after the original request finishes.

06 · How it connects

Frontend

API

Backend

SQL

PostgreSQL

Rows

Response

The backend usually owns the database connection and turns application actions into safe, validated queries.

07 · Do Not Confuse

PostgreSQL

A database management system that stores and works with application data.

SQL

A language used to define, query, and change relational data.

PostgreSQL vs a table

PostgreSQL is the system; a table is one structure inside it.

PostgreSQL vs an API

PostgreSQL stores data; an API exposes application operations.

08 · Topic Glossary

ENGLISH TERM	中文	SIMPLE ENGLISH	中文小白理解
PostgreSQL	PostgreSQL	An open-source relational database management system.	一个开源的关系型数据库管理系统。
Relational Database	关系型数据库	A database that organizes related data in tables.	把有关联的数据组织在表中的数据库。
DBMS	数据库管理系统	Software that stores, queries, and manages database data.	用来保存、查询和管理数据库数据的软件。
Table	表	A structure that stores records in rows and columns.	用行和列保存记录的数据结构。
Primary Key	主键	A value that uniquely identifies a row.	能够唯一标识一行数据的值。
Foreign Key	外键	A column that links one table to another.	把一张表连接到另一张表的列。
Schema	模式	A named organization for database objects.	给数据库对象分类组织的命名空间。
Index	索引	A data structure that can speed up selected lookups.	可以加快某些查询的数据结构。
Transaction	事务	A group of changes treated as one unit.	被当作一个整体处理的一组数据变更。
SQL	结构化查询语言	A language used to work with relational database data.	用来操作关系型数据库数据的语言。

09 · What to Remember

PostgreSQL is a database system, not just a file or a table.

It stores related application data and lets software query and change that data with SQL.

Constraints, relationships, and transactions help applications keep important data reliable.