Introduction to SQL Optimisations: Tuning PostgreSQL Queries
Learn a systematic approach to PostgreSQL query tuning: find the queries worth the effort, diagnose what is wrong, then change the query, index or schema, in that order. Recognise when data volume, not query design, is the limit.
- €150per person, before VAT
- 1 live session1½ hours
- Theory and live demoswith Q&A
- Live online via Zoomin English
- Certificate of Attendance
Upcoming dates
What you'll learn
This session is about how to reason about performance and where to look first. It is the layer above indexing, statistics and execution plans: it tells you which of those tools to reach for, and when.
The course covers
- Deciding which queries are worth optimising: frequency, user-visible latency and hot paths
- Finding them with pg_stat_statements, the slow-query log and application metrics
- Replacing row-by-row loops with set-based SQL: joins, RETURNING, ON CONFLICT and MERGE
- Moving less data: selecting only the columns you need, filtering in the database, proper pagination
- Fewer round-trips, the N+1 problem, and connection pooling
- Transaction scope, lock queues, deadlocks, SKIP LOCKED work queues and timeouts
- When data volume is the limit: retention, archiving and partitioning
Prerequisites
For developers responsible for the performance of an application on PostgreSQL who want a method rather than a list of tricks.
You should be comfortable writing SQL. The session is self-contained: no other session of the track is required.
For details see the course terms & conditions.
Syllabus
1What is worth optimising
- The Pareto principle, and three criteria for "worth optimising".
- Finding targets with pg_stat_statements, the slow-query log, application latency metrics and EXPLAIN.
2Think relational, not procedural
- Set against row thinking: replacing loops with sets.
- Joins instead of subqueries that hide work.
- RETURNING, MERGE, and the "check then act" anti-pattern.
3Data access cost
- Fetch only the columns and rows you need; filter in the database, not the application.
- Proper pagination and the N+1 problem.
- Connection cost and pooling.
4Transactions, locking and contention
- Locks that block, lock queues and long transactions.
- Deadlocks and a predictable locking order.
- SELECT FOR UPDATE SKIP LOCKED, and timeouts as safety nets.
5Data volume
- Growth patterns, retention and archiving.
- Time-series and append-only data.
- When partitioning helps, and when it does not.
Frequently asked questions
Who is this session for?
Developers who own the performance of a PostgreSQL application. It assumes working SQL, not knowledge of PostgreSQL internals.
Can I book this session on its own?
Yes. Every session of the developer track is self-contained. It is also one of the seven sessions of High Performance SQL with Postgres, which you can book in full at a package price.
Is the session recorded?
No. The session is live, with time for questions, and is not recorded. You receive the handouts: the slides with the trainer's commentary and the full demo transcripts.
Can my company pay by invoice?
Yes — write to connect@dataegret.com and we invoice your company; card booking is also available online.
Instructors
Over 20 years of industry experience, with extensive expertise in supporting clients on their PostgreSQL journey and achieving high availability for business-critical projects.
A specialist in monitoring, high availability, performance tuning and scalable system architecture, with a background in banking, finance and telecommunications.
Ready to book, or still deciding?
Live, small-group, taught by the people who run PostgreSQL in production. Questions first? Write to us — no sales pitch.