Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Looking for PostgreSQL Performance Issues

DZone's Guide to

Looking for PostgreSQL Performance Issues

· Performance Zone
Free Resource

Download our Introduction to API Performance Testing and learn why testing your API is just as important as testing your website, and how to start today.

 As traffic goes up, even your PostgreSQL may become a bottleneck.
In this cases it is important to analyze the traffic and understand the usage pattern. The way you will be able to tune the system to meet the challenge.
Understand Usage Pattern at Peak Time
Use PostreSQL top project to get key usage patterns in real time:

  1. Current active SQL statements running
  2. Query plans
  3. Locks
  4. User tables and indexes statistics
Understand Overall Usage Pattern

To get a broad insight of PostgreSQL behavior use  pgFouine. This tool analyzes the PostgreSQL logs and provides detailed usage patternsreports such as leading queries, duration, queries by type and queries patterns.
You can get some of these metrics by querying the pg_catalog schema (such as pg_stat_user_tables and pg_stat_user_indexes), and use log_statement to analyze all queries.

Enable Slow Queries

Probably #1 tool to eliminate performance issues:
  1. Add pg_stat_statements  to shared_preload_libraries statement @ postresql.conf
  2. Restart PostgreSQL daemon.
  3. use the pg_stat_statements view to pinpoint the bottlenecks:

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

Explain the Execution Plans
Use the  Explain statement to analyze slow queries execution paths and eliminate them:
EXPLAIN (FORMAT JSON) SELECT * FROM table_name;

Bottom Line
Using these great tools, you can boost your PostgreSQL and meet the business challenges

Keep Performing,
Moshe Kaplan

Find scaling and performance issues before your customers do with our Introduction to High-Capacity Load Testing guide.

Topics:

Published at DZone with permission of Moshe Kaplan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}