Table Partition

Description / Background

Partitioning is a database design principle that helps improve performance and manageability of large tables by dividing them into smaller, more manageable pieces. Each partition can be managed and queried separately, which can lead to performance improvements for large datasets.

Create a table partition for inventory, package agenda start times, inventory reservation and inventory tablecheck.

Glossary

Private (https://app.clickup.com/9003122396/docs/8ca1fpw-35796/8ca1fpw-41516)

Objectives

• Optimize the performance of the inventory worker, reducing processing time.
• The inventory system working normal
• Speed up data lookup
• Reduce server load
• The generate_partitions (inventory, package agenda start times, inventory reservation and inventory tablecheck) method in the migration defines partitions based on ranges of restaurant_id .

example:

partition p1 includes all rows where restaurant_id is less than 100, p2 includes rows where restaurant_id is less than 200, and so on.

Scope

data flow

Location

-

Sequence Diagram / Flow

-

ERD

In the database, this partition table doesn't relate to any other table, they just use the Active Record Association, so the model can be connected. This model uses composite primary keys, which means that the primary key is a combination of two columns: :id and :restaurant_id.

Backend Implementation

•   *   Add gem `composite_primary_keys`
  

  • Delete old inventory and package agenda start time partition table
  • Add new inventory and package agenda start time partition table
  • Migrate the old data partition to the new partition table
  • Change the inventory model to use the inventory_partitions table. Use the ID and restaurant ID as the composite ID for the inventory partition table.
  • Change the HhPackage::AgendaStartTime model to use the hh_package_agenda_start_times_partitions table. Use the ID and restaurant ID as the composite ID for the hh_package_agenda_start_times_partitions table.
  • Add restaurant_id, when the worker send agenda_start_times params
  • Add restaurant_id when you find inventory by id. example: Inventory.find([params[:id], params[:restaurant_id]])

[

github.com

https://github.com/hungryhub-team/hh-server/pull/5658/files

](https://github.com/hungryhub-team/hh-server/pull/5658/files)

Frontend Implementation

-

PRD & Task

Private (https://app.clickup.com/t/86cvv9hzt) no PRD

Design

-

API Blueprint

New Query

-

DB Schema / Database Migration

Add new table inventories_partitions Add new table hh_package_agenda_start_times_partitions Add new table inventory_tablechecks_partitions Add new table inventory_reservations_partitions

Improvement: