Manage disk quotas-AnalyticDB(AnalyticDB)-阿里云帮助中心

The Diskquota extension enables you to manage disk quotas for AnalyticDB for PostgreSQL. A superuser can set disk usage quotas for schemas and roles. This topic describes how to enable and use Diskquota in AnalyticDB for PostgreSQL.

Note

The disk quota management feature is supported only for AnalyticDB for PostgreSQL instances in elastic storage mode.

Enable and disable Diskquota

Create the `diskquota` database. The Diskquota module uses this database to store a list of databases where the module is enabled.
```
createdb diskquota;
```
Submit a ticket to contact technical support. Request that Diskquota be added to `shared_preload_libraries` and that the AnalyticDB for PostgreSQL instance be restarted.

Enable Diskquota.

CREATE EXTENSION diskquota;
CREATE EXTENSION

Disable Diskquota.

drop extension diskquota;
DROP EXTENSION

Note

If you enable Diskquota on a database that already contains data, you must initialize the Diskquota table. This process may take a long time if the database contains many files.

SELECT diskquota.init_table_size_table();

Set the disk quota for a schema or role

Set the disk quota for a schema.

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

(1 row)

Set the disk quota for a role.

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

(1 row)

Note

You can specify disk quotas in MB, GB, TB, or PB. To remove a quota limit, set the quota to -1. Before a query is executed, Diskquota checks the disk quota and the blacklist. If the quota for a schema or role is exceeded, that schema or role is automatically added to the blacklist. Subsequent queries are canceled for any schema or role on the blacklist. This is a soft limit.

Example: Manage the disk quota for a schema

Create a database and a schema.

createdb myadbpg    
psql myadbpg

CREATE EXTENSION diskquota;        # Enable diskquota 
CREATE EXTENSION                            

CREATE SCHEMA adbpg1;
CREATE SCHEMA

Set the schema disk quota.

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

(1 row)

Create a table and insert data.

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

When the quota is exceeded, an error is returned, and further insert operations will fail.

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

Set the quota to -1 to remove the disk quota limit for `adbpg1`. You can then insert data again.
The 5-second sleep period before the `INSERT` command ensures that the disk quota table size is updated before the command is run.
```
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
```

View disk usage

View the disk usage for a 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)

View the disk usage for a 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

Internal tests on AnalyticDB for PostgreSQL show that enabling Diskquota results in a 2% to 3% performance loss.