Having already read several questions on SO, external blog posts and manual
- SO: Foreign Key constraint to partitioned table in Pg
- dba.SE: Different ways of handling FK to partitioned table in Pg
- Manual: Inheritance
- Manual: Partitioning
- Manual: Constraint triggers
- Blog: Postgres modelling with inheritance
I still find myself wondering if I should go with partitioning considering my case or not.
The case - simplified
Storing customer data. All names of tables mentioned below are made up for clarity.
Having objects that are identifiable by customer and are non-physical beings, also their physical objects in which they are actually stored in case of needing to send some objects back to the customer on demand, or process it in some other ways. They are mapped in a many-to-many relationship.
Second many-to-many relationship is between those non-physical objects and their metrics. There are objects which are bound with some metrics.
Both non-physical and physical objects have their hierarchy tables which are child-parent relations.
Depending on each customers' needs and requirements the data about physical objects can be supplied or might need to be created from scratch. Basically, what I need to do is:
Maintain internal system for fast
SELECTstatements, because here is where the mapping is going to take place.
Maintain the system for external customer to view and operate on their non-physical objects - fast retrieval of data. Strong need of efficiency for
SELECTstatements - this data is available for many customers to search whenever they want.
There can be a customer, who may access the data, view and operate on it, but that doesn't need to be a contractor that we got the data from / are processing the data for.
This has lead me to introduce table partitioning into my system, considering that I always know which partition data should fall into (partitioning for contractors), and then to mainain system for external customer where I need partitioning for customers (this would be done with some delay using automation tools and set of rules to rewrite the data in customers manner, so that for each customer we'd only scan one partition for each table.
My data is going to grow constantly, especially when importing new customers' objects and metrics. The pace of new data arriving into the system is unpredictable at the moment for the long run. There is really no way to measure it not knowing who is going to be the next customer. Right now there are just 2 customers with more or less 1M rows for every customer in every table. But in future I predict new customers to come with a volume of 10M rows or so as well.
These questions are all related to each other.
- Should partitioning really be considered here, or is that an overkill? I consider it to be of use since I'm always scanning exactly one partition.
- If partitioning is the way to go, how do I enforce
FKconstraint the most effectively considering my needs? Should I go for
constraint triggers, or just keep it in the application layer for internal system, or maybe some other method?
- If partitioning isn't the way to go, what should I dive into?
If there is not enough data supplied, please let me know in the comments below.