La base de datos parece inexplicablemente grande

Soporte Instalación
1

tl;dr: Parece que rake db:stats reporta alrededor de 10 GB de datos, pero postgres_data ocupa 18 GB. ¿Es probable que haya 8 GB de índices ocupando el resto del espacio, o hay algún tipo de limpieza que deba realizarse?

Versión más larga de la misma pregunta

El directorio de datos de PostgreSQL ocupa 18 GB. Una copia de seguridad de la base de datos tiene aproximadamente 5 GB.

var/discourse# du -hs shared/data/postgres_data/
18G     shared/data/postgres_data/

Esto es lo que revela rake db:stats. He recortado las filas reportadas en kB (aproximadamente 10 GB).

table_name row_estimate size
post_timings 49643076 3011 MB
posts 2289502 990 MB
user_actions 5506773 915 MB
post_search_data 2136003 875 MB
email_logs 2934363 717 MB
topic_views 3498593 507 MB
topic_users 3714046 448 MB
post_stats 1894296 319 MB
notifications 1178136 296 MB
post_replies 1077331 127 MB
topics 146814 107 MB
post_actions 456903 63 MB
draft_sequences 671770 54 MB
post_revisions 74596 51 MB
user_profile_views 465818 49 MB
topic_search_data 124471 46 MB
topic_links 160437 33 MB
scheduler_stats 168878 29 MB
uploads 82100 22 MB
post_custom_fields 35255 22 MB
top_topics 106478 21 MB
unsubscribe_keys 109936 19 MB
optimized_images 67107 18 MB
user_histories 77643 13 MB
topic_link_clicks 124501 12 MB
1 me gusta
2

You may have a restore schema with tables, try list those with psql.

3

Thanks. Is this what I’m looking for? I don’t think I see a restore schema, but I’m not quite sure what I’m looking for.

                List of relations
Schema Name Type Owner
public api_keys table discourse
public application_requests table discourse
public ar_internal_metadata table discourse
public badge_groupings table discourse
public badge_types table discourse
public badges table discourse
public categories table discourse
public categories_web_hooks table discourse
public category_custom_fields table discourse
public category_featured_topics table discourse
public category_featured_users table discourse
public category_groups table discourse
public category_search_data table discourse
public category_tag_groups table discourse
public category_tags table discourse
public category_users table discourse
public child_themes table discourse
public color_scheme_colors table discourse
public color_schemes table discourse
public custom_emojis table discourse
public developers table discourse
public directory_items table discourse
public draft_sequences table discourse
public drafts table discourse
public email_change_requests table discourse
public email_logs table discourse
public email_tokens table discourse
public embeddable_hosts table discourse
public facebook_user_infos table discourse
public github_user_infos table discourse
public given_daily_likes table discourse
public google_user_infos table discourse
public group_archived_messages table discourse
public group_custom_fields table discourse
public group_histories table discourse
public group_mentions table discourse
public group_users table discourse
public groups table discourse
public groups_web_hooks table discourse
public incoming_domains table discourse
public incoming_emails table discourse
public incoming_links table discourse
public incoming_referers table discourse
public instagram_user_infos table discourse
public invited_groups table discourse
public invites table discourse
public message_bus table discourse
public muted_users table discourse
public notifications table discourse
public oauth2_user_infos table discourse
public onceoff_logs table discourse
public optimized_images table discourse
public permalinks table discourse
public plugin_store_rows table discourse
public post_action_types table discourse
public post_actions table discourse
public post_custom_fields table discourse
public post_details table discourse
public post_replies table discourse
public post_revisions table discourse
public post_search_data table discourse
public post_stats table discourse
public post_timings table discourse
public post_uploads table discourse
public posts table discourse
public queued_posts table discourse
public quoted_posts table discourse
public remote_themes table discourse
public scheduler_stats table discourse
public schema_migration_details table discourse
public schema_migrations table discourse
public screened_emails table discourse
public screened_ip_addresses table discourse
public screened_urls table discourse
public search_logs table discourse
public single_sign_on_records table discourse
public site_settings table discourse
public stylesheet_cache table discourse
public tag_group_memberships table discourse
public tag_groups table discourse
public tag_search_data table discourse
public tag_users table discourse
public tags table discourse
public theme_fields table discourse
public themes table discourse
public top_topics table discourse
public topic_allowed_groups table discourse
public topic_allowed_users table discourse
public topic_custom_fields table discourse
public topic_embeds table discourse
public topic_invites table discourse
public topic_link_clicks table discourse
public topic_links table discourse
public topic_search_data table discourse
public topic_tags table discourse
public topic_timers table discourse
public topic_users table discourse
public topic_views table discourse
public topics table discourse
public translation_overrides table discourse
public twitter_user_infos table discourse
public unsubscribe_keys table discourse
public uploads table discourse
public user_actions table discourse
public user_api_keys table discourse
public user_archived_messages table discourse
public user_auth_token_logs table discourse
public user_auth_tokens table discourse
public user_avatars table discourse
public user_badges table discourse
public user_custom_fields table discourse
public user_emails table discourse
public user_exports table discourse
public user_field_options table discourse
public user_fields table discourse
public user_histories table discourse
public user_open_ids table discourse
public user_options table discourse
public user_profile_views table discourse
public user_profiles table discourse
public user_search_data table discourse
public user_stats table discourse
public user_visits table discourse
public user_warnings table discourse
public users table discourse
public versions table discourse
public watched_words table discourse
public web_hook_event_types table discourse
public web_hook_event_types_hooks table discourse
public web_hook_events table discourse
public web_hooks table discourse

