PostgreSQL – the best way to make a large number of updates to the index set

CREATE TABLE flag_names (
 id smallint PRIMARY KEY,
 name text NOT NULL
 );

CREATE TABLE flag (
 flagname_id smallint NOT NULL REFERENCES flag_names(id),
 data_id text NOT NULL REFERENCES data(id),
 value boolean NOT NULL ,
 PRIMARY KEY (flagname_id, data_id)
);

This question The environment is PostgreSQL 9.6.5 on AWS RDS.

The question is about the best model design and batch update strategy for a 300 million-row table containing the following logical data model:

> id: primary key, a string of up to 40 characters
>code: integer 1-999
>year: integer year
> flags: each variable number associated with the name (1000) , New flags added over time. Ideally, flags should be considered to have three values: absent (null), on (true / 1) and off (false / 0). It is possible to update with additional (see below) For the cost, treat the flag as a simple bit (on or off, not present). The “on” value is usually very sparse: <1/1000.
Queries usually involve the presence of one or more flags (by name) Boolean expression of, which also contains the code and year.

Data passed Apache Spark batch updates, that is, updates can be expressed as flat files, for example, in COPY format, or as SQL operations. Only one update is active at any time. Updates to the code and year are very rare. Updates to the flag affect each time Update 1-5% of the rows (3-15 million rows). The updated row can include all flags and their values, including only the “on” flags to be updated or only flags whose values ​​have changed. In the former case Next, Spark needs to query data to get the current value of the flag.

There will be a small read load during the update.

The question is about the best mode to support the query and related updates. Strategy. Update as described above.

Some research comments so far:

>Using 1,000 Boolean columns will create a very effective row representation, but apart from some DDL complexity In addition, 1,000 indexes are required.
>If there is a way to index a single bit, then bit strings would be great. Also, they don’t provide a good way to indicate an absence flag. Using this method requires the flag name and bit ID The lookup table is maintained between. If needed, merge updates can be used with ||, but, given PostgreSQL’s MVCC, only updating the flag instead of replacing the entire row does not seem to be of much benefit.
> JSONB fields provide indexes. They also provide Empty representation, but this comes at a price: all “off” flags need to be explicitly set, which makes the field very large. If we ignore the empty representation, the JSONB field will be relatively small. To further reduce them, we can use short The 1-3 character field name and lookup table. The same comment: merge with bit string.
> tsvector / tsquery: No experience with this data type, but theoretically, it seems to be a set of “on” on the name “The precise representation of the logo. A lookup table must be used to map the logo name to the token, with additional requirements to ensure that there will be no conflicts due to stemming.

Do not store the flags in the main table.

Assuming that the main table is called data, please define the following:

CREATE TABLE flag_names (< br /> id smallint PRIMARY KEY,
 name text NOT NULL
);

CREATE TABLE flag (
 flagname_id smallint NOT NULL REFERENCES flag_names(id),
 data_id text NOT NULL REFERENCES data(id) ,
 value boolean NOT NULL,
 PRIMARY KEY (flagname_id, data_id)
);

If a new flag is created, please insert a new row in flag_names.

p>

If the flag is set to TRUE or FALSE, insert or update a row in the flag table.

Add a data flag to test whether a certain flag is set.