Amazon RDSデータベースプレビュー環境でPostgreSQL 19 Beta 1の新機能を試してみた

postgres=> SELECT version();
                                              version
----------------------------------------------------------------------------------------------------
 PostgreSQL 19beta1 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 12.4.0, 64-bit

CREATE TABLE persons (id INT PRIMARY KEY, name TEXT);
CREATE TABLE friendships (id INT PRIMARY KEY, person_id INT REFERENCES persons(id), friend_id INT REFERENCES persons(id));
INSERT INTO persons VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'Diana');
INSERT INTO friendships VALUES (1, 1, 2), (2, 2, 3), (3, 3, 4), (4, 1, 3);

CREATE PROPERTY GRAPH friends_graph
  VERTEX TABLES (persons KEY (id))
  EDGE TABLES (
    friendships KEY (id)
      SOURCE KEY (person_id) REFERENCES persons (id)
      DESTINATION KEY (friend_id) REFERENCES persons (id)
  );

SELECT * FROM GRAPH_TABLE (friends_graph
  MATCH (a IS persons WHERE a.name = 'Alice')-[e IS friendships]->(b IS persons)
  COLUMNS (a.name AS person, b.name AS friend)
);

 person | friend
--------+---------
 Alice  | Bob
 Alice  | Charlie
(2 rows)

SELECT * FROM GRAPH_TABLE (friends_graph
  MATCH (a IS persons WHERE a.name = 'Alice')-[IS friendships]->(b IS persons)-[IS friendships]->(c IS persons)
  COLUMNS (a.name AS person, b.name AS via, c.name AS friend_of_friend)
);

 person |   via   | friend_of_friend
--------+---------+------------------
 Alice  | Bob     | Charlie
 Alice  | Charlie | Diana
(2 rows)

CREATE TABLE customers (id INT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE products (id INT PRIMARY KEY, name TEXT, price NUMERIC);
CREATE TABLE purchases (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id), product_id INT REFERENCES products(id), purchased_at DATE);

INSERT INTO customers VALUES (1, 'Alice', 'Tokyo'), (2, 'Bob', 'Osaka'), (3, 'Charlie', 'Tokyo'), (4, 'Diana', 'Nagoya');
INSERT INTO products VALUES (1, 'Laptop', 120000), (2, 'Keyboard', 8000), (3, 'Mouse', 3000), (4, 'Monitor', 45000);
INSERT INTO purchases VALUES (1, 1, 1, '2025-01-01'), (2, 1, 2, '2025-01-05'), (3, 2, 1, '2025-02-01'), (4, 3, 2, '2025-02-10'), (5, 4, 1, '2025-03-01');

CREATE PROPERTY GRAPH shop_graph
  VERTEX TABLES (customers LABEL customer, products LABEL product)
  EDGE TABLES (
    purchases
      SOURCE KEY (customer_id) REFERENCES customers (id)
      DESTINATION KEY (product_id) REFERENCES products (id)
      LABEL bought
  );

SELECT DISTINCT * FROM GRAPH_TABLE (shop_graph
  MATCH (alice IS customer WHERE alice.name = 'Alice')-[IS bought]->(prod IS product)<-[IS bought]-(other IS customer)
  COLUMNS (prod.name AS shared_product, other.name AS also_bought_by)
) WHERE also_bought_by != 'Alice';

 shared_product | also_bought_by
----------------+----------------
 Keyboard       | Charlie
 Laptop         | Bob
 Laptop         | Diana
(3 rows)

CREATE TABLE bloat_test (id SERIAL PRIMARY KEY, data TEXT);
INSERT INTO bloat_test (data) SELECT repeat('x', 100) FROM generate_series(1, 100000);
DELETE FROM bloat_test WHERE id % 5 != 0;  -- 80% を削除

REPACK bloat_test;

REPACK (CONCURRENTLY) bloat_test;

REPACK (VERBOSE) bloat_test;

INFO:  repacking "public.bloat_test" in physical order
INFO:  "public.bloat_test": found 0 removable, 5000 nonremovable row versions in 468 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

VACUUM (FULL, CONCURRENTLY) bloat_test;

ERROR:  unrecognized VACUUM option "concurrently"

SELECT name, setting, context, boot_val, reset_val
FROM pg_settings WHERE name = 'wal_level';

   name    | setting |  context   | boot_val | reset_val
-----------+---------+------------+----------+-----------
 wal_level | logical | postmaster | replica  | logical
(1 row)

ALTER SYSTEM SET wal_level = 'replica';

ERROR:  ALTER SYSTEM command is not supported

SELECT pg_reload_conf();

ERROR:  permission denied for function pg_reload_conf