Tuning PostGIS for production environments?

Since Postgis is a component of Postgres I would recommend this great book (I own it and I found it extremely valuable) on Postgres performance tuning:

http://www.packtpub.com/postgresql-90-high-performance/book

It starts from the basics (planning the hardware, os, etc) and then grows into explaining all those misterious configuration params that I never knew how to tune before. After that it shows how to analyze slow queries, explains how the optimizer works, how to monitor general database activity and find bottlenecks.

The author is a postgres developer so he really knows what he's talking about and the book has been also praised from the development group.

The book is focused on version 9 but it always says when a solution applies or not and with which differences to prior versions (down to 8.0, if I recall correctly).


PostGIS in Action, which has been released last month is a good book for worth reading.

enter image description here 

PART 1 - LEARNING POSTGIS
1 - What is a spatial database? Completed download chapter code and data
2 - Geometry Types Completed download chapter code and data
3 - Data Modeling Completed download chapter code and data
4 - Geometry Functions Completed download chapter code and data
5 - Relationships between geometries Completed download chapter code and data
6 - Spatial reference system considerations Completed download chapter code and data
7 - Working with real data Completed download chapter code and data

PART 2 - PUTTING POSTGIS TO WORK
8 - Techniques to solve spatial problems Completed download chapter code and data
9 - Performance Tuning Completed download chapter code and data

PART 3 - USING POSTGIS WITH OTHER TOOLS 
10 - Enhancing SQL with add-ons Completed download chapter code and data
11 - Using PostGIS in web applications Completed download chapter code and data
12 - Using PostGIS in a Desktop Environment Completed download chapter code and data
13 - PostGIS Raster Completed download chapter code and data

OPENGEO has a simple tutorial that will serve as a good starting point:

  • Tuning PostgreSQL for Spatial

Although not directly related to PostGIS, I've found these two posts informative when it comes to more 'general' PostgreSQL setups:

  • Tuning PostgreSQL on your Dedicated Server
  • Performance Tuning PostgreSQL

This presentation by PGexperts is also very informative:

  • Five Steps to PostgreSQL Performance (see also other presentations)

Tags:

Performance

Postgis

References

Recent Posts