PostgreSQL Performance
Tuning
This talk is designed for advanced PostgreSQL users who want
to know how to maximize PostgreSQL performance. It covers
every aspect of performance: server settings, caching, sizing
operating system resources, optimizer processing, problem queries,
storage efficiency, and some hardware selection details. It
includes how to size shared memory, how to understand the output
of the optimizer, when to restructure queries, and how to configure
storage for optimal performance.
Duration: 3 hours, 4 hours with questions
- Tver.io Meetup, February 10, 2020 (video)
- PGCon, May 23, 2017
- PG Day France, June 5, 2014
- PostgreSQL Conference Europe, October 29, 2013
- Southeast LinuxFest June 10, 2012
- ConFoo, February 29, 2012
- Gulev, December 8, 2006
- NordU Usenix, January 29, 2004
- International PHP Conference, November 2-5, 2003
- Fosdem, February 8-9, 2003
- O'Reilly Open Source Convention, July 22, 2002
- SRA, December 10, 2001
Explaining the Postgres
Query Optimizer
The optimizer is the "brain" of the database, interpreting SQL
queries and determining the fastest method of execution. This talk uses
the explain command to show how
the optimizer interprets queries and determines optimal execution.
The talk will assist developers and administrators in
understanding how Postgres optimally executes their queries and what
steps they can take to understand and perhaps improve its behavior.
Duration: 45 minutes, 60 minutes with questions
- Citus Con, April 12, 2022 (video)
- PostgresConf.CN & PGConf.Asia, December 15, 2021 (video)
- Tver.io Meetup, February 10, 2020 (video)
- GDG Tyumen, February 7, 2020
- Postgres Expert Day with Bruce Momjian, June 18, 2019
- Percona Live Europe Open Source Database Conference, November 6, 2018
- Warsaw PostgreSQL Users Group, April 9, 2018 (video)
- PGCon, May 23, 2017
- pgDaySF, March 10, 2015
- Scale, February 20, 2015 (video)
- PG Master, October 28, 2014
- Postgres Open, September 18, 2014 (video)
- PGCon, May 23, 2014 (video)
- PGDay.IT, October 25, 2013
- National Research Nuclear University (MEPhI), October 22, 2013
- HighLoad++, October 22, 2012 (video in Russian)
- O'Reilly Open Source Convention, July 20, 2012
- Southeast LinuxFest, June 10, 2012 (video)
- PostgreSQL Conference Europe, October 19, 2011
- Boston PostgreSQL Users Group, July 12, 2011
Database Hardware
Selection Guidelines
Database servers have hardware requirements different from other
infrastructure software, specifically unique demands on I/O and memory.
This presentation covers these differences and various I/O options and
their benefits. Topics include solid-state drives (SSD),
battery-backed RAID, controllers, and caching.
Duration: 30 minutes, 45 minutes with questions
- Percona Live Online, May 13, 2021 (video)
- Hopla webinar, June 3, 2020 (video)
- Tver.io Meetup, February 10, 2020 (video)
- PGCon, May 23, 2017
- PGConf Silicon Valley, November 15, 2016
- Postgres Open, September 18, 2015 (video)
- Scale, February 21, 2015 (video)
- PG Day France, June 5, 2014
- ConFoo, February 27, 2013
- Southeast LinuxFest, June 10, 2012 (video)
- New York PostgreSQL Meetup Group, May 7, 2012 (video)
- webinar, June 22, 2011 (video, registration required)
Postgres Scaling
Opportunities
Database scaling is the ability to increase database throughput
by utilizing additional resources such as I/O, memory, cpu, or additional computers. However,
the high concurrency and write requirements of database servers make
scaling a challenge. Sometimes scaling is only possible with multiple
sessions, while other options require data model adjustments or
server configuration changes. This talk explores the multi-session,
single-session, and multi-host scaling options and the workloads
where these options are appropriate.
Duration: 45 minutes, 1 hour with questions
- PostgreSQL Conference Europe, October 22, 2014
The Future of Postgres
Sharding
Database sharding involves spreading database contents across
multiple servers, with each server holding only part of the database.
While it is possible to vertically scale Postgres, and to scale
read-only workloads across multiple servers, only sharding allows
multi-server read-write scaling. This presentation will cover the
advantages of sharding and future Postgres sharding implementation
requirements, including foreign data wrapper enhancements,
parallelism, and global snapshot and transaction control. This is
a followup to my Postgres
Scaling Opportunities presentation.
Duration: 30 minutes, 45 minutes with questions
- Chinese Postgres Open Source Software Alliance, May 16, 2020 (video)
- PostgreSQL Conference Europe, October 16, 2019
- Postgres Expert Day with Bruce Momjian, June 18, 2019
- PGCon, May 31, 2019 (video)
- Deutschsprachige PostgreSQL Konferenz, April 13, 2018
- Siberian State Aerospace University, February 8, 2016
- HighLoad++, November 2, 2015 (video, video in Russian)
- PGDay.IT, October 23, 2015
- San Francisco Bay Area PostgreSQL Meetup Group, August 17, 2015