47

What PostGIS documentation have you come across that has been helpful when tuning PostGIS for a production environment?

I would like to get together with my DBA to setup a Postgresql/PostGIS installation that is fit for production. I have read that there is some tweaking involved to achieve this, and I was hoping to find the answer on the refractions web site.

So far I have found some documents on the OpenGeo site helpful, like this one.

And this old PostGIS forum post is the kind of information I have found helpful, this is probably just basic DB stuff but to me its good stuff.

What resources have helped others in achieving a stable production installation of PostGIS?

0

7 Answers 7

22
+50

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).

0
18

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
0
11

Ensure you are storing valid geometries, otherwise unexpected errors can happen (server crashes, erroneous distance and area calculations, etc., see What are the implications of invalid geometries).

I highly recommend adding check constraints to production geometry columns to make sure the geometries are valid. It is simple to add (just replace "my_valid_table" and "geom" for your table/column):

ALTER TABLE public.my_valid_table
  ADD CONSTRAINT enforce_valid_geom CHECK (st_isvalid(geom));

See details here.

10

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

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

This presentation by PGexperts is also very informative:

6

Always keep your data in a different schema than public.

Keeping your data separated from the public schema will make it easier for your to updagre PostGIS and will make it easier for you to restore schema dumps.

Imagine that you dump your data inside the public schema. Since dump is so nice, it will also dump table definitions and functions, so to restore again (in public) you will have name conflicts with the other (already) installed functions and tables (geometry_columns, for example).

0
-1

Versioning, metatdata, QC checks, tuning, throughput optimization, planning, specification and beta developement

Not the answer you're looking for? Browse other questions tagged or ask your own question.