Suggest modification to this talk

Title

Description

RailsConf 2019 - Optimizing your app by understanding your PostgreSQL database by Samay Sharma
_______________________________________________________________________________________________
Cloud 66 - Pain Free Rails Deployments
Cloud 66 for Rails acts like your in-house DevOps team to build, deploy and maintain your Rails applications on any cloud or server.

Get $100 Cloud 66 Free Credits with the code: RailsConf-19
($100 Cloud 66 Free Credits, for the new user only, valid till 31st December 2019)

Link to the website: https://cloud66.com/rails?utm_source=-&utm_medium=-&utm_campaign=RailsConf19
Link to sign up: https://app.cloud66.com/users/sign_in?utm_source=-&utm_medium=-&utm_campaign=RailsConf19
_______________________________________________________________________________________________
This is a sponsored talk by Citus Data.

I’m a Postgres person. Period. After talking to many Rails developers about their application performance, I realized many performance issues can be solved by understanding your database a bit better. So I thought I’d share the statistics Postgres captures for you and how you can use them to find slow queries, un-used indexes, or tables which are not getting vacuumed correctly. This talk will cover Postgres tools and tips for the above, including pgstatstatements, useful catalog tables, and recently added Postgres features such as CREATE STATISTICS.

Date

Summarized using AI?

If this talk's summary was generated by AI, please check this box. A "Summarized using AI" badge will be displayed in the summary tab to indicate that the summary was generated using AI.

Show "Summarized using AI" badge on summary page

Summary

Markdown supported

In this RailsConf 2019 talk titled "Optimizing your app by understanding your PostgreSQL database," Samay Sharma emphasizes the importance of comprehending PostgreSQL statistics to enhance application performance. The focus is on identifying issues causing slow application performance through database understanding. Key points discussed include:

- **Performance Issues**: Common complaints from users often relate to slow queries, prompting developers to dig deeper into PostgreSQL performance metrics.  
- **Understanding PostgreSQL Statistics**: PostgreSQL provides various statistics that can aid in diagnosing performance issues, such as cache hit ratios and monitoring statistics.  
- **Key Tables**: Vital PostgreSQL tables like `pg_stat_database`, `pg_stat_user_tables`, and `pg_stat_statements` are highlighted for their roles in providing insights about database performance, query execution, and user activity.  
- **Cache Hit Ratio**: A crucial measure indicating how efficiently the database serves queries from memory rather than disk. Ideally, transactional applications should aim for a cache hit ratio greater than 95%.  
- **Bloat Management**: Auto vacuum processes need to be properly tuned to manage unused data and optimize cache utilization to maintain performance.  
- **Index Usage**: Developers should monitor both used and unused indexes to understand their impact on performance. Misconfigured indexes may slow down queries due to additional maintenance overhead.  
- **Tuning Queries**: The use of the `EXPLAIN ANALYZE` command helps identify the slowest parts of queries and offers insights for optimization. Query plans should be closely examined, especially for discrepancies between estimated and actual row returns.  
- **Statistics Correlation**: The presentation emphasizes leveraging statistics for better query optimization, including the ability to declare multi-column statistics for improved accuracy in query execution plans.

In conclusion, understanding and utilizing PostgreSQL's rich set of tools and statistical data can significantly improve application performance by effectively diagnosing and resolving database-related bottlenecks and inefficiencies.

Samay also shares that Citus Data has been acquired by Microsoft, indicating ongoing advancements in PostgreSQL technologies and support. The session wraps with an invitation for further discussion and a demonstration of PostgreSQL's capabilities in optimization and maintenance.

Cancel