Hello,
I’m working on the backend of a Mobile Application that let’s users create Parties and upload Video Stories related to a specific Party.
I have previously used Scylla to store Parties and Story Metadata. But I did hit some roadblocks, like ttl expiration not being reported in CDC and no Geo search support. I now want to try out Aerospike and see if it is possible to migrate my current data model and all queries to Aerospike.
Party Data Model: It has a unique id and stores the user_id of the party creator. Lat/long can be used for Geo Searching (not possible in Scylla but in Aerospike). And a party also has a default TTL of 24 hours.
Supported Party Queries: A Party should have basic CRUD functionality and be identified by it’s id. Another query I want to support is getting all Parties of a user, in descending order of creation data, which i was able to use the timeuuid (UUID V1) for. To support party_by_user I had to create a materialized view which just copies the Party row into another table with a different primary key and clustering order, results in twice the data to be stored.
CREATE TABLE IF NOT EXISTS parties (
id timeuuid PRIMARY KEY,
user_id text,
is_public boolean,
lat float,
long float,
start_date timestamp,
) WITH default_time_to_live = 86400;
CREATE MATERIALIZED VIEW parties_by_user AS
SELECT * FROM parties
WHERE id IS NOT NULL AND user_id IS NOT NULL
PRIMARY KEY (user_id, id)
WITH CLUSTERING ORDER BY (id DESC);
The Story Data Model is similar: It also has a unique id of type timeuuid which I can use to order by in the materialzed views. The party_id & user_id are also stored so that I can query all Stories of a Party/User. The lat/long of a story is also recorded to allow Geo Searching for Stories nearby. For the Stories I needed two extra Materialized views to allow querying a Story by Party/User and also set the order to descending order of creation data. This result in a 3x of data.
Supported Story Queries: Basic CRUD operation through the id of a Story. Support finding nearby Stories through Geo Search on Lat/Long. Support getting all Stories of a User/Party ordered by creation date in descending order (Latest Story returned first)
CREATE TABLE IF NOT EXISTS stories (
id timeuuid PRIMARY KEY,
party_id text,
user_id text,
lat float,
long float,
url text,
tagged_friends list<text>,
) WITH default_time_to_live = 86400;
CREATE MATERIALIZED VIEW stories_by_party AS
SELECT * FROM stories
WHERE id IS NOT NULL AND party_id IS NOT NULL
PRIMARY KEY (party_id, id)
WITH CLUSTERING ORDER BY (id DESC);
CREATE MATERIALIZED VIEW stories_by_user AS
SELECT * FROM stories
WHERE id IS NOT NULL AND user_id IS NOT NULL
PRIMARY KEY (user_id, id)
WITH CLUSTERING ORDER BY (id DESC);
I know that Geo Searching would not be a problem in Aerospike since I could create a Secondary Index on a GeoJSON point.
Getting all Parties/Stories of a user could be implemented with a Secondary Index on the user_id but I don’t know if it’s possible to order the results in Aerospike while using Secondary Indexes.
It would realy help me out if I could get some tips on how to implement these Queries optimally the Aerospike way and if Aerospike is even suited for my kind of Queries.