SOC & user quotas

"Sergey E. Koposov" <math@sai.msu.ru> writes:

1) The main idea is to implement the per-user quota (not per tablespace
for example). So, during the creation of the new user some quota can be
specified, and after that the size of all the relations *owned* by that
user should be limited by that number.

This seems impractical as stated; there is no way to determine what a
user owns in some other database. Possibly you could do it if the quota
were both per-user and per-database.

2) I looked into the code, and from my understanding, the main part of the
code which should be affected by the quotas is storage/smgr/md.c.

md.c is too low level to do catalog accesses and thus too low level to
know who owns what.

3) How the quota should be controlled: I think, that generally, for all
the users which have quotas, the shared memory should contain the number
of blocks left from the quota. And each backend extending or truncating
the relations owned by the user should appropriately change that number of
blocks left in the shared memory.

What will you do with ALTER TABLE OWNER? What if such a command is
rolled back? (Likewise for some other commands such as TRUNCATE, or
even just DROP TABLE.) What if there are too many users to fit in your
(necessarily fixed size) shared memory area? What sort of contention
will there be for access to this area?

regards, tom lane

Sergey E. Koposov wrote:

Hello hackers,

I was starting to think about next SOC and the project for it. And for a
long time I wanted to implement the user quotas in PG.
So, I'll try to explain my understanding of the implementation, and I'll
be happy to hear any comments, objections, or pointings to my
misunderstanding. This is very first very rough idea, but I still would
like to hear whether it contains some obvious flaws...

1) The main idea is to implement the per-user quota (not per tablespace
for example). So, during the creation of the new user some quota can be
specified, and after that the size of all the relations *owned* by that
user should be limited by that number.

I could see this being useful per database, maybe. It seems like kind of
an odd feature.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Wed, 28 Feb 2007, Tom Lane wrote:

"Sergey E. Koposov" <math@sai.msu.ru> writes:

1) The main idea is to implement the per-user quota (not per tablespace
for example). So, during the creation of the new user some quota can be
specified, and after that the size of all the relations *owned* by that
user should be limited by that number.

This seems impractical as stated; there is no way to determine what a
user owns in some other database. Possibly you could do it if the quota
were both per-user and per-database.

yes, agreed. I didn't think of that.

3) How the quota should be controlled: I think, that generally, for all
the users which have quotas, the shared memory should contain the number
of blocks left from the quota. And each backend extending or truncating
the relations owned by the user should appropriately change that number of
blocks left in the shared memory.

What will you do with ALTER TABLE OWNER? What if such a command is
rolled back?

I don't know, but I guess the ALTER OWNER should be considered
differently. It probably should proceed only if it sees that there are
enough place to perform the whole operation. If there are, then it should
block any writing to the tables of the user, perform the alter owner and
unblock everything again.

(Likewise for some other commands such as TRUNCATE, or
even just DROP TABLE.)

I didn't think of yet, but I will.

What if there are too many users to fit in your
(necessarily fixed size) shared memory area?

We really don't need to create the array for all users. We only need to
create that array for users 1) having quotas 2) the users,
whose tables are accessed at the moment
So I don't think that in that case the amount of required space is a
problem here.

What sort of contention
will there be for access to this area?

I think, that the only requirement is that the incrementation or
decrementation of number of blocks left for each user
should be atomic operation.

regards,
Sergey

*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

On Wed, 28 Feb 2007, Joshua D. Drake wrote:

I could see this being useful per database, maybe. It seems like kind of
an odd feature.

Per user AND per database (as Tom noted). But I dont see what's odd in
it... It exists in Oracle, and I need quotas in the project on which I'm
working. And I remember user requests for quotas in the mailing lists ...

regards,
Sergey

*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

Sergey E. Koposov wrote:

On Wed, 28 Feb 2007, Joshua D. Drake wrote:

I could see this being useful per database, maybe. It seems like kind of
an odd feature.

Per user AND per database (as Tom noted). But I dont see what's odd in
it... It exists in Oracle, and I need quotas in the project on which I'm
working. And I remember user requests for quotas in the mailing lists ...

Well Oracle isn't really our goal is it? I am not questioning that you
are well intended but I just don't see a use case.

For example, what happens if I hit my quota?

Joshua D. Drake

regards,
Sergey

*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for
Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Wed, 28 Feb 2007, Tom Lane wrote:

2) I looked into the code, and from my understanding, the main part of the
code which should be affected by the quotas is storage/smgr/md.c.

md.c is too low level to do catalog accesses and thus too low level to
know who owns what.

That's probably a dumb question(I dont know the PG infrastructrure that
well), but Is it possible to put the information about the owner into
SMgrRelation/Relation structures? As I see the smgrextend() in smgr.c get
the SMgrRelation agrument...

