PostgreSQL でテーブル情報などを確認する方法

PostgreSQL でテーブル情報などを確認する方法のメモ。

データベース一覧

# \list
                                                     List of databases
       Name       |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | Locale | ICU Rules |   Access privileges
------------------+----------+----------+-----------------+---------+---------+--------+-----------+-----------------------
 postgres         | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 template0        | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +
                  |          |          |                 |         |         |        |           | postgres=CTc/postgres
 template1        | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           | =c/postgres          +
                  |          |          |                 |         |         |        |           | postgres=CTc/postgres
 test1            | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |
 test2            | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |        |           |

データベースに接続

# \connect test1

テーブル定義

# \d test1
                      Table "public.test1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | character varying(32) |           | not null |
 name   | character varying(32) |           | not null |
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id)

select

# select * from test1;
   id   |   name
--------+----------
 id_001 | name_001
 id_002 | name_002
 id_003 | name_003
 id_004 | name_004
 id_005 | name_005

検索結果を縦方向に表示

# \x
Expanded display is on.

# select * from test1;
-[ RECORD 1 ]--
id   | id_001
name | name_001
-[ RECORD 2 ]--
id   | id_002
name | name_002
-[ RECORD 3 ]--
id   | id_003
name | name_003
-[ RECORD 4 ]--
id   | id_004
name | name_004
-[ RECORD 5 ]--
id   | id_005
name | name_005

PostgreSQL で実行中のクエリの確認

PostgreSQL で実行中のクエリを確認する方法のメモ。

pg_stat_activity テーブルで実行中のクエリを参照することができます。

# select * from pg_stat_activity;
 datid | datname  | pid  | leader_pid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type |     wait_event      | state  | backend_xid | backend_xmin | query_id |              query              |         backend_type
-------+----------+------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+---------------------------------+------------------------------
     5 | postgres | 3983 |            |       10 | postgres | psql             | 127.0.0.1   |                 |       58884 | 2025-02-18 23:24:17.79372+09  | 2025-02-18 23:26:01.543685+09 | 2025-02-18 23:26:01.543685+09 | 2025-02-18 23:26:01.543687+09 |                 |                     | active |             |       246389 |          | select * from pg_stat_activity; | client backend
     5 | postgres | 4527 |            |       10 | postgres | psql             | 127.0.0.1   |                 |       40056 | 2025-02-18 23:25:47.587781+09 | 2025-02-18 23:25:58.352106+09 | 2025-02-18 23:25:58.352106+09 | 2025-02-18 23:25:58.352109+09 | Timeout         | PgSleep             | active |             |       246389 |          | select pg_sleep(1000);          | client backend
       |          | 1145 |            |          |          |                  |             |                 |             | 2025-02-18 23:08:03.235239+09 |                               |                               |                               | Activity        | AutovacuumMain      |        |             |              |          |                                 | autovacuum launcher
       |          | 1146 |            |       10 | postgres |                  |             |                 |             | 2025-02-18 23:08:03.235243+09 |                               |                               |                               | Activity        | LogicalLauncherMain |        |             |              |          |                                 | logical replication launcher
       |          | 1121 |            |          |          |                  |             |                 |             | 2025-02-18 23:08:03.193927+09 |                               |                               |                               | Activity        | CheckpointerMain    |        |             |              |          |                                 | checkpointer
       |          | 1122 |            |          |          |                  |             |                 |             | 2025-02-18 23:08:03.194386+09 |                               |                               |                               | Activity        | BgwriterHibernate   |        |             |              |          |                                 | background writer
       |          | 1143 |            |          |          |                  |             |                 |             | 2025-02-18 23:08:03.234863+09 |                               |                               |                               | Activity        | WalWriterMain       |        |             |              |          |                                 | walwriter

PostgreSQL の extension のバージョン確認

PostgreSQL で extension のバージョンを確認する方法のメモ。

pg_available_extensions テーブルでバージョンを確認することができます。

# select * from pg_available_extensions;

        name        | default_version | installed_version |                                comment
--------------------+-----------------+-------------------+----------------------------------------------------------------
 pageinspect        | 1.12            |                   | inspect the contents of database pages at a low level
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
...
 xml2               | 1.1             |                   | XPath querying and XSLT
 vector             | 0.8.0           |                   | vector data type and ivfflat and hnsw access methods
 hstore_plpython3u  | 1.0             |                   | transform between hstore and plpython3u
 jsonb_plpython3u   | 1.0             |                   | transform between jsonb and plpython3u
 plpython3u         | 1.0             |                   | PL/Python3U untrusted procedural language
 textsearch_ja      | 42              | 42                | Integrated Full-Text-Search for Japanese using morphological an
 ltree_plpython3u   | 1.0             |                   | transform between ltree and plpython3u

PostgreSQL で textsearch_ja を使った日本語の全文検索

PostgreSQL で textsearch_ja を使って日本語のテキストを全文検索する方法のメモ。

