Aurora PostgreSQL 18.3がリリースされたのでPostgreSQL 18の新機能を試してみた

PostgreSQL 18.3 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 10.5.0, 64-bit

CREATE EXTENSION roaringbitmap;

CREATE TABLE user_segments (
  segment_id int PRIMARY KEY,
  users roaringbitmap
);

INSERT INTO user_segments VALUES (1, rb_build(ARRAY[1,2,3,4,5,100,200]));
INSERT INTO user_segments VALUES (2, rb_build(ARRAY[3,4,5,6,7,200,300]));

-- 積集合
SELECT rb_to_array(rb_and(a.users, b.users))
FROM user_segments a, user_segments b
WHERE a.segment_id = 1 AND b.segment_id = 2;

 rb_to_array
-------------
 {3,4,5,200}

-- 和集合
SELECT rb_to_array(rb_or(a.users, b.users))
FROM user_segments a, user_segments b
WHERE a.segment_id = 1 AND b.segment_id = 2;

       rb_to_array
--------------------------
 {1,2,3,4,5,6,7,100,200,300}

SELECT segment_id, rb_cardinality(users) FROM user_segments;

 segment_id | rb_cardinality
------------+----------------
          1 |              7
          2 |              7

SELECT extname, extversion FROM pg_extension WHERE extname = 'roaringbitmap';

    extname    | extversion
---------------+------------
 roaringbitmap | 1.1

CREATE TABLE orders (
  status smallint,
  created_at timestamp,
  id serial PRIMARY KEY
);

INSERT INTO orders (status, created_at)
SELECT (random()*4+1)::smallint, now() - (random()*365)::int * interval '1 day'
FROM generate_series(1, 100000);

CREATE INDEX idx_orders_status_created ON orders (status, created_at);
ANALYZE orders;

EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';

Index Scan using idx_orders_status_created on orders
  Index Cond: (created_at > (now() - '7 days'::interval))
  Index Searches: 6

SELECT status, count(*) FROM orders GROUP BY status ORDER BY status;

 status | count
--------+-------
      1 | 12278
      2 | 25033
      3 | 25243
      4 | 25020
      5 | 12426

SELECT n, v, v > lag(v) OVER (ORDER BY n) AS is_ordered
FROM (SELECT n, uuidv7() AS v FROM generate_series(1,10) AS g(n)) t
ORDER BY n;

 n  |                  v                   | is_ordered
----+--------------------------------------+------------
  1 | 019ebb09-b44c-7a03-a6cb-3ab47829fac5 |
  2 | 019ebb09-b44c-7a1b-a0c7-327cc7a70d4d | t
  3 | 019ebb09-b44c-7a1e-9f92-651835e8e0b6 | t
  4 | 019ebb09-b44c-7a1f-bff8-1a8ee8a1b769 | t
  5 | 019ebb09-b44c-7a21-85a4-34632ff35610 | t
  6 | 019ebb09-b44c-7a22-bd32-521bcf4799f8 | t
  7 | 019ebb09-b44c-7a24-9c77-392305d57fed | t
  8 | 019ebb09-b44c-7a25-820c-8e896d6e27dc | t
  9 | 019ebb09-b44c-7a27-9adc-e463421a57e2 | t
 10 | 019ebb09-b44c-7a28-8cb5-014d40f4c18f | t

CREATE TABLE products (
  id serial PRIMARY KEY,
  price numeric,
  tax_rate numeric DEFAULT 0.10,
  price_with_tax numeric GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL
);

INSERT INTO products (price, tax_rate) VALUES (1000, 0.10), (2000, 0.08);
SELECT * FROM products;

 id | price | tax_rate | price_with_tax
----+-------+----------+----------------
  1 |  1000 |     0.10 |        1100.00
  2 |  2000 |     0.08 |        2160.00

CREATE TABLE accounts (
  id serial PRIMARY KEY,
  balance numeric DEFAULT 0
);
INSERT INTO accounts (balance) VALUES (1000), (2000), (3000);

UPDATE accounts SET balance = balance - 100
WHERE id = 1
RETURNING old.balance AS before, new.balance AS after;

 before | after
--------+-------
   1000 |   900

DELETE FROM accounts WHERE id = 3
RETURNING old.balance AS deleted_balance;

 deleted_balance
-----------------
            3000

postgres=> SHOW io_method;
ERROR:  must be Aurora superuser to examine "io_method"