I am planning on using UUID field for storing items which can have a parent item. I am using PostgreSQL 9.5. I imagine there will be 3-4 levels at most. The design is to hold a structure such as Corporations->sub-companies->stores/shops So perhaps +/- 2 levels.

Now if I have the item and parent on same table wouldnt it be a problem in future if I want to divide data into multiple databases? What would be the best way to proceed?

                 uuid                 |             parent_uuid              
 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b |                                      
 c569ae30-87af-6bcf-fdd1-8143e3bae27a | 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b 
 d7460788-ff07-e82d-83ed-5a0182ae2fbf | 89b4052a-fd9a-43d3-b5f7-c69ee6ea9f5b 
  • how many nodes are you talking about? – Evan Carroll Dec 3 '17 at 18:34

There is no elegant way to proceed. If the hierarchy has an unknown amount of levels, you can't serialize it into fixed relations. But partitioning it is probably a bad idea. Perhaps you want a Graph Database?


You definitely want to take a look at the ltree extension which lets you store hierarchical structures as materialized paths and supports queries against portions of the path. It would be the canonical solution for the problem you described.

I wouldn't worry about partitioning until you know you need it; at which time you will have a much better idea of the constraints that will govern which partitioning strategies you're going to use. I will note that partitioning on a uuid is a bad idea since the whole point of uuids is that they are randomly distributed in a very large key space and have no natural ordering.

  • Yes, I decided to worry about partitioning etc. later. Ltree looks interesting, but I am using doctrine and I wonder how it integrates with that. I have a lot of reading to do :) – yurtesen Dec 4 '17 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.