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 forum post is the kind of information I have found helpful, this is probably just basic DB stuff but to me its good stuff.

I would be interested to see what resources have helped others in achieving a stable production installation of PostGIS.

3
  • 3
    bostongis.com is good starting point
    – Mapperz ♦
    May 26 '11 at 13:49
  • 2
    time for an update from the community me thinks.
    – BWill
    Aug 29 '11 at 11:58
  • 2
    And a supplementary question, any db managmeent software that makes things go more smoothly in production environments? I use dbvis since it gives me (a nnob) the ability to see diagrams of tables and keys, but it lacks other basic functionality in other areas such as restoring dbs. So I also use pgAdminIII. Any others?
    – BWill
    Aug 31 '11 at 0:51
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).

Improve this answer
4
  • It seems the cost of Postgis is all about the "learning". I am thinking a cloud server with PostgreSQL and Postgis sitting on it is going to cost $10 /mnth? But reading the book? Wll I am grateful for the link to the book, but I think you are saying that there is no recipe yet, very much a site specific result. Thanks
    – BWill
    Aug 31 '11 at 0:29
  • 'PostgreSQL 9 Admin Cookbook' from the same publisher packtpub.com/postgresql-9-admin-cookbook/book is pretty good as well
    – radek
    Aug 31 '11 at 12:22
  • 1
    @BWill I have been using Postgres for many years now and I just can't say how much I wished I had a book like that only three years ago! Unfortunately Postgres is not the most user friendly database and its complexity (together with some awkward design choices like the dreaded transaction-id-wraparound and its nemesis the vacuum) requires careful planning to get the best from it. With version 9.0 things are thankfully improving, but if you're managing anything bigger that a hundred thousands records you should really read the book because you will need that information before or after. HTH.
    – unicoletti
    Aug 31 '11 at 14:10
  • Okey Dokey unicolleti, I appreciate your comment. Have 430,000 rows in one table & another which is growing by 2000 rows per day. And still using pg 8.4 ! I will get the books..... thanks guys.
    – BWill
    Sep 2 '11 at 10:57
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
Improve this answer
5
  • Indeed, it is a good book - well the only book - that covers PostGIS. Chapter 9 covers the necessary tweaks and optimisations. As far as I know, pretty much any other optimisations are done at the Postgres level, which your DBA should be able to sort out. The PGIA site also has a forum that would be a good resource for this.
    – MerseyViking
    May 26 '11 at 12:54
  • 3
    You should include a summary rather than just a link.
    – Sean
    May 26 '11 at 13:24
  • 2
    What is it about the book which makes it food for answering this question? If possible we should provide information and context in our answers, then provide a reference so someone can look up the details.
    – scw
    May 26 '11 at 19:07
  • 1
    Yeah I grabbed PostGIS in Action the other day, I am approximately 50% through the book. I flicked ahead to chapter 9 but it wasn't quite what I was after (although still helpful). One of my biggest concerns is what changes need to be made to a database created from the postGIS_template in order to make it worthy for production and future upgrades - Similar to the forum link I provide in my original post.
    – Ando
    May 26 '11 at 21:45
  • Update for the 2nd Edition: Chapter 15 Query performance tuning is 40 pages long and considers queries, indexes, server configuration and data organization. Good advice on performance is evident throughout the book, not just confined to this chapter.
    – Toby Speight
    Jun 17 '15 at 19:48
10

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.

Improve this answer
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:

Improve this answer
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).

Improve this answer
2
0

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

Improve this answer

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