regards,
Sergey

*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

On Wed, 28 Feb 2007, Joshua D. Drake wrote:

Sergey E. Koposov wrote:

On Wed, 28 Feb 2007, Joshua D. Drake wrote:
Per user AND per database (as Tom noted). But I dont see what's odd in
it... It exists in Oracle, and I need quotas in the project on which I'm
working. And I remember user requests for quotas in the mailing lists ...

Well Oracle isn't really our goal is it? I am not questioning that you
are well intended but I just don't see a use case.

For example, what happens if I hit my quota?

Then you cannot run any queries that extend the size of your relations
(for example INSERT, UPDATE etc.). Unless you drop your tables or DELETE
something

The use case for that is the situation when you provide the access to
different people to do something on the DB. The real world example (in
which I'm interested) is when the large science project produce a huge
amount of data, store it in large database, and let different scientists
work on that data, having their little accounts there. (example
http://casjobs.sdss.org/CasJobs/Guide.aspx ). That's the way how most of
large astronomical projects start to work now.

Regards,
Sergey

*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

Then you cannot run any queries that extend the size of your relations
(for example INSERT, UPDATE etc.). Unless you drop your tables or DELETE
something

Interesting. Well my two cents is don't go any deeper than database.
I.e; don't try and track to the individual relation.

Joshua D. Drake

The use case for that is the situation when you provide the access to
different people to do something on the DB. The real world example (in
which I'm interested) is when the large science project produce a huge
amount of data, store it in large database, and let different scientists
work on that data, having their little accounts there. (example
http://casjobs.sdss.org/CasJobs/Guide.aspx ). That's the way how most of
large astronomical projects start to work now.

Regards,
Sergey

*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for
Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

"Sergey E. Koposov" <math@sai.msu.ru> writes:

Per user AND per database (as Tom noted). But I dont see what's odd in
it... It exists in Oracle, and I need quotas in the project on which I'm
working. And I remember user requests for quotas in the mailing lists ...

It hasn't ever made it onto the TODO list, which means there's not a
consensus that we need it. If it were a simple, small, low-impact patch
then you probably wouldn't need to do much convincing that it's an
important feature to have, but I'm afraid the patch will be none of
those things.

regards, tom lane

Tom Lane wrote:

"Sergey E. Koposov" <math@sai.msu.ru> writes:

Per user AND per database (as Tom noted). But I dont see what's odd in
it... It exists in Oracle, and I need quotas in the project on which I'm
working. And I remember user requests for quotas in the mailing lists ...

It hasn't ever made it onto the TODO list, which means there's not a
consensus that we need it. If it were a simple, small, low-impact patch
then you probably wouldn't need to do much convincing that it's an
important feature to have, but I'm afraid the patch will be none of
those things.

Tom what about at just the DB level?

E.g; if user foo then pg_database_size may not be > than X?

I guess the big question would be when do we check though? At each
transaction seems like it would add significant overhead, especially if
we had to rollback the transaction because it was going to go over their
quota.

Egad.

Joshua D. Drake

regards, tom lane

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Wed, Feb 28, 2007 at 09:58:52AM -0800, Joshua D. Drake wrote:

E.g; if user foo then pg_database_size may not be > than X?

I guess the big question would be when do we check though? At each
transaction seems like it would add significant overhead, especially if
we had to rollback the transaction because it was going to go over their
quota.

Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.

Seem to me if the RelationOpen stores a pointer to a counter that gets
incremented on mdextend, it should work reasonably well. Extending
doesn't happen that often relative to other database activity.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout wrote:

On Wed, Feb 28, 2007 at 09:58:52AM -0800, Joshua D. Drake wrote:

E.g; if user foo then pg_database_size may not be > than X?

I guess the big question would be when do we check though? At each
transaction seems like it would add significant overhead, especially if
we had to rollback the transaction because it was going to go over their
quota.

Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.

Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).

J

Seem to me if the RelationOpen stores a pointer to a counter that gets
incremented on mdextend, it should work reasonably well. Extending
doesn't happen that often relative to other database activity.

Have a nice day,

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Wed, 28 Feb 2007, Joshua D. Drake wrote:

Tom Lane wrote:

"Sergey E. Koposov" <math@sai.msu.ru> writes:

Per user AND per database (as Tom noted). But I dont see what's odd in
it... It exists in Oracle, and I need quotas in the project on which I'm
working. And I remember user requests for quotas in the mailing lists ...

It hasn't ever made it onto the TODO list, which means there's not a
consensus that we need it. If it were a simple, small, low-impact patch
then you probably wouldn't need to do much convincing that it's an
important feature to have, but I'm afraid the patch will be none of
those things.

We need this kind of feature in our scientific project I and Sergey are
working on. We provide access to big pool of astronomical catalogs and
ability to match users data with these huge catalogs and we want to
be able to provide sort of QoS.

Pg became very popular in Russia, especially after the biggest
accounting and enteprize management software developer "1C"
(about 800,000 installations) has been supporting Pg and I expect
a large interest to Pg this year, especially from the application providers,
shared environment.

btw, this should be announced in -advocacy, I and Teodor
worked on Pg port, some patches we have submitted was grown from that work.

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

"Joshua D. Drake" <jd@commandprompt.com> writes:

Martijn van Oosterhout wrote:

Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.

Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).

It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves). If the limit is strictly
enforced then a user who has reached his quota is really totally
screwed: the only easy way to get back under quota will be to completely
drop tables, ie, discard data. VACUUM probably won't reduce the
physical table size much, and VACUUM FULL will fail, and other
approaches such as CLUSTER won't work either.

[ thinks for a bit... ] Possibly you could drop all your indexes,
VACUUM FULL, reconstruct indexes. But it would be painful and would
certainly prevent you from working normally until you finish that
maintenance. If the quota limit includes temp files you might find that
rebuilding the indexes fails, too, because of the transient space needed
to rebuild.

Plus, all that forced maintenance activity will be degrading response
for other users while it happens.

On the whole I'm not convinced that a quota is a good idea.

regards, tom lane

On Wed, 28 Feb 2007, Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

Martijn van Oosterhout wrote:

Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.

Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).

