PostgreSQL Basics

What is it? #

PostgreSQL is a program that stores your data and answers questions about it. Your application does not open files on disk itself — it asks PostgreSQL, and PostgreSQL does the reading, writing and locking safely.

The part people find confusing at the start is that "database" means several different things depending on who is speaking. A PostgreSQL server is the running program. Inside that server there can be many databases. Inside each database there are schemas, and inside each schema there are tables.

A table holds rows, and each row has columns. That is where your actual data lives — one row per user, one row per order.

Everything else in this track sits on top of those few ideas.

Think of it like this #

Think of an office building.

The building is the PostgreSQL server. Each floor is a database. On a floor there are filing rooms — those are schemas. In a filing room there are cabinets, and those are tables. Inside a cabinet, each sheet of paper is a row, and the printed fields on that sheet are the columns.

To get in, you need the building address (host), the door number (port), your name (username) and your key (password). Reception then sends you to one specific floor — the database you asked for.

Simple example #

A small shop application stores customers and their orders.

There is one database called shop. Inside it, the default schema public holds two tables: customers and orders. The customers table has one row per customer, with columns for id, name and email. The orders table has one row per order, with a column that points back at the customer who placed it.

When someone loads their order history, the application connects to the shop database and asks PostgreSQL for the rows in orders belonging to that customer.

Code #

How a request reaches your data

Application  (your code: Node, Python, Java, anything)
     │
     │  connection: host, port, user, password, database
     ▼
PostgreSQL server        (the running program, usually port 5432)
     │
     ▼
Database                 (e.g. "shop" — a server can hold many)
     │
     ▼
Schema                   (e.g. "public" — a named group of tables)
     │
     ▼
Table                    (e.g. "customers")
     │
     ▼
Rows and columns         (the actual data)

One table, drawn out

customers
+----+-------------+---------------------+
| id | name        | email               |   <- columns
+----+-------------+---------------------+
|  1 | Asha Verma  | [email protected]    |   <- a row
|  2 | Ravi Nair   | [email protected]    |   <- another row
+----+-------------+---------------------+
  ▲
  └── primary key: the column that uniquely identifies a row

Two tables, linked

customers                    orders
+----+------------+          +----+-------------+--------+
| id | name       |          | id | customer_id | total  |
+----+------------+          +----+-------------+--------+
|  1 | Asha Verma |◀────────┐|  1 |           1 | 450.00 |
|  2 | Ravi Nair  |         ││  2 |           1 | 120.00 |
+----+------------+         └┤  3 |           2 | 990.00 |
                             +----+-------------+--------+
                                         ▲
     orders.customer_id is a FOREIGN KEY ┘
     it must match some customers.id

How it works #

A connection is the conversation between your application and the server. To start one you need five things: the host (which machine), the port (which program on that machine, 5432 by default), a username, a password, and which database you want to talk to.

The server is a running process. Stopping the server stops every database inside it at once — that is why "restart the database" on a production machine is a bigger action than it sounds.

A database is an isolated container. A query cannot join a table in one database to a table in another, the way it can join two tables in the same database. That isolation is deliberate.

A schema is a named group of tables inside a database. Every PostgreSQL database starts with one called public. Small projects often never create another. Larger ones use schemas to keep areas apart — billing.invoices and reporting.invoices can both exist without clashing.

A primary key is the column that uniquely identifies a row. No two rows may share it, and it is never empty. It is how you say "this exact customer" rather than "some customer named Asha".

A foreign key is a column that points at another table's primary key. orders.customer_id holding the value 1 means "this order belongs to customer 1". PostgreSQL enforces it: you cannot insert an order for customer 99 if no customer 99 exists, and by default you cannot delete a customer who still has orders. That enforcement is what keeps the data honest over years of use.

Client and server are different programs. psql is a client. Your application is a client. They connect to the server; they are not the server. You can have the client installed on your laptop and the server running on a machine in another country.

Real-world use #

In production, the database server usually runs on its own machine, or on a managed service, and only the application servers are allowed to connect to it. The database port is not open to the internet.

The choice between PostgreSQL and MySQL comes up constantly. Both are mature, free and widely used. PostgreSQL has historically been stricter about correctness, has richer data types (proper JSON, arrays, ranges), stronger support for complex queries, and a more capable extension system. MySQL has historically been simpler to operate and very common in shared hosting. For a new project where you care about data integrity and expect queries to get complicated, PostgreSQL is a safe default. Neither choice is wrong, and both will comfortably outlive most applications built on them.

A single PostgreSQL server holding several databases is common on small deployments — one database per application. On larger systems, each important application tends to get its own server so that one noisy application cannot slow down another.

The one habit worth forming now: know which database you are connected to before you run anything. Most serious accidents in this track's later lessons start with someone running a correct command against the wrong database.

Common mistakes #

• Mixing up the server and a database — restarting the "database" actually stops every database on that server.
• Thinking a schema is a separate database. Schemas live inside one database and can be queried together.
• Creating tables with no primary key, which makes it impossible to reliably point at one exact row.
• Skipping foreign keys "for speed", then discovering orders pointing at customers that no longer exist.
• Exposing port 5432 to the internet instead of only allowing application servers to connect.

Practice #

Draw your own version of the diagram above for an application you know — a blog, a to-do app, anything. Write down: the database name, the schema, two or three table names, the columns of one table, which column is its primary key, and which column would be a foreign key pointing at another table. You do not need PostgreSQL installed yet; this is on paper.