[Geocoding] Table space settings and storage

[Marc Tobias]

Hi,

We faced the same issue from the start because our hoster doesn't offer
SSD drives larger than 480GB. Over time we moved more and more tables
to slower (10000rpm) drives. My strategy is to keep indices on SSD and
if possible the 'word' and 'placex' table.

Moving tables around is easy and doesn't require downtime with
http://blog.lodeblomme.be/2008/03/15/move-a-postgresql-database-to-a-different-tablespace/

Below our current setup. I'm not claiming it's the best and would
appreciate if somebody can recommend a better solution.

SELECT
   REGEXP_REPLACE(C.relname, '_\d+', '_[NUMBERS]') AS tablename,
   /* N.nspname AS schemaname, */
   CASE WHEN C.relkind='r' THEN 'table' WHEN C.relkind='i' THEN 'index' 
END AS kind,
   /* pg_get_userbyid(C.relowner) AS tableowner, */
   CASE WHEN T.spcname='pg_default' THEN '' WHEN T.spcname IS NULL THEN 
'SSD' WHEN T.spcname='fastspace' THEN 'SSD' END AS tablespace,
   pg_size_pretty( SUM(pg_relation_size(C.oid)) )
FROM pg_class C
LEFT JOIN pg_namespace  N ON (N.oid = C.relnamespace)
LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE
   N.nspname NOT IN ('pg_catalog', 'information_schema')
   AND C.relname NOT LIKE 'pg_%'
/*  AND C.relkind = 'r' */
   AND pg_relation_size(C.oid) > 1024*1024*10 /* that is 10MB */
GROUP BY tablename, kind, tablespace
ORDER BY tablespace DESC, SUM(pg_relation_size(C.oid)) DESC;


       tablename                  | kind  | tablespace | pg_size_pretty
--------------------------------------------+-------+------------+----------------
idx_search_name_nameaddress_vector         | i | SSD | 93 GB
idx_place_addressline_address_place_id     | i | SSD | 33 GB
idx_place_addressline_place_id             | i | SSD | 27 GB
idx_placex_geometry                        | i | SSD | 25 GB
idx_placex_sector                          | i | SSD | 19 GB
search_name_[NUMBERS]                      | t | SSD | 16 GB
idx_placex_osmid                           | i | SSD | 14 GB
idx_placex_linked_place_id                 | i | SSD | 11 GB
idx_place_osm_unique                       | i | SSD | 9961 MB
idx_placex_rank_address                    | i | SSD | 8413 MB
idx_placex_rank_search                     | i | SSD | 8405 MB
idx_place_id                               | i | SSD | 8298 MB
location_road_[NUMBERS]                    | t | SSD | 8100 MB
idx_placex_parent_place_id                 | i | SSD | 7705 MB
place_id_idx                               | i | SSD | 7008 MB
location_area_large_[NUMBERS]              | t | SSD | 6801 MB
idx_search_name_[NUMBERS]_centroid         | i | SSD | 4443 MB
idx_search_name_centroid                   | i | SSD | 4154 MB
idx_search_name_name_vector                | i | SSD | 3817 MB
idx_search_name_[NUMBERS]_name_vector      | i | SSD | 3384 MB
pagelinks_pkey                             | i | SSD | 3372 MB
word                                       | t | SSD | 2350 MB
idx_location_road_[NUMBERS]_geometry       | i | SSD | 2223 MB
idx_osm_id                                 | i | SSD | 1717 MB
idx_word_word_token                        | i | SSD | 1679 MB
idx_search_name_place_id                   | i | SSD | 1600 MB
idx_search_name_[NUMBERS]_place_id         | i | SSD | 1405 MB
idx_word_word_id                           | i | SSD | 807 MB
idx_location_road_[NUMBERS]_place_id       | i | SSD | 794 MB
idx_wikipedia_redirect_from_title          | i | SSD | 682 MB
idx_placex_pendingsector                   | i | SSD | 643 MB
idx_location_area_large_[NUMBERS]_geometry | i | SSD | 430 MB
idx_placex_adminname                       | i | SSD | 263 MB
idx_location_area_large_[NUMBERS]_place_id | i | SSD | 133 MB
planet_osm_ways_idx                        | i | SSD | 73 MB
location_area_country                      | t | SSD | 66 MB
country_osm_grid                           | t | SSD | 17 MB
planet_osm_ways_nodes                      | i |     | 136 GB
planet_osm_nodes                           | t |     | 114 GB
planet_osm_nodes_pkey                      | i |     | 60 GB
place                                      | t |     | 60 GB
placex                                     | t |     | 58 GB
place_addressline                          | t |     | 56 GB
planet_osm_ways                            | t |     | 41 GB
search_name                                | t |     | 28 GB
planet_osm_ways_pkey                       | i |     | 6732 MB
wikipedia_article                          | t |     | 6425 MB
wikipedia_redirect                         | t |     | 1194 MB
planet_osm_rels                            | t |     | 1076 MB
planet_osm_rels_parts                      | i |     | 753 MB
gb_postcode                                | t |     | 123 MB
planet_osm_rels_pkey                       | i |     | 56 MB