mecab のインストール

Groonga リポジトリを追加

sudo yum install https://packages.groonga.org/almalinux/9/groonga-release-latest.noarch.rpm

rpm パッケージをインストール

sudo yum install mecab mecab-devel mecab-ipadic

textsearch_ja のインストール

textsearch_ja をダウンロード

$ git clone https://github.com/oknj/textsearch_ja.git

インストール

$ make
$ export PATH="${PATH}:/usr/pgsql-17/bin"
$ sudo --preserve-env=PATH make install
$ psql -f textsearch_ja--42.sql # 必要に応じて -h、-U オプションを指定

postgres にログインして textsearch_ja を有効化

$ psql -h 127.0.0.1 -U postgres
# create extension textsearch_ja;

動作確認

# select ja_wakachi('日本語のテキストを単語に分割します。');

                  ja_wakachi
-----------------------------------------------
 日本語 の テキスト を 単語 に 分割 し ます 。

テーブル作成、データ登録

$ psql -h 127.0.0.1 -U postgres

# create database test_textsearch1;

# create table textsearch1 (
    id    integer not null,
    body  text,
    primary key (id)
  );

# create index on textsearch1 using gin (to_tsvector('japanese', body));

# insert into textsearch1 (id, body) values (0, '日本語のテキストです。');
# insert into textsearch1 (id, body) values (1, '英語のテキストではありません。');
# insert into textsearch1 (id, body) values (2, 'フランス語のテキストではありません。');
# insert into textsearch1 (id, body) values (3, '日本語の文章です。');
# insert into textsearch1 (id, body) values (4, '英語の文章ではありません。');

テーブル検索

単純な検索の場合

# select * from textsearch1 where to_tsvector('japanese', body) @@ to_tsquery('japanese', ' 日本語');

 id |          body
----+------------------------
  0 | 日本語のテキストです。
  3 | 日本語の文章です。

複数ワードでのAND検索の場合

# select * from textsearch1 where (to_tsvector('japanese', body) @@ to_tsquery('japanese', '日本語')) and (to_tsvector('japanese', body) @@ to_tsquery('japanese', 'テキスト'));

 id |          body
----+------------------------
  0 | 日本語のテキストです。

PostgreSQL でテーブル定義を確認する方法

PostgreSQL でテーブル定義を確認する方法のメモ。

vector2 テーブルのテーブル定義は \d vector2 で確認できます。

# \d vector2
                      Table "public.vector2"
 Column |          Type          | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
 id     | integer                |           | not null |
 name   | character varying(256) |           | not null |
 vec    | vector(1000)           |           |          |
Indexes:
    "vector2_pkey" PRIMARY KEY, btree (id)
    "vector2_vec_idx" hnsw (vec vector_l2_ops)
Check constraints:
    "vector2_vec_check" CHECK (vector_dims(vec::vector) = 1000)

PostgreSQL のベクトル検索インデックス

PostgreSQL のベクトル検索のインデックス作成方法のメモ。

ベクトルカラムにインデックスを張ることで、高速にベクトル検索を行うことができます。

1000次元のベクトルのテーブルに10万レコードを登録して検索性能を比較

インデックスを張らない場合の検索実行時間

# select now();
# select id, name, vec <-> '[...]' from vector2 order by vec <-> '[...]' limit 3;
# select now();

              now
-------------------------------
 2025-02-10 22:41:46.857061+09
(1 row)

  id   |      name       |      ?column?
-------+-----------------+--------------------
 98408 | name_0000098408 | 11.862095295923496
 59794 | name_0000059794 | 11.920021563004825
 96991 | name_0000096991 | 11.927945292725399
(3 rows)

              now
-------------------------------
 2025-02-10 22:41:47.403764+09
(1 row)

約0.6秒かかっています。

インデックスを張った場合の検索実行時間

# create index on vector2 using hnsw(vec vector_l2_ops);

# select now();
# select id, name, vec <-> '[...]' from vector2 order by vec <-> '[...]' limit 3;
# select now();

              now
-------------------------------
 2025-02-10 23:00:28.030441+09
(1 row)

  id   |      name       |      ?column?
-------+-----------------+--------------------
 43107 | name_0000043107 | 11.891565593957367
 93189 | name_0000093189 | 11.940329011764277
 29278 | name_0000029278 | 11.961626368144666
(3 rows)

              now
-------------------------------
 2025-02-10 23:00:28.111582+09
(1 row)

約0.1秒で検索結果を取得できるようになりました。

インデックス作成方法

/* コサイン */
create index on {table名} using hnsw ({column名} vector_cosine_ops);

/* L2 距離 */
create index on {table名} using hnsw ({column名} vector_l2_ops);

/* 内積 */
create index on {table名} using hnsw ({column名} vector_ip_ops);

PostgreSQL でのベクトル検索

PostgreSQL でベクトル検索を行う方法のメモ。

