Документ взят из кэша поисковой машины. Адрес оригинального документа : http://www.sai.msu.su/~megera/postgres/talks/what_is_postgresql.pdf Дата изменения: Mon Apr 18 23:13:18 2005 Дата индексирования: Sat Dec 22 07:10:37 2007 Кодировка: Поисковые слова: movie

PostgreSQL ?

PostgreSQL ?
PostgreSQL - - (ORDBMS), . PostgreSQL post-gress-Q-L ( mp3 postgresql.mp3), postgres (-). , pgsql (----). : http://www.sai.msu.su/~megera/postgres/talks/what_is_postgresql.html

PostgreSQL
PostgreSQL , . , . , PostgreSQL POSTGRES, (Michael Stonebraker), (UCB). , PostgreSQL . ( ) RDBMS . , . , . , , , Wikipedia, , , . , , 4 , -, , . , - , . , , . , , ACM Software System Award #6 IBM "System R" - INGRES, ACM SIGMOD ( - ) #1 1992 ., (James Gray, Microsoft) - #2 1993 . , , - "System R", IBM 70-, "INGRES", . , (Ted Codd) IBM 1969,1970 . , - , - CODASYL Data Base Task Group () IBM IMS (Information Management System ). "Oracle",

15


PostgreSQL ?
, , IBM SQL. "System R" , SQL ( SEQUEL, - ). "System R" SQL/DS DB2. , IBM , . "The 1995 SQL Reunion: People, Projects, and Politics", . INGRES ( Ingres89), "System R", , ( ). 1980 1000 . "INteractive Graphics REtrieval System" Jean Auguste Dominique Ingres. , UNIX, PDP 11, , "System R" mainframe. QUEL, , , SEQUEL , , . INGRES BSD, , , . , 70- Ingres Corporation ( , , , ), Ingres, 1994 CA (Computer Associates) 2004 Ingres r3. "NonStop SQL" Tandem Computers Ingres, . . . , Tandem Computers Compaq (2000 .), HP. Sybase ( ) Ingres. , a "SQL Server" - Sybase, Windows NT. 1993 Sybase Mirosoft 1995 Sybase ASE (Adaptive Server Enterprise), Microsoft MS SQL. Informix Ingres, , - CEO , Informix 1995 Ilustra, - (DataBlade), Postgres 1992 . 2001 IBM, Informix . , DB2 -. Postgres Ingres , . 1985 1985-1988 , , POSTQUEL, Postgres. POSTGRES . , , . , , - , , , . POSTGRES - . DMBS (data management) (object management) (knowledge management). , (rules), . POSTGRES " , , ".

15


PostgreSQL ?
POSTGRES class. Class (instances) . . - , , (view), ( ), (parent) . 1989 , (rule system). , Ingres Postgres ! POSTGRES , , timetravel - (, 6.3 , , ). 1992 Illustra, 1993 c 4.2. , , BSD Andrew Yu Jolly Chen 1994 . 1995 POSTQUEL SQL, Postgres95, , ( ). 1996 , "Postgres95" - "PostgreSQL", POSTGRES SQL. , , 6.0. 1997 , -hackers 3 1997 . "Elephants can remember" ( ). , (). , PGDG (PostgreSQL Global Development Group). -hackers.

PostgreSQL ?
PostgreSQL v8 (19 2005 ), , , , . , , Fujitsu 8, Extended Storage Management. BSD- PostgreSQL . , Pervasive Pervasive Postgres. PostgreSQL Unix (34 ), , Linux, FreeBSD, NetBSD, OpenBSD, SunOS, Solaris, DUX, Mac OS X. 8.X PostgreSQL "native" MS Windows NT, Win2000, WinXP, Win2003. , PostgreSQL Novell Netware 6 OS2. PostgreSQL , , Linux New Media AWARD 2004, 2003 Editors' Choice Awards, 2004 Editors' Choice Awards. PostgreSQL , - TelegraphCQ, 2002 Telegraph ( ). , Streambase, 2003 ( "Grassy Brook") , .


PostgreSQL (1300 ). PostgreSQL :

15


PostgreSQL ?
ACID - , , , . Atomicity - , , . Consistency - ( ) ( ). , , , , , , . Isolation - Durability - PostgreSQL , . (Multiversion Concurrency Control,MVCC) , . MVCC , ( ) , , . , () , . MVCC , , MVCC , . "" . Write Ahead Logging (WAL) - , . WAL , , , . , , . Point in Time Recovery (PITR) - ( WAL) , PostgreSQL. PostgreSQL. , , Slony ( 1.1), , master-slaves . , Slony-II multi-master . PostgreSQL. MVCC, PostgreSQL - (foreign keys), (constraints). PostgreSQL, , , . . , . - . PostgreSQL , BSD . PostgreSQL , , , , . - B-tree, hash, R-tree, GiST ( ) (partial indices) -

15


PostgreSQL ?
, ,
create index idx_partial on foo (x) where x > 0;

(expressional indices) , ,
create index idx_functional on foo ( length(x) );

, . . , . . , .

