openskills.info
Course catalog
PostgreSQL Fundamentals logoCourse Preview

PostgreSQL Fundamentals

PostgreSQL is an open source object-relational database server. Applications use SQL to store related data in tables, enforce rules around that data, and retrieve or change it safely.

itDatabases and data storage

Don't Panic — PostgreSQL Fundamentals

PostgreSQL is the bit of your system that remembers things with enough ceremony to prevent tomorrow's application from quietly disagreeing with today's. It is a running database server, not a convenient file hiding inside an application. Clients connect to it, speak SQL, and ask it to keep related data in a form that remains usable after several people and several programs have had ideas at once.

The first useful map is a nesting doll with paperwork. A server instance manages a cluster. The cluster contains databases. A database contains schemas. Schemas contain tables and other objects. A connection enters one database, which is why a table can be present, perfectly healthy, and still nowhere near the session asking for it. Computers enjoy boundaries. PostgreSQL has made several of them official.

A table is more than a spreadsheet that learned to live in a server room. Its columns have types, and its constraints decide which values and relationships count. Constraints are the rules that stay awake even when a second client, a bulk import, or an overconfident script turns up. A primary key identifies a row. A foreign key refuses a relationship to a row that does not exist. This is considerably less glamorous than fixing bad data afterward, which is why it works.

The next anchor is the transaction, a group of statements that commits together or rolls back together. That matters when one change without another would be nonsense, such as moving money between accounts. PostgreSQL also uses MVCC, its multiple-version concurrency model, so readers and writers can often work at the same time. The word “often” has been assigned a great deal of labor here: conflicting work can still wait, deadlock, or need a retry.

Queries have their own small parliament. FROM and JOIN create source rows. WHERE filters them. Grouping and aggregates calculate. ORDER BY requests a result order. Without that last request, an observed order is a coincidence wearing a tie. PostgreSQL’s planner then chooses an execution plan. An index can help it find rows, but it also costs storage and write work, and it does not issue orders to the planner. EXPLAIN is how you ask what was chosen.

Start with the Intro for the full client-to-server map. The Slides compress the relationships. The Cheatsheet keeps the commands and clause order close at hand. Then use the Practice Reference and exercise on a disposable database. After the model feels familiar, the Reference tab leads into transactions, concurrency, indexes, and the operational work that turns a database server into a dependable service.

Where this skill leads

Relevant careers

See how this topic contributes to broader role-level skill maps.

Sources