nosql - DynamoDB Single Table Design: Optimizing User-Lane Relationships and Time-Based Access Pattern - Stack Overflow

admin管理员组

文章数量:1344645

I am modelling a system in DynamoDB using single table design. I have lane items (connection from region to region). It has responsible person assigned. Also it might have substitute assign (optional). My access patterns:

Get all lanes valid at given date (currently using GSI1PK = LANE AND GSI1SK <= VALIDFROM#<given-date>) Get all lanes valid at given date where user is responsible person or substitute

{
  "PK": "LANE#eb203ae8-e8f1-5925-8679-706087a86e35",
  "SK": "METADATA",
  "GSI1PK": "LANE",
  "GSI1SK": "VALIDFROM#2025-04-01",
  "ID": "eb203ae8-e8f1-5925-8679-706087a86e35",
  "Type": "COUNTRY_TO_COUNTRY",
  "CreatedAt": "2025-04-03T10:32:22.763129824Z",
  "UpdatedAt": "2025-04-03T10:49:59.607851718Z",
  "ValidFrom": "2025-04-01T00:00:00Z",
  "ValidTo": null,
  "RegionFrom": {
    "ID": "ab010b43-8008-4399-ad01-c968b6c0992e",
    "CountryCode": "ISO2_CODE",
    "CountryName": "Country From",
    "Name": "Region From"
  },
  "RegionTo": {
    "ID": "ab010b43-8008-4399-ad01-c968b6c0992e",
    "CountryCode": "ISO2_CODE",
    "CountryName": "Country To",
    "Name": "Region To"
  },
  "ResponsiblePerson": {
    "Email": "<email>",
    "ID": "bbdff35f-d40c-5b23-b48c-9f98ff9e4f51",
    "Name": "responsible"
  },
  "Substitute": {
    "Email": "<email>",
    "ID": "ccdff35f-d40c-5b23-b48c-9f98ff9e4f51",
    "Name": "substitute"
  }
}

Currently first access pattern is working, only problem is that it might create a hot partition. Im struggling with the second pattern. Currently i add items for responsible and substitute

[
  {
    "PK": "LANE#eb203ae8-e8f1-5925-8679-706087a86e35",
    "SK": "USER#bbdff35f-d40c-5b23-b48c-9f98ff9e4f51#RESPONSIBLE",
    "GSI1PK": "USER#bbdff35f-d40c-5b23-b48c-9f98ff9e4f51",
    "GSI1SK": "VALIDFROM#2025-04-01",
    "Type": "RESPONSIBLE",
    "LaneID": "eb203ae8-e8f1-5925-8679-706087a86e35",
    "UserID": "bbdff35f-d40c-5b23-b48c-9f98ff9e4f51",
    "CreatedAt": "2025-04-03T10:49:59.607868899Z",
    "UpdatedAt": "2025-04-03T10:49:59.607868965Z",
    "ValidFrom": "2025-04-01T00:00:00Z",
    "ValidTo": null
  },
  {
    "PK": "LANE#eb203ae8-e8f1-5925-8679-706087a86e35",
    "SK": "USER#ccdff35f-d40c-5b23-b48c-9f98ff9e4f51#SUBSTITUTE",
    "GSI1PK": "USER#ccdff35f-d40c-5b23-b48c-9f98ff9e4f51",
    "GSI1SK": "VALIDFROM#2025-04-01",
    "Type": "SUBSTITUTE",
    "LaneID": "eb203ae8-e8f1-5925-8679-706087a86e35",
    "UserID": "ccdff35f-d40c-5b23-b48c-9f98ff9e4f51",
    "CreatedAt": "2025-04-03T10:49:57.970181061Z",
    "UpdatedAt": "2025-04-03T10:49:57.970181109Z",
    "ValidFrom": "2025-04-01T00:00:00Z",
    "ValidTo": null
  }
]

And first query is GSI1PK = USER#<user-id> AND GSI1SK <= VALIDFROM#<given-date>. This gets me the lane ids. Second step is batch get all lanes by ids

This doesnt feel right but I cant wrap my head around how this should be handled properly. I am starting to question my overall design for all this lane stuff.

本文标签：