Login
Database · Relational data, SQL, and performance

PostgreSQL

Learn PostgreSQL as a powerful relational database platform with advanced SQL, schema design, indexing, transactions, views, functions, JSONB, query optimization, security, and backup practices.

Move from SQL fundamentals to a complete Analytics Database project with reporting queries, indexes, views, JSONB attributes, documentation, and performance analysis.

Beginner to Advanced 6 Weeks 10 Modules Online / Classroom Analytics Database Project
Explore curriculum → View capstone project

Course overview

PostgreSQL is a powerful open-source relational database management system used for transactional applications, analytics, reporting, and data-intensive services. It supports advanced SQL, rich data types, JSONB, extensibility, and strong data-integrity features.

This course introduces PostgreSQL fundamentals, databases, schemas, roles, SQL syntax, data types, constraints, relationships, joins, subqueries, common table expressions, window functions, indexes, transactions, views, functions, JSONB, query optimization, security, and backup practices.

The proposed capstone is an Analytics Database project covering normalized tables, reporting queries, indexes, views, JSONB attributes, documentation, and performance analysis.

A strong PostgreSQL design does more than store data. It enforces integrity, supports business rules, enables efficient reporting, and remains maintainable as data and requirements grow.

Prerequisites

This course is designed for learners with basic computer knowledge. Basic SQL knowledge is recommended.

  • Basic computer knowledge.
  • Basic SQL knowledge is recommended.
  • Basic understanding of tables, rows, and columns.
  • Basic logical thinking and problem-solving skills.
  • Basic command-line knowledge is helpful.
  • pgAdmin, psql, or DBeaver is recommended.

Readiness activity

Explain the difference between a database, schema, table, row, and column. Identify two reasons why duplicate records can create problems in an analytics database.

Who can explore this course?

SQL learners

Advance SQL skills

Build practical PostgreSQL and advanced SQL skills.

Developers

Build backend skills

Learn how applications store, query, and manage relational data.

Data learners

Analyze business data

Write reporting queries, aggregations, and analytical SQL.

Career changers

Enter database roles

Develop practical skills for database and data-engineering pathways.

What you will learn

  • Explain relational-database and PostgreSQL fundamentals.
  • Install and connect to PostgreSQL using common clients.
  • Create databases, schemas, tables, and relationships.
  • Use PostgreSQL data types and constraints.
  • Write SELECT, INSERT, UPDATE, and DELETE queries.
  • Use filtering, sorting, grouping, and aggregation.
  • Write joins, subqueries, CTEs, and window functions.
  • Create and evaluate indexes appropriately.
  • Understand transactions, isolation, and concurrency.
  • Create views, functions, procedures, and triggers.
  • Use JSON and JSONB for semi-structured data.
  • Analyze query plans and optimize slow queries.
  • Manage roles, privileges, backups, and recovery.

Curriculum outline

The ten-module outline moves from PostgreSQL fundamentals to a complete Analytics Database. Exact PostgreSQL version, client tools, and sample datasets should be confirmed before delivery.

01

PostgreSQL fundamentals

Understand relational databases and set up a PostgreSQL working environment.

  • Database and relational-model concepts.
  • PostgreSQL overview and use cases.
  • Installing and connecting to PostgreSQL.
  • psql, pgAdmin, and DBeaver.
  • Databases, schemas, tables, rows, and columns.
  • Roles and basic database objects.

Practice: Install or connect to PostgreSQL, create a database and schema, and inspect tables and columns.

02

SQL and data types

Create database structures and manage data using SQL commands.

  • CREATE DATABASE and CREATE TABLE.
  • ALTER TABLE and DROP TABLE.
  • PostgreSQL data types.
  • Primary keys and foreign keys.
  • NOT NULL, UNIQUE, DEFAULT, and CHECK constraints.
  • INSERT, UPDATE, DELETE, and SELECT.

Practice: Create tables with constraints, insert sample records, and query the data safely.

03

Querying data

