[Geocoding] Table space settings and storage

[Sarah Hoffmann]

Hi,

On Fri, Feb 13, 2015 at 01:36:05PM +0000, Anders Gunnarsson wrote:
> I'm currently looking over our setup of Noiminatim, since data is growing out of our current storage capacity with high performance.
> 
> I noticed the new tablespace settings attached below, which enables the option to partition into fast and slow storage.
> Does anyone have experience with this? How much space is used by each type of data for a full planet? What needs to be on fast media and what can go on slower? I guess all indexes need fast media, and maybe some of the data too.

That's a fairly new feature I added for our production servers at osm.org
where it is already deployed. Here is the order I would give to the
importance of the tablespaces. This is purely subjective, I have no
numbers to back this:

 1. CONST_Tablespace_Search_Index
 2. CONST_Tablespace_Search_Data
 3. CONST_Tablespace_Aux_Index
 4. CONST_Tablespace_Address_Index
 5. CONST_Tablespace_Place_Index
 6. CONST_Tablespace_Address_Data
 7. CONST_Tablespace_Place_Data
 8. CONST_Tablespace_Osm2pgsql_Index
 9. CONST_Tablespace_Aux_Data
10. CONST_Tablespace_Osm2pgsql_Data

On our installation 1-2 and 4-5 use 420G together, 5 and 6 use 100 GB,
8 uses 120 GB (add another 100GB if you don't use the flat-nodes feature).
Again, take that with a grain of salt because the indexes tend to bloat
pretty badly over time.

> We currently use one master instance, feeding read only slaves which are used for lookup. If I interpret the settings correct it would be possible to use walbouncer to filter out tablespaces only containing data marked with "update only", so that the slave instances only contain lookup data. Is that true?

That is an interesting thought. The tablespace feature wasn't created
with such an application in mind but it should work, if you use the
tablesspaces "update only" and the default tablespace (there are some
tiny tables which are created without any tablespace). Alternatively,
here is a minimal list of tables, you need for a read-only copy:

*columns
import_polygon_*
import_status
place_addressline
placex
search_name
seq_*
word
place_classtype_* (if you allow special searchterms)
location_property_tiger
location_property_aux

That's around 300GB after a fresh import (add room for index bloat)
and another 350GB if you use Tiger data. You could just manually
move them (and their indexes) in a new dataspace in your existing
DB and try walbouncer on that. That would save you the reimport
that is necessary to be able to use the new tablespace feature.

Kind regards

Sarah