PostgreSQL 11 İle Gelen Yenilikler

Günce Kaya

guncekaya.blogspot.com

PGDay Türkiye

22 Eylül 2018

PostgreSQL 11 Release Notes

https://www.postgresql.org/docs/11/static/release-11.html

İçerik

Partitioning
- Hash Partition
- Partition Key Güncellemesi
- Partition Tablolarında Index
- Default Partition
- Partition Tablolarında Unique Key ve Foreign Key
- PartitionWise Query Planning
Include Indexes
Arttırılmış Paralellik
Default Roles
WAL Size

6. Backup and Streaming Replication

Logical Replication'da TRUNCATE
Base Backup - Unlogged ve Temporary Tablolar
Base Backup ile Replication Slot Oluşturulması

7. Alter Table Add Column

8. SHA-2 Fonksiyonları

9. Stored Procedure

10. Group Access on Data Folder

11. Insert On Conflict Do Nothing

12. Pg_stat_statement İyileştirmesi

PARTITION

pic: www.fotolia.com

HASH PARTITION

Hash değere göre verileri partition tablolarına yerleştirir.
MODULUS, REMAINDER

pic: https://www.fotolia.com

pic: http://www.bakayelektronik.com

HASH PARTITION

CREATE TABLE <partitioned_table>
(col1 data_type,
...,
...)
PARTITION BY HASH(<hash_key>);


CREATE TABLE <partition_table>
PARTITION OF <partitioned_table>
FOR VALUES WITH (MODULUS X, REMAINDER Y);

hash_key = sütun adı

HASH PARTITION

CREATE TABLE ->

CREATE PARTITION -> TABLES

INSERT 10000 ->

rows

[pg11] # create table part_t1 
partition of part_t for values with (modulus 3, remainder 0);
CREATE TABLE
[pg11] # create table part_t2 
partition of part_t for values with (modulus 3, remainder 1);
CREATE TABLE
[pg11] # create table part_t3 
partition of part_t for values with (modulus 3, remainder 2);
CREATE TABLE

[pg11] # create table part_t(date_ date) 
partition by hash (date_);
CREATE TABLE

[pg11] # insert into part_t 
            select i from 
            generate_Series('20180101',
                            '20190202',
                            '1 hour'::interval
                            ) as i;
INSERT 0 9529

HASH PARTITION

[pg11] # select count(*) from part_t1;
 count 
-------
  2904
(1 row)

[pg11] # select count(*) from part_t2;
 count 
-------
  3144
(1 row)

[pg11] # select count(*) from part_t3;
 count 
-------
  3481
(1 row)

PARTITION KEY GÜNCELLENMESİ

PG10'da sadece partition içi veri güncellemesi
PG11'de partition tabloları arasında verilerin güncellenmesi

PARTITION TABLOLARINDA INDEX

PG10'da manuel index yapılıyordu
PG11'de partitioned table -> partition table
- CREATE INDEX
- DROP INDEX
Partition tablolarına ait index oluşturulabilir.
Aynı isimde index varsa!
Lock seviyesi -> ShareLock!
Partition tablolarında otomatik oluşturulmuş indexler o tablo için manuel silinemez.

DEFAULT PARTITION

Partition kriterlerinin dışında kalanlar
Diğer partitionlardan önce yaratıldıysa DETACH!
- Partition tablolarını oluştur.
- ATTACH Default Partition.
HASH partitionlarla kullanılmaz.

PARTITION TABLOLARDA UNIQUE KEY VE FOREIGN KEY

PG10'da Partition tablosu üzerinde UNIQUE KEY oluşturulamazdı.
PG11 ile Partitionlar arası UNIQUE KEY
- Mevcut/oluşturulacak child tablolarda otomatik oluşturulur.
Foreign Key desteği geldi!

ENABLE_PARTITIONWISE_AGGREGATE

Default off
Query Planner'i her bir partition tablosunun AGGREGATE yapması için değiştirebiliriz.
Partition tablolarında GRUPLAMA ve AGGREGATION işlemlerinin ayrı ayrı yapılılr.
Gruplama partition key'e göre yapılır!
Daha performanslı
Daha fazla IO ve Memory

