BitClout Node Postgres DB Tweaks

BitCloutPostgresIndexes.sql

-- Additional indexes for BitClout Node Postgres DB
-- Indexes take space. more columns added = more space But it can speed up queries a lot. Use wisely. 
-- Index creation can take some time to complete

-- pg_post indexes. Based on what you want you might not need all
-- Index: pg_posts_timestamp_desc to sort posts when when quering by timestamp. From most recent to old

CREATE INDEX pg_posts_timestamp_desc
    ON public.pg_posts USING btree
    ("timestamp" DESC NULLS LAST)
    TABLESPACE pg_default;
    
-- Index: pg_posts_poster_timestamp index to sort user posts from recent to oldes. Speeds up user post queries

CREATE INDEX pg_posts_poster_timestamp
    ON public.pg_posts USING btree
    (poster_public_key ASC NULLS LAST, "timestamp" DESC NULLS LAST)
    TABLESPACE pg_default;


-- Full Text Index for searching in post body. 
-- This requires a special query format also see https://www.postgresql.org/docs/12/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH
CREATE INDEX pg_posts_fulltext_body
    ON public.pg_posts USING gin
    (to_tsvector('english'::regconfig, body))
    TABLESPACE pg_default;


-- pg_profile indexes

-- Full Text Index for searching in username and description field at the same time. 
-- This requires a special query format also see https://www.postgresql.org/docs/12/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH
CREATE INDEX pg_profiles_fulltext_desc_username
    ON public.pg_profiles USING gin
    (to_tsvector('english'::regconfig, (username || ' '::text) || description))
    TABLESPACE pg_default;
    
-- If you use c# let me know there is an ORM model which can beused which makes querying a lot easier. Also with full text