Retrieve and analyze data using SQL filtering, sorting, and aggregation.

  • WHERE conditions and operators.
  • ORDER BY and LIMIT.
  • DISTINCT and NULL handling.
  • GROUP BY and HAVING.
  • Aggregate functions.
  • String, date, and numeric functions.

Practice: Write queries to filter, sort, group, and summarize a sample dataset.

03

Advanced queries

Combine data from multiple tables and write analytical SQL queries.

  • INNER, LEFT, RIGHT, and FULL joins.
  • Self joins and multiple-table joins.
  • Subqueries.
  • Common table expressions.
  • Window functions.
  • Set operations and query readability.

Practice: Write analytical queries using joins, CTEs, and window functions on a sample dataset.

04

Indexes and query performance

Improve query performance using appropriate indexing strategies.

  • Index concepts and benefits.
  • B-tree indexes.
  • Unique and composite indexes.
  • Partial and expression indexes.
  • Index selection guidelines.
  • Index-aware query design.

Practice: Compare query performance before and after adding an appropriate index.

05

Views, functions, and procedures

Create reusable database objects for reporting and business logic.

  • Views and use cases.
  • Materialized views.
  • Functions and return types.
  • Procedures.
  • Triggers.
  • Maintainability and testing.

Practice: Create a reporting view and a function for a controlled business task.

05

Transactions and concurrency

Understand how PostgreSQL protects data during multi-step operations.

  • Transactions and ACID concepts.
  • BEGIN, COMMIT, and ROLLBACK.
  • Savepoints.
  • Isolation levels.
  • Locking concepts.
  • Handling concurrent updates.

Practice: Create a transaction that updates related records and test rollback behavior.

06

JSON and advanced data

Work with structured and semi-structured data using PostgreSQL JSON features.

  • JSON versus JSONB.
  • JSONB storage and indexing.
  • Querying JSONB data.
  • Arrays and composite types.
  • Enums and custom types.
  • Choosing appropriate data models.

Practice: Store, query, and index JSONB attributes for a controlled use case.

07

Performance and optimization

Analyze query plans and improve database performance using operational best practices.

  • EXPLAIN and EXPLAIN ANALYZE.
  • Query planning concepts.
  • Slow-query analysis.
  • Statistics and vacuum concepts.
  • Query optimization workflow.
  • Database maintenance practices.

Practice: Analyze a slow query using EXPLAIN ANALYZE and apply an appropriate optimization.

08

Security and backup

Manage database access safely and protect data using operational best practices.

  • Roles and users.
  • GRANT and REVOKE privileges.
  • Least-privilege access.
  • Backup types and strategies.
  • pg_dump and restore concepts.
  • Recovery and maintenance practices.

Practice: Create a limited database role, grant only required privileges, and document a backup-and-restore workflow.

09

Capstone delivery

Complete the Analytics Database project and prepare a professional demonstration.

  • Define business requirements and entities.
  • Design normalized tables and relationships.
  • Create constraints, indexes, views, and JSONB fields.
  • Write business and reporting queries.
  • Test transactions and data integrity.
  • Analyze query performance.
  • Document schema, queries, and maintenance steps.
  • Present the database design and results.

Practice: Submit a complete Analytics Database with SQL scripts, sample data, documentation, and reporting queries.

Practical exercise ideas

Complete these smaller activities before assembling the final Analytics Database project.

SQL

Sales report

Filter, sort, and summarize sales records using SQL.

Joins

Customer analysis

Join customers, orders, and products to answer business questions.

Window functions

Ranking report

Rank products, customers, or regions using window functions.

Indexes

Query tuning

Analyze a slow query and apply an appropriate index.

JSONB

Product attributes

Store and query flexible product attributes using JSONB.

Security

Limited role

Create a database role with only the required privileges.

Suggested six-week learning plan

This is an illustrative learning sequence. Confirm the academy's official timetable, PostgreSQL version, client tools, datasets, and assessment requirements before publishing.

