GIN index uses to find posts by tag

-remove hive_post_tags table -extend hive_posts for a column with an arrays of tags id -tags are added next after a post, not in paralles as it was previously -fix bug with not removing tags for a post whose author removed tags during edition

hive/db/sql_scripts/upgrade/upgrade_table_schema.sql

@@ -15,7 +15,7 @@ BEGIN
   EXECUTE 'ALTER DATABASE '||current_database()||' SET join_collapse_limit TO 16';
   EXECUTE 'ALTER DATABASE '||current_database()||' SET from_collapse_limit TO 16';
 END
-$$; 
+$$;
 
 SHOW join_collapse_limit;
 SHOW from_collapse_limit;
@@ -31,13 +31,13 @@ IF NOT EXISTS(SELECT data_type
     alter table ONlY hive_accounts
       add column is_implicit boolean,
       alter column is_implicit set default True;
-  
+
     --- reputations have to be recalculated from scratch.
     update hive_accounts set reputation = 0, is_implicit = True;
-  
+
     alter table ONlY hive_accounts
       alter column is_implicit set not null;
-  
+
     perform deps_restore_dependencies('public', 'hive_accounts');
 
     INSERT INTO hive_db_data_migration VALUES ('Reputation calculation');
@@ -187,6 +187,15 @@ IF EXISTS(SELECT data_type FROM information_schema.columns
 ELSE
   RAISE NOTICE 'SKIPPING hive_posts upgrade - adding a block_num_created column, type change for abs_rshares/vote_rshares columns';
 END IF;
+
+IF NOT EXISTS(SELECT data_type FROM information_schema.columns
+          WHERE table_name = 'hive_posts' AND column_name = 'tags_ids') THEN
+    ALTER TABLE ONLY hive_posts
+            ADD COLUMN tags_ids INTEGER[];
+ELSE
+    RAISE NOTICE 'SKIPPING hive_posts upgrade - adding a tags_ids column';
+END IF;
+
 END
 $BODY$
 ;
@@ -200,7 +209,7 @@ DROP INDEX IF EXISTS hive_mentions_post_id_idx;
 
 -- updated up to 7b8def051be224a5ebc360465f7a1522090c7125
 -- updated up to 033619277eccea70118a5b8dc0c73b913da0025f
-INSERT INTO hive_db_data_migration 
+INSERT INTO hive_db_data_migration
 select 'update_posts_rshares( 0, head_block_number) execution'
 where not exists (select null from hive_db_patch_level where patched_to_revision = '033619277eccea70118a5b8dc0c73b913da0025f')
 ;
@@ -228,13 +237,13 @@ IF NOT EXISTS(SELECT data_type
     alter table ONLY hive_follows
       add column follow_muted boolean,
       alter column follow_muted set default False;
-  
+
     --- Fill the default value for all existing records.
     update hive_follows set follow_muted = False;
-  
+
     alter table ONlY hive_follows
       alter column follow_muted set not null;
-  
+
     perform deps_restore_dependencies('public', 'hive_follows');
 ELSE
   RAISE NOTICE 'hive_follows::follow_muted migration skipped';
@@ -245,7 +254,7 @@ $BODY$;
 
 --- 4cdf5d19f6cfcb73d3fa504cac9467c4df31c02e - https://gitlab.syncad.com/hive/hivemind/-/merge_requests/295
 --- 9e126e9d762755f2b9a0fd68f076c9af6bb73b76 - https://gitlab.syncad.com/hive/hivemind/-/merge_requests/314 mentions fix
-INSERT INTO hive_db_data_migration 
+INSERT INTO hive_db_data_migration
 select 'update_hive_post_mentions refill execution'
 where not exists (select null from hive_db_patch_level where patched_to_revision = '9e126e9d762755f2b9a0fd68f076c9af6bb73b76' )
 ;
@@ -270,7 +279,7 @@ CREATE INDEX IF NOT EXISTS hive_votes_voter_id_last_update_idx ON hive_votes (vo
 
 --- https://gitlab.syncad.com/hive/hivemind/-/merge_requests/306 update posts children count fix
 --- 0e3c8700659d98b45f1f7146dc46a195f905fc2d
-INSERT INTO hive_db_data_migration 
+INSERT INTO hive_db_data_migration
 select 'update_hive_posts_children_count execution'
 where not exists (select null from hive_db_patch_level where patched_to_revision = '0e3c8700659d98b45f1f7146dc46a195f905fc2d' )
 ;
@@ -357,3 +366,5 @@ CREATE INDEX IF NOT EXISTS hive_posts_promoted_id_idx ON hive_posts (promoted, i
   WHERE NOT is_paidout AND counter_deleted = 0
  ;
 
+-- requires intarray extension: CREATE EXTENSION IF NOT EXISTS intarray;
+CREATE INDEX IF NOT EXISTS hive_posts_tags_ids_idx ON hive_posts USING gin(tags_id gin__int_ops);