I got this interview question during system design round of AWS interviews loop.

Question - Design a service to allocate pool of resources optimally. Resources could be of types - compute resources, storage resources.
Scale - 500K customers should be able to use it concurrently
Expectations - APIs design, Database design, Allocate resource efficiently
Assumptions - resources are already available, SAAS application

I came up with below APIs

• POST createPool - it will return pool_id
• GET pool - it will return pool and resource allocated to pool
• GET resources - it will return available resources
• POST allocateResourceToPool(resource_id, pool_id, resource_type) - it will allocate resource to pool
• POST deAllocateResourceToPool(resource_id) - it will deallocate resource to pool

I was not able to answer below questions

1. How to avoid full table scan whenever searching for available resources while creating pool? - I suggested few approaches like

• Create pool at the time of creating resources and make them available to end user but interviewer was not convinced

2. How to handle load of 500K APIs to create pool in 5 mins of interval?
3. How to allocate and deallocate resource from pool in optimal way?

Suggestion welcome

[UPDATE] Adding the data model and flow of queries proposed by me

Not sure indexing on boolean column (like is_available) will help to reduce row scan

pool_table - indexed over timestamp
primary_key pool_id timestamp

pool_resource_link_table
primary_key pool_id resource_id

resource_table (indexed over is_available and resource_type, not sure how beneficial is to index on boolean column like is_available)
primary_key resource_id is_available resource_type

createPool-will insert a row in pool_table

getPool-it will return pool_id and resource_ids will come with inner join between pool_table and pool_resource_link_table

allocateResourceToPool-
Get the last pool_id based on last entry (pool_table) indexed by timestamp
Get available resources from resource_table
Make entries in pool_resource_link_table

deAllocateResourceToPool-
Delete linking of pool_id and resource_id in link table
Make resource_id available in resource_table