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 | OpenSkills.info
Intro
PostgreSQL Fundamentals
PostgreSQL is an open source object-relational database system. It runs as a server, stores related data in tables, and accepts SQL from client applications. Its job is larger than saving rows. It also enforces data rules, coordinates concurrent work, plans queries, and recovers committed changes after a failure.
This course gives you a working map of PostgreSQL. It does not try to teach every SQL expression or administration procedure. You will learn what the server owns, how data is organized, what happens when a query runs, and which guarantees belong in the database.
The client-server model
A PostgreSQL session joins a client to a server. The server program, called postgres, manages database files, accepts connections, and performs database actions. A client might be the psql terminal, a graphical tool, an application server, or a maintenance program.
One running PostgreSQL server instance manages a database cluster. A cluster contains one or more databases. A connection enters one database, and SQL statements work with objects inside that database. Schemas create named areas within a database. Tables, views, functions, and other objects live in schemas.
Keep these boundaries distinct:
cluster -> databases -> schemas -> tables and other objects
client -> connection -> transaction -> SQL statements
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Sources
- https://www.postgresql.org/about/
Supports
- PostgreSQL identity, open source status, object-relational model, SQL use, extensibility, and general fit
- 00-novice.md, 01-intro.md, 02-slides.md, and 07-video-script.md
- https://www.postgresql.org/docs/current/tutorial.html
Supports
- Official beginner study path and tutorial scope
- Intro competence route and PostgreSQL Tutorial link rationale
- https://www.postgresql.org/docs/current/tutorial-arch.html
Supports
- Client-server model, postgres server role, client types, connections, and concurrency
- Intro, slides, cheatsheet mental model, quiz hierarchy, and video script
- https://www.postgresql.org/docs/current/managing-databases.html
Supports
- Cluster and database boundaries
- Intro, slides, cheatsheet, quiz, and video script hierarchy
- https://www.postgresql.org/docs/current/ddl-schemas.html
Supports
- Schemas as namespaces inside a database
- Intro, slides, cheatsheet, quiz, and video script
- https://www.postgresql.org/docs/current/ddl-basics.html
Supports
- Rows, columns, data types, CREATE TABLE, and unspecified row order
- Intro, slides, cheatsheet, quiz, and video script
- https://www.postgresql.org/docs/current/datatype.html
Supports
- Built-in type categories and type behavior
- Intro, slides, cheatsheet, video script, and Data Types link rationale
- https://www.postgresql.org/docs/current/ddl-constraints.html
Supports
- Check, not-null, unique, primary-key, and foreign-key behavior
- Intro, slides, cheatsheet, foreign-key quiz, and video script
- https://www.postgresql.org/docs/current/dml.html
Supports
- INSERT, UPDATE, DELETE, and RETURNING study scope
- Intro, slides, cheatsheet, video script, and Data Manipulation link rationale
- https://www.postgresql.org/docs/current/queries.html
Supports
- Query clauses, table expressions, grouping, sorting, and common table expressions
- Intro, slides, cheatsheet, video script, and Queries link rationale
- https://www.postgresql.org/docs/current/tutorial-join.html
Supports
- Inner and outer join relationships and explicit JOIN syntax
- Intro, slides, cheatsheet, and video script
- https://www.postgresql.org/docs/current/tutorial-agg.html
Supports
- Aggregate results, GROUP BY, HAVING, and query processing order
- Intro, slides, cheatsheet, and video script
- https://www.postgresql.org/docs/current/functions-comparison.html
Supports
- NULL comparison behavior and IS NULL tests
- Intro, slides, cheatsheet, and video script
- https://www.postgresql.org/docs/current/tutorial-transactions.html
Supports
- Atomic transaction blocks, BEGIN, COMMIT, ROLLBACK, and savepoints
- Intro, slides, cheatsheet, transaction quiz, video script, and link rationale
- https://www.postgresql.org/docs/current/mvcc.html
Supports
- Isolation levels, locking, deadlocks, and retry-relevant concurrency behavior
- Intro, slides, cheatsheet, MVCC quiz, video script, and link rationale
- https://www.postgresql.org/docs/current/mvcc-intro.html
Supports
- Snapshots, multiple row versions, and reduced read-write contention
- Intro, slides, MVCC quiz, and video script
- https://www.postgresql.org/docs/current/indexes.html
Supports
- Index types, access paths, write overhead, and index applicability
- Intro, slides, cheatsheet, planner quiz, video script, and link rationale
- https://www.postgresql.org/docs/current/indexes-types.html
Supports
- B-tree default and supported equality and range strategies
- Intro, cheatsheet, planner quiz, and video script
- https://www.postgresql.org/docs/current/using-explain.html
Supports
- Planner costs, sequential scans, EXPLAIN, and EXPLAIN ANALYZE execution
- Intro, slides, cheatsheet, EXPLAIN quizzes, video script, and link rationale
- https://www.postgresql.org/docs/current/external-extensions.html
Supports
- Extension packaging and object types
- Intro, slides, cheatsheet, extension quiz, and video script
- https://www.postgresql.org/docs/current/sql-createextension.html
Supports
- Per-database extension loading and server availability requirement
- Intro, slides, cheatsheet, extension quiz, and video script
- https://www.postgresql.org/docs/current/app-psql.html
Supports
- psql connection form, backslash commands, and client role
- Cheatsheet commands and introductory practice path
- https://github.com/sindresorhus/awesome
Supports
- Discovery path to the PostgreSQL awesome list
- https://github.com/dhamaniasad/awesome-postgres
Supports
- Discovery and category relevance of all Awesome Links entries
- https://www.pgadmin.org/docs/
Supports
- pgAdmin administration, object inspection, query tool, and graphical EXPLAIN rationale
- https://pgcli.readthedocs.io/en/latest/
Supports
- pgcli interactive client, autocompletion, and syntax-highlighting rationale
- https://www.postgis.net/docs/index.html
Supports
- PostGIS spatial objects, indexes, analysis, and processing rationale
- https://pgbackrest.org/user-guide.html
Supports
- pgBackRest backup, restore, WAL, retention, encryption, and monitoring rationale