PostgreSQL Performance & Query Optimization · レッスン

pg_stat_statements と pg_buffercache の使用

`pg_stat_statements` や `pg_buffercache` などの強力な拡張機能を活用し、パフォーマンスを詳しく分析する方法を学びます。

「pg_stat_statements と pg_buffercache の使用」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。これはレッスン1/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはPostgreSQL Performance & Query Optimization学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

PostgreSQL Extensions: Power-Ups

PostgreSQL is incredibly powerful, and its functionality can be extended even further using extensions.

Extensions are modules that add new functions, data types, operators, and more to your database. They're like plugins that enhance your database's capabilities without modifying its core code.

In this lesson, we'll explore two crucial extensions for performance monitoring: pg_stat_statements and pg_buffercache.

Meet pg_stat_statements: Query Profiler

pg_stat_statements is an invaluable tool for understanding your database's workload. It tracks execution statistics for all SQL statements executed by the server.

This extension helps you identify:

Which queries are run most frequently.
Which queries consume the most total time.
Queries with high average execution times.
Queries that read/write a lot of disk blocks.

It's your go-to for pinpointing performance bottlenecks at the query level.

Activating pg_stat_statements

To use pg_stat_statements, you first need to enable it in your PostgreSQL configuration.

1. Edit your postgresql.conf file and add pg_stat_statements to the shared_preload_libraries parameter. E.g., shared_preload_libraries = 'pg_stat_statements'.

2. Restart your PostgreSQL server for the change to take effect.

3. Finally, connect to your database and create the extension:

CREATE EXTENSION pg_stat_statements;

Deciphering pg_stat_statements Output

Once enabled, pg_stat_statements collects data in a view named pg_stat_statements. Here are some key columns you'll often check:

query: The normalized SQL statement.
calls: How many times the query was executed.
total_time: Total time spent executing this query (in milliseconds).
mean_time: Average execution time per call.
rows: Total rows returned or affected.
shared_blks_hit / shared_blks_read: Cache hits vs. disk reads.

Sorting by total_time helps find the biggest overall resource consumers.

Practical Example: Top Queries

Let's see how to find the top 5 queries that have consumed the most total execution time. This query helps you prioritize your optimization efforts.

The hit_percent column gives you an idea of how effective PostgreSQL's buffer cache is for that query.

SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / (shared_blks_hit + shared_blks_read + 1) AS hit_percent
FROM
    pg_stat_statements
ORDER BY
    total_time DESC
LIMIT 5;

Meet pg_buffercache: Memory Inspector

While pg_stat_statements shows you query performance, pg_buffercache gives you insight into PostgreSQL's shared buffer cache. This is the memory area where PostgreSQL stores frequently accessed data blocks.

Understanding what's in the buffer cache helps you determine:

Which tables or indexes are most actively used.
If your shared_buffers setting is adequate.
Whether queries are benefiting from cached data or hitting disk.

Activating pg_buffercache

Enabling pg_buffercache is simpler than pg_stat_statements. It typically does not require modification to shared_preload_libraries or a server restart.

You just need to connect to your database and create the extension:

CREATE EXTENSION pg_buffercache;

Peeking into the Shared Buffer Cache

After creating the extension, you can query the pg_buffercache view to see which relations (tables or indexes) occupy the most buffers. Each buffer typically represents an 8KB data block.

This query shows the top 5 relations by the number of buffers they occupy in the cache:

SELECT
    c.relname AS relation_name,
    count(*) AS buffers_in_cache
FROM
    pg_buffercache b
JOIN
    pg_class c ON b.relfilenode = c.relfilenode
JOIN
    pg_database d ON b.reldatabase = d.oid AND d.datname = current_database()
GROUP BY
    c.relname
ORDER BY
    buffers_in_cache DESC
LIMIT 5;

Interpreting Cache Effectiveness

If a table or index consistently appears at the top of the pg_buffercache output with many buffers, it means that data is frequently accessed and kept in memory.

This is generally a good sign, as memory access is much faster than disk I/O. A high hit_percent in pg_stat_statements often correlates with data being present in the buffer cache.

You can use this information to decide if increasing shared_buffers or optimizing queries to access less data would be beneficial.

Quick Check: Monitoring Tools

Which of the following statements are true regarding pg_stat_statements and pg_buffercache?

Recap: Deep Dives with Extensions

Great job! You've learned how to leverage two powerful PostgreSQL extensions for performance monitoring:

pg_stat_statements: Your essential tool for profiling SQL queries, identifying slow-running or frequently executed statements, and understanding their resource consumption.
pg_buffercache: Gives you visibility into the shared buffer cache, helping you understand what data is actively residing in memory and how effectively your caching is working.

Combined, these extensions provide deep insights into your database's workload and memory utilization, guiding your optimization efforts.

よくある質問

「pg_stat_statements と pg_buffercache の使用」レッスンは無料ですか？

はい。「pg_stat_statements と pg_buffercache の使用」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「pg_stat_statements と pg_buffercache の使用」で何を学びますか？

`pg_stat_statements` や `pg_buffercache` などの強力な拡張機能を活用し、パフォーマンスを詳しく分析する方法を学びます。ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

PostgreSQL Performance & Query Optimizationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのPostgreSQL Performance & Query Optimizationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン1/4です。

「pg_stat_statements と pg_buffercache の使用」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このPostgreSQL Performance & Query Optimizationレッスンでコードを書いて実行できますか？

はい。すべてのPostgreSQL Performance & Query Optimizationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← PostgreSQL Performance & Query Optimizationに戻る