(131 rows)

1 me gusta
4

Is that giant inline table really necessary?

1 me gusta
5

El tamaño de la tabla post-timings de nuestro Discourse es de aproximadamente 17 GB. ¿Es esto normal?

table_name                           | row_estimate | table_size | index_size | total_size
--------------------------------------|--------------------|----------------|-------------|----------
post_timings                         | 120967448    | 6676 MB    | 10 GB      | 17 GB
posts                                | 3616224      | 2413 MB    | 3219 MB    | 5632 MB
user_actions                         | 15159571     | 1215 MB    | 3699 MB    | 4914 MB
post_search_data                     | 3531969      | 3756 MB    | 1031 MB    | 4787 MB
notifications                        | 2241273      | 687 MB     | 824 MB     | 1510 MB
topic_views                          | 7696258      | 369 MB     | 1110 MB    | 1478 MB
post_actions                         | 3475097      | 350 MB     | 618 MB     | 968 MB
topic_users                          | 3343623      | 365 MB     | 257 MB     | 622 MB
user_auth_token_logs                 | 1123545      | 410 MB     | 110 MB     | 520 MB
post_stats                           | 2779471      | 354 MB     | 155 MB     | 509 MB
incoming_links                       | 3154654      | 219 MB     | 251 MB     | 469 MB
topics                               | 64301        | 142 MB     | 286 MB     | 429 MB
post_replies                         | 2327812      | 220 MB     | 120 MB     | 340 MB
user_visits                          | 289994       | 17 MB      | 229 MB     | 246 MB
stylesheet_cache                     | 4605         | 204 MB     | 840 kB     | 205 MB
top_topics                           | 40202        | 19 MB      | 148 MB     | 167 MB
user_profile_views                   | 593591       | 61 MB      | 97 MB      | 158 MB
post_revisions                       | 102832       | 87 MB      | 8072 kB    | 95 MB
6

Sí, esa es la tabla más grande en cualquier sitio de Discourse activo, y uno de sus índices ocupa la mayor parte del espacio.

Este año implementaremos una actualización de PostgreSQL que reducirá su tamaño a la mitad, así que mantente atento :wink:

8 Me gusta