Use Case for Table Partitioning?

Question

I am looking for advice on large tables and partitioning

The Data

• I have two resources: participants and measurements
• There are 500,000 participants
• Each user will have 8,760 measurements
• That means there will be 4.38 billion measurements
• Once the data is loaded, it will not change
  • Values are not going to be updated (except for e.g. fixing an error)
  • No new participants or measurements will be added

The Queries

• The typical query will fetch all the measurements for a single participant
  • e.g. SELECT * from measurements WHERE part_id = 384352;
• There will not be aggregation of values across participants
  • e.g. SELECT pulse FROM measurements WHERE hour = 23 AND day = 12; won't happen

The Questions

This seems like a candidate for table partitioning. But I'm not familiar enough with partitioning.

I'm hoping someone can tell me:

1. Will a partitioned table likely be faster than a single table in this use case?
2. Given the data will be "static" once loaded, is there a better approach?
3. Which partition method would be most appropriate? Range?
   • It seems like it would be best to get all measurements for a single participant on the same partition.
4. What's a good rule of thumb for partition sizing?

The Tables

The participants table with some example data:

CREATE TABLE participants (
    id      serial PRIMARY KEY,
    uuid    uuid UNIQUE NOT NULL
);

-- for example...

id     | uuid
-------+--------------
1      | 51243542...
2      | abcbdbab...
...
500000 | efe65e76...

Without partitioning, the measurements table would have data like this:

CREATE TABLE measurements (
    id      bigserial PRIMARY KEY,
    part_id integer NOT NULL,
    hour    integer NOT NULL,
    day     integer NOT NULL,
    month   integer NOT NULL,
    pulse   real NOT NULL
);
ALTER TABLE measurements
    ADD CONSTRAINT fk_measurements_to_participant_id
        FOREIGN KEY(part_id)
        REFERENCES participants(id)
        ON DELETE CASCADE;


-- for example...

id         | part_id   | hour  | day   | month | pulse
-----------+-----------+-------+-------+-------+-------
1          | 1         | 0     | 1     | 1     | 58.2
2          | 1         | 1     | 1     | 1     | 52.6
3          | 1         | 2     | 1     | 1     | 56.2
4          | 1         | 3     | 1     | 1     | 57.4
...
8760       | 1         | 23    | 31    | 12    | 67.9
8761       | 2         | 0     | 1     | 1     | 81.0
8762       | 2         | 1     | 1     | 1     | 83.3
...
4380000000 | 500000    | 23    | 31    | 12    | 77.7

Cheers.

Answer 1

There is no benefit in partitioning here, since you don't plan to delete data. You'd have to partition by ranges of participant ids, since 500000 partitions are too many.

With an index on the participant id of measurements, the query will be more efficient on a non-partitioned table, because planning time will be shorter and the speed of an index scan is independent of the table size.