Database

The database being used is Postgres.

Originally, it was hosted on Digital Ocean as a managed service, but now is hosted on Google Cloud SQL. Considering switching back to Digital Ocean and hosting the database on a droplet.

The storage reserved is 137gb, which as of 30th November 2021, we are only using 66% of.

It is the option with the lowest CPU and RAM available (1vCPU & 3.75gb RAM).

Relations by size: (updated 30 November 2021)

relation_name	size
company_accounts	32 GB
accounts_pkey	21 GB
filing_events	3440 MB
person_officers	2890 MB
accounts_value_idx	2500 MB
accounts_company_number_end_date_idx	1788 MB
filing_events_legacy	1718 MB
company_events	1680 MB
accounts_label_idx	1592 MB
accounts_company_number_index	1516 MB
officer_appointments	1476 MB
companies	1306 MB
detailed_postcodes	981 MB
filing_events_new_pkey	815 MB
filing_events_description_code_idx1	653 MB
person_officers_nationality_idx	640 MB
companies_number_idx	548 MB
companies_pkey	547 MB
officer_appointments_person_number_idx	528 MB
officer_appointments_company_number_idx	498 MB
person_officers_pkey	441 MB
wide_accounts_combined	426 MB
indexed_name	424 MB
filing_events_timepoint_idx	397 MB
companies_upper_idx1	385 MB
companies_upper_idx	381 MB
filing_events_company_number_index	363 MB
trgm_idx_companies_upper_name	339 MB
trgm_idx_companies_name	339 MB
company_events_company_number_idx	337 MB
accounts_scanned	301 MB
company_events_pkey	294 MB
company_events_captured_idx	294 MB
sic	241 MB
list_accounts	226 MB
wide_accounts_part_two	215 MB
wide_accounts	211 MB
officer_name_index	193 MB
company_events_published_idx	191 MB
filing_events_company_number_idx	175 MB
sic_pkey	170 MB
filing_events_captured_idx	167 MB
filing_events_pkey	167 MB
sic_company_number_idx	153 MB
company_events_date_trunc_idx5	132 MB
detailed_postcodes_county_idx1	130 MB
companies_postcode_idx	130 MB
accounts_scanned_pkey	122 MB
person_officers_occupation_idx	117 MB
filing_events_expr_idx	116 MB
short_list_accounts	115 MB
company_events_date_trunc_idx3	101 MB
company_events_date_trunc_idx	100 MB
person_officers_birth_date_idx	97 MB
company_events_date_trunc_idx2	96 MB
filing_events_published_idx	95 MB
companies_date_idx	93 MB
filing_events_filing_date_idx1	91 MB
company_events_date_trunc_idx4	90 MB
company_events_date_trunc_idx1	87 MB
short_list_accounts_company_number_end_date_idx	82 MB
wide_accounts_combined_company_number_idx	80 MB
very_long_accounts	70 MB
detailed_postcodes_pk	67 MB
wide_accounts_combined_employees_idx	58 MB
filing_events_date_trunc_idx5	57 MB
filing_events_date_trunc_idx3	56 MB
filing_events_date_trunc_idx4	55 MB
filing_events_description_code_idx	55 MB
filing_events_date_trunc_idx	55 MB
filing_events_filing_date_idx	52 MB
filing_events_date_trunc_idx1	52 MB
filing_events_date_trunc_idx2	50 MB
sic_sic_code_idx	38 MB
wide_accounts_combined_accountants_idx	19 MB
wide_accounts_combined_accouting_software_idx	19 MB
wide_accounts_combined_balance_sheet_date_idx	18 MB
detailed_postcodes_county_idx	18 MB
very_long_accounts_pkey	6504 kB
accountants	1528 kB
trgm_idx_accountants_upper_name	1272 kB
trgm_idx_accountants_lower_name	1272 kB
legacy_accountants	960 kB
trgm_idx_accountants_lower_software	936 kB
saved_filters	592 kB
cached_filters	560 kB
accountants_pkey	368 kB
cached_filter_results	232 kB
unique_cached_result	216 kB
filing_history_descriptions	168 kB
accountants_software_idx	144 kB
accountants_lower_idx	144 kB
filing_history_descriptions_pkey	104 kB
sic_map	64 kB
cached_filters_id_pk	40 kB
sic_map_pkey	32 kB
user_exports_pk	16 kB
access_token	16 kB
sessions_pkey	16 kB
user_role_quotas_pk	16 kB
verification_requests_pkey	16 kB
unique_role_operation	16 kB
user_filters_pk	16 kB
postcode_summary_area_uindex	16 kB
email	16 kB
saved_filters_pk	16 kB
postcode_summary_pk	16 kB
roles_pk	16 kB
user_operations_pk	16 kB
token	16 kB
user_filters_unique_per_user	16 kB
postcode_summary	16 kB
session_token	16 kB
users_uid_uindex	16 kB
users_pkey	16 kB
cached_filter_results_id_seq	8192 bytes
companies_count	8192 bytes
users	8192 bytes
accounts_pkey1	8192 bytes
accounts_id_seq	8192 bytes
verification_requests_id_seq	8192 bytes
sessions_id_seq	8192 bytes
user_exports	8192 bytes
compound_id	8192 bytes
provider_account_id	8192 bytes
psc_enumerations_pkey	8192 bytes
psc_pkey	8192 bytes
user_operations	8192 bytes
user_exports_id_seq	8192 bytes
users_id_seq	8192 bytes
user_role_quotas	8192 bytes
user_roles	8192 bytes
sessions	8192 bytes
verification_requests	8192 bytes
provider_id	8192 bytes
user_id	8192 bytes
user_filters_id_seq	8192 bytes
user_filters	8192 bytes
user_role_quotas_id_seq	8192 bytes
charge_events_pk	8192 bytes
insolvency_prediction_input	0 bytes
accounts	0 bytes
user_export_usage	0 bytes
psc	0 bytes
accountant_view	0 bytes
psc_enumerations	0 bytes
readable_role_quotas	0 bytes
charge_events	0 bytes
officer_ages	0 bytes
company_view	0 bytes

Query to update this table:

SELECT
  relname as Relation_name,
  pg_size_pretty(table_size) AS size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name ='public'
ORDER BY table_size DESC;