Amazon Aurora PostgreSQLがラージオブジェクトに対応しました

quiver

2022.06.15

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Aurora PostgreSQLが、画像や動画といったサイズの多きデータを効率的に格納するラージオブジェクトに対応しました。

ラージオブジェクトを利用すると、オブジェクト管理をまるっとデータベースにオフロードできます。実データはAmazon S3のような外部ストレージで管理し、データベースにはオブジェクトのメタデータだけを管理するアプローチと対極的です。、

不要なラージオブジェクトが残り続けると、ストレージがブクブクと膨れ上がるため、ラージオブジェクト向けのVACUUM操作(vacuumlo)など、ストレージの定期運用が欠かせません。

本エントリでは、ラージオブジェクトの基本操作方法を紹介します。

やってみた

対応バージョン

ラージオブジェクトは Amazon Aurora PostgreSQL の 13.7, 12.11, 11.16, 10.21 以上のマイナーバージョンで対応しています。

今回の検証では、 13.7 を利用しました。

エクステンションを有効化

ラージオブジェクト用エクステンション lo を有効化(create EXTENSION lo)します。

test=> select * from pg_available_extensions where name = 'lo';
 name | default_version | installed_version |         comment
------+-----------------+-------------------+--------------------------
 lo   | 1.1             |                   | Large Object maintenance
(1 row)

test=> create EXTENSION lo;
CREATE EXTENSION

test=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 lo      | 1.1     | public     | Large Object maintenance
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)


test=> \df
                         List of functions
 Schema |   Name    | Result data type | Argument data types | Type
--------+-----------+------------------+---------------------+------
 public | lo_manage | trigger          |                     | func
 public | lo_oid    | oid              | lo                  | func
(2 rows)

ラージオブジェクトの操作

ラージオブジェクトの入出力を行う lo_import/lo_export を呼び出してみます。

これら関数は、サーバーサイドとクライアントサイドの2種類が存在します。

サーバーサイド関数は、データベースが動作しているサーバー内のファイルシステムを操作します。 PostgreSQL のドキュメントにある通り、様々なリスクがあるため、デフォルトではスーパーユーザー以外は操作できません。

Caution

It is possible to GRANT use of the server-side lo_import and lo_export functions to non-superusers, but careful consideration of the security implications is required. A malicious user of such privileges could easily parlay them into becoming superuser (for example by rewriting server configuration files), or could attack the rest of the server's file system without bothering to obtain database superuser privileges as such. Access to roles having such privilege must therefore be guarded just as carefully as access to superuser roles. Nonetheless, if use of server-side lo_import or lo_export is needed for some routine task, it's safer to use a role with such privileges than one with full superuser privileges, as that helps to reduce the risk of damage from accidental errors.

https://www.postgresql.org/docs/current/lo-funcs.html

Amazon Auroraもこの挙動を踏襲しており、スーパーユーザーではないAmazon Aurora管理ユーザーは、サーバーサイド操作を行なえません。そもそも、Aurora(RDS)のマネージドサービスの特性上、PostgreSQLが動いているサーバーにログインできないため、同サーバーにインポート用のユーザーファイルを配置することも、同サーバーに出力したファイルを取得することもできないため、仮にサーバーサイド lo_import/export が許可されていても、使いみちがありません。

サーバーサイド lo_import を呼び出すと、権限不足エラーが発生します。

# サーバーサイドのファイルをデータベースにインポート
test=> select lo_import('/etc/hosts');
ERROR:  permission denied for function lo_import

'\' が接頭するクライアントサイド関数は呼び出せます。

# ローカル環境のファイルをデータベースにインポート
test=> \lo_import 'lob1.json';
lo_import 16444

test=> \lo_list
         Large objects
  ID   |  Owner   | Description
-------+----------+-------------
 16444 | postgres |
(1 row)

ラージオブジェクトを取り込むと、オブジェクトID が割り振られます。

