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)
Worldwide Locations
Global Knowledge Delivered Course
Training Exclusives
Virtual Classroom Live
Guaranteed to Run
Download course details