PostgreSQL Basics

• 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

Module 1: Introduction to PostgreSQL

• Introducing PostgreSQL

Module 2: Installation and Configuration

• Configuring PostgreSQL for optimal performance
• Using two ways to modify PostgreSQL settings
• Locating PostgreSQL configuration files
• Configuring PostgreSQL for remote connections
• Managing client access with pg_hba.conf

Module 3: Data Types and Table Basics

• Tables in PostgreSQL
• Integer data type
• Numeric or Decimal data type
• Character data type
• Date/Time data type
• Boolean data type
• Arrays
• JSON data type
• Special data types
• Using PostgreSQL for vector storage

Module 4: SQL Fundamentals

• Adding, updating, and deleting table data
• SELECT syntax
• WHERE clause and filtering with comparison operators
• Combining conditions with logical operators
• Pattern matching with LIKE and wildcards
• Range filtering in SQL
• Combining BETWEEN with other operators
• Working with NULL values and combining NULL checks with other conditions
• Combining data with JOINs

Module 5: Database Design and Management

• Entities and relationships
• Normalization task
• Trade off from denormalization
• Table relationships
• Constraint types and usage
• Subqueries for querying and data base manipulation
• Removing duplicate rows
• Cartesian product
• Schema organization

Module 6: Indexing and Performance

• Indexing fundamentals
• Optimizing index usage in PostgreSQL
• Query performance analysis
• Using EXPLAIN and EXPLAIN ANALYZE
• Identifying common query issues
• Query optimization techniques
• Auto vacuum and index maintenance

Module 7: Transaction Management

• Common errors in transaction management
• Transaction isolation levels
• Query performance analysis
• Handling concurrent access
• Deadlock prevention and resolution

Module 8: Security Implementation

• Checking current privileges
• Auditing privileges
• Principle of Least Privilege
• Schema permissions
• Object privileges
• Password policies
• Basic backup and recovery

• Foundational knowledge of databases, operating systems, networking, and programming