3

I am investigating how I might structure a PostgreSQL table to store a large amount of time stamped data that also needs to be portioned by another field. My expected data structure will be something like:

CREATE TABLE event (
    event_time       timestamp not null,
    object_sha       char(64) not null,       ; sha256 as hex digits
    username         text not null,           ; actual name not a foreign key
    payload          jsonb not null           ; many other fields, not indexed
);

Events will be written into the table at a fast rate, possibly as high as 1000 per second, stored for around 6 months, and exported to cheaper storage. (perhaps pgdump files to an S3 bucket). So it would make sense to use declarative portioning on the event time, using pg_partman to create and manage new partition tables each week or so.

However, there is a strong requirement to run queries on the data by the object_sha, and my concern is that if the data is portioned only by timestamp, then the most recent partition table will be under heavy I/O load and might not keep up, so it appears logical to me that the data should also be partitioned by the prefix on object_sha, (perhaps on the first one or two hex digits), as that way reads and writes will be evenly distributed across many tables.

My questions are:

  1. The pg_partman documentation says Sub-partitioning with multiple levels is supported, but it is of very limited use in PostgreSQL and provides next to NO PERFORMANCE BENEFIT – Why is that? Is that advice up to date and correct, as I can’t see how it would be a bad idea.
  2. All the queries that I plan to run will always have order by event_time desc limit 50 or suchlike included and most of the time will be satisfied by recent events in one or two recent tables. Is the PostgreSQL query engine smart enough to limit the query to the most recent table, and only look in older tables if it does not find enough results?
  3. Given that my front end application will know more about the data and query that postgres. Would it make sense to write to, or query the partition tables directly instead of sending insert and query statements to the top level table?

Please note that I am aware that PostgreSQL might not be the best way to solve this data storage problem, and that NoSQL alternatives exist. I have been tasked with investigating using a relational database, my team mates are looking into other technologies as well.

3
  • 1
    The "or suchlike" is intimidating: you have to know pretty much exactly the queries that should run efficiently. Commented Jan 13, 2023 at 14:30
  • Hi, and welcome to dba.se! You might want to look at the capabilities of Timescaledb and/or possibly Citusdata which have (IMHO) much promise in the areas you describe. Caveat: I have never used them professionally, but I remain firmly convinced that PostgreSQL's impressive "ordinary" SQL's strengths (which go back decades) with their "extras" are potentially far better solutions than recent NoSQL stores and/or recent TimeSeries databases!
    – Vérace
    Commented Jan 14, 2023 at 22:47
  • Thanks @Vérace for those suggestions, I will look into them. Unfortunately my employer is wedded to AWS to the extent that it will be virtually impossible to get any commercial service that is not part of AWS approved through purchasing, so I will have to stick with the free/community editions of those products. Commented Jan 16, 2023 at 9:25

1 Answer 1

2

my concern is that if the data is portioned only by timestamp, then the most recent partition table will be under heavy I/O load and might not keep up

I think your fundamental premise is flawed. It is the job of your storage layer (RAID controller, LVM, etc.) to balance the IO load over the available underlying storage hardware by using striping; and with modern systems they are good at it. Trying to do it in PostgreSQL with partitioning (and different tablespaces for the different partitions) is the road to madness. I think this answers your first question: assuming your storage layer is vaguely competent, there is no additional performance benefit to be had.

All the queries that I plan to run will always have order by event_time desc limit 50 or suchlike included and most of the time will be satisfied by recent events in one or two recent tables.

As long as there is an index leading with "event_time" and that is also the range partition key, yes it will start with the newest partitions. If there is no such index, then it will not start with the newest ones and stop once it reaches the LIMIT (even though I would argue that this is when it is most important to start with the newest ones). Of course "suchlike" might hide a multitude of sins...

For your 3rd question, I don't think it matters much with declarative partitioning, though it would have been important under the older inheritance-based partitioning. But if you can arrange for different partitions to take their turn in the "hot seat" that could make a big difference in cache effectiveness, and having the clients aware of the partitioning scheme would probably be necessary (but not sufficient) to make that happen.

7
  • "Trying to do it in PostgreSQL with partitioning (and different tablespaces for the different partitions) is the road to madness." We regularly and successfully did that in the 1990s and early 2000s (though not using Postgresql): partition by something besides the time key, and then put each partition on a different RAID array.
    – RonJohn
    Commented Jan 13, 2023 at 19:07
  • 1
    @RonJohn Sure, but that was a long time ago before modern storage was nearly as good. And what is the evidence you are not mad :). I think the early 2000s is when SAME (Stripe And Mirror Everything) was becoming the norm. I know some Oracle consultants clung to the micromanaging the storage themselves long after that, but this may have been motivated more by paycheck protection than fundamentals.
    – jjanes
    Commented Jan 13, 2023 at 22:53
  • It honestly wasn’t that hard to do, even with multi-billion row tables (at least with Rdb on OpenVMS).
    – RonJohn
    Commented Jan 14, 2023 at 11:07
  • @RonJohn - I've been on this group for about 10 years and I'm fairly sure that it's the first time I've seen Rdb being mentioned - designed by Jim Starkey - do you know if it's anything like Interbase/Firebird?
    – Vérace
    Commented Jan 15, 2023 at 14:04
  • @Vérace Rdb/ELN was only related to Rdb by name. I've never used Rdb/ELN (it had a pretty short life in a niche market), but if it uses MVCC then it's nothing like Rdb. That definitely uses locks, and a separate file for storing rollback info. Rdb was only released on OpenVMS (VAX, Alpha, Itanium) and Digital Unix (not popular there). Since VMS started it's decline 30+ years ago, it's no surprise this is the first you've seen it...
    – RonJohn
    Commented Jan 15, 2023 at 21:11

Your Answer

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

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