使用 Postgres 的全文搜尋構建可擴充套件的事件驅動搜尋架構

-- Create the Documents index table
CREATE TABLE IF NOT EXISTS index."documents_index" 
                    (
                     "id" SERIAL,
                     "created_on" bigint NOT NULL,
                     "updated_on" bigint NOT NULL,
                     "customer_id" character varying(150) NOT NULL,
                     "document_id" character varying(255) NOT NULL,
                     "document_type" character varying(50) NOT NULL,
                     "document_title" text,
                     "document_description" text,
                     "words" text,
                     "ts" tsvector GENERATED ALWAYS AS (to_tsvector('english',document_id || ' ' || document_title || ' ' || document_description)) STORED,
                     "metadata" jsonb,
                     CONSTRAINT "documents_index.primary_key" PRIMARY KEY ("customer_id", "document_id", "document_type"))

-- Create GIN index on the ts (tsvector) column to improve search.
CREATE INDEX IF NOT EXISTS documents_index_ts_idx ON index."documents_index" USING GIN (ts);

-- Create Trigram Index on words column. (Requires installing pg_trgm Postgres extension)
CREATE INDEX IF NOT EXISTS documents_index_trgm_idx on index."documents_index" USING GIN ("words" gin_trgm_ops);

import {BeforeInsert, BeforeUpdate, Column, Entity, Generated, PrimaryColumn} from 'typeorm';

@Entity({name: 'index', schema: 'documents_index'})
export class Document {

    @PrimaryColumn({name: 'id', nullable: false})
    @Generated('increment')
    id: number;

    @Column({name: 'created_on', nullable: false})
    createdOn: number;

    @Column({name: 'updated_on', nullable: false})
    updatedOn: number;
    
    @Column({name: 'customer_id', nullable: false})
    zoneId: string;

    @Column({name: 'document_id', nullable: false})
    documentId: string;

    @Column({name: 'document_type', nullable: false})
    documentType: string;

    @Column({name: 'document_title', nullable: false})
    documentTitle: string;

    @Column({name: 'document_description', nullable: false})
    documentDescription: string;

    @Column({name: 'words', type: 'text'})
    words: string;

    @BeforeInsert()
    @BeforeUpdate()
    async calculateWords() {
        const fullText = this.documentId + ' ' + this.documentTitle + ' ' + this.documentDescription;
        const unique = Array.from(new Set(fullText.split(' ')));
        this.words = unique.join(' ');
    }

    @Column({name: 'metadata', type: 'jsonb'})
    metadata: any;

    @Column({name: 'ts', type: 'tsvector'})
    tsVector: any;

}

SELECT id, document_id, document_title, document_description, 
COALESCE(similarity(words, 'management system'),0) + COALESCE(ts_rank_cd(ts, 'management & system'),0) as relevancy 
FROM "index"."documents_index" WHERE 
customer_id = '1' AND (
    ts @@ to_tsquery('english', 'management & system') 
--    OR words ILIKE '%management system%'
    ) 
ORDER BY relevancy DESC, id ASC

-- Create the Documents index table
CREATE TABLE IF NOT EXISTS index."documents_index" 
                    (
                     "id" SERIAL,
                     "created_on" bigint NOT NULL,
                     "updated_on" bigint NOT NULL,
                     "customer_id" character varying(150) NOT NULL,
                     "document_id" character varying(255) NOT NULL,
                     "document_type" character varying(50) NOT NULL,
                     "document_title" text,
                     "document_description" text,
                     "words" text,
                     "ts" tsvector GENERATED ALWAYS AS (to_tsvector('english',document_id || ' ' || document_title || ' ' || document_description)) STORED,
                     "metadata" jsonb,
                     CONSTRAINT "documents_index.primary_key" PRIMARY KEY ("customer_id", "document_id", "document_type"))  PARTITION by HASH("customer_id")

-- Create GIN index on the ts (tsvector) column to improve search.
CREATE INDEX IF NOT EXISTS documents_index_ts_idx ON index."documents_index" USING GIN (ts);

-- Create Trigram Index on words column. (Requires installing pg_trgm Postgres extension)
CREATE INDEX IF NOT EXISTS documents_index_trgm_idx on index."documents_index" USING GIN ("words" gin_trgm_ops);

CREATE TABLE IF NOT EXISTS index."documents_index_part_1" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 0);
CREATE TABLE IF NOT EXISTS index."documents_index_part_2" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 1);
CREATE TABLE IF NOT EXISTS index."documents_index_part_3" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 2);
CREATE TABLE IF NOT EXISTS index."documents_index_part_4" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 3);
CREATE TABLE IF NOT EXISTS index."documents_index_part_5" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 4);
CREATE TABLE IF NOT EXISTS index."documents_index_part_6" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 5);
CREATE TABLE IF NOT EXISTS index."documents_index_part_7" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 6);
CREATE TABLE IF NOT EXISTS index."documents_index_part_8" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 7);
CREATE TABLE IF NOT EXISTS index."documents_index_part_9" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 8);
CREATE TABLE IF NOT EXISTS index."documents_index_part_10" partition of index."documents_index_part" for values with (MODULUS 10, REMAINDER 9);