본문 바로가기
엑셈 경쟁력/DB 인사이드

DB 인사이드 | PostgreSQL 16 Release - New Feature (2)

by EXEM 2023. 11. 30.

이번 문서에서는 앞서 나열된 PostgreSQL 16 Release의 주요 변경내용 중 일부를 테스트한 내용을 기술합니다.

 

 

[성능 개선] FULL OUTER JOIN 및 RIGHT OUTER JOIN의 병렬 처리

PostgreSQL 16 버전부터 FULL OUTER JOIN 및 RIGHT OUTER JOIN의 병렬 처리를 지원함에 따라 Query Planner가 기존 버전 보다 더 효율적인 실행계획을 수립할 수 있습니다.

📢 병렬 처리는 enable_parallel_hash 파라미터로 제어할 수 있습니다. 
-- PostgreSQL 15 버전
EXPLAIN (COSTS OFF)
SELECT  COUNT(*) 
FROM    repltab a FULL OUTER JOIN repltab b USING (c1);
               QUERY PLAN
-----------------------------------------
 Aggregate
   ->  Hash Full Join
         Hash Cond: (a.c1 = b.c1)
         ->  Seq Scan on repltab a
         ->  Hash
               ->  Seq Scan on repltab b
-- PostgreSQL 16 버전
EXPLAIN (COSTS OFF)
SELECT  COUNT(*)
FROM    repltab a FULL OUTER JOIN repltab b USING (c1);
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Hash Full Join
                     Hash Cond: (a.c1 = b.c1)
                     ->  Parallel Index Only Scan using repltab_idx01 on repltab a
                     ->  Parallel Hash
                           ->  Parallel Index Only Scan using repltab_idx01 on repltab b

 

 

[성능 개선] 집계 함수의 병렬 처리

대용량 데이터에 대한 집계 함수(Aggregation Function)를 병렬 및 분산 방식으로 처리하여 쿼리 실행 속도를 높일 수 있습니다. 집계 함수에 대한 병렬 처리가 가능 함에 따라 Query Planner가 기존 버전 보다 더 효율적인 실행계획을 수립할 수 있습니다.

-- 테스트 데이터 생성
CREATE TABLE test001 ( c1 INTEGER , c2 TEXT , c3 INTEGER ) ;
INSERT INTO test001 
   SELECT t ,
          CASE mod( t , 4 ) WHEN 0 THEN 'A'
                            WHEN 1 THEN 'B'
                            WHEN 2 THEN 'C'
                            WHEN 3 THEN 'D'
          ELSE 'ZZZZ' END ,
          t % 7 
   FROM   generate_series( 1, 10000000 ) t ;
-- PostgreSQL 15 버전
                                   QUERY PLAN
---------------------------------------------------------------------------------
 HashAggregate  (cost=242301.54..242301.64 rows=7 width=68)
   Group Key: c3
   ->  Seq Scan on test001  (cost=0.00..161624.45 rows=10756945 width=9)
-- PostgreSQL 16 버전
EXPLAIN
SELECT  c3 ,
        string_agg( c2 , ',' ) AS a ,
        array_agg( c2 ) AS b
FROM    test001
GROUP BY c3 ;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=133491.28..133493.19 rows=7 width=68)
   Group Key: c3
   ->  Gather Merge  (cost=133491.28..133492.91 rows=14 width=68)
         Workers Planned: 2
         ->  Sort  (cost=132491.26..132491.27 rows=7 width=68)
               Sort Key: c3
               ->  Partial HashAggregate  (cost=132491.05..132491.16 rows=7 width=68)
                     Group Key: c3
                     ->  Parallel Seq Scan on test001  (cost=0.00..98875.60 rows=4482060 width=9)

동일 SQL에 대해 PostgreSQL 15와 16의 Cost를 비교 하면 PostgreSQL 16 버전의 최종 Cost는 133493.19이고 PostgreSQL 15 버전의 최종 Cost는 242301.64 입니다. 비용 측면에서 성능 개선이 있음을 확인 할 수 있습니다.

 

 

[사용 편의성] Subquery Alias 불필요

PostgreSQL 15 버전까지는 Subquery Alias를 지정하지 않으면 에러가 발생하였지만, PostgreSQL 16 버전부터는 Alias를 지정하지 않아도 에러가 발생하지 않습니다.

-- PostgreSQL 15 버전에서 Subquery Alias 미지정 시 에러
ERROR:  subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS] foo
-- PostgreSQL 15 버전 Syntax
SELECT *
FROM   ( 
         SELECT 1 AS num 
       ) a ;
-- PostgreSQL 16 버전 Syntax
SELECT *
FROM   ( 
         SELECT 1 AS num 
       ) ;

 

 

