Database design for timeseries in SQL

Question

I have timestamped geolocation data and some other information about users and I am looking for advice for designing the database. I guess a naive design would be:

CREATE TABLE user(
    user_id INT NOT NULL,
    name VARCHAR(128) NOT NULL,
    gender VARCHAR(128) NOT NULL,
    age INT NOT NULL,
    time TIMESTAMPTZ NOT NULL,
    xloc FLOAT(4) NOT NULL,
    yloc FLOAT(4) NOT NULL,
    PRIMARY KEY(user_id),
);

Here xloc, yloc are floats indicating location. The obvious problem with this table is that fields gender, age, and name will be redundantly repeated many times for each timestamp. After reading the very comprehensive accepted answer in Storing time-series data, relational or non? I decided that a better solution would be to have geolocation data in separate table, i.e. have two tables:

CREATE TABLE geodata(
    user_id INT NOT NULL,
    time TIMESTAMPTZ NOT NULL,
    xloc FLOAT(4) NOT NULL,
    yloc FLOAT(4) NOT NULL,
    PRIMARY KEY (user_id, time),
);

CREATE TABLE user(
    user_id INT NOT NULL,
    name VARCHAR(128) NOT NULL,
    gender VARCHAR(128) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (user_id),
);

Note that in the geodata table I am using both user_id and time as PK to try to conform to the Sixth Normal Form (6NF) as suggested by the answer in the above link -- this presumably enables higher performance. Strictly speaking though, 6NF requires only one other attribute for each PK , but in my case I have two (xloc and yloc). Recent PostgreSQL versions allow one to use array types, so another option would be:

CREATE TABLE geodata(
    user_id INT NOT NULL,
    time TIMESTAMPTZ NOT NULL,
    loc FLOAT(4) ARRAY[2] NOT NULL,
    PRIMARY KEY (user_id, time),
);

In this case, the client should know that the array represents x and y locations, in that order, but this is not an issue for now. The table now technically has just one attribute per PK, but I am more interested in its performance. I'm new to Postgres and DBs in general. Would using array types be better in terms of performance?

Data and use case: The location timeseries for each user can be tens of millions of measurements long, and at varying intervals. read ops will outnumber write ops -- in fact right now my data is static and the resulting database will be used by a small team for statistical analyses, at least for now. My queries will be e.g. measurements for male users, or Sunday measurements for users under 30s.

What alternative designs would you recommend?

Answer 1

Time series & temporal data do not per se use 6NF. (Pace that link.) What is needed is a CK & associated data that you want to record an atomic change to. 6NF is just frequently wanted but it is not per se the goal. The non-CK data could be multiple columns--you want to record changes to a location, not to a coordinate. (Similarly when you want to know whether an integer changed no one's disturbed that you don't have a table for every CK & digit.) You can think of that as a transformation of a 6NF table with CK & one tuple- or record-valued column.

So here a design with a CK & X & Y is fine--as long as you don't need to know when a particular coordinate value changed.

"I'm new to Postgres and DBs in general." Then forget about "performance" until you learn enough to know what it means. Make straightforward designs. Next learn about constraints & indexes.

Re temporal data (including 6NF) everyone should read Date, Darwen & Lorentzos. Avoid Snodgrass.

PS PKs are irrelevant to relational model theory, CKs matter & a PK is just some CK you called the PK. PS Beware that an SQL PK is more or less a superkey not CK; it could contain a smaller UNIQUE/superkey.

PS 6NF means satisfies no non-trivial JDs. It implies "Primary Key, and at most one other attribute" but the latter is not a definition of 6NF. Also note, that condition doesn't itself mean exactly one CK; there could be more.

PS Wikipedia is not a sound source for relational model information. Eg There's no one "1NF" & they are orthogonal to normalization to NFs leading to 6NF. Eg PKs don't matter. Eg normalization to higher NFs is not done by moving through lower NFs. (Moreover it can preclude good destination-NF designs.) Eg DKNF does not belong on that page among the NFs leading to 6NF. Eg Its definition of 6NF is wrong.