. Tablespaces ( ) , , , . , , . . PostgreSQL , . PostgreSQL , , , ,, . - PostgreSQL , , . PostgreSQL , . ER PostgreSQL, , . PostgreSQL ( initdb) - template0 template1, . template1, , template1.

15


PostgreSQL ?

PostgreSQL , , . , CREATE DATABASE, - CREATE DOMAIN, - CREATE OPERATOR, - CREATE TYPE. : / CREATE FUNCTION CREATE TYPE CREATE OPERATOR CREATE FUNCTION , primary key - CREATE OPERATOR CLASS . GiST. PostgreSQL GiST ( ), . GiST DataBlade, IBM (. ). GiST (Joseph M. Hellerstein) Generalized Search Trees for Database Systems. GiST POSTGRES PostgreSQL. , 2001 , - NULL, . GiST, : tsearch2 (tree-like) ltree

15


PostgreSQL ?
intarray PostgreSQL contrib/ ( 80) -, , , , xml, , , .. , , mysql, oracle, . SQL, , SQL , PostgreSQL : ANSI SQL 92, ANSI SQL 99 ANSI SQL 2003. . , SQL. , , . . , , . , ( SEARCH_PATH) , . Subqueries - (subselects), SQL92. SQL . Outer Joins - (LEFT,RIGHT, FULL) Rules - , . , rule , - , .. (PER ROW). , view. Views - , . , . 'view' 'view, . 'view' () PostgreSQL pl/pgsql. Cursors - , , , , . Table Inheritance - , , . (DEFAULTS) (CONSTRAINTS). , (only). partitioning. Prepared Statements ( ) - , , PREPARE, (parser), (rewriting rules) (planner), , EXECUTE, . . Stored Procedures - () . , . Savepoints(nested transactions) - " ", , savepoints , , . , . . (GRANT), (REVOKE). - LISTEN NOTIFY

15


