PostgreSQL Administration and Performance Tuning
Dates & Duration
See available dates for this course!
If there is no date and time available for a certain training you are interested in, please contact us.
This training can also be held at your place or as online training especially for your company. In this case the course agenda can also be adapted to suit your needs best.
Learn more about our customized training >>
This course takes 5 days with 8 hours each (on site) or 4 hours each (online).
The only difference is that there will be less practical exercises in an online course. However, we will hand them over to you and you can still do them on your own and ask our consultants for feedback or help if needed.
Summary
This course covers all major topics related to PostgreSQL. In addition to basic administrative tasks, we deal with performance tuning, monitoring, replication and other important topics.
Audience
This course is especially suitable for database administrators (Linux / Windows / Solaris / Mac OS X) and sysadmis. We will deal with administration and performance tuning related topics.
Available Language
This course can be held in English, German, Spanish, Polish, Estonian, Ukrainian and Russian.
COURSE CONTENT
DAY 1:
Installing PostgreSQL
- Installing PostgreSQL on Windows (if required)
- Installing PostgreSQL on Linux
- Running PostgreSQL as container (Docker)
- Creating database instances
- Creating and dropping databases
- Encoding and character sets
- Adjusting kernel parameters
The PostgreSQL architecture
- Overview of PostgreSQL
- Postmaster and its child processes
- Backend connections
- Using shared memory
- Stats collector
- Checkpoint subprocesses
PostgreSQL indexing and performance
- Types of PostgreSQL indexes
- Understanding the PostgreSQL optimizer
- EXPLAIN and EXPLAIN ANALYZE
- Reading execution plans
- Detecting slow queries
- Find missing indexes
- Full text search
- GIS indexing and geospatial search
DAY 2:
Transactions and locking
- The PostgreSQL transactional model
- Understanding MVCC (MultiVersion Concurrency Control)
- FOR UPDATE vs. FOR SHARE vs. LOCK TABLE
- Managing transactional integrity
- Using advisory locks
- Understanding deadlocks
- Making use of transaction isolation levels
- Detecting locking problems
Storage management
- Using tablespaces
- VACUUM and VACUUM FULL
- Understanding pg_squeeze
- Preventing table bloat
- Improving UPDATE performance
- HOT-UPDATE and FILLFACTOR
- PostgreSQL partitioning
Upgrading PostgreSQL
- pg_upgrade at work
DAY 3:
WAL: The PostgreSQL transaction log
- How PostgreSQL writes data to disk
- Improving checkpoint performance (postgresql.conf)
- Speeding up bulk loads
- Asynchronous COMMIT
- Unlogged tables
Memory management
- Adjusting shared_buffers
- Improving work_mem and maintenance_work_mem
- Understanding additional memory parameters
PostgreSQL replication
- Setting up streaming replication
- Synchronous vs. asynchronous replication
- Automated failover (Patroni)
- Managing service IPs
- Handling replication conflicts
- Using pg_rewind manually
- Logical replication
Database backups
- Utilizing pg_dump and pg_restore
- Binary backups (pg_basebackups)
- Point-In-Time-Recovery (PITR)
- Backup tools (pgbackrest)
DAY 4:
PostgreSQL security
- TCP vs. UNIX sockets
- Managing pg_hba.conf
- Central authentication
- Encrypted database connections
- Creating users / roles
- Access permissions (GRANT / REVOKE)
- Defining default priviledges
- PostgreSQL on disk encryption
- Row Level Security (RLS)
- Security barrier, leakproof functions, etc
PostgreSQL monitoring and logging
- Managing log files
- Handling log rotation
- Understanding PostgreSQL system tables
- Using pg_stat_statements
- Inspecting performance vitals
- Checking stored procedure performance
- Making use of pgwatch2 monitoring
DAY 5:
Parallel queries
- Configuring parallel queries
- How the optimizer handles parallelism
- Performance considerations
Server side code
- Optimizing PL/pgSQL procedures
- Finding bottlenecks in serverside code
- Monitoring procedures
- Deploying PostgreSQL extensions
Foreign Data Wrappers
- Accessing remote data
- Connecting to Oracle
- Connecting to PostgreSQL
- Migrating data to PostgreSQL
- Implications for the optimizer
Contact us to sign up for this course >>
Other courses that might interest you
We offer these services
9/5 Basic Support
CYBERTEC 9/5 Basic Support includes all Product Support services and on top also performance tuning and extended troubleshooting. 9/5 means that we’re here for you from Monday to Friday from 9am to 5pm (CET/MEZ). Other timezones on request. CYBERTEC provides all bug fixes for the most used platforms, processors or architectures. This includes not only […]
9/5 Dedicated DBA
CYBERTEC 9/5 Dedicated DBA support service includes a dedicated CYBERTEC expert who remotely takes care of your databases. We will handle any issues before you would even notice them and proactively inform you about possible future problems and needed actions. 9/5 means that your DBA is here for you from Monday to Friday from 9am […]
CYBERTEC Partner Network (CPN)
PostgreSQL expertise for your product portfolio The perfect solution for resellers and software retailers Are you a system house or a software retailer that offers PostgreSQL to its customers instead of or next to other database systems? Do you need a competent partner, with lots of experience, sophisticated services, solutions and products? Join the […]