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 _______________________________________________ Geocoding mailing list [email protected] https://lists.openstreetmap.org/listinfo/geocoding