PostgreSQL ?
( , notify PID ). (INSERT,UPDATE,DELETE), ( BEFORE), (AFTER). NEW (, ) OLD ( ). Cluster table - , . PostgreSQL : (character(n)) SQL text . Numeric , . SQL:2003 (Large Objects) 2Gb (point, line, circle,polygon, box,...) . (GIS) PostgreSQL PostgreSQL . PostGis. (Network types) inet IPV4, IPV6, cidr (Classless Internet Domain Routing) macaddr (composite types) . (timestamp, interval, date, time) serial bigserial (AUTO_INCREMENT ). PostgreSQL , . 25 (charsets), ASCII, LATIN, WIN, KOI8 UNICODE, locale, . NLS(Native Language Support) - , , , , , , , , , , . PostgreSQL (C,C++,C#,python,perl,ruby,php,Lisp ) (JDBC, ODBC). , , , SQL C. ( ) PL/pgSQL, pl/Tcl, Pl/Perl pl/Python. , PHP, Java, Ruby, R, shell. . psql ( ) , SQL, ( ), , , . phpPgAdmin ( GPL) PostgreSQL . pgAdmin III (GNU Artistic license) PostgreSQL Linux, FreeBSD Windows 2000/XP.

15


PostgreSQL ?
PgEdit - SQL-, Windows Mac. . PostgreSQL 4- : PostgreSQL - SSL,SSH - IP /. , , Kerberos, IDENT , . , , , PostgreSQL .

PostgreSQL
Unlimited 32 TB 1.6 TB 1 GB Unlimited

250 - 1600 Unlimited


Wikipedia ACID Referential integrity Transaction Unicode Schema Temporary table View Materialized view Expression index Partial index Inverted index Bitmap index Domain Cursor Trigger Stored procedure ASE DB2 FireBird InterBase MS SQL MySQL Oracle PostgreSQL $$$ $$$ Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No No No No No No No Yes Yes No No No Yes No Yes Yes IPL2 Yes Yes Yes Yes Yes No Yes No No No No No Yes Yes Yes Yes Yes $$$ Yes Yes Yes Yes Yes Yes Yes No No No No No Yes Yes Yes Yes Yes $$$ Yes Yes Yes Yes No
5

GPL/$$$ Depends1 Depends1 Depends1 Yes No Yes No No No No Yes No No No No4 No4 No4

$$$ Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes

BSD Yes Yes Yes Yes Yes Yes Yes No3 Yes Yes No No Yes Yes Yes Yes Yes

Yes Yes No No No No No No Yes Yes Yes Yes

Yes Yes Yes Yes Yes Yes

User Defined Functions Yes Yes

15


PostgreSQL ?

Tablespace :

Yes Yes

No

?

No5

No1

Yes

Yes

ASE DB2 FireBird InterBase MS SQL MySQL Oracle PostgreSQL

1 - InnoDB ( ) 2 - Interbase Public License 3 - Materialized view ( ) PL/pgSQL 4 - MySQL 5.0, 5 - MS SQL Server 2005 (Yukon)


TODO, (Bruce Momjian), 8.1 , , . , 8.1 , SQL, : bitmap autovacuum Two phase commit JDBC driver IN,OUT,INOUT pl/pgsql (CVS) (100 ) (CVS) MIN,MAX (CVS) , table partitioning, . : 8.0.2, , libpq ( ! , libpq, , DBD::Pg), "ARC", IBM, , "-" "2Q". "ARC" PostgreSQL ( "" ), (buffer management) PostgreSQL. , (A. Elein Mustain) The Saga of the ARC Algorithm and Patent.

PostgreSQL
, , , . , , , , , , . , , , , () , . LRU (Least Recently Used) , , . , , , "cache flooding", (sequential scan) , , .., . , "scan-resistant", , "cache flooding".

0 15


PostgreSQL ?
PostgreSQL , LRU/K, (Tom Lane). K- ( , , LFU ), . (priority queue) : P1 K- (, K=2, LRU/2 ) P2, P1 P2. LRU LRU/1, () . , , , . , . ARC (Adaptive Replacement Cache) , , "" , LRU/K . . (Jan Wieck) 7.5 ( 8.0), , CAR (Clock with Adaptive Replacement) . , ( ) PostgreSQL 8.0 (. (Neil Conway) ), IBM ARC 2002 . - 8.0 , . , IBM ARC , IBM OSS , PostgreSQL, , ARC "-" . PostgreSQL " " BSD , PostgreSQL - . 2005 ARC , 2Q 1994 ARC, "cache flooding" ("scan resistant") ( ), 8.0.2. 2Q (Two Queue) LRU/K, , . , "" , "" LRU/2. : A1 (FIFO), A1, , LRU . A1, , , "" 2Q .

PGDG - PostgreSQL Global Development Group
PostgreSQL (PGDG), , , PostgreSQL (Public Relation), , , 2005 200 . , PGDG - , . , . , , . , .


10-12 ( 2-3 ) ( ): -hackers. ,

1 15


PostgreSQL ?
proposal . - , , , SQL . CVS , , . , , -patches. (anonymous CVS, -commiters - CVS). , , , (backporting). , . , 7.4.7 - bugfix- 7.4. code freeze( ), CVS , . , (contribution modules contrib/) . , CVS , , . pgbuildfarm, , , . , , (regression test), . ( , CVS) . CVS PostgreSQL OSDL ( ), " ". 8 OSDL STP PLM (STP - Scalable Test Platform PLM - Patch Lifecycle Manager). "" , 1-3 . (production), . , - . -. , -, , . , - , . , PR , , . , . , PGDG, .

2 15


PostgreSQL ?


(6 ). . ( 2 ). , . (23). (22) PGDG, PostgreSQL "The PostgreSQL Foundation", PostgreSQL. www.thepostgresqlfoundation.org. PostgreSQL , , : PGDG - PGDG , , , .


POSTGRES , , PostgreSQL . , .org, .info PostgreSQL, , Lycos, BASF. , PostgreSQL, , 2000-2002 , .


PostgreSQL ,

3 15


PostgreSQL ?
, , , , .


PostgreSQL www.postgresql.org, . ( , , ), , PostgreSQL. , Web : archives.postgresql.org pgsql-ru-general - , . www.pgsql.ru/db/mw , , . 2005 32812 , - . PostgreSQL www.pgsql.ru 1 2005 . 20-
name | count --------------------+-------HACKERS | 107696 GENERAL | 93272 SQL | 27574 COMMITTERS | 21384 ADMIN | 20397 PATCHES | 17354 NOVICE | 13772 BUGS | 13700 MISC | 13545 INTERFACES | 13029 JDBC | 12705 QUESTIONS | 7865 ADVOCACY | 6676 CYGWIN | 6166 WWW | 5636 PERFORMANCE | 5359 ODBC | 5182 PORTS | 4769 DOCS | 3991 PHP | 3106 # | Year ------------19355 | 2005 68403 | 2004 71884 | 2003 61604 | 2002 58072 | 2001 38793 | 2000 25258 | 1999 16779 | 1998 15315 | 1997 612 | 1996 7 | 1995

PGsearch ( -) . 480000 67 , . PostgreSQL techdocs.postgresql.org Varlena Powerpostgresql PGnotes ( ) http://www.linuxshare.ru/postgresql/. "PostgreSQL FAQ " ( ): PostgreSQL ftp- . http://www.postgresql.org/download/. , Linux PostgreSQL . PostgreSQL CD (Live CD) - bittorent ISO . ,

4 15


PostgreSQL ?
PostgreSQL Mac OS X . Win32 Pginstaller. , www.postgresql.org/support/. , PostgreSQL.


, PostgreSQL, , PGDG , , : gborg.postgresql.org pgfoundry.org


PostgreSQL - , . , , .


, () 05-07-90225-. 2005 , . : ( , ) PGDG ( ) 1996 , "GreatBridge", "The PostgreSQL Foundation". PostgreSQL ( - o , , ), 1996 locale PostgreSQL, ( Delta-Soft) GiST PostgreSQL, , , , . PostgreSQL OpenFTS. ( ) pgsql.ru. PostgreSQL , , , pgSphere - .

5 15