I am a bit of a newbie learning Postgres for a complex use case and trying to see how to structure my data. The documentation are pretty clear how to achieve all that and how to create indexes, but I couldn't find an answer to any performance difference given that indexes and sharding is done appropriately.
My actual use case is very complicated and scientific in nature so this example is very similar without having to explain what I am truly trying to do. The nature of the data itself is about the same as this.
here is an example of what I am trying to achieve.
- I have millions of trucks and
- each truck have hundreds of boxes and
- each box has up to a 100 items each having a name, serial no, expiration date and price.
I wanna be able to query items by either name, exp_date, serial number, or price and be able to add and remove individual items as needed. Also I have stored procedures that would calculate statistics and report it in different tables, do joins etc. I also need ACID support and immediate consistency so most No Sql options are not appropriate.
The way I see it I have four approaches to this
1) have a table having the following columns: truck, box, item, serial_no, exp_date and price.
2) have three tables, one with what truck carrying what boxes, one with one boxes carrying what item, and one for the individual item properties.
3) Table having three columns: store, shelve, items as JSONB
[{"name":"foo","price":4.99,"serial_no":12345,"exp_date":10-10-2018},
{"name":"foo2","price":599,"serial_no":178944 "exp_date":10-10-2019}, etc...]
4) Create composite type item with properties: name, price, serial_no, exp_date and then create a table with store, shelve and column of the type item.
From what I understand, option 1 is easiest to write queries for, but when you do the math I end up with over a 100 billion rows in one table, which can create even slower indexes, which I understand can make it very slow to manipulate such massive tables even with indexes.
Option 2: I will have the same number of rows in the item table as option 1, but with two less columns who are very short text so they really don't save that much storage, but I don't know if it affects speed.
options 3 and 4 are similar once indexes are configured I think. will result in a lot of fewer, but overall larger rows.
one of the main queries I would like to do is a query to see which truck is carrying expired items, a stored procedure would be used to fill another table telling the driver of each of the truck of what items in his truck are expired and shouldn't be delivered.
In order to run a query like that, at the end of the day no matter how the data is written on the disk, Postgres will have to join the tables in option 2, or un_nest the data arrays in the other options, in order to find which truck carrying which items. So wouldn't be easier to have everything in one table with indexes on the columns of truck, box and items.