Migrate from Cloud SQL to DataStore

问题:

I'm creating App where I need to make Post Feeds. Right now I'm using App Engine Standard and Cloud SQL instance db-n1-standard-1 which is based on MySQL But I notice it is very expensive. App is under production mode and In first month price is much higher than my expectation. Most costly thing in Cloud SQL is instance hours rate So, I decide to migrate it on datastore.

I have three tables, 1- users 2- posts 3- comments And Every Table has round about 10 Millions Rows. Posts and Comments table are growing very fast 100 of millions rows are expected.

Users Table

user_id         name        email           phone
--------------------------------------------------
u123            Abc         [e-mail]       123456
u124            Cde         [e-mail]       789065
u786            Qwe         [e-mail]       754599
 .               .              .               .
 .               .              .               .
 .               .              .               .

Posts Table

post_id         user_id         type        src                 date
------------------------------------------------------------------------
p098            u123            img         path/to/file        13-3-17
p456            u123            vid         path/to/file        14-3-17
p239            u124            img         path/to/file        15-3-17
 .               .               .              .                   .
 .               .               .              .                   .
 .               .               .              .                   .

Comments Table

cmnt_id         post_id         user_id         comment
--------------------------------------------------------
m392            p098            u123            Some Text
m234            p098            u786            Some Text
m324            p456            u123            Some Text
 .               .               .                .
 .               .               .                .
 .               .               .                .

As you can see User can post one or more posts and single post has one or more comments

I need to get posts data along with post comments I know I need to use JOIN for this. And I can easily use it in Cloud SQL because it's based on MySQL.

These three tables have relation between them and As I know Cloud SQL is relational database but DataStore is not.

I have idea in my mind to convert these tables in DataStore

1- Create Entity as Users and save all users records there 2- Create Entity as Posts and save all posts records there 3 - Create Entity as Comments and save all comments records there

Post id is always I know for which post I need to get data

For example I need post data about id p098 I'm using Java I decide to create two threads First thread get post data from Posts Entity and second thread get comment data from Comments Entity. Then I merge these two data set by using java to generate same result as JOIN Like MySQL Because I think DataStore is not allow JOINS

Here I need some Suggestions.

1. Is it a good way to do that ?
2. Which Provide the best Performance in this Situation ?
3. If Thousands of User(Large Number of Users) is accessing Posts and Comments data as same time which one can handle it better ?
4. What about Pricing Effect if I migrate from Cloud SQL to DataStore in this situation ?
5. If your App need continuous database then SQL instance run continuous which make it high cost, isn't it ? For example in my App every user when open app need to show Posts if there is large number of users then I think SQL instance never shutdown. Which means it run 730 hr in a month.
6. Any recommendation ?

回答1:

I would put Comments as Child entity of Posts. Query by Ancestors is faster than other queries for your Join.

I might also have Posts as Child entity of User

So you end up with a structure like:

| User, 123 | --> | Post, 456 | --> | Comment, 789 | (Grand Dad) (Dad) (Child)

EDIT: Tim Hoffman is right, Comment should be child of "User" and not "User > Post".