Talks
Speakers
Events
Topics
Sign in
Home
Talks
Speakers
Events
Topics
Leaderboard
Use
Analytics
Sign in
Suggest modification to this talk
Title
Description
To many developers the database is a black box. You expect to be able to put data into your database, have it to stay there, and get it out when you query it... hopefully in a performant manner. When its not performant enough the two options are usually add some indexes or throw some hardware at it. We'll walk through a bit of a clearer guide of how you can understand how database is doing from a 30,000 foot perspective as well as analyze specific problematic queries and how to tune them. In particular we'll cover: * Postgres Caching * Postgres Indexing * Explain Plans * Extensions * More
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 the talk titled "Postgres Performance for Humans," Craig Kerstiens, a product lead on the data team at Heroku, provides an insightful overview of Postgres database performance optimized for developers who may find the database's inner workings opaque. The focus is on practical strategies to enhance performance and troubleshoot issues while better understanding the system's capacity. Key points discussed include: - **Database Workloads**: Differentiate between OLTP (Online Transaction Processing) for web applications and OLAP (Online Analytical Processing) for data warehousing. - **Postgres Configuration**: Initial setup considerations, particularly in cloud environments, and best practices for optimizing performance via recommended configuration settings. - **Cache Management**: Emphasizes the importance of RAM for caching frequently accessed data, aiming for a cache hit ratio of 99% or higher. - **Effective Indexing**: Discusses methods for assessing and implementing indices, stressing a target index hit rate of 95% or higher and listing steps for creating or dropping indices as needed. - **Data Retrieval Methods**: Explains the difference between sequential scans and index scans, highlighting how index-only scans introduced in version 9.2 enhance efficiency. - **Query Analysis**: Introducing the EXPLAIN command and its variants (EXPLAIN ANALYZE) to analyze query execution plans for better performance optimization, alongside tips on managing and optimizing slow queries. - **Types of Indexes**: Outlines different types of indices such as GIN, GiST, and the advantages of B-tree indexes as a default choice unless unique requirements dictate otherwise. - **Connection Pooling**: Suggests using connection poolers like PG Bouncer to manage database connections effectively under load. - **Scaling and Backups**: Provides insight on scaling strategies like sharding and replication for high availability, emphasizing the critical importance of regular and tested backups, differentiating between logical and physical backup methods. - **Monitoring Tools**: Advocates for utilizing PG_STAT_STATEMENTS for query performance tracking. Overall, Kerstiens underscores that mastering these fundamental practices—particularly cache management and index optimization—greatly improves Postgres performance, providing a reliable starting point for managing workloads efficiently. He concludes that enhancing memory resources can be a significant performance booster for any application utilizing Postgres.
Suggest modifications
Cancel
