PostgreSQL adalah oper source relation database system yang sangat powerful. PostGreSQL sudah lebih dari 15 tahun aktif dalam pengembangannya dan arsitektur yang dibangunnyapun memiliki reputasi yang bagus, handal, lengkap, dan akurat. PostGreSQL dapat berjalan di semua sistem operasi yang ada, termasuk Linux, Unix (AIX, BSd, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), dan windows. PostGreSQL mendukung penuh foreign keys, joins, views, triggers, dan stored procedures. PostgreSQL memiliki hampir semua data type SQL92 dan SQL99, termasuk integer, numeric, boolean, char, varchar, data, interval, dan timestamp. PostGreSQL juga memiliki kemampuan menyimpan objek binary yang cukup besar, termasuk gambar, suara, dan video. Selain itu postgre memiliki native programming interface untuk C/C++, Java, Perl, Python, Ruby, Tcl, ODBC, dll.
PostgreSQL membanggakan fitur-fiturnya yang mutakhir, contohnya Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, dan write ahead logging for fault tolerance. Postgre juga mendukung international character sets, multibyte character encodings, Unicode, and locale-aware for sorting, case-sensitivity, and formatting. Selain itu, PostGreSQL dapat diukur baik kualitas datanya maupun jumlah concurent user yang dapat ditangani. Ada beberapa sistem Postgre yang aktif dalam lingkuangan produksi yang memanage data sebesar 4 terabytes.
| Limit | Value |
| Maximum Database Size | Unlimited |
| Maximum Table Size | 32 TB |
| Maximum Row Size | 1.6 TB |
| Maximum Field Size | 1 GB |
| Maximum Rows per Table | Unlimited |
| Maximum Columns per Table | 250 - 1600 depending on column types |
| Maximum Indexes per Table | Unlimited |
PostgreSQL telah mendapatkan pujian dari para penggunanya serta pengakuan dari dunia industri, termasuk Linux New Media Award for Best Database System dan tiga kali memenangkan The Linux Journal Editors' Choice Award for best DBMS.
Fitur
PostgreSQL memiliki standart-standart yang harus dipenuhi. Implementasi SQL berdasarkan standar ANSI-SQL 92/99. Postgre support untuk subqueries (including subselects in the FROM clause), read-committed and serializable transaction isolation levels. Selain memiliki fully relational system catalog yang mensupport multiple skema per database, catalog juga dapat diakses melalui Information Schema seperti pada standar SQL.
Data integrity meliputi include (compound) primary keys, foreign keys with restricting and cascading updates/deletes, check constraints, unique constraints, dan bukan null constraints.
Postgre memiliki host extension dan advance fitur.
- GiST (Generalized Search Tree) indexing adalah advance sistem yang membawa array dalam jumalah besar dengan sorting dan searching argoritma yang berbeda termasuk B-tree, B+-tree, R-tree, partial sum trees, ranked B+-trees dan masih banyak lagi.
- GiST menyediakan pondasi untuk banyak public project yang menggunakan POstGreSQl seperti OpenFTS dan POstGIS. OpenFTS (Open Source Full Text Search engine) menyediakan indexing data secara online dan relevansi rangking untuk satavase searching. PosGIS adalah project yang support untuk geografik objek di PostGreSQL, memungkinkan untuk digunakan sebagai spatial database untuk geographic information systems (GIS).
- Fitur lainnya adalah table inheritance, a rules systems, and database events.
- Table inheritance object oriented slant on table creation,allowing database designers untuk membuat table baru dari table lain.
- Peraturan dalam sistem ini juga disebut query rewrite system, memperbolehkan designer membuat peraturan untuk operasi yang spesifik untuk tabel yang mereka buat dan juga secara dinamik mentransformasikan ke alternatif operasi yang lain saat mereka di proses.
Highly Customizable
PostgreSQL menyediakan prosedur penyimpanan di lebih dari satu lusin bahasa pemrograman, termasuk Java, Perl, Python, Ruby, Tcl, C/C++, and miliknya sendiri PL/pgSQL,yang mirip dengan oracle PL/SQL.
Trigger dan prosedur penyimpanan dapat ditulis dalam bahasa C dan di-load ke dalam database sebagai library, memungkinkan fleksibilitas yang tinggi dalam mengembangakan kemampuannya. PostgreSQL memiliki framework yg mengizinkan developers untuk mendefinisikan dan membuat custom data type mereka sendiri dengan fungsi dan operasi pendukung. Sebagai hasilnya, host of advanced data types telah banyak dibuat dengan range mulai dari geometric sampai spatial primitives juga network addresses bahkan ISBN/ISSN (International Standard Book Number/International Standard Serial Number) data types, semua ini secara opsional dapat ditambahkan ke dalam sistem ini.
Seperti halnya banyak procedure languages didukung oleh PostgreSQL, ada banyak library interface juga. Interface tersebut untuk Java (JDBC), ODBC, Perl, Python, Ruby, C, C++, PHP, Lisp, Scheme, dll.
Yang terbaik adalah, Source dari kode PostGreSQl ini tersedia secara open source. Ini membuat pengguna menjadi bebeas untuk menggunakan, memodifikasi. PostGreSQL tidak hanya dapat digunakan daam perusahaan tapi juga di tumah, web, komersial produk yang membutuhkan RDBMS.
INDEXING
Ketika kita mengakses tabel, PosgreSQL normalnya membaca dari awal ke akhir table, mencari untuk row yang relevan. Dengan sebuah index, maka akan dengan cepat untuk mencari nilai index tersebut, lalu langsung menemukan row yang tepat.
Misalnya, anggap query SELECT * FROM customer WHERE col = 43. Tanpa sebuah index, PostgreSQL harus mencari di seluruh tabel untuk mencari rows dimana col sama dengan 43. Dengan sebuah index pada col, PostgreSQL dapat langsung menuju ke baris dimana col sama dengan 43, melewati (bypass) rows yang lain.
Secara internal, PostgreSQL menyimpan data dalam operating system files. Masing-masing tabel mempunyai file-nya sendiri dan data rows disimpan dalam file tersebut. Sebuah index merupakan file terpisah yang diurutkan oleh satu atau lebih columns. Index tersebut mengandung pointer ke tabel file, mengizinkan akses yang cepat ke nilai tertentu pada tabel tersebut.
PostgreSQL tidak membuat index-index secara automatis, namun user yang harus untuk membuatnya.
Untuk membuat index, dapat menggunakan command CREATE INDEX, contohnya:
|
| CREATE INDEX customer_custid_idx ON customer (customer_id); | |
Pada contoh diatas customer_custid_idx adalah nama index, customer adalah nama tabel yang diberi index dan customer_id adalah column yang diberi index. Index ini berguna hanya untuk mencari rows dalam tabel customer untuk column customer_id yang spesifik. Index ini tidak dapat digunakan untuk mencari pada columns yang lain, karena index-nya hanya diurutkan oleh column yang spesifik saja.
User-defined index juga dapat menggunakan built-in B-tree, hash dan GiST indexes. Indexing pada PostgreSQL juga men-support features berikut:
- PostgreSQL dapat melakukan backward scanning indexes.
- Expressional indexes dapat dibuat dimana index berupa hasil dari sebuah expresi atau fungsi.
- Partial indexes, which only index part of a table, dapat dibuat dengan menambahkan WHERE clause ke akhir dari CREATE INDEX statement.
create index partial_salary on employee(age) where salary>2100; | |
Index partial_salary hanya diberikan pada rows yang mempunyai salary > 2100.
- Pada versi 8.1, men-support bitmap index scans.
ADMINISTRATIVE TOOLS
Instalasi
Tahapan dibawah ini hanya untuk proses instalasi yang tidak menggunakan distribusi pre-packaged, seperti RPM (.rpm) atau Debian Package (.deb).
Requirements:
Software dibutuhkan untuk membangun PortgreeSQL pada Windows berbasis NT (Windows XP dan 2003), yaitu MinGW atau Cygwin. Untuk instalasi komplit, hanya 2 software tadi yang direkomendasikan, namun tidak menutup kemungkinan untuk hanya menginstall C client library (libpq) dan terminal interaktif (psql) dengan menggunakan tool lain.
Paket-paket berikut hanya tambahan. Tidak dibutuhkan untuk konfigurasi standart, namun dibutuhkan untuk beberapa tambahan lain. Antara lain:
- PL/Perl server programming language.
- PL/Phyton server programming language.
- PL/Tcl procedural language.
- Native language support.
- Kerberos, OpenSSL, dan/atau PAM.
- GNU Flex dan Bison.
Prosedur Instalasi:
1. Konfgurasi
2. Build
3. Regression Test
4. Instalasi File
Shared Library:
Pada kebanyakan sistem yang menggunakan shared library kita harus memberitahu sistem kita bagaimana mencari shared library yang baru saja diinstall. Beberapa system yang tidak perlu melakukan proses ini antara lain: Linux, NetBSD, OpenBSDm Tru64 UNIX, IRIX, HP-UX, FreeBSD, BSD/OS.
Konfigurasi Server
Ada banyak parameter konfigurasi yang mempengaruhi perilaku dari sistem database.
Parameter Setting
Semua nama parameter case-insensitive. Setiap parameter tipe data: Boolean, floating point, integer, atau string. Satu cara untuk mengkonfigurasi parameter-parameter tersebut yaitu dengan meng-edit file postgresql.conf, yang normalnya berada pada direktori data. Contoh dari isi file tersebut seperti ini:
# This is a comment
log_connections = yes
log_destination = ’syslog’
search_path = ’$user, public’
Satu parameter satu baris.
Koneksi dan Otentikasi
Setting Koneksi
listen_addresses (string)
Menentukan alamat IP dimana server yang akan dihubungkan oleh aplikasi klien. Nilai defaultnya yaitu localhost.
port (integer)
Port TCP dimana server mendengarkan; defaultnya 5432.
max_connections (integer)
Menentukan jumlah maksimum dari koneksi konkuren ke server database. Defaultnya 100, tapi mungkin kurang kalau konfigurasi kernel kita tidak mendukungnya.
superuser_reserved_connections (integer)
Menentukan jumlah slot koneksi yang disediakan untuk berhubungan dengan superuser PostgreSQL. Paling banyak koneksi sebanyak max_connection bisa aktif secara simultan.
unix_socket_directory (string)
Menetukan direktori dari socket Unix-domain yang mana server akan mendengarkan koneksi dari aplikasi klien.
unix_socket_group (string)
Mengeset grup kepemilikan socket Unix-domain. Defaultnya isinya adalah string kosong, yang menggunakan grup default untuk current user.
unix_socket_permissions (integer)
Mengeset izin akses ke socket Unix_domain. Socket Unix_domain menggunakan set izin standar sistem berkas Unix.
bonjour_name (string)
Menentukan nama broadcast Bonjour
tcp_keepalives_idle (integer)
Pada sistem yang mendukung TCP_KEEPIDLE. Menentukan berapa detik antara mengirim keepalives dan koneksi idle.
Memanage Database
Dalam membuat basis data, server PostgreSQL harus sudah dalam keadaan berjalan atau running. Basis data dibuat dengan menggunakan perintah SQL “CREATE DATABASE”.
CREATE DATABASE nama;
dimana nama mengikuti aturan standar untuk identifier SQL.
Kemudian, database pertama dibuat dengan menggunakan perintah initdb saat tempat penyimpanannya di inisialisasi. Database ini disebut postgres.
Untuk mudahnya ada program yang bisa dieksekusi dari shell untuk membuat database baru dapat digunakan perintahcreatedb.
createdb dbname
perintah tersebut melakukan koneksi ke postgres kemudian mengeluarkan perintah CREATE DATABASE seperti dijelaskan diatas.
Saat kita ingin membuatkan database untuk orang lain, dan orang tersebut yang akan menjadi pemilik database yang baru, sehingga dia dapat memanage databasenya sendiri, untuk hal ini dapat digunakan perintah:
CREATE DATABASE dbname OWNER rolename;
atau,
createdb -O rolename dbname
Kita harus sebagai superuser untuk membuat database untuk orang lain.
Template
CREATE DATABASE sebenarnya bisa dilakukan dengan mengkopi dari database yang sudah ada. Secara default, dia mengkopi dari database sistem standar bernama template1.
Untuk membuat database dari template1, gunakan peritah berikut
CREATE DATABASE dbname TEMPLATE template1;
dari the SQL environment, or
createdb -T template0 dbname
dari shell.
Menghancurkan Database
Database dapat dihancurkan dengan menggunakan perintah DROP DATABASE
DROP DATABASE nama;
Namun hanya pemilik database atau superuser yang bisa meng-drop database. Melakukan dropping mengapus semua objek yang terkandung didalamnya. Dan tidak bisa di undo. Kita tidak bisa meng-eksekusi DROP DATABASE saat terhubung ke database yang akan di drop.
Untuk mudahnya, ada juga program shell untuk men-drop database, dropdb:
dropdb dbname
Backup and Restore
Database PostgreSQL memiliki kemampuan untuk mem-backup secara teratur.
Ada tiga cara pendekatan untuk mem-backup data PostgreSQL:
- SQL Dump
Ide dari metode SQL dump adalah membentuk file text dengan perintah-perintah SQL yang pada saat dilempar kembali ke server, akan membentuk ulang database dengan state yang sama seperti pada saat di dump. PostgreeSQL menyediakan program utiliti pg_dump untuk ini. Cara dasar penggunaannya sbb:
pg_dump dbname > outfile
Seperti yang bisa kita lihat, pg_dump menulis hasilnya ke standar output. Kemudian hasil dari standar output tersebut akan di redirect kedalam outfile
pg_dump dbname > outfile
Namun perlu diingat, pg_dump tidak beroperasi dengan izin spesial. Untuk suatu hal tertentu, ia harus punya hak akses untuk membaca semua tabel yang ingin di backup, jadi pada prakteknya hampir selalu dilakukan sebagai superuser database.
Restoring the Dump
File text yang dibuat oleh pg_dump nantinya akan dibaca oleh program psql. Perintah umum untuk merestore dump adalah
psql dbname <>
dimana infile adalah file yang kita gunakan saat meridirect standar output pada saat membuat dump.
Database dbname tidak akan dibuat dengan perintah diatas, kita harus membuatnya sendiri bisa dari template0 atau template1 sebelum mengeksekusi psql.
Bisa juga melakukan dump terhadap database secara langsung dari satu server ke server lain
contohnya:
pg_dump -h host1 dbname | psql -h host2 dbname
Menggunakan pg-dumpall
Mekanisme diatas tidak cocok saat kita melakukan back up untuk seluruh database cluster, oleh karena itu disediakan program pg_dumpall. pg_dumpall mem-backup setiap database pada cluster yang diberikan, juga menyediakan cluster-wide data seperti sebagai user dan group. Cara penggunaan dasarnya sebagai berikut:
pg_dumpall > outfile
hasil dari dump bisa direstore dengan perintah psql:
psql -f infile postgres
b. File System Back up
Cara lainnya adalah dengan langsung melakukan pengandaan file yang digunakan PostgreSQL untuk menyimpan data di database. Kita bisa menggunakan cara apapun yang kita sukai untuk melakukan backup file biasa, sebagai contoh
tar -cf backup.tar /usr/local/pgsql/data
Ada dua batasan, juga, apa yang membuat metode ini kurang praktis atau kurang canggih dibandingkan dengan metode pg_dump :
1. Server database harus dimatikan untuk mendapatkan backup yang berguna. Cara setengah-setengah seperti menghalangi semua koneksi tidak akan berguna (karena tar dan tool semacamnya tidak mengambil atomic snapshot state dari filesystem pada waktu tertentu). Kita juga harus mematikan server sebelum melakukan restore.
2. Filesystem backup hanya berguna untuk restorasi komplit dari keseluruhan cluster database.
- Online Back up and Point in time recovery
PostgreSQL memaintain sebuah write ahead log (WAL) di subdirektori p_xlog direktori cluster data. Log tersebut menjelaskan setiap perubahan yang dibuat terhadap file data pada databases asalan utamanya adalah crash-safety Namun, log ini memungkinkan kita untuk menggunakan strategi ketiga untuk membackup database yaitu dengan menggabungkan antara file-system-level backup dengan backup dari file WAL. Kalau restorasi dibutuhkan, kita merestore backupnya kemudian kita melakukan “replay” dari file WAL untukmembawa backup ke waktu kini. Pendekatan ini lebih rumit untuk dilakukan dibanding pendekatan sebelumnya, tapi memiliki keuntungan yang cukup banyak, yaitu:
l Kita tidak butuh backup yang sempurna seperti awal, inkonsistensi internal pada backup akan dikoreksi oleh log replay. Jadi kita tidak butuh kemampuan snapshot file-system, hanya tar atau tool sejenisnya.
l Karena kita bisa menggabungkan sepanjang tak terhingga dari sequence file WAL, kontinuitas backup dapat diperoleh hanya dengan secara kontinyu mengarsipkan file WAL.
l Tidak ada yang mengatakan bahwa kita haru mereplay seluruh file WAL sampai akhir. Kita bisa memberhentikan replay pada point apapun dan memiliki snapshot yang konsisten dari database seperti pada saat itu.
l Kalau kita terus menerus secara kontinyu memberikan seri-seri file WAL ke mesin lain yang telah diisi dengan file back upnya, maka kita punya sebuah “hot standby” system: pada satu waktu kita bisa bawa mesin kedua tersebut dan kita punya database yang hampir mirip dengan aslinya.
Memonitor Aktifitas Database
Ada beberapa tool yang dapat digunakan untuk memonitor aktifitas database dan menganalisa performanya.
Unix Tool Standar
Ada regular Unix monitoring program seperti ps, top, iostat, dan vmstat.
Statistic Collector
PostgreSQL statistic collector merupakan subsistee yang mensupport pengumpulan dan pelaporan informasi mengenai aktifitas server. Saat ini, collector bisa menghitung akses ke tabel dan index-index pada kedua disk-block dan individual-row. Selain itu statistic collector ini menjelaskan perintah yang sedang dieksekusi oleh proses server lain.
Statistics Collection Configuration
Karena statistic collection menambahkan beberapa overhead untuk eksekusi query, system bisa dikonfigurasi untuk mengumpulkan atau tidaknya informasi. Hal ini dikontrol dengan configuration parameter yang normalnya di set di postgresql.conf.
Parameter stats_start_collector harus di set true agar statistic collector di jalankan seluruhnya. Ini merupakan konfigurasi default dan yang direkomedasikan, tapi boleh dimatikan kalau kita tidak tertarik akan hal ini.
Parameter stats_command_string, stats_block_level, dan stats_row_level mengontrol berapa banyak informasi yang sebenarnya dikirim ko collector dan menjelaskan berapa banyak run-time overhead yang terjadi. Normalnya, parameter tersebut di konfigurasi di postgresql.conf supaya berjalan untuk seluruh proses server, tapi bisa saja di matikan untuk sesi individu dengan perintah SET.
Viewing Collected Statistics
Saat menggunakan statistic untuk memonitor aktifitas sekarang, pentinglah untuk menyadari bahwa informasi tersebut tidak diupdate secara instan. Setiap proses server secara individual mentransmit block baru dan jumlah row access kepada collector sesaat sebelum idle; jadi transaksi yang sedang berjalan tidak mempengaruhi tampilan seluruhnya.
Hal penting lainnya yaitu saat proses server diminta untuk menampilkan statistic, dia pertama kali mengambil the most recent report yang dikeluarkan oleh proses collector dan kemudian menggunakan snapshot ini untuk semua statisticccal views dan fungsinya sampai pada akhirnya dari current transactionnya sendiri. Sehingga statistic yang tampil tidak akan berubah selama kita meneruskan current transaction. Hal ini mengizinkan kita melakukan beberapa query pada statistic dan mengkorelasikan hasilnya tanpa khawatir akan angka-angka yang berubah pada sistem.
Viewing Locks
Tool lain yang berguna untuk memonitor aktifitas database yaitu pg_locks sistem tabel. Dia mengizinkan DBA untuk melihat informasi mengenai outstanding locks pada lock manager.
PLATFORM
Pada umumnya, beberapa Unix-compatible platform yang modern seharusnya dapat menjalankan PostgreSQL. Platform-platform, yang telah menerima testing eksplisit pada saat dirilis, kemudian didaftar dalam instruksi-instruksi instalasi.
PostgreSQL juga dapat berjalan pada Microsoft Windows NT yang berbasis sistem operasi seperti Win2000, WinXP, dan Win2003. Selain itu, MSDOS yang berbasis versi-versi dari Windows (Win95, Win98, WinMe) juga dapat menjalankan PostgreSQL dengan menggunakan Cygwin.
Berikut adalah beberapa platform lainnya yang juga dapat menjalankan PostgreSQL:
- Apple Computer, Inc.: Mac OS Any version
- Cisco Systems, Inc.: Cisco IOS Any version
- Conectiva: Conectiva Linux 10
- Data General: DG/UX Any version
- Hewlett-Packard Company: HP-UX Any version
- Hewlett-Packard Company: Tru64 UNIX Any version
- IBM: AIX Any version dan OS/2 Any version
- Linux: Linux Any version
- Microsoft Corporation: Windows 95
- Microsoft Corporation: Windows 98
- Microsoft Corporation: Windows 98 Second Edition
- Microsoft Corporation: Windows Me
- Microsoft Corporation: Windows 2000 Any version
- Microsoft Corporation: Windows 2003 Any version
- Microsoft Corporation: Windows NT 4.0
- Microsoft Corporation: Windows XP Any version
- Novell, Inc.: Novell NetWare Any version
- PostgreSQL: PostgreSQL 7.x
- PostgreSQL: PostgreSQL 8.x
- Santa Cruz Operation, Inc.: SCO Unix Any version
- SGI: IRIX Any version
- Sun Microsystems: Solaris Any version
- Wind River Systems, Inc.: BSD Any version
PRICE
PostgerSQL merupakan software yang open source.
SECURITY
Sekuriti dari basis data dibagi menjadi beberapa level:
- Proteksi berkas basis data, yaitu semua berkas yang tersimpan di basis data terlindungi dari account manapun kecuali superuser account dari postgres
- Komunikasi antara user dengan server basis data by default, dilakukan melalui local UNIX socket, bukan TCP/IP.
- Koneksi user dapat diatur sehingga koneksi hanya bisa dari IP yang ditentukan saja
- Setiap user di postgre mempunyai user name dan password. By default, user hanya dapat melihat tabel basis data yang dibuat oleh user tsb.
- User dapat dimasukkan ke sebuah group sehingga akses tabel bisa dibatasi oleh anggota group tersebut.
Authentikasi
Adalah proses pengecekan oleh server apakah user yang mengakses suatu berkas basis data itu adalah user yang berhak atau tidak. User yang mengakses akan di cek ke kelas pg_user untuk memastikan bahwa yang mengakses itu adalah orang yang benar-benar yang punya basis data tersebut.
Postgre mengecek autentikasi dengan cara Host-based Access Control, yaitu user mana yang boleh mengakses sebuah basis data dan user tersebut harus mengautentikasi dirinya sendiri.
Postgre juga menyediakan keluasaan bagi user untuk menentukan siapa saja yang boleh mengakses basis data mereka melalui Access Control. Access Control ini membatasi siapa saja yang bisa mengakses dengan cara seperti memberikan superuser atau membatasi privileged yang didapat oleh seorang user (misalnya membatasi hanya boleh membaca, tidak boleh mengubah).
PostgreSQL mengauthentikasikan user dalam beberapa metode:
· Trust, koneksi user diizinkan tanpa syarat/pertimbangan
· Reject, koneksi user ditolak tanpa syarat/pertimbangan
· Crypt, client akan meminta password untuk mengauthentikasi user. Password dikirim dari client ke server dengan cara dienkripsi terlebih dahulu dengan crypt(3), lalu membandingkannya dengan password yang disimpan di dalam table pg_shadow, yaitu table yang memuat password para user. Jika cocok, maka koneksi diizinkan
· Password, client akan meminta password kepada user, lalu password ini akan dikirim langsung ke server dan dicocokkan dengan table pg_shadow. Jika passwordnya cocok, maka mungkin akan diminta mengirimkan berkas password untuk dicocokkan kemudian.
Semua metode di atas mensupport UNIX dan TCP/IP domain sockets.
Metode di bawah ini hanya untuk TCP/IP domain sockets:
· Krb4, kerberos v4
· Krb5, kerberos v5
- Ident, ‘ident server’ pada client akan mengauthentikasi user (RFC 1413). Kemudian, user dapat memberikan ‘map name’ untuk dicocokkan kemudian ke user name yang tersimpan pada postgre server.
Selain itu, terdapat cara juga untuk melakukan ‘secure TCP/IP connection’, yaitu dengan cara menggunakan metode ssh untuk mengenkripsi koneksi jaringan antara client dengan postgre server. Caranya adalah:
- Buatlah sebuah koneksi ke server dengan cara ‘tunnel’ seperti
Ø ssh –L 3333:server.domain.org:5432 postgres@server.org
- angka pertama (3333) adalah port number dari tunnel yang berada di client. Angka kedua (5432) adalah remote end dari tunnel atau port postgre yang berada di server. Alamat server berada di antara kedua angka tersebut. Argumen terakhir adalah optional user name. User name ini bisa aja tidak berkaitan dengan postgre, asalkan diterima oleh server.
- Setelah mempunyai sesi ssh, koneksikan postgre client dengan port yang merupakan tunnel daripada koneksi ssh sebelumnya.
Ø Psql –h localhost –p 3333 –d mpw
- -h disini adalah untuk memberitahu bahwa socket yang digunakan adalah TCP/IP, bukan UNIX socket. Kita dapat juga menghilangkan –p jika port tunnel yang digunakan adalah 5432
PERFORMANCE
PostgreSQL terlihat telah menampilkan performa yang sangat baik. Pada beberapa tahun belakangan ini, PostgreSQL telah mengalami peningkatan yang pesat dan mempunyai fitur-fitur yang menarik, seperti partial locking yang lebih baik daripada row level locking. Peningkatan performa seperti ini tidak terlihat jika kita hanya memasukkan 100.000 baris data, namun akan terlihat jelas pada situasi querying yang kompleks.
Setiap ahli database mengetahui bahwa sangat sulit dan subjektif untuk membandingkan RDBMS’s. Namun, untuk mengetahui kualitas performa dari PostgreSQL, kita akan membandingkan PostgreSQL dengan database sebelumnya, yaitu dengan melakukan test setup.
The test servers
PostgreSQL 7.3.2 pada Pentium4 2.2 GHz/512MB/SCSI, RedHat linux 9
Larry 8i pada Pentium yang sama, Pentium4 2.2 GHz/512MB/SCSI, Win2000 Adv.
Server 100MB LAN, 2.4 GHz client workstation
Pelu diperhatikan bahwa PostgreSQL dikonfigurasikan untuk menggunakan banyak shared memory daripada umumnya, karena pengaturan umumnya sangat konservatif dan hal ini merusak performa dari database. Namun, memori yang digunakan oleh PostgreSQL di dalam tes secara signifikan lebih sedikit daripada memori yang digunakan Larry.
The databases
Database di-setup dengan data dan skema database yang paling baru dari sebuah produksi database. Database tersebut menggunakan 48 tabel, dengan 179 index, 77 foreign keys, 30 sequences dan triggers, serta 138 views. Total dari 200.000 lebih rekaman dimasukkan ke dalam tabel dengan ukuran yang berbeda.
Real life simulation
Queries yang dikirim ke database di-log dari aplikasi regular klien selama sesi typical. Queries tersebut cocok untuk kedua database. Log tersebut dimodifikasi untuk merefleksikan user-logins yang berbeda dan digunakan sebagai input untuk sesi simulasi pada test setup. Queries yang digunakan mempunyai karakteristik sebagai berikut. Queries mempunyai sekitar 80 queries, dengan 10% entri/update/hapus, 74 (sub)selects, 46 menggunakan views yang kompleks (cascaded). Hasilnya pada pengembalian 2000 rekaman per-sesi. Kebiasaan membuat software klien berjalan 200 sesi dari 6 pengguna yang konkuren, dimulai dengan penundaan startup untuk meningkatkan querying yang asinkronus. Sesi klien yang sebenarnya adalah sekitar 10 menit, sesi simulasi 4 detik. Tes software untuk mensimulasikan sesi-sesi menggunakan pustaka vendor’s driver asal, bukan ODBC. Untuk setiap database, sejumlah 24.000.000 rekaman dikembalikan sebagai hasil dari 1200 sesi klien yang disimulasikan. Database dan queries mungkin tidak benar-benar kompleks, namun banyak fitur yang digunakan, dan itu merupakan sistem yang hidup dan matang, dengan sedikit redudansi dan integritas yang baik, digunakan oleh banyak pelanggan. Kegunaan jaringan tidak pernah melebihi 50% dari kapasitas total selama tes, dan batasan/limit dari tes klien tidak pernah tercapai selama tes berlangsung.
Hasilnya (waktu untuk menyelesaikan tes)
| PostgreSQL 7.3.2 | 14:38 min |
| Larry 8i | 16:35 min |
Jadi, walaupun kesimpulan umum tidak dapat digambarkan, namun PostgreSQL sepertinya telah membuktikan bahwa ia merupakan pesaing yang serius.
SPEED
PostgreSQL terkenal akan fitur-fitur yang disediakannya, di mana telah diketahui sejak dulu bahwa PostgreSQL mempunyai sekumpulan fitur yang kaya dan lengkap, yang mendukung transaksi, view, subselect, foreign key support, pemeriksaan, dan lain-lain. Namun kekayaan fitur tersebut tidak berlaku dalam hal kecepatan. Padahal seperti yang kita ketahui, kecepatan merupakan salah satu aspek penting yang menentukan kinerja dari sebuah database, di mana sebuah database memerlukan performa dengan kecepatan yang tinggi.
PostgreSQL pada versi-versi awal mempunyai performa yang lambat dan kecepatan dari PostgreSQL database telah diperdebatkan sejak lama. Namun, isu ini tidak pernah benar-benar dibahas secara detail, kecuali pada TPC-C Test yang diterbitkan oleh Great Bridge LLC. Tes ini menunjukkan bahwa performa dari PostgreSQL paling tidak dapat disamakan, bahkan lebih baik, daripada tiga database vendor utama dan MySQL, terutama untuk load yang besar. Validitas dari tes tersebut ditanyakan oleh beberapa kelompok orang, dikarenakan ODBC driver yang digunakan pada PostgreSQL ini kurang meyakinkan.
ADVANTAGES
PostgreSQL memberikan banyak keuntungan kepada para perusahaan atau kegiatan bisnis yang menggunakan database system ini dibandingkan dengan database system yang lain. Berikut adalah keuntungan-keuntungan tersebut, antara lain:
- Resisten terhadap over-deployment
Dengan menggunakan PostgreSQL, tidak ada seorangpun dapat menuntut untuk pelanggaran terhadap perjanjian lisensi, sebagaimana tidak ada biaya lisensi yang diasosiasikan (digabungkan) untuk software. Hal ini menyebabkan PostgreSQL memberikan keuntungan tambahan, antara lain:
- bisnis menjadi lebih profitable dengan skala penyebaran yang luas
- tidak ada kemungkinan diperiksa untuk pemenuhan lisensi
- fleksibel untuk menjalankan konsep penelitian dan trial deployment tanpa memerlukan biaya lisensi tambahan.
- Support yang lebih baik
Selain memberikan dukungan yang kuat, PostgreSQL juga mempunyai komunitas ahli dan para peminat.
PostgreSQL telah didesain dan dibuat sedemikian rupa untuk mempunyai tingkat pemeliharaan dan kebutuhan yang lebih rendah daripada database systems sebelumnya, walaupun tetap mempertahankan kualitas fitur, stabilitas, dan performa. Sehingga biaya yang diperlukan PostgreSQL menjadi lebih efektif dan lebih dapat diatur.
Banyak perusahaan yang melaporkan bahwa PostgreSQL tidak pernah, bahkan sekalipun, mengalami crashed pada saat melakukan operasi dengan tingkat aktivitas yang tinggi.
Kita tidak memerlukan biaya sekecil apapun dan hanya memerlukan sedikit usaha untuk melakukan perluasan terhadap PostgreSQL.
PostgreSQL dapat dijalankan hampir di setiap jenis Unix (34 platform yang paling baru dirilis), juga di Windows dengan menggunakan Cygwin.
- Didesain untuk high volume environments
PostgreSQL menggunakan penyimpanan data dengan banyak baris (multiple rows) yang dinamakan MVCC. Hal ini dimaksudkan agar PostgreSQL sangat responsif pada high volume environments.
- Desain database GUI dan administration tools
Beberapa tools GUI yang berkualitas tinggi tersedia untuk mengelola database (pgAdmin, pgAccess), sekaligus desain database(Tora, database architect).
- Fitur-fitur yang sangat mendukung
PostgreSQL sangat kaya akan fitur-fitur yang disediakannya, antara lain: rules, views, triggers, unicode, sequences, inheritance, outer join, sub-selects, open API, dan masih banyak lagi.
CONCURRENCY CONTROL
Concurrent control atau lebih dikenal dengan Multiversion Concurrency Control (MVCC) biasanya digunakan database management system untuk akses secara concurrent ke database. MVCC menghubungkan setiap user ke database dengan sebuah “snapshot” dari database ke setiap orang yang sedang bekerja dengan database tersebut. Setiap perubahan tidak akan terlihat sampai transaksi selesai dilakukan.
Transaction isolation
Ada empat standar level pembatasan pada SQL standar untuk menghindari 3 kejadian pada concurrent transaction. Yaitu :
- dirty read : sebuah transaksi membaca data dari transaksi yang belum disimpan
- nonrepeatable read : sebuah transaksi membaca kembali data yang sebelumnya penah dibaca dan menemukan data tersebut telah diubah oleh transaksi lainnya
- phantom read : sebuah transaksi meng-execute sebuah query dan menemukan beberapa baris telah dirubah oleh transaksi lain yang sedang berjalan.
Dan empat batasan tersebut adalah :
Di dalam PostGreSQL, user dapat me-request apa saja dari salah satu standar level pembatasan . Tetapi hanya ada 2 batasan yang jelas yaitu Read Committed dan Serializable. Ketika user memilih Repeatable Read, maka user akan mendapatkan Seriliazable dan ketika user memilih Read Uncommitted, maka user akan mendapatkan Read Committed.
Explicit Locking
PostGreSQL menyediakan beberapa model locking untuk mengontrol concurrent access ke table data.
- Table-Level Locks
ACCESS SHARE
Commands SELECT dan ANALYZE memerlukan lock tipe ini. Jenis query yang hanya memabaca table dan tidak mengubah data memerlukan lock mode ini.
ROW SHARE
Commands SELECT FOR UPDATE dan SELECT FOR SHARE memerlukan lock jenis ini pada table target.
ROW EXCLUSIVE
Diperlukan saat ada commands untuk merubah data pada table.
SHARE UPDATE EXCLUSIVE
Mode ini memproteksi table dari perubahan concurrent schema.
SHARE
Mode ini memproteksi table dari perubahan data secara concurrent.
SHARE ROW EXCLUSIVE
Secara otomatis diperlukan semua PostGreSQL command.
EXCLUSIVE
Mode ini mengizinkan hanya only concurrent ACCESS SHARE lock.
Lock ini secara otomatis diperlukan pada user table oleh PostGreSQL Command.
ACCESS EXCLUSIVE
Mode ini menjamin hanya 1 user yang sedang mengakses table.
- Row-Level Locks
Ada 2 level yaitu share lock level dan exclusive lock level. Exclusive row secara otomatis diperlukan ketika mengupdate atau menghapus suatu baris. Shared lock level tidak memproteksi table dari transaksi – transaksi yang mengakses baris tersebut.
- Deadlocks
Ketika ada 2 proses yang sama – sama merequest exclusive-lock, maka PostGreSQL akan melakukan deadlock.
Locking and Indexes
Beberapa cara pengindex-an pada PostGreSQL adalah :
Index tipe ini menyediakan concurrency tertinggi tanpa kondisi deadlock
Menyediakan concurrency yang lebih baik, tetapi kemungkinan deadlock masih ada.
Lock dilepaskan setelah semua comman selesai. Jenis ini sudah jarang digunakan pada saat ini.
STORAGE
Database file layout
Semua data yang diperlukan untuk database cluster disimpan di dalam cluster's data directory. Umumnya mengacu sebagai PGDATA. Lokasi umum untuk PGDATA adalah /var/lib/pgsql/data. Multiple clusters, di-manage oleh postmasters yang berbeda dapat berada pada mesin yang sama.
PGDATA directory mengandung sub-directory:
| Item | Description |
| PG_VERSION | A file containing the major version number of PostgreSQL |
| base | Subdirectory containing per-database subdirectories |
| global | Subdirectory containing cluster-wide tables, such as pg_database |
| pg_clog | Subdirectory containing transaction commit status data |
| pg_multixact | Subdirectory containing multitransaction status data (used for shared row locks) |
| pg_subtrans | Subdirectory containing subtransaction status data |
| pg_tblspc | Subdirectory containing symbolic links to tablespaces |
| pg_twophase | Subdirectory containing state files for prepared transactions |
| pg_xlog | Subdirectory containing WAL (Write Ahead Log) files |
| postmaster.opts | A file recording the command-line options the postmaster was last started with |
| postmaster.pid | A lock file recording the current postmaster PID and shared memory segment ID (not present after postmaster shutdown) |
Subdirectory di atas adalah lokasi default untuk file database.
TOAST (The Oversized-Attribute Storage Technique).
PostgreSQL mengizinkan field value yang besar untuk di kompres dan atau dibagi ke dalam banyak physical rows yang dikenal sebagai TOAST.
TOAST code mengenali 4 strategi yang berbeda untuk menyimpan TOAST-able columns:
· PLAIN prevents either compression or out-of-line storage. This is the only possible strategy for columns of non-TOAST-able data types. · EXTENDED allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big. · EXTERNAL allows out-of-line storage but not compression. Use of EXTERNAL will make substring operations on wide text and bytea columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed. · MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough.) | |
Database Page Layout
Overall Page Layout
| Item | Description |
| PageHeaderData | 20 bytes long. Contains general information about the page, including free space pointers. |
| ItemPointerData | Array of (offset,length) pairs pointing to the actual items. 4 bytes per item. |
| Free space | The unallocated space. New item pointers are allocated from the start of this area, new items from the end. |
| Items | The actual items themselves. |
| Special space | Index access method specific data. Different methods store different data. Empty in ordinary tables. |
PageHeaderData Layout
| Field | Type | Length | Description |
| pd_lsn | XLogRecPtr | 8 bytes | LSN: next byte after last byte of xlog record for last change to this page |
| pd_tli | TimeLineID | 4 bytes | TLI of last change |
| pd_lower | LocationIndex | 2 bytes | Offset to start of free space |
| pd_upper | LocationIndex | 2 bytes | Offset to end of free space |
| pd_special | LocationIndex | 2 bytes | Offset to start of special space |
| pd_pagesize_version | uint16 | 2 bytes | Page size and layout version number information |
HeapTupleHeaderData Layout
| Field | Type | Length | Description |
| t_xmin | TransactionId | 4 bytes | insert XID stamp |
| t_cmin | CommandId | 4 bytes | insert CID stamp |
| t_xmax | TransactionId | 4 bytes | delete XID stamp |
| t_cmax | CommandId | 4 bytes | delete CID stamp (overlays with t_xvac) |
| t_xvac | TransactionId | 4 bytes | XID for VACUUM operation moving a row version |
| t_ctid | ItemPointerData | 6 bytes | current TID of this or newer row version |
| t_natts | int16 | 2 bytes | number of attributes |
| t_infomask | uint16 | 2 bytes | various flag bits |
| t_hoff | uint8 | 1 byte | offset to user data |
STORED PROCEDURE
Stored procedure adalah program (prosedur) yang tersimpan di dalam database. Biasanya stored procedure ini ditulis dalam sebuah bahasa database yang disupport oleh vendor. Keunggulan daripada pemakaian stored procedure adalah program yang dibuat akan dijalankan di engine databasenya, bukan pada client dan bukan pada database servernya itu sendiri. Engine database ini pada umumnya lebih cepat dalam menangani query dan request. Database server itu sendiri mempunyai akses langsung ke data di dalam database itu sendiri, sehingga proses manipulasi dari data tersebut dilakukan di dalam servernya sendiri dan database server hanya tinggal memberikan hasil akhir dari sebuah prosedur tersebut. Bayangkan jika prosedur tersebut berada pada client-side program. Pemanipulasian data akan berada di client, sehingga data traffic antara client dan server akan tinggi. Selain itu, program harus menunggu response dari server untuk bisa masuk dalam tahap prosedur selanjutnya.
Beberapa kegunaan dasar dari stored procedure adalah misalnya data validation (biasanya dalam suatu trigger), atau peng-enkapsulasian suatu rangkaian proses query yang kompleks. Rangkaian query tersebut akan berjalan lebih cepat jika berada di database server daripada berjalan di client program dengan cara mengirimkan querynya satu persatu.
Kegunaan lain dari stored procedure adalah memudahkan data management. Kita bisa langsung memasukkan business logic ke dalam cara pengambilan data kita melalui stored procedure, sehingga meminimalkan penulisan code pengambilan data pada program. Hal ini dapat meminimalisir kesalahan code pada program dan juga meminimalisir data corruption pada proses manipulasi data.
Procedural language yang disupport langsung oleh postgreSQL adalah PL/pgSQL (Procedural Language/postgre SQL). PL/pgSQL ini adalah sebuah procedural language yang memberikan fleksibilitas tinggi dibandingkan dengan query biasa, seperti fasilitas loop dan control structures (if/case). Program yang dibuat oleh PL/pgSQL adalah sebuah function dan dianggap oleh postgreSQL sebagai sebuah statement atau sebuah aksi yang dilakukan trigger.
Tujuan dari PL/pgSQL adalah membuat procedural language yang:
- dapat membuat function dan prosedur trigger
- memfasilitasi control structure pada query
- melakukan manipulasi data yang kompleks
- meng-inherit semua types, operator, dan function yang dibuat oleh user
- mudah dibuat dan mudah dijalankan
Procedural language lainnnya yang disupport oleh postgreSQL misalnya PL/Tcl, PL/Perl, PL/Python, dll.
Procedural language harus di “install” ke dalam sebuah database terlebih dahulu untuk dapat digunakan oleh database tersebut.
Untuk membuat sebuah function, kita bisa membuatnya dengan cara sebagai berikut:
CREATE FUNCTION handler_function_name()
RETURNS language_handler
AS 'path-to-shared-object'
LANGUAGE C;
Lalu, untuk membuat sebuah validator, kita menggunakan
CREATE FUNCTION validator_function_name(oid)
RETURNS void
AS 'path-to-shared-object'
LANGUAGE C;
Lalu, kita mendeclare functionnya
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE language-name
HANDLER handler_function_name
[VALIDATOR validator_function_name] ;
keyword [trusted] di sana adalah agar user yang bukan superuser dapat menggunakan function tersebut.
Jika kita ingin membuat PL dalam PL/pgSQL
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
'$libdir/plpgsql' LANGUAGE C;
CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
'$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER plpgsql_call_handler
VALIDATOR plpgsql_validator;
Bentuk umum untuk membuat sebuah function dalam postgreSQL adalah sbb
CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
-- declarations
BEGIN
PERFORM my_function();
END;
$$ LANGUAGE plpgsql;
DATA MANIPULATION
Untuk membuat sebuah table
CREATE TABLE products (
product_no integer,
name text,
price numeric
);
Untuk memasukkan data ke table di atas
INSERT INTO products VALUES (1, 'Cheese', 9.99);
atau kita dapat memasukkan sesuai tuple/field yang kita inginkan
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
Kita juga dapat mengosongkan sebuah field, sehingga diisi dengan nilai defaultnya
INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
kita juga dapat meminta untuk dimasukkan default value untuk memperjelas query
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;
Untuk mengupdate, kita harus memberikan nama table dan column yang diupdate, nilai baru dari column tersebut, dan row mana saja yang diupdate. Contoh:
UPDATE products SET price = 10 WHERE price = 5;
Untuk menghapus, kita bisa langsung menghapus sebuah row dari primary keynya atau dari sebuah conditional statement. Secara garis besar, menghapus tidak jauh berbeda dari update. Contohnya:
DELETE FROM products WHERE price = 10;
atau kita juga bisa menghapus semua entry dari sebuah table
DELETE FROM products;
KESIMPULAN
· PostgreSQL adalah oper source relation database system yang sangat powerful. PostGreSQL sudah lebih dari 15 tahun aktif dalam pengembangannya dan arsitektur yang dibangunnyapun memiliki reputasi yang bagus, handal, lengkap, dan akurat.
· PostgreSQL terkenal akan fitur-fitur yang disediakannya, namun tidak untuk hal kecepatan. PostgreSQL pada versi-versi awal mempunyai performa yang lambat dan kecepatan dari PostgreSQL telah diperdebatkan sejak lama.
· Ada banyak platform yang dapat menjalankan PostgreSQL, antara lain: Unix-compatible platform, IBM, Microsoft corporation, dan lain-lain.
· PostgreSQL memberikan banyak keuntungan, antara lain: Resisten terhadap over-deployment, menyediakan support yang lebih baik, menghemat biaya staffing, terpercaya dan stabil, extensibleI, cross platform, didesain untuk high volume environments, desain database GUI dan administration tools, serta fitur-fitur yang sangat mendukung.
· Untuk memanipulsi data ada tiga jenis perintah utama yang dapat dilakukan pada postGreSQL yaitu Insert data, Manipulasi data, dan terakhir menghapus data.
· PostgreSQL menyediakan keluasaan bagi user untuk menentukan siapa saja yang boleh mengakses basis data mereka melalui Access Control.
· PostgreSQL mengauthentikasikan user dalam beberapa metode: Trust, Reject, Crypt, Password
· Administrative Tool berupa backup dan restore, yang disediakan oleh PostgreSQL, diantaranya dump, file system, dan online backup yang menyediakan hot standby system.
· PostgreSQL menggunakan multiversion model (Multiversion Concurrency Control, MVCC). PostgreSQL memiliki 2 level transaksi isolation yaitu Read Commited Isolation Level dan Serializable Isolation Level.
· PostgreSQL menyediakan indexing yang di-define sendiri oleh user yang akan memudahkan pencarian dalam tabel.
DAFTAR PUSTAKA
PostgreSQL Global Development Group. http://www.postgresql.org. (27 Februari 2006, 16:07)
Schaeffer, C. dan Hondius, J. http://research.rem.nl (27 Februari 2006, 16:11)
http://www.astroconsulting.com/FAQs/art_evolution_in_greece_and_rome.htm (27 Februari 2006, 16:11)
http://www.bulfinch.org/fables/welcome.html#Contents (27 Februari 2006, 16:11)
Daithankar, Shridhar dan Berkus, Josh. http://www.varlena.com. (27 Februari 2006, 16:05)
