[Geocoding] Table space settings and storage

[Anders Gunnarsson]

Hi Marc,

Thanks for sharing this info, it's much appreciated!

Good to know it's possible to move tables between table spaces without a complete rebuild. This time it was needed anyway, so one is just started.

/Anders

> 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