Re: Proposal for disk quota feature

Thanks Chapman.
@Pavel, could you please explain more about your second suggestion "implement
some quotas on storage level?"
We will not keep the long-lived processes attach to all databases(just like
you mentioned servers with thousands of databases)
And you are right, we could share ideas with autovacuum process, fork
worker processes in need.
"autovacuum checks for tables that have had a large number of inserted,
updated or deleted tuples. These checks use the statistics collection
facility"
diskquota process is similar to autovacuum at caring about insert, but the
difference is that it also care about vucuum full, truncate and drop. While
update and delete may not be interested since no file change happens. So a
separate diskquota process is preferred.

So if we implemented disk quota as a full native feature, and in the first
initial version I prefer to implement the following features:
1 Fork diskquota launcher process under Postmaster serverloop, which is
long-lived.
2 Diskquota launcher process is responsible for creating diskquota worker
process for every database.
3 DIskquota setting is stored in a separate catalog table for each database.
4 Initialization stage, Diskquota launcher process creates diskquota worker
process for all the databases(traverse like autovacuum). Worker process
calculates disk usage of db objects and their diskquota setting. If any
db object exceeds its quota limit, put them into the blacklist in the
shared memory, which will later be used by enforcement operator. Worker
process exits when works are done.
5 Running stage, Diskquota launcher process creates diskquota worker
process for the database with a large number of insert, copy, truncate,
drop etc. or create disk quota statement. Worker process updates the file
size for db objects containing the result relation, and compare with the
diskquota setting. Again, if exceeds quota limit, put them into blacklist,
remove from blacklist vice versa. Worker process exits when works are done
and a GUC could control the frequency of worker process restart to a
specific database. As you know, this GUC also controls the delay when we do
enforcement.
6 Enforcement. When postgres backend executes queries, check the blacklist
in shared memory to determine whether the query is allowed(before execute)
or need rollback(is executing)?

If we implemented disk quota as an extension, we could just use background
worker to start diskquota launcher process and use
RegisterDynamicBackgroundWorker() to fork child diskquota worker processes
by the launcher process as suggested by @Chapman. Diskquota setting could
be stored in user table in a separate schema for each database(Schema and
table created by create extension statement) just like what Heikki has done
in pg_quota project. But in this case, we need to create extension for each
database before diskquota worker process can be set up for that database.

Any comments on the above design and which is preferred, native feature or
extension as the POC?

-- Hubert

On Fri, Aug 31, 2018 at 3:32 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> 2018-08-30 16:22 GMT+02:00 Chapman Flack <chap(at)anastigmatix(dot)net>:
>
>> On 08/30/2018 09:57 AM, Hubert Zhang wrote:
>>
>> > 2 Keep one worker process for each database. But using a parent/global
>> > quota worker process to manage the lifecycle of database level worker
>> > processes. It could handle the newly created database(avoid restart
>> > database) and save resource when a database is not used. But this needs
>> to
>> > change worker process to be hierarchical. Postmaster becomes the
>> grandfather
>> > of database level worker processes in this case.
>>
>> I am using background workers this way in 9.5 at $work.
>>
>> In my case, one worker lives forever, wakes up on a set period, and
>> starts a short-lived worker for every database, waiting for each
>> one before starting the next.
>>
>> It was straightforward to implement. Looking back over the code,
>> I see the global worker assigns its own PID to worker.bgw_notify_pid
>> of each of its children, and also obtains a handle for each child
>> from RegisterDynamicBackgroundWorker().
>>
>> I imagine the global quota worker would prefer to start workers
>> for every database and then just wait for notifications from any
>> of them, but that seems equally straightforward at first glance.
>>
>
> There are servers with thousands databases. Worker per database is not
> good idea.
>
> It should to share ideas, code with autovacuum process.
>
> Not sure, how to effective implementation based on bg workers can be. On
> servers with large set of databases, large set of tables it can identify
> too big table too late.
>
> Isn't better to implement some quotas on storage level?
>
> Regards
>
> Pavel
>
>
>
>> -Chap
>>
>>
>

--
Thanks

Hubert Zhang