[사용 편의성] 숫자 형식의 천 단위 구분자 적용

PostgreSQL 16 버전부터 언더스코어(_)를 사용하여 숫자 형식을 천 단위로 구분하여 사용할 수 있습니다.

-- PostgreSQL 15 버전
SELECT 10_000 , 1_000_000 ;
ERROR:  trailing junk after numeric literal at or near "10_"
LINE 1: SELECT 10_000 , 1_000_000 ;
-- PostgreSQL 16 버전
SELECT 10_000 , 1_000_000 ;
 ?column? | ?column?
----------+----------
    10000 |  1000000

 

 

[사용 편의성] psql의 \bind 명령어 추가

psql의 \bind 명령어가 추가되어 매개변수를 포함한 쿼리 수행 가능합니다.

-- PostgreSQL 15 버전
SELECT $1::INTEGER + $2::INTEGER \bind 1 2 \g
invalid command \bind
Try \? for help.
-- PostgreSQL 16 버전
SELECT $1::INTEGER + $2::INTEGER \bind 1 2 \g
 ?column?
----------
        3
📢 16버전의 psql을 사용하여 PostgreSQL 15 버전에 접근할 경우 \bind 명령어를 사용할 수 있습니다.

 

 

[사용 편의성] libpq를 사용하는 Client의 Load Balancing

PostgreSQL 10 버전 부터 libpq를 사용하는 모든 Client는 여러 호스트를 연결할 수 있는 기능을 제공하였습니다.

psql 'host=10.10.45.240,10.10.45.241,10.10.45.242 port=5432,5433,5434 dbname=postgres user=postgres' -c '\conninfo'
You are connected to database "postgres" as user "postgres" on host "10.10.45.240" at port "5432".

psql 'host=10.10.45.240,10.10.45.241,10.10.45.242 port=5432,5433,5434 dbname=postgres user=postgres' -c '\conninfo'
You are connected to database "postgres" as user "postgres" on host "10.10.45.240" at port "5432".

이 예제에서 10.10.45.240:5432, 10.10.45.241:5433, 10.10.45.242:5434 순서로 연결 시도를 합니다.

PostgreSQL 16 버전 부터 load_balance_hosts 파라미터와 PGLOADBALANCEHOSTS 환경 변수가 생겼으며, 이를 통해 무작위 연결을 시도할 수 있습니다.

  • random : 나열된 연결 정보를 무작위로 연결 시도
  • disable : 기존 방식으로 연결 시도(순차적 연결)
psql 'load_balance_hosts=random host=10.10.45.240,10.10.45.241,10.10.45.242 port=5432,5433,5434 dbname=postgres user=postgres' -c '\conninfo'
You are connected to database "postgres" as user "postgres" on host "10.10.45.242" at port "5434".

psql 'load_balance_hosts=random host=10.10.45.240,10.10.45.241,10.10.45.242 port=5432,5433,5434 dbname=postgres user=postgres' -c '\conninfo'
You are connected to database "postgres" as user "postgres" on host "10.10.45.241" at port "5433".

더 가중치를 부여할 서버가 있는 경우 여러 번 나열하여 사용할 수 있습니다.

psql 'load_balance_hosts=random host=10.10.45.240,10.10.45.240,10.10.45.242 port=5432,5432,5434 dbname=postgres user=postgres' -c '\conninfo'
You are connected to database "postgres" as user "postgres" on host "10.10.45.240" at port "5432".

 

 

[Monitoring] last_seq_scan & last_idx_scan 컬럼 추가

PostgreSQL 16 버전에는 테이블의 Last Sequentail Scan및 Last Index Scan을 수행한 시간을 기록합니다. pg_stat_*_tables에는 last_seq_scan과 last_idx_scan 컬럼이 추가 되었고, pg_stat_*_indexes에는 last_idx_scan 컬럼이 추가되었습니다. 이 컬럼들은 오버헤드를 최소화 하기 위하여 트랜잭션 커밋 시에만 업데이트 됩니다. last_idx_scan 컬럼을 통해 불필요한 인덱스를 식별하고, last_seq_scan 컬럼을 통해 새 인덱스의 필요성을 식별하는데 도움을 받을 수 있습니다.

-- 테스트 데이터 생성
-- Table : test001 (3,000건)
-- Index : test001_idx01
EXPLAIN SELECT * FROM test001 WHERE c1 = 2 ;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on test001  (cost=4.30..14.14 rows=3 width=103)
   Recheck Cond: (c1 = 2)
   ->  Bitmap Index Scan on test001_idx01  (cost=0.00..4.30 rows=3 width=0)
         Index Cond: (c1 = 2)

