GK840202 PostgreSQL Basics

COURSE OBJECTIVE:
• Install and configure PostgreSQL in a development environment • Select and implement fundamental data types including numeric, character, and date/time types • Write essential SQL queries using basic joins, filtering, and aggregations • Design normalized database schemas and implement key constraints for data integrity • Understand and implement B-tree indexes to optimize common query patterns • Manage basic transactions and understand isolation levels • Execute concurrent operations safely using proper transaction management • Create and manage users and roles with appropriate privileges using GRANT/REVOKE • Implement basic security policies and access controls • Perform fundamental backup and restore operations

TARGET AUDIENCE:
– Software developers
– Data scientists
– System administrators
– Technical professionals.

COURSE PREREQUISITES:
• Foundational knowledge of databases, operating systems, networking, and programming

COURSE CONTENT:
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)FOLLOW ON COURSES:
Not available. Please contact.