テーブル作成・データ登録

postgres ユーザで test_vector1 DB で vector を利用できるようにします。

$ psql -h 127.0.0.1 -U postgres
# create databse test_vector1;
# \connect test_vector1
# create extension vector;

テーブルを作成し、ベクトルの次元の制約を追加

# create table vector1 (
    id      integer not null,
    name    varchar(256) not null,
    vec     vector(2),
    primary key (id)
);

# alter table vector1 add check (vector_dims(vec::vector) = 2);

データを登録

# insert into vector1 (id, name, vec) values (0, 'name_000', array[0.0, 0.0]);
# insert into vector1 (id, name, vec) values (1, 'name_001', array[0.0, 1.0]);
# insert into vector1 (id, name, vec) values (2, 'name_002', array[1.0, 0.0]);
# insert into vector1 (id, name, vec) values (3, 'name_003', array[2.0, 1.0]);
# insert into vector1 (id, name, vec) values (4, 'name_004', array[1.0, 2.0]);
# insert into vector1 (id, name, vec) values (5, 'name_005', array[2.0, 2.0]);
# insert into vector1 (id, name, vec) values (6, 'name_006', array[3.0, 1.0]);
# insert into vector1 (id, name, vec) values (7, 'name_007', array[3.0, 2.0]);
# insert into vector1 (id, name, vec) values (8, 'name_008', array[1.0, 3.0]);
# insert into vector1 (id, name, vec) values (9, 'name_009', array[2.0, 3.0]);

検索

<->: 距離での検索

# select * from vector1 order by vec <-> '[1.5, 1.5]' limit 3;

 id |   name   |  vec
----+----------+-------
  3 | name_003 | [2,1]
  4 | name_004 | [1,2]
  5 | name_005 | [2,2]

<=>: コサインでの検索

#select * from vector1 order by vec <=> '[1.5, 1.5]' limit 3;

 id |   name   |  vec
----+----------+-------
  5 | name_005 | [2,2]
  7 | name_007 | [3,2]
  9 | name_009 | [2,3]

<#>: 内積 * -1での検索

select * from vector1 order by vec <#> '[1.5, 1.5]' limit 3;

 id |   name   |  vec
----+----------+-------
  7 | name_007 | [3,2]
  9 | name_009 | [2,3]
  6 | name_006 | [3,1]

条件付き検索

# select * from vector1 where id % 2 = 0 order by vec <-> '[1.5, 1.5]' limit 3;

 id |   name   |  vec
----+----------+-------
  4 | name_004 | [1,2]
  2 | name_002 | [1,0]
  6 | name_006 | [3,1]

PostgreSQL の pgvector のインストール

PostgreSQL の pgvector のインストール方法のメモ。

redhat-rpm-config のインストール

redhat-rpm-config がインストールされていない場合、以下で redhat-rpm-config をインストールする。

sudo yum install redhat-rpm-config

pgvector のインストール

ソースをダウンロード

git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git

環境変数の設定

export PATH="${PATH}:/usr/pgsql-17/bin"
export PG_CONFIG=/usr/pgsql-17/bin/pg_config

インストール

cd pgvector
make
sudo --preserve-env=PG_CONFIG make install

確認

ls /usr/pgsql-17/lib/bitcode

vector  vector.index.bc

参考

https://github.com/pgvector/pgvector/blob/master/README.md

PostgreSQL の pgxs を rpm でインストール

PostgreSQL の拡張構築基盤の pgxs を rpm でインストールする方法のメモ。

以下で postgresql17-devel をインストールしようとしたところ、perl(IPC::Run) が必要というエラーが出力されました。

sudo rpm install postgresql17-devel

Error:
 Problem: cannot install the best candidate for the job
  - nothing provides perl(IPC::Run) needed by postgresql17-devel-17.2-1PGDG.rhel9.x86_64 from pgdg17
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)

そこで、以下を参考に Perl(IPC::Run) をインストールしてから、postgresql17-devel をインストールします。

https://ossc-db.github.io/pg_bulkload/pg_bulkload-ja.html#install

Perl(IPC::Run) のインストール

sudo yum --enablerepo=crb install perl-IPC-Run

postgresql17-devel のインストール

sudo yum install postgresql17-devel

確認

ls /usr/pgsql-17/lib/pgxs
config  src

PostgreSQL 17 のインストール

Rocky Linux に PostgreSQL 17 をインストールする方法のメモ。

リポジトリ追加

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

インストール

sudo yum install postgresql17-server

初期化

sudo su -
postgresql-17-setup initdb

起動

sudo su -
systemctl start postgresql-17

停止

sudo su -
systemctl stop postgresql-17

ログイン・パスワード設定

sudo su - postgres
psql

\password
{パスワード入力}

\q

別ユーザでのログイン

psql -h {ホスト名} -p {ポート番号} -U {ロール名(ユーザ名)} -d {データベース名}