Weekly focus and practical milestones
Week Focus Suggested milestone
01 PostgreSQL fundamentals and SQL basics Create a database, schema, table, and basic queries.
02 Data types, constraints, and querying Insert, update, filter, sort, and aggregate data.
03 Joins, CTEs, and window functions Write multi-table and analytical queries.
04 Indexes, transactions, and views Improve a query using an index and test a transaction.
05 JSONB, performance, security, and backups Query JSONB, optimize a query, and restore a backup.
06 Capstone presentation Submit and present the Analytics Database.
Design an analytics-oriented database

Capstone project

Analytics Database

Design and build an Analytics Database for a controlled, approved business scenario. Possible subjects include sales analytics, customer behavior, product performance, website events, inventory analytics, or another suitable educational dataset.

Core project requirements

  • Define the business requirements and main entities.
  • Create an entity-relationship diagram or schema design.
  • Create normalized tables for customers, products, orders, order items, and payments.
  • Use appropriate primary keys, foreign keys, and relationships.
  • Apply NOT NULL, UNIQUE, DEFAULT, and CHECK constraints where appropriate.
  • Insert controlled sample data for testing.
  • Write queries for customer, product, sales, and revenue reporting.
  • Create at least two reporting views.
  • Use a CTE and a window function in reporting queries.
  • Create appropriate indexes for common query patterns.
  • Use JSONB for flexible product or event attributes.
  • Use a transaction for a controlled multi-step workflow.
  • Create a limited database role with least-privilege access.
  • Document the schema, assumptions, queries, and backup plan.

Quality requirements

  • Use consistent naming conventions for tables and columns.
  • Avoid storing duplicated customer or product information unnecessarily.
  • Use foreign keys to maintain referential integrity.
  • Use appropriate PostgreSQL data types for each column.
  • Use JSONB only where flexible or semi-structured data is appropriate.
  • Use indexes only where they support real query patterns.
  • Test constraints, joins, transactions, and reporting queries.
  • Use EXPLAIN ANALYZE to review query performance.
  • Use parameterized queries in application code to reduce SQL-injection risk.
  • Document backup and restore procedures.
  • Clearly state assumptions, limitations, and future improvements.

A strong analytics database should support accurate reporting, protect data integrity, make common queries efficient, and remain maintainable as data volume and business requirements grow.

Suggested project structure

Keep schema scripts, seed data, queries, documentation, and backup materials organized for maintainability.

analytics-database/
├── sql/
│   ├── 01_create_database.sql
│   ├── 02_create_schemas.sql
│   ├── 03_create_tables.sql
│   ├── 04_create_indexes.sql
│   ├── 05_create_views.sql
│   └── 06_create_roles.sql
├── data/
│   ├── seed-data.sql
│   └── sample-queries.sql
├── docs/
│   ├── schema-design.md
│   ├── business-rules.md
│   └── backup-plan.md
├── diagrams/
│   └── erd.png
├── README.md
└── .gitignore

Do not commit database passwords, connection strings, private keys, real customer data, or production backup files to a public repository.

PostgreSQL database workflow

Database projects are iterative. New business rules, reporting needs, performance findings, and data-quality issues may require updates to tables, constraints, indexes, queries, or security.

Requirements

Define entities

Identify business objects, relationships, and required reports.

Design

Model the schema

Create tables, keys, relationships, and integrity constraints.

Data

Load sample data

Insert controlled test data and validate expected results.

Queries

Build reports

Write joins, aggregations, CTEs, window functions, and views.

Performance

Optimize queries

Review query plans, indexes, statistics, and slow-query patterns.

Operations

Protect data

Manage roles, privileges, backups, restores, and maintenance.

Normalization helps reduce duplicate data and improve integrity, while indexes help improve query performance when designed around actual access patterns.

Tools and technologies

The exact PostgreSQL version and client tools may vary by delivery. The proposed toolkit focuses on practical PostgreSQL database development and analytics.

  • PostgreSQL
  • psql
  • pgAdmin
  • DBeaver
  • SQL
  • JSONB
  • ER diagrams
  • Git
  • GitHub
  • Visual Studio Code