SELECT * FROM test001 WHERE c1 = 2 ;
-- pg_stat_user_tables 조회
SELECT schemaname, relname, last_seq_scan, last_idx_scan
FROM   pg_stat_user_tables;

 schemaname | relname |         last_seq_scan         |         last_idx_scan
------------+---------+-------------------------------+-------------------------------
 public     | test001 | 2023-11-09 16:26:37.409146+09 | 2023-11-09 16:27:44.971963+09
-- pg_stat_user_indexes 조회
SELECT schemaname, relname, indexrelname, last_idx_scan
FROM   pg_stat_user_indexes;

 schemaname | relname | indexrelname  |         last_idx_scan
------------+---------+---------------+-------------------------------
 public     | test001 | test001_idx01 | 2023-11-09 16:27:44.971963+09

 

 

[Monitoring] pg_stat_io Catalog

PostgreSQL 15 버전까지 pg_stat_database, pg_statio_all_tables, pg_statio_all_indexes, pg_stat_bgwriter, pg_stat_statements 등의 Catalog를 통해 I/O 통계를 확인 및 분석 할 수 있었습니다. 하지만 위에 나열된 Catalog에서는 아래와 같은 이유로 정확한 I/O 측정을 할 수 없습니다.

  • Writes 포함 내용 (Writes = Flushes + extends)
  • Backend Type 별 Reads, Write 확인 불가 (모든 backend types을 통합해서 표시)
  • contexts(I/O 작업)와 object(저장 유형)에 따른 통계 확인 불가 (모든 contexts와 objects를 통합해서 표시)

PostgreSQL 16 버전에는 I/O Access Pattern을 세부적으로 분석할 수 있는 pg_stat_io 도입되었습니다. track_io_timing = on으로 설정하면 pg_stat_io System View를 통해 I/O 성능 문제를 추적하는데 용이합니다.

Column Description
backend_type Backend Type. (e.g. background worker, client backend, walsender, standalone backend, autovacuum worker, autovacuum launcher, background writer, startup, checkpointer)
object I/O 대상(저장 유형). relation, temp relation
context I/O 작업. normal, vacuum, bulkread, bulkwrite
reads Read 작업 수 (크기 bytes = reads * op_bytes)
read_time Read 작업 소요 시간(ms) → track_io_timing = on 설정 필요(off 경우 0)
writes Write 작업 수 (크기 bytes = writes * op_bytes)
write_time Write 작업 소요 시간(ms) → track_io_timing = on 설정 필요(off 경우 0)
writebacks 커널에서 OS로 데이터 보낸 수 (크기 bytes = wirtebacks * op_bytes)
writeback_time writeback 작업 소요 시간(ms) → track_io_timing = on 설정 필요(off 경우 0)
extends Relation의 확장 수 (크기 bytes = extends * op_bytes)
extend_time Relation의 확장 작업 소요 시간(ms) → track_io_timing = on 설정 필요(off 경우 0)
op_bytes I/O 단위 당 크기. block_size Parameter로 정해지며 기본 값 = 8192 (8k)
hits Shared Buffer에서 원하는 Block을 찾은 횟수
evictions 새로운 블록을 위해 공간 확보를 한 횟수
reuses  
fsyncs fsync 호출 횟수. normal context만 추적 가능
fsync_time fsync 호출 소요 시간(ms) → track_io_timing = on 설정 필요(off 경우 0)
stats_reset 통계가 마지막으로 재설정된 시간 
-- pg_stat_io 통계정보 Reset
SELECT pg_stat_reset_shared('io') ;
-- Test Table 생성
CREATE TABLE test01 ( c1 INTEGER , c2 TEXT ) ;
-- Test Data Insert
INSERT INTO test01 
    SELECT generate_series(1,10000) , md5(random()::text) ;
-- pg_stat_io 확인
SELECT backend_type , 
       object , 
       reads , 
       writes , 
       extends , 
       extend_time ,       -- track_io_timing = on 설정 필요. off 경우 0
       op_bytes
FROM   pg_stat_io
WHERE  backend_type = 'client backend'    
AND    object = 'relation' 
AND    context = 'normal' ;

backend_type  |object  |reads|writes|extends|extend_time|op_bytes|
--------------+--------+-----+------+-------+-----------+--------+
client backend|relation|    0|     0|     87|      1.076|    8192|

본 예시에서 test01 Table은 87번의 extends가 발생하였으며, 그 크기는 712,704 Byte(extends * op_bytes = 87 * 8192 = 712,704)입니다. 87번 extends 하면서 소요된 시간은 1.076ms 입니다. 실제 Table의 크기를 확인 해보면 아래와 같습니다.

SELECT pg_relation_size( 'test01' ) ;

