PostgreSQL Basics [GK840202]

OVERVIEW

Learn the essentials of PostgreSQL and enhance your database management skills.

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.

OBJECTIVES

• 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

AUDIENCE

- Software developers
- Data scientists
- System administrators
- Technical professionals.

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)