System Source

PostgreSQL Basics provides a solid foundation in PostgreSQL, a powerful open-source relational database system. You'll learn how to install, configure, and manage PostgreSQL in a development environment. The course covers essential topics such as working with different data types, writing SQL queries, designing efficient database schemas, and optimizing query performance. Additionally, you'll explore transaction management, user and role management, and basic security policies to ensure data integrity and security.

By the end of the course, you'll have the skills to effectively manage PostgreSQL databases and perform fundamental backup and restore operations. The course combines theoretical knowledge with practical exercises, ensuring you can apply what you learn in real-world scenarios. Join us to unlock the full potential of PostgreSQL and enhance your database management skills.


05/12/25 - GVT - Virtual Classroom - Virtual Instructor-Led
06/30/25 - GVT - Virtual Classroom - Virtual Instructor-Led
08/25/25 - GVT - Virtual Classroom - Virtual Instructor-Led
10/13/25 - GVT - Virtual Classroom - Virtual Instructor-Led

Introduction to PostgreSQL

overview, key features, and benefits

Installation and Configuration

Installing PostgreSQL on Windows/ Mac

Using package managers for Linux/Mac.

Install and Configure PostgreSQL Tools(pgAdmin,psql)

Verify installation: connect using psql , run basic command

Modify postgresql.conf Essential settings(Memory, logging, connection)

Modify Settings directly, or use ALTER SYSTEM

File Locations: Typical file paths for different OS (Linux, Windows, macOS (SHOW config_file; or SHOW hba_file).

Adjust Connection Settings for remote access: listen_addresses parameter, Network Security Considerations

Client Authentication Configuration: pg_hba.conf structure and syntax, Supported authentication methods(Trust, md5, peer)

Data Types and Table Basics

Numeric Types (Integer Types(int, bigint, smallint), Decimal Types(decimal, numeric, float), Serial Type(serial, bigserial))

Character Types (char, varchar, text)

Date/Time Types (DATE, TIME, TMESTAMP, INTERVAL,Common Operations, Time Zone Handling)

Boolean Type(TRUE, FALSE, NULL)

Arrays: definition and syntax

Custom data types

JSON data type

Using PostgreSQL for vector storage

Creating Tables (CREATE TABLE), Modifying table(ALTER TABLE), Dropping tables (DROP TABLE, CASCADE)

SQL Fundamentals

SELECT Statement Essentials: (SELECT syntax, using DISTINCT to remove duplicates, using AS to rename columns or tables)

WHERE Clause and Filtering(basic filtering with WHERE,

Basic Filtering using WHERE

Comparison Operators (=, !=, >, <, >=, <=)

Logical Operators(AND, OR, NOT)

Pattern Matching with LIKE

Range Filtering with BETWEEN

NULL Handling

JOIN Operations (INNER JOIN, LEFT JOIN)

Basic Aggregations (COUNT, SUM, AVG, MIN, MAX)

Grouping results with GROUP BY and HAVING clause

Data modification: INSERT, UPDATE, DELETE Operations

Database Design Principles

Database design concepts

Normalization task (1NF, 2NF, 3NF)

Normalization example

Tade off of denormalization

Primary Key Selection for ensuring row uniqueness

Foreign Key Implementation for maintaining referential integrity, Examples of foreign key constraints: ON DELETE CASCADE and ON UPDATE CASCADE

Table Relationships (one-to-one, one-to-many, many-to-many)

Constraint Types and Usage( NOT NULL, UNIQUE, CHECK, DEFAULT, Primary key, and foreign key)

Schema Organization(What is a Schema?, Benefits of Schema Organization: Logical grouping, Security and access control, Better organization in large systems, Easier maintenance)

Indexing and Performance

Indexing fundamental( B-Tree indexes(Definition, Structure, Search Mechanism, why use B-trees?), other index types(HASH, JIN, BRIN,GiST))

When to create indexes (frequent use in WHERE, JOIN, ORDER BY, GROUP BY, high cardinality, covering indexes, composite indexes, large tables

When not to create indexes? (small tables, Frequent writes, columns with low cardinality, columns with sparse usage)

Query Performance Analysis(Introduction)

EXPLAIN, its output components: (Node Type, Relation, Filter, Cost, Rows, Width)

EXPLAIN ANALYZE

Identifying common query issues (Sequential Scans on Large Tables, Missing or Inefficient Indexes, Cost estimation.)

Query optimization techniques (Efficient query design, Index Optimization, Reducing Query complexity, autovacuum process )

Monitoring and Troubleshhoting Query Performance(Using Explain and Logs, Real-time Monitoring tools, Fixing performance issues)

Transaction Management

Transaction Basics (ACID): (ACID properties, real world examples of Transactions)

Transaction life-cycle and control(Life cycle, Transaction control using BEGIN, COMMIT, ROLLBACK, savepoints)

common errors in transaction management

Transaction Isolation Levels(Introduction, Levels of isolation, Trade-offs in Isolation Levels, Example scenarios)

Handling Concurrent Access(concurrency control, Locks, Optimistic vs. Pessimistic Concurrency Control, Serializable transactions)

Deadlock Prevention and Resolution(Deadlock basics, deadlock detection, Deadlock Prevention Strategies)

Security Implementation

User and Role Management: ( 1. User and role concepts, 2. Creating, altering and dropping users and roles, 3. Manging role membership)

GRANT and REVOKE Operations:

Role of GRANT and REVOKE (1. GRANT, 2. REVOKE, 3.Permission types)

Syntax for Granting and Revoking Privileges

Checking current privileges

Auditing privileges

Principle of least privilege

Schema Permissions(Schema-level permissions, restricting access to sensitive data, Managing access to Schema Objects)

Object Privileges(Object types and privileges, GRANT and REVOKE for object-level access)

Password Policies(password complexity requirements, password expiration policies, user authentication methods)

Basic Backup and Recovery(Importance of Backups, Backup types (Logical vs. Physical), tools for automating backups, Restoring from backups)

foundational knowledge of databases, operating systems, networking, and programming

Understanding Networking Fundamentals
Introduction to Programming

Software developers
Data scientists
System administrators
Technical professionals.

PostgreSQL Basics

Course Overview

Scheduled Classes

Outline

Prerequisites

Who Should Attend