1

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.

4
  • What kind of hardware are you deploying this into? PostgreSQL's documentation says "a rule of thumb is that the size of the table should exceed the physical memory of the database server." - your measurements table's rows would be 28 bytes long, so multiplied by 4.38 billion is 122,640,000,000 bytes - or 114GB in total. Now, in 2021, a DB server with 256GB+ of RAM is not that extraordinary, so it might not be worth partitioning at all in your case.
    – Dai
    Aug 23, 2021 at 22:23
  • @Dai That's a good point. We're using AWS RDS, but looking at an 8xlarge instance at $3.60/hr is waaaaay out of our price range. But maybe it follows to then size the partitions to approx the instance RAM? Or is that the cart leading the horse?
    – hommel
    Aug 23, 2021 at 22:51
  • 1
    Big-name cloud DaaS (AWS RDS, Azure SQL, etc) rarely makes sense for cost-effective databases sized in the hundreds-of-gigabytes - for those I find it's much cheaper (and with waaaay higher perf!) to build a simple $250 box from spare-parts (and a $50 1TB NVMe SSD) and host it on-prem with a VPN connection to AWS/Azure (assuming you have reasonable network latency to your app-server).
    – Dai
    Aug 23, 2021 at 23:14
  • 1
    Running your own DB server locally with its own bare-metal disk IO for single-application workloads gives you crazy-high IO perf simply because there's no contention for disk IO - it's amazing how terrible the IOPS are for sub-$200/mo DB tiers at Azure and AWS compared to a $200 self-made box, even with spinning-rust disks. There's definitely a lot of over-provisioning going-on in the Cloud DBaaS space...
    – Dai
    Aug 23, 2021 at 23:16

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.