I need to improve the performance of a service that fetches data from Bigtable and think the row key design is mostly the problem, given the access patterns and data involved. I also need the performance of a different service that pushes the data in to remain consistent (or improve).
Here's an example (bear with me). I receive millions of records sent in a batch each day. Say it's global pet owner information updated every day and each record has this format:
- FirstName
- LastName
- Address
- HomeType
- Pets
Example:
- Peter
- Sherman
- 42 Wallaby Way, Sydney
- Apartment
- [cat, dog, fish, bird, kangaroo]
Currently, the types of queries directed to the service are (in order of frequency/importance):
- Give me the pet owner info for all people that own birds, but not fish or cats on Day 32. This is the typical format - ie, tell me people that own this particular pet, but not these X number of other pets. We often want to exclude many many pets. People in this world can own lots of pets too - sometimes many tens of pets.
- Tell me which of these 1000 people I'm providing you are or are not pet owners on Day 3, 4, 5.
- Give me the pet owner info for all "new" pet owners starting from Day 7 to Day 20. If on Day 7 there were 2 and on Day 20 there was 1 of the original 2 plus 1 new one, just give me info for the 1.
The formats of the row keys / columns right now are this (the columns include all other info):
- DayX-Pet-FirstName-LastName (answers 1)
- DayX-FirstName-LastName (answers 2/3)
Some things to note: The day is always important. Queries always revolve around the day. Batches only contain data for a single day, not many (and pretend many batches can arrive at once). Also, no need to worry about edge cases - nobody has domesticated an animal called a "Steve" and nobody names their kid "Raven" in this world.
Today, for the first query, we include a column value filter (it's a regex filter) when testing whether the row we're looking at contains an entry for a Pet we want to exclude - the set is just a string. I think this is where we get screwed perf-wise since the list of pet exclusions is sometimes quite long. I've tried forgetting the value filters, and just doing the filtering myself in memory (which has been dicey...sporadic memory problems), and that improves the turnaround time of a request but I'd hope I can do better.
Constraints:
- Can't change the datastore at this time (open to suggestions of what would be better)
- The service pushing data to Bigtable should ideally push roughly the same amount of data (or less) in roughy the same amount of time (or less). It's fine to push more just not by orders of magnitude over longer periods of time.
My imagination is failing me.