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:
- 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.
- All the queries that I plan to run will always have
order by event_time desc limit 50or 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?
- 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.