Skip to content

Replace existing hive_follows table with optimized tables

  • The current hive_follows table is bigger than it should be.
  • Replace it with the tables below which are smaller and better optimized for the queries we use. In particular, this should speed up muting, since there are fewer mutes than follows.
  • This will require both the sync and API queries to be changed.
  • Functions that return created_at can look up the time using hafd.blocks.
CREATE TABLE follows (
  follower INTEGER,
  following INTEGER,
  block_num INTEGER,
  PRIMARY KEY (follower, following)
);

CREATE INDEX follows_follower_idx ON follows(follower);
CREATE INDEX follows_following_idx ON follows(following);

CREATE TABLE muted (
  follower INTEGER,
  following INTEGER,
  block_num INTEGER,
  PRIMARY KEY (follower, following)
);

CREATE INDEX muted_follower_idx ON muted(follower);
CREATE INDEX muted_following_idx ON muted(following);

CREATE TABLE blacklisted (
  follower INTEGER,
  following INTEGER,
  block_num INTEGER,
  PRIMARY KEY (follower, following)
);

CREATE INDEX blacklisted_follower_idx ON blacklisted(follower);
CREATE INDEX blacklisted_following_idx ON blacklisted(following);

CREATE TABLE follow_muted (
  follower INTEGER,
  following INTEGER,
  block_num INTEGER,
  PRIMARY KEY (follower, following)
);

CREATE INDEX follow_muted_follower_idx ON follow_muted(follower);
CREATE INDEX follow_muted_following_idx ON follow_muted(following);

CREATE TABLE follow_blacklisted (
  follower INTEGER,
  following INTEGER,
  block_num INTEGER,
  PRIMARY KEY (follower, following)
);

CREATE INDEX follow_blacklisted_follower_idx ON follow_blacklisted(follower);
CREATE INDEX follow_blacklisted_following_idx ON follow_blacklisted(following);
Edited by Dan Notestein