0

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!

3
  • 3
    Does this answer your question? When should we use table partitioning in Postgresql?
    – mustaccio
    Dec 14, 2021 at 20:46
  • Well, it does answer some of the questions I had about partioning in general, but not my question directly. Say that I have already decided to partition my table, would partition by range work as expected/desired when dealing with the select query as described? I did find this post on SO: stackoverflow.com/q/38199910/2867088 but I'm not certain that it applies to my case.
    – Arcuturus
    Dec 14, 2021 at 22:06
  • Unless you expect hundreds or thousands of records with different versions for each ag_id I don't think partitioning will give you any benefit. As the linked answer says, partitioning is not a performance optimization tool.
    – mustaccio
    Dec 14, 2021 at 22:24

1 Answer 1

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.

1
  • This is usefull information. Maybe I'm approaching this the wrong way.. I thought that if I had a query that is targeted towards a small subset of the data that I should think about partitioning around that. The data is usually archived and placed into another table entirely after a user specifed amount of time (e.g. 3, 5, 10 years). Maybe I should think about partitioning around that? And could you be so kind to elaborate on the "..sligthly worse than not partitioning". Thanks!
    – Arcuturus
    Dec 15, 2021 at 8:03

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.