ENABLE_PARTITIONWISE_AGGREGATE

SET ENABLE_PARTITIONWISE_AGGREGATE=OFF

SET ENABLE_PARTITIONWISE_AGGREGATE=ON

ENABLE_PARTITIONWISE_JOIN

Default off
Partitioned tablolar arasındaki JOIN, partition keyleri eşleşen partition tablolarının JOIN yapılmasıyla gerçekleşir!
JOIN koşulunda tüm partition key'lerinin olması gerekir!
- Partition key'lerin veri tipi aynı olmalı.
Daha fazla IO ve Memory

SET ENABLE_PARTITIONWISE_JOIN_AGGREGATE=ON

INCLUDE INDEXES

Non-key alanlarıyla birlikte index-only scan
Sadece Index-only scan
Sadece B-tree Index
Index-only scan non-key sütun değerini index tablosuna uğramadan döndürür.
Sorgunun tüm sütunları için INDEX oluşturmak
- Gereksiz Disk kullanımı
- Silinen/güncellenen satırlar için daha çok VACUUM!

INCLUDE INDEXES

CREATE UNIQUE INDEX id_idx ON products_2
USING btree (id) INCLUDE (p_status, date);

p_status ve date sütunlarını içeren sorgular index-only kullanmaya devam edecekler.
ROUND gibi fonksiyonlar INCLUDE ile kullanılamaz.

=# CREATE UNIQUE INDEX new_unique_idx_2 
ON new_example(a, b) INCLUDE (round(c));
ERROR:  0A000: expressions are not supported in 
included columns

ARTTIRILMIŞ PARALELLİK

[pg11] # EXPLAIN CREATE MATERIALIZED VIEW mv1 
AS SELECT COUNT(*) FROM products;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=255070.26..255070.27 rows=1 width=8)
   ->  Gather  (cost=255070.05..255070.26 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=254070.05..254070.06 rows=1 width=8)
               ->  Parallel Append  (cost=0.00..233226.32 rows=8337489 width=0)
                     ->  Parallel Seq Scan on products_p2  (cost=0.00..63928.14 rows=2782714 width=0)
                     ->  Parallel Seq Scan on products_p1  (cost=0.00..63809.85 rows=2777585 width=0)
                     ->  Parallel Seq Scan on products_p3  (cost=0.00..63800.90 rows=2777190 width=0)
(8 rows)

CREATE TABLE AS SELECT
CREATE MATERIALIZED VIEW

ARTTIRILMIŞ PARALELLİK

CREATE INDEX
- max_parallel_maintenance_workers en az 2 olmalı
UNION ifadesi kullanılan scriptler

[pg11] # EXPLAIN 
SELECT max(id) INTO val FROM products;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=255070.26..255070.27 rows=1 width=4)
   ->  Gather  (cost=255070.05..255070.26 rows=2 width=4)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=254070.05..254070.06 rows=1 width=4)
               ->  Parallel Append  (cost=0.00..233226.32 rows=8337489 width=4)
                     ->  Parallel Seq Scan on products_p2  (cost=0.00..63928.14 rows=2782714 width=4)
                     ->  Parallel Seq Scan on products_p1  (cost=0.00..63809.85 rows=2777585 width=4)
                     ->  Parallel Seq Scan on products_p3  (cost=0.00..63800.90 rows=2777190 width=4)
(8 rows)

SELECT INTO

DEFAULT ROLES

pg_read_server_files
pg_write_server_files
pg_execute_server_program

Superuser olmayan kullanıcıların sunucu üzerinde işlem yapmasını sağlar.

GRANT pg_execute_server_program TO admin_user;

DEFAULT ROLES

[pg11] # \du gunce 
                  List of roles
 Role name | Attributes |        Member of        
-----------+------------+-------------------------
 gunce     |            | {}

[pg11] # select current_user;
 current_user 
--------------
 gunce
(1 row)

[pg11] # copy(select 1 ) to '/tmp/copytest.csv';
ERROR:  42501: must be superuser or a member of the 
pg_write_server_files role to COPY to a file

[pg11] # GRANT pg_write_server_files to gunce ;
GRANT ROLE

