database space quotas with hibernate and postgresql, limit database megabytes used per user

Question

Good day/night to all of you.

In our university project we have a database created by hibernate (using postgresql) having 17 tables(obejct). We need to post a limit to 4 object i.e. student_comments, student_attachments, student_drafts, student_fotos

1. limit number of comments posted by the student to 1000 per student and their combine size in the database should not exceed 40 MB.

2. limit student_attachments to maximum of 120 MB per student i.e. students can post any number of attachments but their combine size should not exceed 120 MB of space.

3. same restriction as student_attachments object, but their combine size should be limited to 80 MB

4. the same but limit should be 50 MB per student.

in case one of this restriction is reached we would like to inform the students account that he reached the limit and can not upload attachments or pictures anymore.

The student_comments are stored as

@Lob
public String comment;

and the attachments, fotos and drafts are stored as

private byte[] data;

Unfortunately my professor said that he might want to change the limits in the future so we need to code those limits in a way that they could be later easily changed i.e. increased.

This question is addressing the best practise’s used, i.e what would be the best solution to our problem using hibernate and postgresql? I suspect that this could be only done on a file system level and not at postgresql, but in case the goal can be achieved with postgres, please let me know what key word should I search for.

Regards,

K

Answer 1

There's no way this can be answered by looking at the file system, SQL queries must be written.

The size in bytes of a bytea field can be obtained with the octet_length function.

If hibernate uses large objects (references through the oid type) for the binary contents, you need to use a self-defined lo_size(oid) function as suggested in Obtaining the size of lobject in PostgreSQL.

Having the right functions, you can then aggregate the sizes to compute the per-student usage for the various content types.

If in addition you need to enforce the quotas on insertion, they should be computed in functions that are TRIGGERed BEFORE the event to be able to cancel the operation when an over-quota limit would be reached.