Database Reference
In-Depth Information
Chapter 10. Maintenance, Optimization,
and Performance Tuning
In this chapter, we will cover the following recipes:
• Organizing the database
• Setting up the correct data privilege mechanism
• Backing up the database
• Using indexes
• Clustering for efficiency
• Optimizing SQL queries
• Migrating a PostGIS database to a different server
• Replicating a PostGIS database with streaming replication
Introduction
Unlikepriorchapters,thischapterdoesnotdiscussthecapabilitiesorapplicationsof
PostGIS.Instead,thischapterfocusesonthetechniquesfororganizingthedatabase,
improving query performance, and ensuring the long-term viability of the spatial data.
These techniques are frequently ignored or set aside by most PostGIS users until it
is too late, such as data loss due to user action or decreasing performance as the
volume of data or users increases. Such neglect is often due to the amount of time
requiredtolearnabouteachtechniqueaswellasimplementit.Thischapterattempts
todemonstrateeachtechniqueinadistilledmannerthatminimizesthelearningcurve
and maximizes the benefits.
