BinaryVeil's questions

Eu tenho uma tabela geográfica que contém

• países
• localidades (cidade, vila, vila, ilha, arquipélago)
• locais (local/empresa + bairros/distrito/área), por exemplo - Big Ben ou Southwark Borough.

Para detalhes adicionais de cada tipo de local, tenho tabelas relacionadas.
Tabela 'country_details' para locais do tipo 'country' e da mesma forma para locais.

Para um local como 'Big Ben', tem referência ao id da sua localidade (ou seja, Londres), e também referência ao país (que pode ser simplesmente pelo iso_code do país)

Exemplo:

 id |     title      |  locality_id  |  country_iso_code |
---------------------------------------------------------|
 1  | United Kingdom |     null      |     UK            |
 2  | London         |     null      |     UK            |
 3  | Big Ben        |      2        |     UK            |
 4  | XYZ District   |      2        |     UK            |

Cenário

Agora, como para enviar ao cliente informações sobre o Big Ben eu também gostaria de obter o nome da localidade (Londres) e do país (Reino Unido), parece que minhas únicas 2 opções são:

1. CTE recursivo
2. JOIN na mesma mesa.

No entanto, uma vez que temos uma tabela de dezenas de milhares de registros, que pode potencialmente crescer para muito mais (alguns milhões), além da complexidade da consulta, isso também afetará o desempenho, suponho.

Pergunta

Qual é a melhor opção para "juntar" detalhes como "Londres" e "Reino Unido"?
Ambas as opções são ruins e é melhor repensar o design do esquema?

Tabelas:

CREATE TABLE places (
    id              int,
    type            smallint, -- ['country', 'locality', 'location']
    sub_type        smallint, -- nullable (city, village, etc.)

    -- names
    title           text,

    -- locality
    locality_name   text,
    locality_id     

    -- country
    country_iso_alpha2 text, -- 'GB'
    country_name       text, -- 'United Kingdom'
    admin_region       text, -- 'England', 'Texas', .. (null for Country)
    
    ...
);

CREATE TABLE country_details(
    place_id      int,
    place_type    smallint NOT NULL CHECK (item_type=1),

    iso_alpha2    text,
    iso_alpha3    text,
    ...

    PRIMARY KEY (place_id, place_type),
    FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);

CREATE TABLE location_details(
    place_id      int,
    place_type    smallint NOT NULL CHECK (item_type=3),

    website            text,
    neighborhood       text,
    formatted_address  text,
    ...

    PRIMARY KEY (place_id, place_type),
    FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);

Eu tenho uma tabela User Relations, que basicamente armazena todas as relações unidirecionais dos usuários. Por exemplo, o usuário A segue o usuário B; O usuário X bloqueia o usuário Y

Códigos de usuário:

o id dos usuários são bigints . Não é como se eu achasse que usar ints realmente acabaria em um gargalo (a menos que meu aplicativo cresça para mais de 2 bilhões de usuários :)), mas até onde eu entendo, se eu integrar inscrições de redes sociais, o Facebook usa bigints para seus ids de usuário, portanto, tenho que manter o id dos usuários como bigint.

Indexação :

A princípio pensei em fazer um índice composto de ambos os usuários (o relacionado e o relacionado), mas como ambos os índices são bigints, ou seja, 8 bytes cada, não seria um desperdício armazenar um índice tão monstruoso? Acabei deixando apenas um índice de coluna, mas não tenho certeza se é a decisão mais sábia neste caso.

Eu também pensei em uma variante com índice de cobertura - mas neste caso o tipo_relação(follow, block) não fará parte da indexação/filtragem, então talvez eu deva incluir isso dentro do índice composto, mas então eu teria 8+8+ 4 bytes de índice. As compensações entre espaço e tempo não são claras para mim neste caso. Agradeceria qualquer opinião sobre o assunto.

Primeira variante (índice de uma coluna):

CREATE TABLE user_relations (
    relating_user_id bigint NOT NULL,
    related_user_id bigint NOT NULL,
    relation_type smallint NOT NULL,
    created_at default current_timestamp,

    PRIMARY KEY (relating_user_id),
    FOREIGN KEY (relation_type) references user_relations_types (id)

    -- will having only one index affect the performance really ? 
    -- should I include the type into the composite type ?
);

Segunda variante (usando índice de cobertura e omitindo PK):

    CREATE TABLE user_relations (
        relating_user_id bigint NOT NULL,
        related_user_id bigint NOT NULL,
        relation_type smallint NOT NULL,
        created_at default current_timestamp,

        FOREIGN KEY (relation_type) references user_relations_types (id),
        CREATE UNIQUE INDEX relating_user_related_user_relation_type_idx ON user_relations 
                    (relating_user_id, related_user_id) INCLUDE (relation_type);

        -- should I include the type into the composite type ?
    );