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
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 (
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!
ag_idI don't think partitioning will give you any benefit. As the linked answer says, partitioning is not a performance optimization tool.