Supporting concepts

  • Relational databases, schemas, and normalization.
  • DDL, DML, and DCL SQL commands.
  • Joins, subqueries, CTEs, and window functions.
  • Constraints, keys, and referential integrity.
  • Indexes, transactions, and query plans.
  • Views, functions, procedures, and triggers.
  • JSONB, roles, privileges, backups, and recovery.

Learning outcomes

By completing the proposed lessons and exercises, aim to demonstrate the following abilities:

  • Explain relational-database and PostgreSQL fundamentals.
  • Design normalized PostgreSQL schemas and relationships.
  • Write SQL queries for filtering, joining, and reporting.
  • Use constraints to protect data integrity.
  • Create and evaluate indexes for query performance.
  • Use transactions and rollback operations safely.
  • Create views, functions, procedures, and triggers.
  • Use JSONB for appropriate semi-structured data.
  • Analyze query plans and optimize database performance.
  • Manage roles and apply least-privilege access.
  • Plan backups, restores, and database maintenance.

These are learning objectives, not guarantees of employment, certification, placement, or a specific database role. Progress depends on SQL practice, data modeling, troubleshooting, and continued learning.

Related career interests

Illustrative directions for continued learning, not job or placement guarantees.

  • PostgreSQL Developer
  • Database Developer
  • Database Engineer Trainee
  • Data Engineer Trainee
  • Backend Developer
  • Data Analyst Trainee
  • Database Support Engineer

Portfolio presentation ideas

  • Explain the business requirements and database entities.
  • Show the ER diagram and table relationships.
  • Demonstrate key business and reporting queries.
  • Explain constraints, indexes, and transactions.
  • Present query-optimization examples and results.
  • Discuss JSONB usage, security, backups, and maintenance.

Frequently asked questions

Who is this course for?

It is suitable for SQL learners, developers, data learners, and career changers who want to build practical PostgreSQL and advanced SQL skills.

Do I need SQL experience?

Basic SQL knowledge is recommended. The course begins with PostgreSQL and SQL fundamentals.

Do I need programming experience?

No. Basic computer knowledge is sufficient. Programming knowledge can help when connecting databases to applications.

Will the course cover database design?

Yes. It covers entities, relationships, normalization concepts, primary keys, foreign keys, constraints, and ER diagrams.

Will the course cover joins?

Yes. It covers INNER, LEFT, RIGHT, and FULL joins, self joins, multiple-table joins, subqueries, CTEs, and window functions.

Will the course cover indexes?

Yes. It covers B-tree, unique, composite, partial, and expression indexes, along with query-plan analysis and performance concepts.

Will the course cover transactions?

Yes. It covers ACID concepts, transactions, commit, rollback, savepoints, isolation levels, and locking concepts.

Will the course cover JSONB?

Yes. It covers JSON versus JSONB, JSONB storage and indexing, querying JSONB data, arrays, and choosing appropriate data models.

Will the course cover functions?

Yes. It covers views, materialized views, functions, procedures, and triggers.

Will the course cover backups?

Yes. It covers backup types, backup strategies, pg_dump and restore concepts, and database-maintenance practices.

What is the capstone project?

The proposed capstone is an Analytics Database covering normalized tables, reporting queries, indexes, views, JSONB attributes, transactions, and documentation.

Which tools are used?

The proposed tools include PostgreSQL, psql, pgAdmin, and DBeaver. Confirm the academy's selected PostgreSQL version and client tools before enrollment.

How long is the course?

The supplied course information proposes a duration of six weeks. Confirm the academy's official schedule, tools, datasets, and assessment requirements.

Does this course guarantee a job?

No. The course can support practical learning and portfolio development, but it does not guarantee employment, placement, certification, or salary.

How do I enroll?

This page is a frontend course-information demonstration. Enrollment, payment, scheduling, and admission workflows are not implemented here.

Build reliable relational databases

Build your PostgreSQL project

Study schema design, SQL, joins, constraints, indexes, transactions, views, JSONB, security, optimization, and backups through a practical Analytics Database project.

Review curriculum → Browse all courses