pg_relation_size|
----------------+
          688128|

extends가 발생하여 계산한 크기와 실제 Table의 크기가 24,576 Byte 차이가 있는 것을 확인할 수 있습니다.(712,704 - 688,128 = 24,576) 이 차이는 Free Space Map(FSM)의 크기로 pg_stat_io의 extends로 계산된 크기는 FSM 크기까지 포함 되어 있습니다.

SELECT pg_relation_filepath( 'test01' ) ;

pg_relation_filepath|
--------------------+
base/5/16423        |
$ ls -al $PGDATA/base/5 | grep 16423
-rw------- 1 postgres postgres 688128 10월 12 17:47 16423
-rw------- 1 postgres postgres  24576 10월 12 17:47 16423_fsm

추가적으로 pg_stat_io를 통해서 Cache Hit Ratio를 확인 할 수 있으며, 이를 통해 shared_buffer의 크기를 조정하는데 사용할 수 있습니다. Cache Hit Ratio는 pg_stat_database Catalog를 통해서도 확인 할 수 있지만 pg_stat_io와의 큰 차이는 Backend Type별로 확인이 가능한지 여부입니다.

SELECT ( SUM( blks_hit ) / ( SUM( blks_read ) + SUM( blks_hit ) )::float ) * 100 AS hit_ratio
FROM   pg_stat_database ;
     hit_ratio
-------------------
 69.29611464023785
SELECT backend_type,
       object,
       context,
       ( hits / NULLIF( ( reads + hits ) ,	0 )::float ) * 100 AS hit_ratio
FROM   pg_stat_io 
--WHERE  backend_type = 'client backend'
--AND    object = 'relation'
--AND    context = 'normal' 
;
    backend_type     |    object     |  context  |     hit_ratio
---------------------+---------------+-----------+-------------------
 autovacuum launcher | relation      | bulkread  |
 autovacuum launcher | relation      | normal    | 97.82608695652173
 autovacuum worker   | relation      | bulkread  |
 autovacuum worker   | relation      | normal    | 99.99135303400419
 autovacuum worker   | relation      | vacuum    |               100
 client backend      | relation      | bulkread  |
 client backend      | relation      | bulkwrite |
 client backend      | relation      | normal    |  98.6307997484155
 client backend      | relation      | vacuum    |

 

 

[보안] 권한 부여를 위한 WITH ADMIN OPTION

PostgreSQL 16 버전부터는 CREATEROLE 속성(Attribute)이 있어도 WITH ADMIN OPTION 없이는 권한을 부여할 수 없습니다. PostgreSQL 15 버전까지는 CREATEROLE 권한이 있으면 권한 부여하는 것이 가능했습니다.

postgres=# CREATE ROLE test001 PASSWORD 'test001' CREATEROLE LOGIN ;
CREATE ROLE

postgres=# \du
                              List of roles
  Role name  |                         Attributes
-------------+------------------------------------------------------------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS
 test001     | Create role

postgres=# \c postgres test001
You are now connected to database "postgres" as user "test001".

postgres=> CREATE ROLE test002 PASSWORD 'test002' LOGIN ;
CREATE ROLE

postgres=# \du
                              List of roles
  Role name  |                         Attributes
-------------+------------------------------------------------------------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS
 test001     | Create role
 test002     |
-- PostgreSQL 15 버전까지는 권한 부여 가능
postgres=> GRANT pg_checkpoint TO test002 ;
GRANT ROLE
-- PostgreSQL 16 버전부터는 권한 부여 불가능
postgres=> GRANT pg_checkpoint TO test002 ;
ERROR:  permission denied to grant role "pg_checkpoint"
DETAIL:  Only roles with the ADMIN option on role "pg_checkpoint" may grant this role.

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

postgres=# GRANT pg_checkpoint TO test001 WITH ADMIN OPTION ;
GRANT ROLE

postgres=# \c postgres test001

postgres=> GRANT pg_checkpoint TO test002 ;
GRANT ROLE

 

 

 

 

 

글 | DB기술기획팀

 

 

 

 

 

저작자표시 비영리 변경금지

'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글

DB 인사이드 | PostgreSQL Extension - Introduction  (0) 2024.04.18
DB 인사이드 | PostgreSQL 16 Release - New Feature (3)  (2) 2023.12.27
DB 인사이드 | PostgreSQL 16 Release - New Feature (1)  (2) 2023.11.30
DB 인사이드 | PostgreSQL Extension - PG_STAT_MONITOR  (0) 2023.09.21
DB 인사이드 | PostgreSQL HOT - 3. Fillfactor와 HOT Update  (0) 2023.08.31

관련글

댓글

티스토리툴바