LB-167: Create schema for storing statistics in PostgreSQL #192
Conversation
Create new tables with JSONB columns to store statistics. One table each for user, artist, release and recordings. All these tables are inside a new schema `statistics` Also minor changes in `db/testing.py` to bring the SQL there out of the code and into the `admin/sql` directory.
paramsingh
changed the title
|
SERIAL is always best for such things. |
|
Okay, noted. I'll change it to use SERIAL then. |
| connection.execute('DROP TABLE IF EXISTS listen CASCADE') | ||
| connection.execute('DROP TABLE IF EXISTS listen_json CASCADE') | ||
| connection.execute('DROP TABLE IF EXISTS api_compat.token CASCADE') | ||
| connection.execute('DROP TABLE IF EXISTS api_compat.session CASCADE') |
There was a problem hiding this comment.
I learned recently that you can use DROP SCHEMA CASCADE to drop all tables in a schema. As we have mostly schemas here, perhaps we could just drop the "user" table and statistics and api_compat schemas.
Are we still using the listen and listen_json tables??
There was a problem hiding this comment.
listen and listen_json aren't being used anymore but the PostgresListenStore tests depend upon them.
I'll update the PR with DROP SCHEMA CASCADE for the schemas and drop the remaining tables normally.
|
|
||
| CREATE TABLE statistics.user ( | ||
| user_id INTEGER NOT NULL, -- PK and FK to "user".id | ||
| artists JSONB, |
There was a problem hiding this comment.
Can you give an example of what these json fields will look like? How often will you read from them or write to them? Will you want to get a specific value from in the field (and therefore require an index)?
There was a problem hiding this comment.
The plan is to use these fields to store the statistics we calculated from BigQuery. For example, in the artists field of statistics.user, we store the top artists listened to by that user over different time intervals. The json would be something like this, I guess:
{
"all_time":
[
{
"name": "example artist 1",
"msid": uuid,
"listen_count": 87
},
{
"name": "example artist 2",
"msid": uuid,
"listen_count": 80
}
],
"last_month":
[
{
"name": "example artist 2",
"msid": uuid,
"listen_count": 12
},
{
"name": "example artist 1",
"msid": uuid,
"listen_count": 3
}
]
}
These stats would then be used to draw the graphs on the site. I don't think we will need to query specific values inside them ever, and we'll only write to them during our regular stat calculation timings, I guess.
There was a problem hiding this comment.
OK, this seems fine. I wasn't sure if it would be a better idea to split this data into separate fields.
It sounds like you're only ever going to write this field in bulk - that is, compute it all at once and then write it to the field, replacing whatever was there. Same for reading - you're always going to want to get all of the data at once in order to display it. If this is the case, then I'm fine with this way of storing the data.
admin/sql/create_tables.sql
Outdated
| recordings JSONB, | ||
| last_updated TIMESTAMP WITH TIME ZONE | ||
| ); | ||
| ALTER TABLE statistics.user ADD CONSTRAINT user_stats_user_id_uniq UNIQUE (user_id); |
There was a problem hiding this comment.
We don't need a UNIQUE constraint if we also have a PRIMARY KEY (a primary key is basically a unique constraint + index)
There was a problem hiding this comment.
Ah, sorry about that, will fix.
|
@alastair Used |
First drop schemas with cascade and then drop the rest of the tables
| CREATE TABLE statistics.artist ( | ||
| id SERIAL, -- PK | ||
| msid UUID NOT NULL, | ||
| name VARCHAR, |
There was a problem hiding this comment.
We have VARCHAR and TEXT fields in our schema definition. Under the hood they appear to be the same, but we should pick one and go with it. It seems that we have lots of VARCHAR and only one TEXT in api_compat. So, no action to take on this. \ø/
Create new tables with JSONB columns to store statistics.
One table each for user, artist, release and recordings. All
these tables are inside a new schema
statisticsAlso minor changes in
db/testing.pyto bring the SQL thereout of the code and into the
admin/sqldirectory.