Optimal approach for large bulk updates to indexed bit sets

user_622495 0
Viewed 69

The environment for this question is PostgreSQL 9.6.5 on AWS RDS.

The question is about an optimal schema design and batch update strategy for a table with 300 million rows containing the following logical data model:

  • id: primary key, string up to 40 characters long
  • code: integer 1-999
  • year: integer year
  • flags: variable number (1000+) each associated with a name, new flags added over time. Ideally, a flag should be thought of as having three values: absent (null), on (true/1) and off (false/0). It is possible, at the cost of additional updates (see below), to treat a flag as a simple bit (on or off, no absent). "On" values are typically very sparse: < 1/1000.

Queries typically involve boolean expressions on the presence or absence of one or more flags (by name) with code and year occasionally involved also.

The data is updated in batch via Apache Spark, i.e., updates can be represented as flat file(s), e.g., in COPY format, or as SQL operations. Only one update is active at any one time. Updates to code and year are very infrequent. Updates to flags affect 1-5% of rows per update (3-15 million rows). It is possible for the update rows to include all flags and their values, just the "on" flags to be updated or just the flags whose values have changed. In the former case, Spark would need to query the data to get the current values of flags.

There will be a small read load during updates.

The question is about an optimal schema and associated update strategy to support the query & updates as described.

Some comments from research so far:

  • Using 1,000+ boolean columns would create a very efficient row representation but, in addition to some DDL complexity, would require 1,000+ indexes.

  • Bit strings would be great if there was a way to index individual bits. Also, they do not offer a good way to represent absent flags. Using this approach would require maintaining a lookup table between flag names and bit IDs. Merging updates, if needed, works with ||, though, given PostgreSQL's MVCC there doesn't seem to be much benefit to updating just flags as opposed to replacing an entire row.

  • JSONB fields offer indexing. They also offer null representation but that comes at a cost: all flags that are "off" would need to be explicitly set, which would make the fields quite large. If we ignore null representation, JSONB fields would be relatively small. To further shrink them, we could use short 1-3 character field names with a lookup table. Same comments re: merging as with bit strings.

  • tsvector/tsquery: have no experience with this data type but, in theory, seems to be an exact representation of a set of "on" flags by name. Must use a lookup table mapping flag names to tokens with the additional requirement to ensure there are no collisions due to stemming.

jsonb apache-spark database-performance postgresql
1 Answers

Don't store the flags in the main table.

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

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)
);

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

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

Join flag with data to test if a certain flag is set.

user_64643080
Related