Berikut ini beberapa pertanyaan untuk memulai:
- Bisakah rentang berisi celah nilai?
- Mengapa kita membutuhkan notasi indeks untuk tipe json?
- Bisakah indeks tumbuh kurang dari tabel dengan pembaruan yang sering? Dan tidak tumbuh sama sekali?
- Berapa lama sesi menganggur di idle_in_transaction?
- Bagaimana membangun diagram ER untuk tabel katalog sistem?
Aplikasi klien
psql: \ dX - lihat statistik tambahan
komit: ad600bba
Perintah baru \ dX menampilkan objek statistik tambahan sebagai daftar.
CREATE STATISTICS flights_from_to (dependencies, MCV)
ON departure_airport, arrival_airport
FROM flights;
\x
\dX
List of extended statistics -[ RECORD 1 ]+------------------------------------------------ Schema | bookings Name | flights_from_to Definition | departure_airport, arrival_airport FROM flights Ndistinct | Dependencies | defined MCV | defined
Untuk setiap jenis statistik (Dependensi, Ndistinct, MCV), hanya fakta pengumpulan yang ditampilkan. Nilainya sendiri perlu dilihat di pg_statistic_ext_data, yang, secara default, hanya dapat diakses oleh superusers.
psql: \ dtS menunjukkan tabel TOAST
komit: 7d80441d
Tabel TOAST terpisah dapat dilihat dengan perintah \ d sebelumnya. Namun, tidak mungkin mendapatkan daftar tabel seperti itu dengan perintah \ dt atau \ dtS. Kelalaian telah diperbaiki, \ dtS sekarang menampilkan tabel TOAST karena merupakan tabel layanan.
Tapi ada satu peringatan. Semua tabel TOAST terletak dalam skema pg_toast, yang tidak mungkin disertakan dalam search_path . Oleh karena itu, untuk mendapatkan daftarnya, Anda perlu menentukan template yang sesuai:
\dtS pg_toast.*165*
List of relations Schema | Name | Type | Owner ----------+----------------+-------------+---------- pg_toast | pg_toast_16529 | TOAST table | postgres pg_toast | pg_toast_16539 | TOAST table | postgres pg_toast | pg_toast_16580 | TOAST table | postgres
Penyelesaian tab Psql ditingkatkan untuk perintah CLOSE, FETCH, MOVE dan DECLARE
komit: 3f238b88 Tidak
diperlukan deskripsi tambahan.
Dokumentasi
Documentation proofreading dan editing
commit: 2a5862f0
Banyak orang memperhatikan bahwa PostgreSQL memiliki dokumentasi yang sangat baik. Tapi itu ditulis oleh pengembangnya sendiri, yang biasanya tidak dianggap sebagai ahli pena. Bagaimana Anda mengelola untuk menjaga kualitas tinggi? Itu mudah. Seperti halnya tulisan apa pun, Anda membutuhkan editor dan korektor. Jadi Justin Prizzby telah melakukan pekerjaan besar dan penting selama dua tahun terakhir: mengoreksi dokumentasi. Hasilnya adalah daftar besar 18 tambalan. Dan Mikael Paquier, sebagai seorang pelaku, membantunya.
Dan itu hanya satu komitmen besar. Dan jumlah tambalan kecil yang meningkatkan dokumentasi tidak dapat dihitung.
bermacam-macam
Idle_session_timeout parameter - kekuatan pemutusan sesi menganggur
komit: 9877374b The
baru idle_session_timeout parameter menentukan batas waktu sesi menganggur. Jika batas waktu yang ditetapkan terlampaui, sesi akan diakhiri. Parameternya sangat mirip dengan idle_in_transaction_session_timeout ,yang muncul di 9.6 , tetapi hanya memengaruhi sesi di mana tidak ada transaksi yang dimulai. Oleh karena itu, jika Anda ingin menghentikan sesi idle, terlepas dari apakah transaksi dimulai di dalamnya atau tidak, kedua parameter tersebut harus disetel.
Disarankan agar parameter ini digunakan dengan sangat hati-hati pada sistem yang menggunakan penarik koneksi atau koneksi postgres_fdw.
Parameter dapat disetel oleh pengguna mana pun untuk sesinya. Dalam contoh berikut, setelah mengatur parameter dan menunggu sebentar, kita melihat bahwa entri penghentian sesi muncul di log server. Setelah itu, upaya untuk menjalankan permintaan gagal, tetapi psql secara otomatis membuat koneksi baru:
SET idle_session_timeout = '1000ms';
--
\! tail -n 1 logfile
2021-02-01 12:25:06.716 MSK [5262] FATAL: terminating connection due to idle-session timeout
SHOW idle_session_timeout;
FATAL: terminating connection due to idle-session timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
SHOW idle_session_timeout;
idle_session_timeout ---------------------- 0
Deskripsi oleh depesz.
Informasi GSS di log server message
commit: dc11f31a
Pesan koneksi sesi baru di log server telah diperbarui dengan informasi GSS jika metode autentikasi ini digunakan.
pageinspect: fungsi untuk indeks GiST
commit: 756ab291
Bagi siapa pun yang tertarik untuk menjelajahi organisasi dan penyimpanan indeks GiST, ekstensi pageinspect menawarkan fitur baru .
Memperbaiki perilaku JELASKAN dalam perintah dengan JIKA TIDAK ADA
commit: e665769e
Upaya untuk membuat tabel yang ada dengan opsi JIKA TIDAK ADA menghasilkan peringatan:
CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
Namun, mendapatkan rencana untuk perintah semacam itu mengarah pada hasil yang tidak terduga. Hanya saja EXPLAIN mengeluarkan rencana untuk SELECT, yang berhasil dibuat oleh perintah sebelum memeriksa keberadaan tabel tiket. Dan tidak ada peringatan!
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
QUERY PLAN ----------------------------------------------------------------- Seq Scan on bookings (cost=0.00..4301.88 rows=262788 width=21)
JELASKAN ANALYZE gagal alih-alih peringatan:
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
ERROR: relation "tickets" already exists
Di versi 14, perilaku menjadi dapat diprediksi:
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
Perubahan yang sama untuk perintah JELASKAN [ANALISIS] BUAT TAMPILAN MATERIALISASI JIKA TIDAK ADA.
Menambahkan kunci utama dan unik ke tabel katalog sistem
komit: dfb75e47 , 62f34097
Menambahkan batasan integritas ke tabel katalog sistem: kunci utama dan unik. Sebelumnya ada indeks unik, sekarang pembatasan dibuat berdasarkan indeks tersebut.
Beginilah tampilannya:
\d pg_class
Table "pg_catalog.pg_class" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- oid | oid | | not null | relname | name | | not null | relnamespace | oid | | not null | ... ... Indexes: "pg_class_oid_index" PRIMARY KEY, btree (oid) "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace) "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
Tetapi ada pengecualian: pg_depend, pg_shdepend. Kedua tabel ini memiliki dua indeks dan keduanya tidak unik. Tidak mungkin menghasilkan kombinasi kolom yang unik. Saya harus mengakui bahwa memahami cara kerja tabel ketergantungan bukanlah tugas yang sepele. Dan ketidakmampuan untuk membuat kunci utama adalah konfirmasi yang jelas tentang ini.
Tetapi kunci asing tidak ditambahkan. Ada alasan bagus untuk ini:
- Untuk sejumlah tabel, kolom oid bisa menjadi 0 jika tidak ada OID yang sebenarnya untuk dirujuk. Untuk membuat kunci asing, Anda perlu mengganti 0 dengan NULL di mana-mana, dan ini adalah pekerjaan penulisan ulang yang besar yang belum siap Anda lakukan.
- Sejumlah tabel memiliki kolom dengan kunci asing potensial bukan hanya tipe oid, tapi oid []. Tidak mungkin membuat kunci asing dari larik.
Ide awal dari patch ini adalah untuk membangun diagram hubungan antar tabel dalam katalog sistem berdasarkan informasi dari database. Ini dapat dilakukan secara otomatis oleh alat eksternal. Lagi pula, membuat diagram tanpa informasi tentang kunci asing hanya dapat dilakukan secara manual dan dengan perubahan rutin setelah setiap rilis, yang sangat merepotkan.
Menyadari bahwa tugas patch belum selesai, segera setelah komit, Tom Lane memulai diskusi baru di mana ia mengusulkan solusi kompromi dalam bentuk fungsi yang mengembalikan daftar kunci asing untuk semua tabel di katalog sistem. Patch diadopsi setelah penutupan commitfest bulan Januari, tetapi lebih logis untuk mendeskripsikannya sekarang.
Jadi, tabel katalog sistem tidak memiliki kunci asing. Tapi kita bisa mendapatkan informasi tentang mereka dengan memanggil fungsi pg_get_catalog_foreign_keys. Kueri berikut menunjukkan siapa yang merujuk ke pg_attribute:
SELECT fktable, fkcols, is_array, is_opt
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_attribute'::regclass
AND pkcols = ARRAY['attrelid','attnum'];
fktable | fkcols | is_array | is_opt ----------------------+-----------------------+----------+-------- pg_attrdef | {adrelid,adnum} | f | f pg_constraint | {conrelid,conkey} | t | t pg_constraint | {confrelid,confkey} | t | f pg_index | {indrelid,indkey} | t | t pg_statistic_ext | {stxrelid,stxkeys} | t | f pg_statistic | {starelid,staattnum} | f | f pg_trigger | {tgrelid,tgattr} | t | f pg_partitioned_table | {partrelid,partattrs} | t | t (8 rows)
Monitoring
Parameter log_recovery_conflict_waits - logging menunggu lama untuk menyelesaikan konflik pemulihan
komit: 0650ff23
Ketika parameter log_recovery_conflict_waits baru diaktifkan , menunggu resolusi konflik pemulihan dengan proses startup akan dicatat ke log server jika batas waktu melebihi deadlock_timeout.
Mari kita simulasikan situasinya. Di replika, aktifkan parameter, lalu mulai transaksi dan tunggu:
ALTER SYSTEM SET log_recovery_conflict_waits = on;
SELECT pg_reload_conf();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
Dan sekarang di master:
DELETE FROM t;
VACUUM t;
Setelah satu detik ( deadlock_timeout ), pesan mendetail yang menjelaskan konflik akan muncul di log replika. Berikut adalah jumlah proses yang berkonflik, dan posisi LSN di mana mereka terjebak, dan nama file (tabel baca) dengan nomor blok:
LOG: recovery still waiting after 1023.267 ms: recovery conflict on snapshot DETAIL: Conflicting process: 29119. CONTEXT: WAL redo at 0/1001BEB0 for Heap2/CLEAN: latestRemovedXid 717; blkref #0: rel 1663/16384/17198, blk 0
Setelah 30 detik menunggu ( max_standby_streaming_delay ), sesi pada replika akan dihentikan, sebagaimana mestinya dalam kasus seperti itu.
Ini adalah kelanjutan dari pekerjaan yang dimulai dan dijelaskan di commitfest sebelumnya.
Pg_stat_database view - statistik tambahan pada sesi pengguna
commit: 960869da
Pengembang sistem monitoring memiliki lebih banyak pekerjaan yang harus dilakukan. Perubahan yang bermanfaat dan keren menunggu pengguna sistem pemantauan!
Banyak kolom telah muncul di pg_stat_database dengan informasi tambahan tentang sesi pengguna di setiap database di cluster:
- session_time - total waktu dari semua sesi yang dihabiskan dalam database ini;
- active_time ― , ;
- idle_in_transaction_time ― ;
- sessions ― ;
- sessions_abandoned ― - ;
- sessions_fatal ― - FATAL;
- sessions_killed ― .
Deskripsi oleh depesz.
ps: memperbarui status proses ketika checkpoint dijalankan
komit: df9274ad Anda dapat
memantau proses startup dan checkpointer dengan menanyakan pg_stat_activity. Tetapi ada tiga situasi di mana tampilan pg_stat_activity tidak tersedia dan checkpointer berfungsi. Ini adalah titik pemeriksaan di akhir proses pemulihan kerusakan, titik pemeriksaan selama penghentian server, dan titik mulai ulang selama penghentian replika.
Dalam tiga situasi ini, Anda dapat memantau status proses startup dan checkpointer di sistem operasi, misalnya, menggunakan utilitas ps.
Contoh tipikal adalah pemulihan bencana. Pada akhirnya, setelah mengubah perubahan dari WAL, proses startup melakukan checkpoint dan ini mungkin memakan waktu. Namun, status proses startup tidak berubah dan menampilkan "memulihkan NNN". Meskipun akan berguna untuk mengetahui bahwa roll-forward perubahan selesai dan tetap menunggu penyelesaian checkpoint. Status sekarang diperbarui untuk menurunkan tingkat siaga DBA dalam keadaan darurat.
pg_stat_statements: Ketika statistik
komit dihapuskan : 2e0fedf0
Tidak ada keraguan bahwa statistik pg_stat_statements harus dihapus secara teratur. Jika tidak, apa gunanya mengumpulkan informasi tentang permintaan yang dieksekusi kemarin, seminggu yang lalu, sebulan, setahun ...
Tetapi bagaimana Anda tahu kapan statistik terakhir kali disetel ulang? Sangat sederhana. Kami melihat ke pg_stat_statements_info:
SELECT now(), pg_stat_statements_reset();
now | pg_stat_statements_reset -------------------------------+-------------------------- 2021-02-03 13:25:44.738188+03 |
SELECT * FROM pg_stat_statements_info;
dealloc | stats_reset ---------+------------------------------- 0 | 2021-02-03 13:25:44.738468+03
Tampilan pg_stat_statements_info diperkenalkan pada versi 14. Anda dapat membaca tentang kolom dealloc di artikel sebelumnya.
Deskripsi oleh depesz.
Yang lebih berguna adalah gagasan untuk tidak hanya membuang statistik secara teratur, tetapi juga menyimpan konten pg_stat_statements sebelum setiap dump. Kemudian, dengan adanya banyak irisan yang dilakukan secara teratur, dimungkinkan untuk memperoleh informasi untuk interval waktu di masa lalu. Pendekatan ini digunakan oleh ekstensi pemantauan pgpro_pwr .
COPY
melakukan kemajuan : 8a4f618e
Kelompok tampilan pg_stat_progress_ * telah diperbarui! Sekarang Anda dapat memantau kemajuan perintah COPY.
Mari buat salinan logis dari database demo:
\! pg_dump -d demo -Fc -f demo.dump
Sekarang mari kita perluas salinan dalam database postgres menjadi dua utas dan, sementara proses sedang berlangsung, lihat tampilan pg_stat_progress_copy:
\! pg_restore tickets.dump -d postgres -j 2 &
SELECT pid, datname, relid::regclass, bytes_processed, bytes_total, lines_processed
FROM pg_stat_progress_copy\gx
-[ RECORD 1 ]---+------------------------- pid | 18771 datname | postgres relid | bookings.tickets bytes_processed | 19088527 bytes_total | 0 lines_processed | 189820 -[ RECORD 2 ]---+------------------------- pid | 18772 datname | postgres relid | bookings.boarding_passes bytes_processed | 14833287 bytes_total | 0 lines_processed | 567652
Kolom bytes_total akan terisi dengan ukuran file ketika perintah COPY… FROM 'file' dijalankan. Tetapi pada contoh di atas, unduhannya dari salinan pg_dump, jadi ukurannya tidak diketahui.
Status dapat dipantau tidak hanya untuk download (COPY… FROM), tetapi juga untuk mendownload (COPY… TO) data.
Deskripsi oleh depesz.
Performa
Optimalisasi pembilasan cache buffer
commit: d6ad34f3 , bea449c6
Sejumlah operasi mengharuskan semua buffer yang terkait dengan tabel tertentu dihapus dari cache buffer. Operasi ini termasuk perintah tabel TRUNCATE dan DROP, perintah CREATE TABLE AS SELECT yang terputus, dan VACUUM ketika blok kosong perlu dihapus dari ujung tabel.
Untuk menghapus, seluruh cache penyangga dipindai, yang bisa menjadi mahal dengan ukuran cache yang besar. Sekarang untuk tabel kecil, struktur khusus akan dipertahankan dalam memori dengan informasi tentang buffer yang ditempati, yang akan menghindari pemindaian seluruh cache buffer.
Pengujian telah menunjukkan bahwa dengan shared_buffers sebesar 100 GB atau lebih, memotong ribuan tabel lebih dari 100 kali lebih cepat.
Ini adalah kelanjutan dari pekerjaan yang dimulai pada versi 13.
postgres_fdw: mode batch untuk memasukkan catatan
komit: b663a413 Mengambil
data dari tabel eksternal postgres_fdw menggunakan mode batch. Record ditransfer dari server eksternal dalam kelompok 100 (nilai default dari parameter fetch_size ). Ini jauh lebih cepat daripada mengirimnya satu per satu. Tapi masukkan, ubah, hapus pekerjaan baris demi baris. Dan karena itu sangat lambat.
FDW API telah ditingkatkan untuk pengoptimalan. Benar, itu ditingkatkan hanya di bagian dari mode batch untuk operasi penyisipan. Pembaruan dan penghapusan untuk referensi di masa mendatang. Tentu saja, postgres_fdw adalah pembungkus pertama yang memanfaatkan API baru.
Mari kita lihat apa yang terjadi. Konfigurasikan postgres_fdw untuk bekerja dengan tabel eksternal di database demo:
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR postgres
SERVER remote_server
OPTIONS (user 'postgres');
Tabel eksternal akan ditempatkan di database postgres tetangga:
postgres=# CREATE TABLE bookings(
book_ref char(6),book_date timestamptz, total_amount numeric(10,2)
);
Mari kita ambil kecepatan penyisipan ke dalam tabel lokal sebagai kecepatan referensi. Dan aktifkan pengaturan waktu untuk pengukuran:
CREATE TABLE bookings_local (LIKE bookings);
\timing
Masukkan ke dalam tabel lokal:
INSERT INTO bookings_local SELECT * FROM bookings;
INSERT 0 262788 Time: 165,653 ms
Dan sekarang masukkan ke dalam tabel eksternal, mode batch dinonaktifkan. (Sebenarnya itu diaktifkan, hanya secara default ukuran batch adalah 1 baris).
CREATE FOREIGN TABLE bookings_remote_no_batch (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings'
);
INSERT INTO bookings_remote_no_batch SELECT * FROM bookings;
INSERT 0 262788 Time: 6729,867 ms (00:06,730)
Hampir 40 kali lebih lambat! Dan ini di antara basis cluster yang sama, di mana tidak ada penundaan jaringan.
Mari ulangi percobaan, tetapi atur ukuran batch (batch_size) menjadi 100.
CREATE FOREIGN TABLE bookings_remote_batch_100 (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings',
batch_size '100'
);
INSERT INTO bookings_remote_batch_100 SELECT * FROM bookings;
INSERT 0 262788 Time: 679,632 ms
Masalah lain. Tentu saja, kerugian penyisipan lokal masih terlihat, ~ 4 kali lipat, tetapi tetap tidak 40!
Dan akhirnya. Ukuran batch untuk penyisipan (batch_size) dapat diatur dalam parameter server eksternal, kemudian akan valid untuk semua tabel yang tidak ditentukan secara eksplisit.
Menghapus baris indeks dari
komit bawah ke atas : 9dc718bd , d168b666
Pengoptimalan ini mencoba untuk menghindari pembagian halaman indeks menjadi dua dalam operasi UPDATE hingga yang terakhir, dalam situasi di mana kolom indeks tidak berubah. Sebelum menambahkan versi baru dari baris ke indeks, Anda perlu melihat apakah Anda dapat menghapus baris yang tidak perlu di halaman ini. Misalnya, jika Anda menemukan rangkaian baris indeks duplikat yang tidak perlu yang mereferensikan baris tabel yang sama, Anda dapat menghapus baris tersebut. Peter Geigan, penulis tambalan, menyebut ini "penghapusan dari bawah ke atas".
Masalah serupa (untuk menghindari pertumbuhan indeks) diselesaikan dengan optimasi pembaruan HOT. Jika UPDATE tidak mengubah kolom yang diindeks, versi baru dari baris di indeks tidak dapat dibuat. Dan jika ada beberapa indeks di atas tabel, dan kolom salah satunya berubah? Dalam hal ini, pembaruan HOT bukanlah asisten.
Mari kita periksa apa yang bisa dilakukan "hapus dari bawah ke atas". Untuk percobaan, mari kita ambil tabel dengan dua kolom yang diindeks terpisah dan pembersihan otomatis dinonaktifkan.
CREATE TABLE t(col1 int, col2 int) WITH (autovacuum_enabled=off);
CREATE INDEX t_col1 ON t(col1);
CREATE INDEX t_col2 ON t(col2);
INSERT INTO t VALUES (1, 1);
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size --------+-------------+------------- 8192 | 16384 | 16384
Ada satu baris di tabel sebelum pembaruan massal. Ukuran tabel satu halaman, dan kedua indeks menempati dua halaman (halaman layanan + halaman data).
Sekarang kita hanya mengubah satu kolom col2 100.000 kali dan melihat ukuran tabel dan indeks.
SELECT 'UPDATE t SET col2 = col2+1' FROM generate_series(1,100000)\gexec
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 2121728 | 2260992
Hasil ini diperoleh di PostgreSQL 12. Seperti yang Anda lihat, pembaruan HOT tidak berfungsi dan kedua indeks tumbuh hampir sama ukurannya.
Sekarang percobaan yang sama di PostgreSQL 13:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 663552 | 2260992
Indeks t_col1, di mana tidak ada perubahan, meningkat jauh lebih sedikit, ~ 3,5 kali. Ini adalah hasil dari pengoptimalan versi 13 yang terkenal: deduplikasi indeks . Tapi bagaimanapun dia tumbuh dewasa.
Dan terakhir, mari kita lihat apa yang ada di PostgreSQL 14:
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 16384 | 2260992
Astaga! Di indeks t_col1, hanya ada satu halaman dengan data tersisa. Itu keren!
Tentu saja, jika autovacuum dihidupkan, mungkin ada waktu untuk menyelesaikan beberapa batas waktu selama percobaan. Tapi untuk itulah eksperimen itu. Selain itu, dalam kondisi nyata, dengan pembaruan yang sering (contoh yang bagus adalah tabel antrian), autovacuum pasti tidak akan punya waktu untuk membersihkan semuanya tepat waktu.
Deskripsi dari Viktor Egorov.
Eksekusi paralel REINDEX CONCURRENTLY
commit: f9900df5
Pada artikel commitfest November, saya sudah menulis tentang eksekusi paralel non-blocking dari CREATE INDEX CONCURRENTLY. Pengoptimalan serupa sekarang tersedia untuk REINDEX SECARA SEKARANG.
Bahasa prosedural
Prosedur lebih cepat untuk mengeksekusi
komit: ee895a65
Prosedur disusun untuk dapat menyelesaikan transaksi. Jika prosedur yang menjalankan COMMIT itu sendiri dipanggil berkali-kali, misalnya, dalam satu perulangan, maka pada setiap iterasi perulangan, semua pernyataan di dalam prosedur akan diurai ulang.
Tidak ada alasan serius untuk mengurai ulang perintah, yang dihilangkan di tambalan. Sekarang prosedur pemanggilan dalam satu loop membutuhkan lebih sedikit pekerjaan dan sumber daya. Dan hasilnya, itu berjalan lebih cepat.
PL / pgSQL: operator penugasan yang sepenuhnya didesain ulang
komit: 844fe9f1 , c9d52984 , 1788828d , 1c1cbe27
Tanpa basa-basi lagi:
DO $$
<<local>>
DECLARE
a bookings[];
x bookings;
BEGIN
/* */
local.a[1].book_ref := 'ABCDEF';
local.a[1].book_date := current_date;
local.a[1].total_amount := 0;
/* */
local.a[2:3] := (SELECT array_agg(t.*)
FROM (SELECT b.* FROM bookings b LIMIT 2) AS t
);
FOREACH x IN ARRAY a LOOP
RAISE NOTICE '%', x;
END LOOP;
END;
$$;
NOTICE: (ABCDEF,"2021-02-04 00:00:00+03",0.00) NOTICE: (00000F,"2017-07-05 03:12:00+03",265700.00) NOTICE: (000012,"2017-07-14 09:02:00+03",37900.00) DO
Sekarang, di dalam blok PL / pgSQL, Anda dapat menetapkan nilai ke elemen array dari tipe komposit, serta irisan array.
Untuk tujuan ini, operator penugasan PL / pgSQL telah didesain ulang sepenuhnya. Dan parser server telah belajar mengurai ekspresi PL / pgSQL.
Untuk mengevaluasi ekspresi, Anda tidak perlu lagi mengeluarkan perintah seperti
«
SELECT expr
»
. Anda dapat dengan mudah memverifikasi ini dengan melihat pesan kesalahan pada contoh berikut:
DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$;
ERROR: invalid input syntax for type integer: "a" LINE 1: 2 + 'a' ^ QUERY: 2 + 'a' CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
Kata SELECT tidak lagi ada di baris QUERY.
Replikasi
Penanganan replika dari perubahan parameter konfigurasi di wizard
commit: 15251c0a
Perubahan wizard ke pengaturan yang mempengaruhi ukuran memori bersama server tidak luput dari perhatian pada replika fisik. Ketika catatan WAL tiba di replika yang menyatakan bahwa parameter ini telah berubah, replika akan secara otomatis berhenti, menghentikan semua sesi saat ini. Daftar parameter dapat ditemukan di dokumentasi .
Ini tidak terlalu bagus. Oleh karena itu, kami melakukan ini: menerima data perubahan parameter, replika terus bekerja, tetapi menghentikan replikasi dan mengeluarkan pesan ke log. Administrator dapat menunggu sesi penting berakhir, memperbarui parameter dalam file konfigurasi agar sesuai dengan nilai pada master, dan membatalkan jeda replikasi. Replika akan berhenti segera setelah itu, tetapi karena perubahan konfigurasi telah dilakukan, replika dapat segera dimulai, meminimalkan waktu henti.
Ubah restore_command tanpa me-restart server
commit: 942305a3
Kelanjutan pekerjaan Sergei Kornilov, diadopsi dalam versi 13. Kemudian menjadi mungkin untuk mengubah parameter tanpa me-restart server primary_conninfo , primary_slot_name dan wal_receiver_create_temp_slot .
Sekarang restore_command telah ditambahkan ke mereka .
Server
Peningkatan penggunaan
komit statistik yang diperluas : 25a9e54d
Statistik yang diperluas sekarang digunakan dalam lebih banyak kasus untuk menilai kardinalitas kondisi dalam kueri. Secara khusus, statistik yang diperluas sekarang akan digunakan saat kondisi berbeda yang dengannya statistik yang diperluas dapat digunakan secara individual digabungkan melalui ATAU.
Dalam contoh, kami akan mengumpulkan statistik tambahan untuk bandara keberangkatan dan kedatangan. Dan kemudian kami akan menghitung jumlah penerbangan antara Sheremetyevo dan Pulkovo atau ke arah sebaliknya.
CREATE STATISTICS s ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;
Jumlah pasti penerbangan adalah 610. Bandingkan dengan perkiraan perencana di versi 13 dan 14.
EXPLAIN SELECT *
FROM flights
WHERE (departure_airport = 'SVO' AND arrival_airport = 'LED')
OR (departure_airport = 'LED' AND arrival_airport = 'SVO');
PostgreSQL 13:
Seq Scan on flights (cost=0.00..1054.42 rows=885 width=63)
PostgreSQL 14:
Seq Scan on flights (cost=0.00..1054.42 rows=607 width=63)
Seperti yang Anda lihat, perkiraan di versi 14 hampir akurat.
Infrastruktur umum untuk mendukung notasi indeks untuk semua tipe data
komit: c7aba7c1 , 0ec5f7e7 , 676887a3
Notasi indeks digunakan untuk bekerja dengan array. Misalnya, mari cari elemen dengan indeks 3:
SELECT (ARRAY[10,20,30,40,50])[3];
array ------- 30
Tetapi ada tipe data lain di mana sintaks ini akan lebih nyaman digunakan. Pertama-tama, kita berbicara tentang json. Dengan gagasan untuk mendukung notasi indeks untuk json, perjalanan panjang Dmitry Dolgov pada tambalan ini dimulai.
Dan sekarang, beberapa tahun kemudian, dukungan seperti itu muncul. Patch pertama membuat infrastruktur notasi indeks yang diperlukan untuk tipe data arbitrer. Patch kedua menambahkan notasi indeks ke tipe hstore, dan yang ketiga ke tipe json b .
Sekarang, alih-alih fungsi dan operator khusus, Anda dapat mengekstrak bagian yang diperlukan dari nilai json. Temukan nomor telepon di detail kontak salah satu tiket:
SELECT contact_data, contact_data['phone'] AS phone
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]+----------------------------------------------------------------------------------------------------------------------------------------------------------- contact_data | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"} phone | "+70844502960"
Notasi indeks juga dapat digunakan untuk menulis ke jsonb. Mari tambahkan alamat ke kontak Irina Antonova yang ditemukan sebelumnya:
UPDATE tickets
SET contact_data['address'] =
'{"city": "",
"street": " ",
"building": "7"
}'::jsonb
WHERE ticket_no = '0005432000994';
Perhatikan bahwa alamat itu sendiri adalah gabungan dan Anda juga dapat menggunakan notasi indeks untuk merujuk ke bagiannya:
SELECT contact_data['address'] AS address,
contact_data['address']['city'] AS city,
contact_data['address']['street'] AS street,
contact_data['address']['building'] AS building,
contact_data['phone'] AS phone,
contact_data['email'] AS email
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]---------------------------------------------------------------- address | {"city": "", "street": " ", "building": "7"} city | "" street | " " building | "7" phone | "+70844502960" email | "antonova.irina04121972@postgrespro.ru"
Sangat nyaman!
(Klarifikasi. Semua kontak dalam database demo adalah fiktif dan tidak ada karyawan seperti itu di Postgres Pro.)
Deskripsi untuk hstore dari depesz.
Perintah SQL
Tipe data multirange
commit: 6df7a969
Setiap tipe data rentang sekarang memiliki tipe data multirange sendiri. Jenis ini pada dasarnya adalah larik rentang individu. Rentang dalam jenis multi-band tidak boleh tumpang tindih, tetapi mungkin ada celah di antara rentang.
Rentang reguler adalah rentang nilai yang berdekatan dari subtipe yang sesuai: rentang in4range untuk subtipe int, rentang timestamptz untuk subtipe cap waktu, dll. Tetapi bagaimana jika Anda perlu menyimpan rentang dengan celah di beberapa tempat? Di sinilah multi-band datang untuk menyelamatkan.
Katakanlah kita ingin menyimpan waktu commitfest di tabel untuk setiap versi PostgreSQL. Sebuah komitmen tunggal dapat dianggap sebagai rentang sebulan penuh. Tapi bagaimana cara merepresentasikan kelima commitfests dari satu versi?
Rentang untuk subtipe timestamptz disebut tstzrange dan multirange adalah tstzmultirange. Jenis yang tersedia dijelaskan dalam dokumentasi . Buat tabel:
CREATE TABLE pg_commitfest (
version text,
working_period tstzmultirange
);
Untuk membentuk nilai, kami menggunakan konstruktor:
INSERT INTO pg_commitfest VALUES
('13', tstzmultirange(
tstzrange('2019-07-01', '2019-08-01', '[)'),
tstzrange('2019-09-01', '2019-10-01', '[)'),
tstzrange('2019-11-01', '2019-12-01', '[)'),
tstzrange('2020-01-01', '2020-02-01', '[)'),
tstzrange('2020-03-01', '2020-04-07', '[]')
)
),
('14', tstzmultirange(
tstzrange('2020-07-01', '2020-08-01', '[)'),
tstzrange('2020-09-01', '2020-10-01', '[)'),
tstzrange('2020-11-01', '2020-12-01', '[)'),
tstzrange('2021-01-01', '2021-02-01', '[)'),
tstzrange('2021-03-01', '2021-04-01', '[)')
)
);
Daftar fungsi dan operator untuk bekerja dengan jenis multi-band mencakup sama seperti untuk band normal, ditambah yang ditujukan untuk multi-band saja.
Misalnya, kami dapat mengetahui versi PostgreSQL mana yang dikerjakan komunitas pengembang selama Tahun Baru yang lalu:
SELECT version
FROM pg_commitfest
WHERE working_period @> '2021-01-01'::timestamptz;
version --------- 14
Atau tanggal mulai dan berakhir untuk bekerja pada versi 13:
SELECT lower(working_period), upper(working_period)
FROM pg_commitfest
WHERE version = '13';
lower | upper ------------------------+------------------------ 2019-07-01 00:00:00+03 | 2020-04-07 00:00:00+03
Jenis multi-rentang kustom baru dapat dibuat. Ini berguna jika tidak ada band built-in dan jenis multi-band yang sesuai. Perintah CREATE TYPE… AS RANGE yang sama digunakan, di mana Anda dapat menentukan nama untuk tipe multi-rentang yang dibuat secara otomatis.
Misalnya, kami tertarik pada rentang dan banyak rentang waktu, waktu subtipe. Untuk membuat rentang, Anda memerlukan fungsi yang menghitung perbedaan antara dua nilai tipe waktu:
CREATE FUNCTION time_diff(a time, b time) RETURNS double precision
AS $$
SELECT extract(epoch FROM (a - b));
$$ LANGUAGE sql STRICT IMMUTABLE;
Kami membuat tipe untuk rentang waktu, dan pada saat yang sama untuk multi-rentang:
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_diff,
multirange_type_name = timemultirange
);
Sekarang waktu kerja dapat dibentuk oleh ekspresi berikut:
SELECT timemultirange(
timerange('09:00', '13:00', '[)'),
timerange('14:00', '18:00', '[)')
) AS working_hours;
working_hours ------------------------------------------- {[09:00:00,13:00:00),[14:00:00,18:00:00)}
Deskripsi oleh depesz.
Fungsi ltrim dan rtrim untuk string biner
komit: a6cf3df4 Anda
juga bisa memangkas byte di awal dan akhir string bytea pada saat yang sama menggunakan fungsi btrim. Anda sekarang dapat memangkas setiap tepi secara terpisah dengan fungsi ltrim dan rtrim baru untuk string biner.
GRANTED BY frase dalam perintah GRANT dan REVOKE
komit: 6aaaa76b
Untuk kompatibilitas dengan standar SQL dalam perintah GRANT dan REVOKE ditambahkan frase opsional GRANTED BY. Sebagai contoh:
GRANT ALL ON TABLE table_name TO role_specification
[GRANTED BY role_specification];
REVOKE ALL ON TABLE table_name FROM role_specification
[GRANTED BY role_specification];
Nama peran di GRANTED BY harus sesuai dengan peran saat ini. Jadi tidak akan berhasil untuk mengeluarkan / mengambil hak atas nama peran lain. Frasa tersebut telah ditambahkan untuk memenuhi standar.
Ini adalah kelanjutan dari pekerjaan yang dijelaskan di artikel commitfest September.
Sistem administrasi
initdb --no-petunjuk
komit: e09155bd
Utilitas initdb digunakan untuk menginisialisasi cluster. Dan di akhir pekerjaannya, ini menampilkan instruksi bagaimana memulai sebuah cluster:
Success. You can now start the database server using: pg_ctl -D /usr/local/pgsql/data -l logfile start
Tapi ini tidak selalu benar. Misalnya, dalam distribusi paket debian, utilitas pg_ctlcluster digunakan untuk memulai cluster, bukan pg_ctl. Dan parameternya berbeda.
Dengan opsi --no-instruksi baru, initdb tidak akan lagi menyediakan saran startup, yang dapat dimanfaatkan oleh distribusi paket.
pg_dump: pulihkan satu partisi sebagai tabel mandiri
komit: 9a4c0e36 , 9eabfe30
Jika tabel yang dipartisi disertakan dalam salinan logis pg_dump, maka tidak mungkin memulihkan partisi terpisah dari salinan seperti tabel independen. Segera setelah perintah CREATE TABLE muncul perintah ALTER TABLE… ATTACH PARTITION, yang tidak hanya tidak diperlukan dalam situasi seperti itu, tetapi juga diakhiri dengan kesalahan. kami tidak memulihkan tabel induk.
CREATE TABLE t (id int) PARTITION BY RANGE(id);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (1) TO (1001);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (1001) TO (2001);
\! pg_dump -Fc -f db.dump
\! pg_restore db.dump -t t_p1 -f -
... CREATE TABLE public.t_p1 ( id integer ); ALTER TABLE ONLY public.t ATTACH PARTITION public.t_p1 FOR VALUES FROM (1) TO (1001); ...
Sekarang ALTER TABLE… ATTACH PARTITION perintah untuk semua partisi dibongkar secara terpisah dan setelah semua perintah untuk membuat partisi CREATE TABLE. Oleh karena itu, saat memulihkan satu partisi yang ditentukan dengan opsi -t, hanya perintah CREATE TABLE yang akan dijalankan, yang memungkinkan untuk memulihkan partisi sebagai tabel independen.
Itu saja untuk saat ini. Kami menunggu commitfest terakhir pada tanggal 14 Maret .