Partitioning strategy for table with two commonly used columns

Question

So, I'm still kind of new to postgresql so sorry in advance if this might be a silly question.

I have this table (let's call it some_table) that I'm trying to partition in a logical way based on how the queries are used to fetch data. Say the table looks like this:

create table some_table(
 id      int,
 ag_id   int,
 version int,
 constraint PK_ST primary key (id, ag_id)
 constraint UN_ST_AGID_VER unique (ag_id, version)
);

(Due to the way partitioning works I have to add ag_id to the primary key if I want to partition based on ag_id, if I understood it correctly)

The way users usually fetch data is by:

select * from some_table where ag_id=2346781 and version=3;

So based on this lookup, would partitioning by range on ag_id work as I want in this case? It seems kind of meaningless to try to partition on a combination or composite of both ag_id and version when I want all versions of a particular ag_id to end up in the same "bin". Or will it not work due to the lookup-query being based on the two columns (ag_id AND version).

Additional note: some ag_id can have a lot of versions. They are usually around 50-100, but some are over 2000.

The way I see it (which might be wrong) is that lookup on ag_id would be fast as tables not matching the range will be pruned, however I'm too sure when it comes to attempting to select something with multiple where clauses. And do I have to think about in which order the WHERE clauses is used when partitioning?

We are talking about a table that gets close to ~500GB/yr. The data is usually archived after a user specified ammount of time (e.g. 3, 5, 10 years), so I might have to look into that.

Due to Postgresql not being very lenient on chosing or changing a partition strategy after a table has been created, I would appreciate some input on this matter if someone has any.

Thanks in advance!

Answer 1

If you partition on ag_id then it can use partition pruning when you search on ag_id=2346781 and version=3. But what does that get you? An index on (ag_id, version) is also very good at "pruning" away irrelevant data, is probably needed anyway, and has less overhead.

Based on your description, all we can really say is that partitioning would probably be only slightly worse than not partitioning. There might be other factors that make it a good idea, but you haven't described what they might be.

You say you are adding data at 500GB/year. When you do you expect to start removing data, and how will that be done? That is what often determines the best partitioning strategy.