It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves). If the limit is strictly
enforced then a user who has reached his quota is really totally
screwed: the only easy way to get back under quota will be to completely
drop tables, ie, discard data. VACUUM probably won't reduce the
physical table size much, and VACUUM FULL will fail, and other
approaches such as CLUSTER won't work either.

[ thinks for a bit... ] Possibly you could drop all your indexes,
VACUUM FULL, reconstruct indexes. But it would be painful and would
certainly prevent you from working normally until you finish that
maintenance. If the quota limit includes temp files you might find that
rebuilding the indexes fails, too, because of the transient space needed
to rebuild.

Plus, all that forced maintenance activity will be degrading response
for other users while it happens.

On the whole I'm not convinced that a quota is a good idea.

On database level it's possible to have soft user quote, just measure
disk usage and warn user if database size is over. This could be realized
using external tools. But Sergey wanted finer granulation. As a workaround,
we could have function which return size of db objects owned by user and
let administrator run cron job to realize soft quota. This will not provide
foundation for enterprize level of QoS, but we certainly don't want to
introduce too much overhead. It's interesting and challenging task though.

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Wed, 28 Feb 2007, Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

Martijn van Oosterhout wrote:

Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.

Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).

It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves). If the limit is strictly
enforced then a user who has reached his quota is really totally
screwed: the only easy way to get back under quota will be to completely
drop tables, ie, discard data. VACUUM probably won't reduce the
physical table size much, and VACUUM FULL will fail, and other
approaches such as CLUSTER won't work either.

I don't know, but in my opinion, I don't see anything bad in requiring
dropping the data if the quota is full. That's what usually occurs in the
case of normal filesystem quota... If you don't have a space there, you
cannot edit files, copy them etc...
And that solution should be definitely better than the filesystem quota
for the PostgreSQL user for example.

regards,
Sergey
*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

On Wed, Feb 28, 2007 at 03:57:56PM -0500, Tom Lane wrote:

It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves). If the limit is strictly

I was thinking that indexes and temp tables wouldn't be counted. I
thought it was more of a "stop people using up lots of disk space"
rather than specifically stopping at a hard limit.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

I don't know, but in my opinion, I don't see anything bad in requiring
dropping the data if the quota is full. That's what usually occurs in
the case of normal filesystem quota... If you don't have a space there,
you cannot edit files, copy them etc...
And that solution should be definitely better than the filesystem quota
for the PostgreSQL user for example.

The bad point is not that we would rollback the transaction. The bad
point is what happens when you need to rollback a transaction and in
your scenario it is quite plausible that a large rollback could occur,
more than once, causing the requirement of something like a vacuum full
to clean things up.

Sincerely,

Joshua D. Drake

regards,
Sergey
*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for
Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Wed, Feb 28, 2007 at 12:56:13PM -0500, Tom Lane wrote:

It hasn't ever made it onto the TODO list, which means there's not a
consensus that we need it.

Such a patch could improve the acceptance of PostgreSQL in shared hosting
environments. Note that a database without quotas can be filled up easily
and the database will stop serving requests to other users' databases.

There is a quota implementation already in the archives but I don't know
more about it than that it exists:

http://archives.postgresql.org/pgsql-hackers/2004-07/msg00392.php

Joachim