Book Image

Troubleshooting PostgreSQL

Book Image

Troubleshooting PostgreSQL

Overview of this book

If you are a database administrator looking for solutions to common PostgreSQL problems, this is the book for you. The book is suitable for people with intermediate and professional expertise.

Related Content you might be interested in

Current Title:

Small book image
Troubleshooting PostgreSQL
Mar, 2015 | 164 pages
No titles found
Table of Contents (12 chapters)
Preface
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Free Chapter
1
1. Installing PostgreSQL
1. Installing PostgreSQL
Deciding on a version number
Methods of installing PostgreSQL
Memory and kernel issues
Adding checksums to a database instance
Preventing encoding-related issues
Avoiding template pollution
Summary
2
2. Creating Data Structures
2. Creating Data Structures
Grouping columns the right way
Deciding on data types and structure
Deciding on normalization
Summary
3
3. Handling Indexes
3. Handling Indexes
Understanding indexes in PostgreSQL
Avoiding trouble with indexes
Solving common problems
Handling LIKE queries
Finding good matches
Fixing full-text search
Summary
4
4. Reading Data Efficiently and Correctly
4. Reading Data Efficiently and Correctly
Understanding the power of NULL
Fixing disastrous joins
Reading large amounts of data
Understanding prepared queries
Summary
5
5. Getting Transactions and Locking Right
5. Getting Transactions and Locking Right
The PostgreSQL transaction model
Transaction isolation
Inspecting locks
Summary
6
6. Writing Proper Procedures
6. Writing Proper Procedures
Choosing the right language
Managing procedures and transactions
Procedures and indexing
Understanding memory
Summary
7
7. PostgreSQL Monitoring
7. PostgreSQL Monitoring
Checking the overall database behavior
Detecting I/O bottlenecks
Checking for conflicts
Chasing down slow queries
Inspecting internal information
Integrating with external tools
Alternative tools
Summary
8
8. Fixing Backups and Replication
8. Fixing Backups and Replication
Using pg_dump
Managing point-in-time recovery
Making use of asynchronous replication
Switching to synchronous replication
Handling timelines
Summary
9
9. Handling Hardware and Software Disasters
9. Handling Hardware and Software Disasters
Checksums – preventing silent corruption
Zeroing out damaged pages
Dealing with index corruption
Dumping individual pages
Resetting the transaction log
Power-out-related issues
Summary
10
10. A Standard Approach to Troubleshooting
10. A Standard Approach to Troubleshooting
Getting an overview of the problem
Attacking low performance
Fixing common replication issues
Summary
11
Index
Index

Chapter 3. Handling Indexes

In this chapter, one of the most important topics in the area of database work will be covered—indexing. In many cases, missing or wrong indexes are the main source of trouble, leading to bad performance, unexpected behavior, and a great deal of frustration. To avoid these issues, this chapter will present all the information that you need to make your life as easy as possible.

The following issues will be covered:

  • Understanding PostgreSQL indexing
  • Types of indexes
  • Managing operator classes
  • Detecting missing indexes
  • Reading execution plans
  • Fixing LIKE
  • Fixing regular expressions
  • Improving indexes and functions
  • Indexes and insertions
  • Indexes and foreign keys
Previous Section
End of Section 1
Next Section