PostgreSQL – Counting Partitions for Table Size Limit

Things to try:

Adding an index on (user_id, date, score)

Group by only on scores table and then join to users:

SELECT s.total, u.name, u.gender, u.dob, u.country
FROM users AS u
  JOIN 
  ( SELECT user_id, SUM(score) AS total
    FROM scores
    WHERE date >= '2012-01-01' AND date < '2012-02-01'
    GROUP BY user_id
    HAVING SUM(score) >= 1000
    ORDER BY total DESC LIMIT 50
  ) AS s
      ON u.id = s.user_id
ORDER BY total DESC ;

Ok, it was simple :)

I wanted to limit each database to 256MB of space per student. In order to achieve that, I enabled smallfiles in /etc/mongod.conf and ran the mongod process with mongod -f /etc/mongod.conf --quota --quotaFiles 4 which limited the size of the database to 256MB.

smallfiles starts preallocating space with 16MB, then 32MB, 64MB and finally 128MB. Add this up and you get 240MB plus 16MB file for the namespace = 256MB

This however doesn't stop them from creating another database with the same user. Any help on this?