PostgreSQL向け、俺よう便利メモ Tweet

testdb=> select version();
                                   version         
                          -----------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit

以降のスクリプトの動作確認用の表定義

testdb=> \d+ hoge
                                 Table "public.hoge"
 Column |          Type          | Modifiers | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+--------------+-------------
 id     | numeric(10,0)          | not null  | main     |              | 
 status | numeric(2,0)           | not null  | main     |              | 
 note   | character varying(100) |           | extended |              | 
Indexes:
    "hoge_pkey" PRIMARY KEY, btree (id)

PL/pgSQLでPL/SQLっぽい感じの無名ブロックを書いてみた。commit/rollbackは含めふことができないってところはちょいとハマるね。Oraclerには。（なれれば問題ないって感じはするが）

testdb=> \! cat ins10000000.sql
DO $$BEGIN
  FOR i IN 1..10000000 LOOP
    INSERT INTO hoge
    (id, status, note)
    VALUES(
     i, 0, LPAD('hoge',100,'x')
    );
  END LOOP;
END$$;
testdb=> \i ins10000000.sql

まあ、いろいろ勝手が違うので、よく使いそうなSQL文も作り置きしておかないと。
以下のSQLは、Oracle Databaseだと表と索引のセグメントサイズの確認で dba_segmentsを問い合わせるのと同じイメージな。ビューは違うけど。

testdb=> \! cat show_segment_size.sql
SELECT
  objectname
  , TO_CHAR(pg_relation_size(objectname::regclass), '999,999,999,999') AS bytes
FROM 
(
  SELECT
    tablename AS objectname
  FROM
    pg_tables
  WHERE 
    schemaname = 'public'
  UNION
  SELECT
    indexname AS objectname
  FROM
    pg_indexes
  WHERE
    schemaname = 'public'
) AS objects
ORDER BY
  bytes DESC
;

testdb=> \i show_segment_size.sql 
 objectname |      bytes       
------------+------------------
 hoge       |    1,412,415,488
 hoge_pkey  |      224,632,832
(2 rows)

Oracle Databaseとは異なるアーキテクチャを採用しているPostgreSQLの特徴がよく見える部分。全体の30％ぐらいの行を削除して、dead tupleを確認してみたところ。
Inside vacuum - 第一回PostgreSQLプレ勉強会

testdb=> \! cat show_dead_tup_ratio.sql
SELECT
  relname
  , n_live_tup
  , n_dead_tup
  , CASE
    WHEN n_live_tup > 0
      THEN ROUND(n_dead_tup * 100 / n_live_tup, 2)
      ELSE NULL
  END AS ratio
FROM
  pg_stat_user_tables;

testdb=> delete from hoge where id between 1 and 3000000;
DELETE 3000000

testdb=> \i show_dead_tup_ratio.sql 
 relname | n_live_tup | n_dead_tup | ratio 
---------+------------+------------+-------
 hoge    |    6973030 |    3020431 | 43.00
(1 row)

最後は、Lockの状態を確認するSQL文。この部分もOracle Databaseとは異なるとこも多いので、Lockがどうなってるか見えるようなSQL文は作り置きしておくと便利。
宣言的パーティションのロックもOracleのPartitionとは異なる部分も多いので事前に確認しておくと、あとから驚くようなことがなくて安心できる、かも。

testdb=> \! cat show_locked_objects.sql
SELECT
  clock_timestamp()
  , pc.relname
  , pl.locktype
  , pl.database
  , pl.relation
  , pl.page
  , pl.tuple
  , pl.virtualtransaction
  , pl.pid
  , pl.mode
  , pl.granted
FROM
  pg_locks pl
  INNER JOIN pg_class pc
  ON
    pl.relation = pc.oid
WHERE
  pc.relname !~ '^pg_'
  AND pc.relname <> 'activelocks';

testdb=> BEGIN;
BEGIN
testdb=> SELECT * FROM hoge FOR UPDATE SKIP LOCKED;
 id | status |                                                 note   
----+--------+------------------------------------------------------------------------------------------------------
  1 |      0 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxhogedkdkhoge
(1 row)

testdb=> \i show_locked_objects.sql
        clock_timestamp        |  relname  | locktype | database | relation | page | tuple | virtualtransaction |  pid  |      mode       | granted 
-------------------------------+-----------+----------+----------+----------+------+-------+--------------------+-------+-----------------+---------
 2019-01-07 07:45:33.742858+00 | hoge_pkey | relation |    16401 |    16405 |      |       | 8/11426            | 18445 | AccessShareLock | t
 2019-01-07 07:45:33.742879+00 | hoge      | relation |    16401 |    16402 |      |       | 8/11426            | 18445 | RowShareLock    | t
(2 rows)