1

Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many times. Let's say I want to make analytics of posts being shared on many twitter profiles.

I've got big table (let's assume 1M rows, but potentially it can be 100 or 1000x larger), with posts and their statistics and table with monitored profiles:

CREATE TABLE post
(
id int,
id_profile int,
created_date date,
text varchar(255),
shared_link varchar(255),
comments int,
stars int
);

CREATE TABLE profile
(
id int,
name varchar(255),
link varchar(255)
);

Because each post is shared on many profiles (possibly many times), I want to see aggregated statistics for each post, profile, days or mixed (ex.: show how much stars had each post on all profiles each day). So something like this:

SELECT * FROM (
    SELECT po.created_date, po.shared_link, sum(comments) as comments, sum(stars) as stars
    FROM post po
    INNER JOIN profile pr ON (po.profile_id = pr.id) 
    WHERE po.created_date >= '2015-06-01' AND po.created_date <= '2015-06-30'
    GROUP BY XXXGROUP
) as a
ORDER BY XXXORDER DESC 
LIMIT 20 
OFFSET 0 

While:

XXXGROUP can be: {po.shared_link} or {po.id_profile} or {po.created_date} or {po.shared_link, po.created_date} or {po.id_profile, po.created_date} or {po.shared_link, po.id_profile} (number of possible group by options with n groupable columns grows by 2^n-2)

XXXORDER can be every aggregated column or created date.

This is generally known problem - for working pagination I need to group in subselect and then sort this subselect, which is of course pain in the ass because of missing indexes. But I need results of this query to have in real time, to be shown at frontend in graphs and grids.

I already read some articles about map reduce, so I have some idea how that work theoretically and I know I will have to do it somehow like this, but I'm not sure if and how exactly is that possible in postgreSQL? Almost everything I found was mongoDB related. I'd like to see some best practises with pgSQL.

So my main question is, what is the best solution and best practises to get same result as this query, but fast and ideally in pgSQL?

And btw I still wonder: As I said, number of possible group by options with n groupable columns grows by 2^n-2, is it needed to write nearly 2^n map functions and for every map-reduce function there is need for special table for reduced results, am I wrong? Google has tens or maybe hundereds groupable criteria in analytics, even if it is possible to use only 3 or whatever of them, it is still C(3, n) possibilities... am I totally missing something?

Thank you for any answer!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.