[pg11] # copy(select 1 ) to '/home/guncek/copytest.csv';
ERROR:  42501: could not open file "/home/guncek/copytest.csv" 
        for writing: Permission denied
HINT:  COPY TO instructs the PostgreSQL server process to write 
        a file. You may want a client-side facility such as psql's \copy.
LOCATION:  BeginCopyTo, copy.c:1866
[pg11] # copy(select 1 ) to '/var/lib/pgsql/copytest.csv';
COPY 1

DEFAULT ROLES

[pg11] # \du postgres 
List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}

[pg11] # select current_user;
 current_user 
--------------
 postgres
(1 row)

[pg11] # copy(select 1 ) to '/home/guncek/copytest.csv';
ERROR:  42501: could not open file 
    "/home/guncek/copytest.csv" for writing: Permission denied
HINT:  COPY TO instructs the PostgreSQL server process to write 
a file. You may want a client-side facility such as psql's \copy.
LOCATION:  BeginCopyTo, copy.c:1866

WAL-SIZE

Default WAL size 16MB
2'nin 0 ile 10'uncu kuvvetleri arasında 2'nin herhangi bir kuvveti olabilir.

initdb -D /pg_data --wal-segsize=32

Transaction sayısı yüksek veritabanlarında daha çok WAL dosyası oluşturulur.
Disk üzerinde daha fazla write işlemi

SHOW wal_segment_size;

/usr/pgsql-11/bin/pg_controldata | grep "Bytes per WAL segment"

WAL-SIZE

Sunucu üzerinde daha az WAL trafiği
- archive_command çağırılma sıklığının azaltılması
Çok büyük veritabanları için avantaj sağlarken, küçük veritabanları için dezavantaj sağlayabilir.

WAL-SIZE

[postgres] # show wal_segment_size ;
 wal_segment_size 
------------------
 64MB
(1 row)

[postgres@asus-guncek ~]$ /usr/pgsql-11/bin/pg_ctl 
                            -D /var/lib/pgsql/11-3/data/ stop

[postgres@asus-guncek ~]$ /usr/pgsql-11/bin/pg_resetwal 
                            -D /var/lib/pgsql/11-3/data/ 
                            --wal-segsize=32
Write-ahead log reset

[postgres@asus-guncek ~]$ /usr/pgsql-11/bin/pg_ctl 
                            -D /var/lib/pgsql/11-3/data/ start


[postgres] # show wal_segment_size ;
 wal_segment_size 
------------------
 32MB
(1 row)

WAL-SIZE

archive_command -> cp, scp, rsync

16MB'lik 100 adet dosya

64MB'lik 25 adet dosya

WAL-SIZE

16MB'lik dosyaların SCP ile aktarımı -> 24sn

WAL-SIZE

64MB'lik dosyaların SCP ile aktarımı -> 8sn

WAL-SIZE

16MB'lik dosyaların RSYNC ile aktarımı -> 3dk24sn

64MB'lik dosyaların RSYNC ile aktarımı -> 14sn

BASE BACKUP AND STREAMING REPLICATION

LOGICAL REPLICATION’da TRUNCATE

PG11'de Logical Replication ile TRUNCATE ifadesini kullanabiliyoruz.
PG10'da TRUNCATE yerine DELETE kullanmak zorundaydık.
- Daha fazla VACUUM
- Daha fazla IO

BASEBACKUP VE UNLOGGED - TEMPOPARY TABLOLAR

BASEBACKUP ILE UNLOGGED VE TEMPOPARY TABLOLAR

UNLOGGED tablolar veri tutar WAL dosyalarına yazılmaz.
- Standby'a replike olmazlar!
- Performans amaçlı kullanılır.
- Restore işleminde sadece tablo TRUNCATE edilir.
Temporary tablolar ve ayrıca pg_internal.init dosyası da çıkarıldı!

ALTER TABLE ADD COLUMN

Bir tabloya nasıl sütun eklenir?
- Tablo baştan yazılır
- Tablodaki INDEXler de baştan yazılır
- Sonuç: Oldukça maliyetli...

ALTER TABLE ADD COLUMN

pg_attribute tablosunda iki yeni sütun
- attmissingval
- atthasmissing
Ciddi oranda azalan lock süresi
Index'lerin yeniden diske yazılması önlenir

