Manage disk quotas - AnalyticDB - Alibaba Cloud Documentation Center

Diskquota is an extension that provides disk quota management in AnalyticDB for PostgreSQL. You can use superusers to configure disk quotas for schemas and roles. This topic describes how to use the diskquota extension in AnalyticDB for PostgreSQL.

Note

Disk quota management is supported only for AnalyticDB for PostgreSQL instances in elastic storage mode.

Enable and disable the diskquota extension

Create a database named diskquota in an AnalyticDB for PostgreSQL instance to store the databases that have the diskquota extension enabled.
```
createdb diskquota;
```
Submit a ticket to add the diskquota database to shared_preload_libraries and restart the AnalyticDB for PostgreSQL instance.

Enable the diskquota extension.

CREATE EXTENSION diskquota;
CREATE EXTENSION

Disable the diskquota extension.

drop extension diskquota;
DROP EXTENSION

Note

If you use the diskquota extension in a database that contains data, you must initialize the diskquota table. This operation is time-consuming if large amounts of data exist.

SELECT diskquota.init_table_size_table();

Configure a disk quota for a schema or role

Configure a disk quota for a schema.

SELECT diskquota.set_schema_quota('adbpg1', '1MB');     
 set_schema_quota
------------------

(1 row)

Configure a disk quota for a role.

select diskquota.set_role_quota('u1', '250 MB');
 set_role_quota
----------------

(1 row)

Note

The disk quota is measured in MB, GB, TB, or PB. If you set the disk quota to -1, no limits are imposed on the disk quota. The diskquota extension is a soft limit of disk usage. Before the SELECT statement is executed, the diskquota extension checks the involved schema or role. If the schema or role is contained in the out-of-quota blacklist, the diskquota extension forbids the query. If the quota limit will be reached during the query, the diskquota extension cancels the query and adds the schema or role to the blacklist.

Example: Configure a disk quota for a schema

Create a database and a schema.

createdb myadbpg    
psql myadbpg

CREATE EXTENSION diskquota;        # Enable the diskquota extension. 
CREATE EXTENSION                            

CREATE SCHEMA adbpg1;
CREATE SCHEMA

Configure a disk quota for the schema.

SELECT diskquota.set_schema_quota('adbpg1', '1MB');     
 set_schema_quota
------------------

(1 row)

Create a table and insert data into the table.

SET search_path TO adbpg1;
SET

CREATE TABLE a(i int);    

INSERT INTO a SELECT generate_series(1,100);
INSERT 0 100

INSERT INTO a SELECT generate_series(1,10000000);
INSERT 0 10000000

Insert data that exceeds the disk quota into the table. An error is reported.

INSERT INTO a SELECT generate_series(1,100);
ERROR:  schema's disk space quota exceeded with name:adbpg1

Set the disk quota to -1 to cancel the limits on the disk quota of the adbpg1 schema. Insert data into the table.
Before the INSERT statement, execute the SELECT pg_sleep(5) statement to ensure that the diskquota table is updated within the delayed 5 seconds.
```
SELECT diskquota.set_schema_quota('adbpg1', '-1');
 set_schema_quota
------------------

(1 row)
```
```
SELECT pg_sleep(5);
 pg_sleep
----------

(1 row)

INSERT INTO a SELECT generate_series(1,100);
INSERT 0 100
```

Query the disk usage

Query the disk usage of the schema.

SELECT * FROM diskquota.show_fast_schema_quota_view;
 schema_name | schema_oid | quota_in_mb | nspsize_in_bytes
-------------+------------+-------------+------------------
 adbpg1      |      16806 |        2000 |        721321984
(1 row)

Query the disk usage of the role.

SELECT * FROM diskquota.show_fast_role_quota_view;
 role_name | role_oid | quota_in_mb | rolsize_in_bytes
-----------+----------+-------------+------------------
 u1        |    16810 |         250 |                0
(1 row)

Note

After the diskquota extension is enabled, a performance loss of less than 2% to 3% is presented in AnalyticDB for PostgreSQL tests.