2025-06-16 –, Maschinenhaus
This talks explains locking mechanisms (MVCC, lock queue) in PostgreSQL, focusing on table-level locks that are acquired by Data Definition Language(DDL) operations. If not managed well, schema changes can result in downtime. Not all operations require the same level of locking, and PostgreSQL offers tools and techniques to minimize locking impact.
In PostgreSQL, managing schema changes without downtime can be a challenging task. Table-level locks, which control access during Data Definition Language (DDL) operations like ALTER or DROP TABLE, can result in unintended application slowdowns or even service interruptions when not fully understood. This talk will provide a comprehensive dive into table-level locking and lock queueing in PostgreSQL, helping attendees gain the insights they need to perform efficient schema changes.
We’ll start by explaining the various types of table-level locks in PostgreSQL such as Access Share, Exclusive, and Access Exclusive and how they are automatically managed during common DDL operations. Then, we’ll break down lock queuing: how PostgreSQL organizes lock requests, what happens when transactions wait for locks, and how deadlocks can arise in complex environments.
Next, we’ll focus on practical approaches to managing table-level locks for near-zero downtime. Attendees will learn techniques to minimize locking impact, including understanding lock conflicts, using online schema migration patterns, and identifying lock-heavy queries. We’ll introduce open-source tools like pgroll, which utilizes the expand/contract pattern to make schema changes in small, lock-free steps.
By the end of this session, attendees will be equipped with practical strategies and knowledge to control lock behavior during schema changes, ensuring data integrity and reducing operational disruptions. This talk will provide the tools needed to manage PostgreSQL schema changes with confidence and minimal impact on production environments.
Data Science, Store, Operations
Level:Intermediate
Gülçin started working with Postgres at a startup company in 2012 and was amazed at how powerful Postgres truly is! Over the years, she has actively contributed to the PostgreSQL community by organizing conferences, delivering talks, and engaging as a dedicated community member. In recognition of her commitment, Gülçin was elected to the PostgreSQL Europe Board in 2017.
Fueled by her passion for PostgreSQL automation and cloud technologies, Gülçin took on the role of Cloud Services Manager and led the cloud development efforts at 2ndQuadrant, which was later acquired by EDB in 2020. Committed to fostering diversity and inclusion, she is an integral part of Postgres Women, advocating for increased representation of women in technical communities.
Currently, Gülçin is a Staff Database Engineer at Xata, where she continues to explore her interests in PostgreSQL. In addition to her engineering work, she is one of the co-founders of Kadin Yazilimci (Women Developers of Turkey) and has led the core team for more than 10 years. In 2023, she launched Diva: Dive into AI as a Kadin Yazilimci initiative and has been part of the organizing team since.
She is now recognized as a PostgreSQL Contributor by the Postgres project. Being part of PostgreSQL Europe Diversity Committee, she looks forward to serving the community and contributing to the project's longevity and health. Gülçin lives in Prague and is the co-founder and organizer of the monthly Prague PostgreSQL Meetup.