Tabel Clustered dan Asing di PostgreSQL

Nama saya Yakupov Azat. Saya seorang Arsitek Data, dan berikut adalah kelanjutan dari kisah tipe tabel di PostgreSQL. Pada bagian ini, kita akan membahas tentang tabel cluster dan tabel asing. Mari kita lihat contoh pembuatannya, area penerapannya, serta pro dan kontra penggunaannya.

Tabel Berkelompok di PostgreSQL

Di PostgreSQL, tabel berkerumun adalah tabel yang urutan fisik barisnya telah diubah agar sesuai dengan urutan baris dalam indeks. Hal ini berbeda dengan perilaku normal tabel, yang urutan fisik barisnya mungkin tidak sesuai dengan urutan baris yang ditentukan oleh indeks tertentu.

Hanya sedikit orang yang menyukai kekacauan, semua orang menyukai keteraturan. Dalam kerangka database relasional, konsep chaos terkait erat dengan penyimpanan informasi, karena sepanjang siklus hidupnya, sebuah tabel terus berubah.

Saat bekerja dengan DBMS di tingkat disk, isi tabel terus berubah. Misalnya, Anda telah memperbarui data dan baris yang diperbarui berada di halaman lain tabel (di sini kami harus menyebutkan FILLFACTOR) dengan tupel mati di posisi saat ini. Kemudian proses autovacuum menghapus tuple yang mati, dan slot yang kosong diisi dengan baris yang baru diterima.

Tes sederhana yang bisa Anda lakukan sendiri. Jadikan perintah berikut ke dalam tabel reguler yang baru dibuat:

INSERT INTO test(id,name) VALUES(1, ‘Peter’);
INSERT INTO test(id,name) VALUES(2, ‘Ivan’);
INSERT INTO test(id,name) VALUES(3, ‘Sergey’);

Setelah menjalankan kueri SQL (harap diperhatikan, tidak ada ORDER BY):

SELECT *
   FROM test;

Anda akan melihat gambar yang diharapkan:

Namun dengan melakukan update baris

UPDATE test
  SET name = ‘Ruslan’
WHERE id = 2;

dan kemudian dengan menjalankan SQL yang sama, Anda akan mendapatkan:

Urutan baris telah berubah! Entropi telah berkembang.

Sekarang bayangkan Anda sedang mencari data di tabel, misalnya nomor 4. Bagaimana Anda bisa melakukan ini dalam topologi hijau kacau yang saya gambar di kiri bawah? Hanya menelusuri catatan demi catatan: Anda secara tidak sengaja menyodok suatu angka dan membandingkannya dengan angka 4 yang diperlukan. Faktanya, Anda harus memeriksa semua entri, karena mungkin ada lebih dari satu angka 4. Dengan kata lain, diperlukan pemindaian berurutan.

Namun ketika Anda memiliki keteraturan, seperti pada tabel di sebelah kanan, Anda akan mengetahui dengan jelas bahwa angka 4 terletak di antara 3 dan 5. Itulah inti dari mengatur keteraturan dan tabel yang dikelompokkan: mereka membantu menciptakan struktur yang teratur dari kekacauan. Jika Anda secara acak memilih posisi acak dalam tabel berwarna biru untuk mencari angka 4, maka ada tiga kemungkinan hasil:

• Jumlahnya sama dengan yang diinginkan.
• Jumlahnya kurang dari yang diinginkan.
• Jumlahnya lebih besar dari yang diinginkan.

Hal ini memberikan keuntungan besar dalam kecepatan eksekusi pencarian. Jika angkanya lebih besar dari 4, Anda akan melanjutkan pencarian di tabel. Jika kurang, Anda akan turun. Atau Anda bisa mendapatkan rentang dan mencari nomor 4 di dalamnya. Ini jauh lebih cepat daripada menelusuri semua data, seperti yang terjadi pada topologi hijau tidak terorganisir, yaitu waktu logaritmik lebih cepat.

Perhatikan contoh pembuatan tabel berkerumun:

CREATE TABLE test.cluster_table
(id       INTEGER,
 name VARCHAR) WITH (FILLFACTOR = 90);

CREATE INDEX id_idx ON test.cluster_table (id);

CLUSTER [VERBOSE] test.cluster_table USING id_idx;

Di sini saya membuat tabel bernama cluster_table dan menetapkan nilai FILLFACTOR menjadi 90% — ini adalah persentase pengisian. Ini tidak mempengaruhi tabel cluster kami dengan cara apa pun, ini hanya contoh bagaimana Anda dapat mengatur properti saat membuat tabel jenis ini. Selanjutnya, saya membuat indeks BTree pada tabel (CREATE INDEX) di kolom id dan memanggil perintah CLUSTER. Perintah CLUSTER melakukan clustering pada tabel menggunakan indeks yang telah kita buat sebelumnya.

Penting untuk diketahui di sini bahwa hingga pengelompokan selesai, semua transaksi saat ini dalam tabel akan diblokir. Pemblokiran lalu lintas terjadi karena Postgres mencoba membangun kembali tabel sesuai urutan yang Anda perlukan berdasarkan indeks. Dan setelah membuat pesanan ini, Postgres harus menyimpannya ke file lain.