ラージオブジェクトは2Kbのページに分割されて保存されます。ラージオブジェクトのシステムカタログ(pg_largeobject)に問い合わせて確認します。

test=> select loid,pageno from pg_largeobject where loid = 16444;
 loid  | pageno
-------+--------
 16444 |      0
 16444 |      1
 16444 |      2
 16444 |      3
 16444 |      4
 16444 |      5
 16444 |      6
(7 rows)

14kB 程度の 'lob1.json' をインポートすると、7ページに渡って保存されました。

オブジェクトIDを指定して、クライアント環境にエクスポートします。

# ローカル環境にエクスポート
test=> \lo_export 16444 '/tmp/foo.json';
lo_export

ファイルのハッシュ値が同じことを確認します。

$ md5sum lob1.json /tmp/foo.json
adddf5f3c6ff14872a19656590cf6e54  lob1.json
adddf5f3c6ff14872a19656590cf6e54  /tmp/foo.json

ファイル入出力を伴わない lo_create や lo_unlink(OID指定したラージオブジェクトを削除) といったサーバーサイド関数は、利用可能です。

ラージオブジェクトをテーブルと連携

ラージオブジェクトを参照するカラムを持ったテーブル(image) を作成します。

test=> CREATE TABLE image(title text, raster lo);
CREATE TABLE

test=> \d image
              Table "public.image"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 title  | text |           |          |
 raster | lo   |           |          |

このテーブルに、レコードを追加します。

test=> \lo_import 'lob2.json';
lo_import 16445
test=> \lo_import 'lob3.json';
lo_import 16446

test=> insert into image values('a', 16444);
INSERT 0 1
test=> insert into image values('b', 16445);
INSERT 0 1

image テーブルからレコードを削除しても、削除レコードが参照しているラージオブジェクトは残ったままです。

test=> delete from image where raster = '16444';
DELETE 1

test=> select loid,pageno from pg_largeobject where loid = 16444;
 loid  | pageno
-------+--------
 16444 |      0
 16444 |      1
 16444 |      2
 16444 |      3
 16444 |      4
 16444 |      5
 16444 |      6
(7 rows)

パージ処理を別途行わないと、ストレージが膨らみ続けてしまいます。

レコード削除時に参照しているラージオブジェクトも削除したい場合、削除イベントをトリガーに lo_manage を呼び出すトリガーを設定します。

CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

トリガー設定後に削除してみます。

test=> delete from image where  = 16445;
DELETE 1

test=> select loid,pageno from pg_largeobject where loid = 16445;
 loid | pageno
------+--------
(0 rows)

今度は、レコードが参照しているラージオブジェクトも削除されました。

同じラージオブジェクトを複数のレコードが参照している場合には、このようなシンプルなロジックは使えません。

また、テーブルを DROP/TRUNCATE すると、トリガーは走りません。DELETE 文や後述のパージ処理をご利用ください。

ラージオブジェクトをパージ

PostgreSQLには、宙ぶらりんな(orphaned)ラージオブジェクトをパージするユーティリティ vacuumlo が存在します。

ドライランオプション(-n) で該当するオブジェクトが存在するか、チェックします。

$ vacuumlo -v -n -h RDS-HOST.amazonaws.com -U postgres DBNAME
Password:
Connected to database "DBNAME"
Test run: no large objects will be removed!
Checking raster in public.image
Would remove 1 large objects from database "DBNAME".

public.image テーブルに削除候補のオブジェクトが存在していますね。

ドライランオプションを外して再実行します。

$ vacuumlo -v -h RDS-HOST.amazonaws.com -U postgres DBNAME
Password:
Connected to database "DBNAME"
Checking raster in public.image
Successfully removed 1 large objects from database "DBNAME".

ラージオブジェクトのシステムカタログ(pg_largeobject) から、削除されたことを確認できます。

Ubuntu 22.04 の場合、 vacuumlo はパッケージ postgresql-contrib からインストールできます。