What indexes should history tables have?

[rod-glover]

What indexes should history tables have?

Fundamental questions

The fundamental question here is: How will the history tables be used? That is, how will they be queried and what indexes will make those queries more efficient?

Let's consider a prime use case: Query the history tables in a way that reflects a past state of the database.

• There are some straightforward conditions I've formulated that define what a true past state is, meaning whether a set of history records taken together reflects a genuine historical state of the database, as opposed to a mixed collection of records from several different past states.
• Those conditions amount to constraints on history table primary keys.

Then the question becomes, how do the queries on primary tables map over to equivalent queries on the history tables?

Let's consider some elementary ones:

• What stations and station history records are associated with a given network?
• What variables are associated with a given network?
• What observations are associated with a given station, for a specified time period? Group these by variable, and order by observation time.

Existing table indexes

Let's first consider the indexes on the existing ("primary") tables, ignoring the indexes that are used to implement unique constraints (including primary keys). We have the following:

meta_network

• none

meta_station

• (network_id)

meta_history

• (station_id)
• (freq)

meta_vars

• (network_id)

obs_raw

• (mod_time)
• (obs_raw_id, obs_time, vars_id, history_id)
• (history_id)
• (obs_raw_id)

General rule: Index each FK. This is unsurprising.

[rod-glover]

Metadata tables

The metadata tables are simpler in definition and use, so let's tackle them first.

First let's consider basic functioning of the history tracking function:

• The key shared with the primary table supports the efficiency of the history-maintenance trigger functions, so this keys should be indexed too. (It already is.)

Then the constraints defining a valid historical state; these are constraints on the history table primary keys.

• Primary keys automatically have indexes, so no more needs to be done here.

Now let's consider the elementary queries noted above:

• In the existing tables (see above), foreign keys all have indexes. The foreign keys between history tables serve a parallel function in these queries, and should be indexed.

Finally, let's ask what the index meta_history(freq) is for:

• TBD

[rod-glover]

Observation table (obs_raw)

All the observations above for metadata tables apply to obs_raw.

Questions:

1. Why does index obs_raw(obs_raw_id) exist separately from the automatic primary-key index?
2. Why does index obs_raw(obs_raw_id, obs_time, vars_id, history_id) include obs_raw_id?
   • Abstractly, the triple (obs_time, vars_id, history_id) is the actual primary key to this table: An observation is identified by time, variable, and location (given by a history record).
   • Therefore it makes sense that this triple is indexed.
   • But why is obs_raw_id in there?
3. Why does index obs_raw(mod_time) exist?

[rod-glover]

History-specific columns and indexes

Question 3 above

"Why does index obs_raw(mod_time) exist?")

provokes some related questions across all metadata tables:

1. Are queries about the time of modification (mod_time) common and/or important?
2. Similarly, are queries about the user making the modification (mod_user) common and/or important?

[jameshiebert]

"Why does index obs_raw(mod_time) exist?")

Queries that use mod_time are very common for reporting how many insertions we've made during a particular time frame.

[Nospamas]

Observation table (obs_raw)

All the observations above for metadata tables apply to obs_raw.

Questions:

1. Why does index `obs_raw(obs_raw_id)` exist separately from the automatic primary-key index?

2. Why does index `obs_raw(obs_raw_id, obs_time, vars_id, history_id)` include `obs_raw_id`?
   
   * Abstractly, the triple `(obs_time, vars_id, history_id)` is the actual primary key to this table: An observation is identified by time, variable, and location (given by a history record).
   * Therefore it makes sense that this triple is indexed.
   * But why is `obs_raw_id` in there?

3. Why does index `obs_raw(mod_time)` exist?

1. Weird one, probably indeed do not need a separate index that duplicates the primary key unless it has some special sorting or type.
2. It might have been created as a covering index to optimize some query, however I agree with your assessment that it could do with either reviewing or at least documenting which query(s) are optimized via this index.

[rod-glover]

"Why does index obs_raw(mod_time) exist?")

Queries that use mod_time are very common for reporting how many insertions we've made during a particular time frame.

So the metadata tables similarly could be part of that kind of query (but with much lower volume and frequency of activity). Given that, it does make some sense to index that column in every history table ... and/or in the primary table, depending on which table would be used for for these kinds of queries.

UPDATE: The history table gives a much more accurate reflection of what has been done, of course, and is the only way to distinguish new inserts from updates to existing records. Right now I think we rely on our policy of not (or rarely) updating existing records in obs_raw, so that mod_time == "insert time". (Yes?)

[rod-glover]

Observation table (obs_raw)

...
2. It might have been created as a covering index to optimize some query, however I agree with your assessment that it could do with either reviewing or at least documenting which query(s) are optimized via this index.

I see no evidence that it is a covering index. That would mean its definition contains INCLUDE, and it does not:

CREATE INDEX IF NOT EXISTS obs_raw_comp_idx
    ON crmp.obs_raw USING btree
    (obs_raw_id ASC NULLS LAST, obs_time ASC NULLS LAST, vars_id ASC NULLS LAST, history_id ASC NULLS LAST)
    TABLESPACE pg_default;

UPDATE: Covering indexes did not exist in PG ver. <=10, which was doubtless when the indexes were added to this table. It's possible that some of the benefits of a covering index could be gained by placing the INCLUDES column(s) in the indexed columns.

However

Before PostgreSQL had the INCLUDE feature, people sometimes made covering indexes by writing the payload columns as ordinary index columns, that is writing

CREATE INDEX tab_x_y ON tab(x, y);

even though they had no intention of ever using y as part of a WHERE clause. This works fine as long as the extra columns are trailing columns; making them be leading columns is unwise for the reasons explained in Section 11.3. However, this method doesn't support the case where you want the index to enforce uniqueness on the key column(s).

The possible payload column is obs_raw_id, but it is first, not trailing.