토마스 본드라 2024년 7월 15일 이 글에서는 PostgreSQL Autovacuum 튜닝의 기본 원리와 설정 방법을 설명합니다. 죽은 행과 bloat가 발생하는 이유부터 모니터링 지표, 임계값과 스케일 팩터, 스로틀링, 작업자 수, 테이블별 설정까지 살펴보며 성능 문제의 위험을 최소화하는 방법을 알아봅니다. 이 글에서는 먼저 왜 Autovacuum이 필요한지에 대해 간략히 설명합니다. 예를 들어, 죽은 행(dead rows)이나 bloat(공간 비효율성) 문제와 이를 Autovacuum이 어떻게 처리하는지에 대해 다룰 것입니다. 그런 다음, 이 글의 핵심 주제인 튜닝 방법으로 넘어갑니다. 모든 관련 설정 옵션과 기본적인 튜닝 규칙들을 하나씩 살펴볼 예정입니다. 참고: 이 글은 제가 2016년에 처음...

Tomas Vondra 2024년 7월 11일 PostgreSQL 체크포인트를 적절히 조정하는 것은 쓰기 작업이 많은 시스템의 성능과 복구 시간을 균형 있게 관리하는 데 중요합니다. 하지만 체크포인트는 커뮤니티 메일링 리스트와 고객 지원·컨설팅 과정에서 설정 혼란이 자주 발생하는 영역이기도 합니다. 이 가이드에서는 PostgreSQL 체크포인트의 목적과 구현 방식부터 checkpoint_timeout, max_wal_size, checkpoint_completion_target의 역할, WAL 생성량 측정 및 설정값 산정 방법까지 살펴봅니다. PostgreSQL 체크포인트의 목적은 무엇인가요? PostgreSQL은 WAL(Write-Ahead Log)을 사용하는 데이터베이스 중 하나입니다. 데이터 파일에...

A few weeks ago I covered the basics of tuning checkpoints , and in that post I also mentioned autovacuum as the second common source of performance issues (based on what we see on the mailing list and at our customers). Let me follow-up on that with this post about how to tune autovacuum, to minimize the risk of performance issues. In this post I'll briefly explain why we even need autovacuum...

On systems doing non-trivial number of writes, tuning checkpoints is crucial for getting good performance. Yet checkpoints are one of the areas where we often identify confusion and configuration issues, both on the community mailing lists and during support and consulting for our customers. This post is meant to explain what checkpoints are - the purpose and how the database implements that - and then also how to tune them.

The sixteenth year of the Prague PostgreSQL Developer Day (P2D2) conference, organized by the local PUG, happened on June 4-5. This year EDB was one of the main sponsors of the event, so let us share a brief summary from the conference overall, and a bit more details about contributions by EDB speakers. P2D2 is a two-day conference, with half-day workshops on the first day, and a single track of...

One of the guiding Postgres design principles is heavy reliance on features provided by the environment (particularly operating system) and file systems are a prime example of this. Unlike other databases Postgres never supported raw devices that would require implementing a “custom” file system.

Welcome to the third – and last – part of this blog series, exploring how the PostgreSQL performance evolved over the years. The first part looked at OLTP workloads, represented by pgbench tests. The second part looked at analytical / BI queries, using a subset of the traditional TPC-H benchmark (essentially a portion of the power test). And this final part looks at full-text search, i.e. the...

In the first part of this blog series, I’ve presented a couple of benchmark results showing how PostgreSQL OLTP performance changed since 8.3, released in 2008. In this part I plan to do the same thing but for analytical / BI queries, processing large amounts of data. There’s a number of industry benchmarks for testing this workload, but probably the most commonly used one is TPC-H, so that’s what...

A couple years ago (at the pgconf.eu 2014 in Madrid) I presented a talk called “ Performance Archaeology” which showed how performance changed in recent PostgreSQL releases. I did that talk as I think the long-term view is interesting and may give us insights that may be very valuable. For people who actually work on PostgreSQL code like me, it’s a useful guide for future development, and for...

After I shared the sequential UUID benchmarks a couple of weeks ago, one of the points raised in feedback was the choice of the storage space. I’ve intentionally used a fairly weak storage system (RAID10 on three 7.2k SATA drives) because I wanted to demonstrate the benefits. But a couple of readers suggested using SSDs might significantly reduce the difference between regular and sequential UUIDs...