You're reading from PostgreSQL 9.0 High Performance If you‚Äôre an intermediate to advanced database administrator, this book is the shortcut to optimizing and troubleshooting your PostgreSQL database. With a balanced mix of theory and practice, it will quickly hone your expertise.

Product type Paperback

Published in Oct 2010

Publisher Packt

ISBN-13 9781849510301

Length 468 pages

Edition 1st Edition

Languages

SQL

Tools

PostgreSQL

Concepts

Database Administration

Author (1):

Gregory Smith

View More author details

Table of Contents (120) Chapters

Preface

1. What this book covers

2. What you need for this book FREE CHAPTER

3. Who this book is for

4. Conventions

Note
Note

5. Reader feedback

6. Customer support

7. Chapter 1. PostgreSQL Versions

8. Performance of historical PostgreSQL releases

9. PostgreSQL or another database?

10. PostgreSQL tools

11. PostgreSQL application scaling lifecycle

12. Performance tuning as a practice

13. Summary

14. Chapter 2. Database Hardware

15. Balancing hardware spending

16. Reliable controller and disk setup

17. Summary

18. Chapter 3. Database Hardware Benchmarking

19. CPU and memory benchmarking

20. Physical disk performance

21. Disk benchmarking tools

22. Sample disk results

Disk performance expectations

23. Summary

24. Chapter 4. Disk Setup

25. Maximum filesystem sizes

26. Filesystem crash recovery

Journaling filesystems

27. Linux filesystems

28. Solaris and FreeBSD filesystems

29. Windows filesystems

FAT32
NTFS

30. Disk layout for PostgreSQL

31. Summary

32. Chapter 5. Memory for Database Caching

33. Inspecting the database cache

34. Crash recovery and the buffer cache

35. Database buffer cache versus operating system cache

36. Analyzing buffer cache contents

37. Summary

38. Chapter 6. Server Configuration Tuning

39. Interacting with the live configuration

40. Server-wide settings

41. Per-client settings

42. New server tuning

43. Dedicated server guidelines

44. Shared server guidelines

45. pgtune

46. Summary

47. Chapter 7. Routine Maintenance

48. Transaction visibility with multiversion concurrency control

49. Vacuum

50. Autoanalyze

51. Index bloat

Measuring index bloat

52. Detailed data and index page monitoring

53. Monitoring query logs

54. Summary

55. Chapter 8. Database Benchmarking

56. pgbench default tests

57. Running pgbench manually

58. Graphing results with pgbench-tools

Configuring pgbench-tools

59. Sample pgbench test results

60. Sources for bad results and variation

61. pgbench custom tests

Insert speed test

62. Transaction Processing Performance Council benchmarks

63. Summary

64. Chapter 9. Database Indexing

65. Indexing example walkthrough

66. Index creation and maintenance

67. Index types

68. Advanced index use

69. Summary

70. Chapter 10. Query Optimization

71. Sample data sets

72. EXPLAIN basics

73. Query plan node structure

Basic cost computation

74. Explain analysis tools

75. Assembling row sets

76. Processing nodes

77. Joins

78. Statistics

79. Other query planning parameters

80. Executing other statement types

81. Improving queries

82. SQL Limitations

83. Summary

84. Chapter 11. Database Activity and Statistics

85. Statistics views

86. Cumulative and live views

87. Table statistics

Table I/O

88. Index statistics

Index I/O

89. Database wide totals

90. Connections and activity

91. Locks

92. Disk usage

Note

93. Buffer, background writer, and checkpoint activity

94. Summary

95. Chapter 12. Monitoring and Trending

96. UNIX monitoring tools

97. Windows monitoring tools

98. Trending software

99. Summary

100. Chapter 13. Pooling and Caching

101. Connection pooling

102. Database caching

103. Summary

104. Chapter 14. Scaling with Replication

105. Hot Standby

106. Replication queue managers

107. Special application requirements

108. Other interesting replication projects

109. Summary

110. Chapter 15. Partitioning Data

111. Table range partitioning

112. Horizontal partitioning with PL/Proxy

113. Summary

114. Chapter 16. Avoiding Common Problems

115. Bulk loading

116. Common performance issues

117. Profiling the database

118. Performance related features by version

119. Summary

Statistics targets

It should be obvious from the previous section that the number of buckets used to hold common values or histogram samples significantly influences how accurate query row estimates will be. The number of them ANALYZE aims to collect is called its target. The postgresql.conf parameter default_statistics_target sets the standard target for tables in each database. The default target was 10 up to PostgreSQL 8.3, increasing to 100 in 8.4. This means that 8.4 version has significantly more statistics to work with by default, which normally results in better queries. The cost for that is slightly worse, query planning time as well as significantly longer ANALYZE processing (that second part not being something most people care very much about).

If you only have simple queries to plan and query planning time is large in a later PostgreSQL version where the target starts at 100, it's possible to reduce default_statistics_target back to the older starting value of 10...

The rest of the chapter is locked

You're reading from PostgreSQL 9.0 High Performance If you‚Äôre an intermediate to advanced database administrator, this book is the shortcut to optimizing and troubleshooting your PostgreSQL database. With a balanced mix of theory and practice, it will quickly hone your expertise.

Table of Contents (120) Chapters

Statistics targets

Unlock this book and the full library FREE for 7 days

Personalised recommendations for you