Sebenarnya, ini adalah operasi migrasi data tingkat disk dari satu file ke file lainnya, tetapi hanya dalam urutan yang ditentukan. Data harus ditempatkan berdasarkan indeks, dalam kasus kami berdasarkan bidang id. Saya telah menunjukkan ini secara grafis pada gambar di bawah dengan mengacu pada metadata sebelum dan sesudah pengelompokan tabel.

Awalnya tabel tersebut ditempatkan pada file dengan nomor 45969. Setelah perintah CLUSTER, nama file diubah. Data dipindahkan dari satu file ke file lainnya. Oleh karena itu, pemblokiran terjadi, dan karenanya, lalu lintas masuk tidak dapat menggunakan tabel ini sampai tersedia.

Anda juga dapat membuat indeks untuk pengelompokan berikutnya yang berisi banyak kolom (indeks multikolom), atau menentukan urutan menurun untuk kolom tertentu (DESC/ASC).

Anda juga dapat menggunakan perintah CLUSTER VERBOSE, yang akan mengembalikan rincian tentang apa yang dilakukan PostgreSQL, yaitu berapa banyak halaman yang ada, halaman mana yang dipindahkan, dan seterusnya.

Kasus Uji dan Urutan Data

Mari kita lakukan tes kecil:

CREATE TABLE test.cluster_table
(id       INTEGER,
 name VARCHAR) WITH (FILLFACTOR = 90);

CREATE INDEX id_idx ON test.cluster_table (id);

INSERT INTO test.cluster_table
  SELECT (random( )*100)::INTEGER, 'test'
     FROM generate_series(1,100) AS g(i);

SELECT id
   FROM test.cluster_table;

Mari kita buat tabel, indeks berdasarkan bidang id dan kemudian buat 100 baris sembarang menggunakan perintah generate_series. Hasilnya adalah data tidak berurutan:

Untuk mencapai keteraturan pada output, kita perlu menambahkan kata kunci ORDER BY. Namun di sini penting untuk diingat bahwa operasi ORDER BY juga memerlukan sumber daya dan Anda harus membayarnya. Setiap nanodetik berarti dengan lalu lintas beban tinggi, dan kemudian terjadi penyortiran.

Dalam hal ini, mari kita lakukan clustering pada tabel dengan perintah CLUSTER VERBOSE, menggunakan indeks yang telah saya buat sebelumnya:

CLUSTER VERBOSE test.cluster_table USING id_idx;

SELECT id
 FROM test.cluster_table;

Voila, data diurutkan tanpa diurutkan:

Tapi ada jebakan di sini. Mari kita perbarui semua baris — namun kenyataannya, itu cukup untuk mengubah nilai satu baris.

UPDATE test.cluster_table
   SET id = id * (random( )::INTEGER);

SELECT id
 FROM test.cluster_table;

Dalam hal ini, kekacauan akan kembali ke tabel cluster kami:

Untuk memulihkan pesanan, Anda perlu menjalankan perintah CLUSTER lagi. Anda bahkan tidak perlu menentukan indeks lagi, karena indeks tersebut disimpan dalam metadata PostgreSQL. Dan database akan memahami apa yang sedang Anda lakukan pengelompokan nanti.

CLUSTER VERBOSE test.cluster_table;

SELECT id
   FROM test.cluster_table;

Anda akan dapat mengamati pesanan itu lagi hanya setelah perintah CLUSTER. Ini adalah kelemahan tabel cluster: perubahan apa pun pada kunci clustering dapat segera menyebabkan kekacauan pada data.

Kapan Tabel Clustered Cocok

Tabel berkerumun cocok jika data Anda adalah tabel referensi (baik, atau SCD — Dimensi yang Berubah Secara Perlahan), misalnya, sistem alamat. Jenis tabel ini berguna jika Anda jarang mengunggah data baru, misalnya sebulan sekali.

Jika tabel sangat sering berubah dan tunduk pada operasi INSERT, UPDATE dan DELETE, maka tabel tersebut harus dikelompokkan terus-menerus, dan ini sangat tidak nyaman dan umumnya kritis. Tujuan pengelompokan adalah untuk menghindari ORDER BY yang tidak perlu dalam kueri konstan ke tabel berdasarkan bidang atau bidang yang berkerumun.

Metadata Tabel Berkelompok

Dari metadata tabel yang dikelompokkan, Anda dapat memahami bahwa tabel tersebut dikelompokkan:

SELECT  c.oid AS “OID”,
        c.relname AS “Relation name”
  FROM pg_class c INNER JOIN pg_index i 
                  ON i.indrelid = c.oid
WHERE c.relkind = ‘r’ AND 
      c.relhasindex AND 
      i.indisclustered;

Nilai “true” di kolom relhasindex menunjukkan bahwa terdapat indeks untuk mendukung pengelompokan. Saat kita membangun kembali cluster pada perintah CLUSTER berikutnya, PostgreSQL akan menggunakan indeks yang ditentukan dari metadata.

Tabel Asing di PostgreSQL