ALTER TABLE ADD COLUMN

PG10

PG11

ALTER TABLE ADD COLUMN

PG10 daki tablonun boyutu

PG11 deki tablonun boyutu

ALTER TABLE ADD COLUMN

add_col tablosunun "g" sütunun pg_attribute tablosundaki görüntüsü

SHA-2 FONKSİYONLARI

sha224(), sha256(), sha384(), sha512()

[postgres] # SELECT LENGTH(sha224('test')),sha224('a');
 length |                           sha224                           
--------+------------------------------------------------------------
     28 | \xabd37534c7d9a2efb9465de931cd7055ffdb8879563ae98078d6d6d5
(1 row)

[postgres] # SELECT LENGTH(sha256('test')),sha256('a');
 length |                               sha256                               
--------+--------------------------------------------------------------------
     32 | \xca978112ca1bbdcafac231b39a23dc4da786eff8147c4e72b9807785afee48bb
(1 row)

[postgres] # SELECT LENGTH(sha384('test')),sha384('a');
 length |                                               sha384                                               
--------+----------------------------------------------------------------------------------------------------
     48 | \x54a59b9f22b0b80880d8427e548b7c23abd873486e1f035dce9cd697e85175033caa88e6d57bc35efae0b5afd3145f31
(1 row)

[postgres] # SELECT LENGTH(sha512('test')),sha512('a');
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------
length | 64
sha512 | \x1f40fc92da241694750979ee6cf582f2d5d7d28e18335de05abc54d0560e0f5302860c652bf08d560252aa5e74210546f369fbbbce8c12cfc7957b2652fe9a75

[postgres] #

STORED PROCEDURE

CREATE PROCEDURE
CALL <PROCEDURENAME>

STORED PROCEDURE

Transactional Control
- Cursor Loop
- COMMIT/ROLLBACK birlikte

GROUP ACCESS on DATA FOLDER

PG10
- Dizin yetkisi 600
- Dosya yetkisi 700

-g veya --allow-group-access

PG11(default PG10 ile aynı)
- Dizin yetkisi 640
- Dosya yetkisi 750

GROUP ACCESS on DATA FOLDER

Amaç
- Cluster sahibi olmayan kullanıcıların backup processlerini gerçekleştirmesi
Write yetkisine sahip olmayan kullanıcılar backup alabilir

/usr/pgsql-11/bin/initdb -g -D /var/lib/pgsql/11-2/data

BASEBACKUP İLE REPLICATION SLOT OLUŞTURULMASI

--create-slot
replication slot adı (--slot)
WAL streaming method (--wal-method=stream)

[postgres@asus-guncek ~]$ /usr/pgsql-11/bin/pg_basebackup 
        -D /var/lib/pgsql/11-3/data/ -p5411 -h 192.168.122.100
        --create-slot --slot='test'

[postgres] # SELECT slot_name, slot_type, restart_lsn 
             FROM pg_replication_slots;
 slot_name | slot_type | restart_lsn 
-----------+-----------+-------------
 test      | physical  | 3/6E000000
(1 row)

pg_basebackup: could not send replication command 
"CREATE_REPLICATION_SLOT "test" PHYSICAL RESERVE_WAL": 
ERROR:  replication slot "test" already exists

DİĞER GELİŞTİRMELER

INSERT ON CONFLICT DO NOTHING

PG10

PG11

PG_STAT_STATEMENTS

Queryid alanı -> 32bit'ten 64bit olarak güncellendi

..Ve Diğerleri

Partition tablolarında TRIGGER desteği
Partition Foreign tablolarında postgres_fdw için push down aggregate özelliği geldi.
SP-GIST(Space partitioned) için yeni TEXT prefix operator -> ^@
- LIKE 'kelime%' ile aynı
- Daha etkili

VACUUM veya ANALYZE ifadelerinin birden fazla tablo için çalıştırılması
pg_verify_checksums
- Veritabanı offline durumundayken checksum yapar
Diğer fonksiyonlar;
- websearch_to_tsquery()
- json(b)_to_tsvector()

..Ve Diğerleri

Teşekkürler

Günce Kaya

guncekaya.blogspot.com

twitter: @gguncesi