Duration
3 days
Objective
You will learn database optimization techniques such as query optimization and indexing while exploring extensive code examples. Lastly, you will explore foreign data wrappers, implementing extensibility, and improving maintainability.
Chapter 1: Getting Started with PostgreSQL
Writing queries using psql
Creating, altering, and truncating a table
Inserting, updating, and deleting data from tables
PostgreSQL's supported data types
PostgreSQL's operators and usage
Constraints in PostgreSQL
Privileges in PostgreSQL
Chapter 2: The Procedural Language
Why should you use PL/pgSQL?
Installing PL/pgSQL
Understanding the structure of PL/pgSQL
Using comments in PL/pgSQL
Declaring variables in PL/pgSQL
Declaring function parameters
Declaring the %TYPE attribute
Declaring the row-type and record type variables
Statements and expressions
Control structures
Exception handling
Native support for other procedural languages
Chapter 3: Working with Indexes
What is an index?
How to create an index
How to drop an index
Types of indexes
Index types
Index bloating
Reindexing
Points to ponder
Chapter 4: Triggers, Rules, and Views
Understanding triggers
PostgreSQL rules
Understanding views
Chapter 5: Window Functions
Understanding window functions
Chapter 6: Partitioning
Creating a table partition
Partitioning in PostgreSQL
Chapter 7: Query Optimization
What is EXPLAIN?
Cost parameters
Sequential scans
Index scans
Bitmap scans
Common Table Expressions
Joins
Query planning
Hints
Configuration parameters to optimize queries
Chapter 8: Dealing with Large Objects
Why large objects?
PostgreSQL large objects
Manipulating large objects through the libpq client
Interface library
Chapter 9: Communicating with PostgreSQL Using LibPQ
Connecting and disconnecting to PostgreSQL
Establishing a connection asynchronously
Connection-supporting functions
Executing a query
Executing prepared statements
Retrieving data
Using status functions
Programme scope, delivery arrangements and suitability are confirmed during enquiry.