Tabel asing di PostgreSQL adalah tabel yang disimpan di luar database. Tabel ini dapat ditempatkan di server database lain atau di file, misalnya. Tabel asing berguna dalam hal mendapatkan data dengan cepat dari sumber lain, jika Anda memiliki kemampuan untuk menggabungkannya.

Selain itu, jika Anda mengutak-atiknya, Anda dapat menyediakan apa yang disebut siklus hidup data, untuk menyediakan metrik Kebijakan Retensi. Seperangkat alat berikut dapat membantu Anda di sini:

• LIHAT (tabel virtual).
• Sekumpulan tabel reguler yang dipisahkan berdasarkan logika retensi data (desain POOD) dengan data terkini.
• Tabel asing yang berfokus pada file yang menyimpan data di luar database pada disk yang lebih murah (di sini Anda akan menemukan data lama yang melebihi metrik Kebijakan Retensi).

Ada banyak tabel asing dan tipe koneksi, misalnya:

• berkas CSV.
• Koneksi dengan banyak RDBMS lainnya.
• Koneksi ke beberapa database NoSQL.

Mari kita lihat contoh tabel asing berdasarkan file CSV. Ekstensi file_fdw berdasarkan fdw — pembungkus data asing — akan membantu kita dalam hal ini:

CREATE EXTENSION file_fdw;

CREATE SERVER csv_log FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE test.csv (
  id   INTEGER,
  name VARCHAR
) SERVER csv_log 
  OPTIONS (filename '/var/lib/postgresql/file.csv', 
                   delimiter ‘;', format 'csv');

Saya membuat tabel asing dan menjelaskan atributnya, menentukan server untuk fdw, yang saya buat sebelumnya dengan opsi untuk bekerja dengan file tersebut.

Jika saya membuat kueri SQL ke tabel asing, saya akan melihat data yang disajikan dalam file tersebut. Karena tabel asing terdaftar (artinya ada entri di metadata PostgreSQL), saya punya hipotesis: apakah data disimpan bukan di file eksternal, tetapi di file data PostgreSQL?

SELECT  oid AS “OID”,
        pg_relation_filepath(oid) AS “File path”,
        pg_relation_size(oid) AS “Relation Size”  
 FROM pg_class
WHERE relname = ‘csv’;

Hasil eksekusi:

Jadi, tabel asing sebagai objek terdaftar di metadata (ada pengidentifikasi OID objek), tetapi tidak ada file data yang sesuai, artinya data hanya disajikan di sumber eksternal.

Pertanyaan ke Tabel Asing

Bagaimana cara kerja kueri ke tabel asing? Mari kita ambil file CSV sebagai contoh.

Saat data sedang dimuat, ada jeda waktu yang cukup lama, jadi kami menyimpan data lama di suatu tempat di disk lama. Untuk mendapatkan datanya, kita perlu membuka deskriptor file eksternal, lalu menyalin data tersebut ke memori atau ke file sementara dan mengembalikan data tersebut kepada kita. Jika kami menjalankan kembali permintaan yang sama beberapa saat kemudian, tidak akan ada percepatan apa pun: prosesnya tetap sama.

Ada banyak sekali perpustakaan tabel asing untuk berbagai kebutuhan. Misalnya, postgres_fdw. Dengan bantuannya, kita dapat terhubung ke PostgreSQL dari PostgreSQL. Ini sangat mirip dengan tautan basis data:

CREATE EXTENSION postgres_fdw;

DROP FOREIGN TABLE test.csv;

CREATE SERVER pg_log FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.56.10', port '5432', dbname ‘course_db');

CREATE USER MAPPING FOR test SERVER pg_log 
OPTIONS (user 'test', password 'test');

CREATE FOREIGN TABLE test.csv (
  id   INTEGER,
  name VARCHAR
) SERVER pg_log 
  OPTIONS (schema_name 'test', table_name ‘user');

Sejumlah besar "perpustakaan" tersedia untuk bekerja dengan sumber eksternal. Misalnya:

• Oracle, MySQL, SQLite, MS SQL Server, Sybase.
• Cassandra, MongoBD, HBase, Redis, Neo4j.
• Twitter, Telegram
• JSON, XLM, GeoFile, LDAP.

Metadata Tabel Asing

Seperti yang kami ketahui, tabel asing sebagai objek diperbaiki dalam metadata:

SELECT  oid AS "OID",
        relname AS “Relation name",
        CASE
         WHEN relpersistence = 'p' THEN 'Permanent'
         WHEN relpersistence = 't' THEN 'Temporary'
         ELSE 'Unlogged'
       END AS “Type”,
       relkind AS “Subtype”
 FROM pg_class
WHERE relname = ‘csv’;

Ini adalah tabel permanen (yang mengejutkan), tetapi memiliki pointer “f” yang merupakan subtipe dari relasi. Dan itu menandakan bahwa meja kita adalah orang asing, yaitu eksternal.

Di Seri Selanjutnya

Itu saja untuk hari ini. Dalam materi berikut kami akan menganalisis:

• Tabel yang dipartisi.
• Tabel yang diwarisi.