Control your disk usage in PG: Introduction to Disk Quota Extension

Hi all,

We implement disk quota feature on Postgresql as an extension(link:
https://github.com/greenplum-db/diskquota),
If you are interested, try and use it to limit the amount of disk space that
a schema or a role can use in Postgresql.
Any feedback or question are appreciated.

Overview

Diskquota is an extension that provides disk usage enforcement for database
objects in Postgresql. Currently it supports to set quota limit on schema
and role in a given database and limit the amount of disk space that a
schema or a role can use.

This project is inspired by Heikki's pg_quota project (link:
https://github.com/hlinnaka/pg_quota) and enhance it to support different
kinds of DDL and DML which may change the disk usage of database objects.

Diskquota is a soft limit of disk uages. It has some delay to detect the
schemas or roles whose quota limit is exceeded. Here 'soft limit' supports
two kinds of encforcement: Query loading data into out-of-quota schema/role
will be forbidden before query is running. Query loading data into
schema/role with rooms will be cancelled when the quota limit is reached
dynamically during the query is running.
<https://github.com/greenplum-db/diskquota/blob/master/README.md#design>
Design

Diskquota extension is based on background worker framework in Postgresql.
There are two kinds of background workers: diskquota launcher and diskquota
worker.

There is only one laucher process per database cluster(i.e. one laucher per
postmaster). Launcher process is reponsible for manage worker processes:
Calling RegisterDynamicBackgroundWorker() to create new workers and keep
their handle. Calling TerminateBackgroundWorker() to terminate workers
which are disabled when DBA modify diskquota.monitor_databases

There are many worker processes, one for each database which is listed in
diskquota.monitor_databases. Currently, we support to monitor at most 10
databases at the same time. Worker processes are responsible for monitoring
the disk usage of schemas and roles for the target database, and do quota
enfocement. It will periodically (can be set via diskquota.naptime)
recalcualte the table size of active tables, and update their corresponding
schema or owner's disk usage. Then compare with quota limit for those
schemas or roles. If exceeds the limit, put the corresponding schemas or
roles into the blacklist in shared memory. Schemas or roles in blacklist
are used to do query enforcement to cancel queries which plan to load data
into these schemas or roles.
<https://github.com/greenplum-db/diskquota/blob/master/README.md#active-table>Active
table

Active tables are the tables whose table size may change in the last quota
check interval. We use hooks in smgecreate(), smgrextend() and
smgrtruncate() to detect active tables and store them(currently
relfilenode) in the shared memory. Diskquota worker process will
periodically consuming active table in shared memories, convert relfilenode
to relaton oid, and calcualte table size by calling
pg_total_relation_size(), which will sum the size of table(including: base,
vm, fsm, toast and index).
<https://github.com/greenplum-db/diskquota/blob/master/README.md#enforcement>
Enforcement

Enforcement is implemented as hooks. There are two kinds of enforcement
hooks: enforcement before query is running and enforcement during query is
running. The 'before query' one is implemented at ExecutorCheckPerms_hook
in function ExecCheckRTPerms() The 'during query' one is implemented at
BufferExtendCheckPerms_hook in function ReadBufferExtended(). Note that the
implementation of BufferExtendCheckPerms_hook will firstly check whether
function request a new block, if not skip directyly.
<https://github.com/greenplum-db/diskquota/blob/master/README.md#quota-setting-store>Quota
setting store

Quota limit of a schema or a role is stored in table 'quota_config' in
'diskquota' schema in monitored database. So each database stores and
manages its own disk quota configuration. Note that although role is a db
object in cluster level, we limit the diskquota of a role to be database
specific. That is to say, a role may has different quota limit on different
databases and their disk usage is isolated between databases.
<https://github.com/greenplum-db/diskquota/blob/master/README.md#install>
Install

1. Add hook functions to Postgres by applying patch. It's required since
disk quota need to add some new hook functions in postgres core. This step
would be skipped after patch is merged into postgres in future.

# install patch into postgres_src and rebuild postgres.
cd postgres_src;
git apply $diskquota_src/patch/pg_hooks.patch;
make;
make install;

1. Compile and install disk quota.

cd $diskquota_src;
make;
make install;

1. Config postgresql.conf

# enable diskquota in preload library.
shared_preload_libraries = 'diskquota'
# set monitored databases
diskquota.monitor_databases = 'postgres'
# set naptime (second) to refresh the disk quota stats periodically
diskquota.naptime = 2
# restart database to load preload library.
pg_ctl restart

1. Create diskquota extension in monitored database.

create extension diskquota;

1. Reload database configuraion

# reset monitored database list in postgresql.conf
diskquota.monitor_databases = 'postgres, postgres2'
# reload configuration
pg_ctl reload

<https://github.com/greenplum-db/diskquota/blob/master/README.md#usage>Usage

1. Set/update/delete schema quota limit using diskquota.set_schema_quota

create schema s1;
select diskquota.set_schema_quota('s1', '1 MB');
set search_path to s1;

create table a(i int);
# insert small data succeeded
insert into a select generate_series(1,100);
# insert large data failed
insert into a select generate_series(1,10000000);
# insert small data failed
insert into a select generate_series(1,100);

# delete quota configuration
select diskquota.set_schema_quota('s1', '-1');
# insert small data succeed
select pg_sleep(5);
insert into a select generate_series(1,100);
reset search_path;

1. Set/update/delete role quota limit using diskquota.set_role_quota

create role u1 nologin;
create table b (i int);
alter table b owner to u1;
select diskquota.set_role_quota('u1', '1 MB');

# insert small data succeeded
insert into b select generate_series(1,100);
# insert large data failed
insert into b select generate_series(1,10000000);
# insert small data failed
insert into b select generate_series(1,100);

# delete quota configuration
select diskquota.set_role_quota('u1', '-1');
# insert small data succeed
select pg_sleep(5);
insert into a select generate_series(1,100);
reset search_path;

1. Show schema quota limit and current usage

select * from diskquota.show_schema_quota_view;

<https://github.com/greenplum-db/diskquota/blob/master/README.md#test>Test

Run regression tests.

cd contrib/diskquota;
make installcheck

<https://github.com/greenplum-db/diskquota/blob/master/README.md#benchmark--performence-test>Benchmark
& Performence Test
<https://github.com/greenplum-db/diskquota/blob/master/README.md#cost-of-diskquota-worker>Cost
of diskquota worker

During each refresh interval, the disk quota worker need to refresh the
disk quota model.

It take less than 100ms under 100K user tables with no avtive tables.

It take less than 200ms under 100K user tables with 1K active tables.
<https://github.com/greenplum-db/diskquota/blob/master/README.md#impact-on-oltp-queries>Impact
on OLTP queries

We test OLTP queries to measure the impact of enabling diskquota feature.
The range is from 2k tables to 10k tables. Each connection will insert 100
rows into each table. And the parallel connections range is from 5 to 25.
Number of active tables will be around 1k.

Without diskquota enabled (seconds)
2k4k6k8k10k
5 4.002 11.356 18.460 28.591 41.123
10 4.832 11.988 21.113 32.829 45.832
15 6.238 16.896 28.722 45.375 64.642
20 8.036 21.711 38.499 61.763 87.875
25 9.909 27.175 47.996 75.688 106.648

With diskquota enabled (seconds)
2k4k6k8k10k
5 4.135 10.641 18.776 28.804 41.740
10 4.773 12.407 22.351 34.243 47.568
15 6.355 17.305 30.941 46.967 66.216
20 9.451 22.231 40.645 61.758 88.309
25 10.096 26.844 48.910 76.537 108.025

The performance difference between with/without diskquota enabled are less
then 2-3% in most case. Therefore, there is no significant performance
downgrade when diskquota is enabled.
<https://github.com/greenplum-db/diskquota/blob/master/README.md#notes>Notes

1. Drop database with diskquota enabled.

If DBA enable monitoring diskquota on a database, there will be a
connection to this database from diskquota worker process. DBA need to
first remove this database from diskquota.monitor_databases in
postgres.conf, and reload configuration by call pg_ctl reload. Then
database could be dropped successfully.

1. Temp table.

Diskquota supports to limit the disk usage of temp table as well. But
schema and role are different. For role, i.e. the owner of the temp table,
diakquota will treat it the same as normal tables and sum its table size to
its owner's quota. While for schema, temp table is located under namespace
'pg_temp_backend_id', so temp table size will not sum to the current
schema's qouta.

--
Thanks

Hubert Zhang, Haozhou Wang, Hao Wu, Jack WU