The Advantages of PostgreSQL - Bruce Momjian

arizonahoopleΔιαχείριση Δεδομένων

28 Νοε 2012 (πριν από 4 χρόνια και 11 μήνες)

253 εμφανίσεις

The Advantages of PostgreSQL
BRUCE MOMJIAN
January,2012
POSTGRESQL offers companies many advantages that can help
their businesses thrive.
Creative Commons Attribution License http://momjian.us/presentations
The Advantages of PostgreSQL 1/43
Introduction

Why use databases?

Why use open-source?

PostgreSQL history

PostgreSQL in the real world
The Advantages of PostgreSQL 2/43
Why use databases?
The Advantages of PostgreSQL 3/43
Non-SQL Database Applications
Part
Application
Order
Customer
Order
Customer Order
Customer Part
PartPartCustomer
Order
The Advantages of PostgreSQL 4/43
SQL Database Applications
OrderCustomer
SQL Server
Part
Application
Order
Customer
Part
OrderOrder
Customer
Part
Customer
Part
The Advantages of PostgreSQL 5/43
Database Transaction Protection (ACID)

Multiple changes either all take place,or none of them

Data always in a consistent state

In-process changes invisible to outside users

Data reliably stored

High level of concurrency and reliability
The Advantages of PostgreSQL 6/43
Why use Open Source
Software?
The Advantages of PostgreSQL 7/43
Closed-Source Software
Black Box
987234VWOJVC
Data ...
Func(...)
The Advantages of PostgreSQL 8/43
Open-Source Software
Clear Box
Data ...
Func(...) 987234VWOJVC
The Advantages of PostgreSQL 9/43
Support of Close-Source Software
Support
Database
Company
The Advantages of PostgreSQL 10/43
Support of Open-Source Software
Support
SupportSupport
Source Code
Mailing Lists
EnterpriseDB
The Advantages of PostgreSQL 11/43
The Future of Open Source
Reliability
Time
Open Source
Close Source
FeaturesPerformance
The Advantages of PostgreSQL 12/43
PostgreSQL License
Use for any purpose,including sale of customized versions with
your closed-source modifications.PostgreSQL supports custom:

Functions

Operators

Data Types
The Advantages of PostgreSQL 13/43
PostgreSQL History
The Advantages of PostgreSQL 14/43
The University of California
at Berkeley
The Advantages of PostgreSQL 15/43
Postgres Postgres95
Michael Stonebraker Jolly Chen and Andrew Yu
The Advantages of PostgreSQL 16/43
PostgreSQL Code Base History

Ingres —research prototype,spawned Relational Technologies,
purchased by Computer Associates

Postgres —research prototype,spawned Illustra,purchased by
Informix

Postgres95 —added SQL,spawned PostgreSQL
The Advantages of PostgreSQL 17/43
PostgreSQL Through the Years
1977-1985
Ingres
1986-1994
Postgres
1994-1995
Postgres95
1996-
PostgreSQL
The Advantages of PostgreSQL 18/43
Developer’s Globe
The Advantages of PostgreSQL 19/43
PostgreSQL Core Team
Top row:Thomas Lockhart,Jan Wieck,TomLane,Marc Fournier
Bottom row:Vadim Mikheev,Bruce Momjian
The Advantages of PostgreSQL 20/43
Release Dates and Sizes
Date
Release
Lines of code
1994
244,581
1996-08-01
1.02.1
1996-10-27
1.09
178,976
1997-01-29
6.0
1997-06-08
6.1
200,709
1997-10-02
6.2
225,848
1998-03-01
6.3
260,809
1998-10-30
6.4
297,918
1999-06-09
6.5
331,278
2000-05-08
7.0
383,270
2001-04-13
7.1
410,500
2002-02-04
7.2
394274
2002-??-??
7.3
453282
The Advantages of PostgreSQL 21/43
PostgreSQL Releases
Release 1.02
Date:1996-08-01

Initial release by PostgreSQL Global Development Team

Apply all outstanding email patches.
The Advantages of PostgreSQL 22/43
PostgreSQL Releases (Continued)
Release 1.09
Date:1996-11-04

Fixes for server and command failures.
The Advantages of PostgreSQL 23/43
PostgreSQL Releases (Continued)
Release 6.0
Date:1997-01-29

Unique indexes

GIST added

Improved authentication

IN/BETWEEN added
The Advantages of PostgreSQL 24/43
PostgreSQL Releases (Continued)
Release 6.1
Date:1997-06-08

New data types:DATETIME,TIMESPAN,CIRCLE

GEQO

Improved optimizer statistics

Libpq++ overhauled

Multi-column btree indexes

new SET/SHOW/RESET commands

New SEQUENCE feature
The Advantages of PostgreSQL 25/43
PostgreSQL Releases (Continued)
Release 6.2
Date:1997-10-02

New Java JDBC driver

Triggers added

Server Programming Interface (SPI) added

NOT NULL constraint

DEFAULT and CONSTRAINT added

ANSI functions added for DATE/TIME and PRECISION
The Advantages of PostgreSQL 26/43
PostgreSQL Releases (Continued)
Release 6.3
Date:1998-03-01

Subselcts

Unix domain socket support for performance

Improved user password configuration

Much improved documentation

Separate permissions for VIEWs

PRIMARY KEY support

PL/TCL procedural language added

UNION added

Python added

ECPG added

New ODBC driver

Pgaccess added
The Advantages of PostgreSQL 27/43
PostgreSQL Releases (Continued)
Release 6.4
Date:1998-10-30

Improved RULEs and VIEWs

PL/PgSQL added

Multi-byte character support

Internet address data types

HAVING added
The Advantages of PostgreSQL 28/43
PostgreSQL Releases (Continued)
Release 6.5
Date:1999-06-09

MVCC

Live backups

NUMERIC data type

Temporary tables

CASE

INTERSECT

EXCEPT

LIMIT/OFFSET

SELECT…FOR UPDATE

Optimizer overhaul
The Advantages of PostgreSQL 29/43
PostgreSQL Releases (Continued)
Release 7.0
Date:2000-05-08

Foreign keys

More optimizer improvements

ANSI JOIN syntax

PL/Perl

Oracle compatibility functions
The Advantages of PostgreSQL 30/43
PostgreSQL Releases (Continued)
Release 7.1
Date:2001-04-13

Write-ahead Log (WAL)

TOAST

Outer Joins

Overhauled function manager
The Advantages of PostgreSQL 31/43
PostgreSQL Releases (Continued)
Release 7.2
Date:2001-12-??

Non-locking VACUUM

Fix for Transaction id/OID wraparound

Security fixes

Optimizer improvements

Error message internationalization
The Advantages of PostgreSQL 32/43
Internals Flowchart
utility
Plan
Optimal Path
Query
Postmaster
Postgres Postgres
Libpq
Main
Generate Plan
Traffic Cop
Generate Paths
Execute Plan
e.g. CREATE TABLE, COPY
SELECT, INSERT, UPDATE, DELETE
Rewrite Query
Parse Statement
Utility
Command
Storage ManagersCatalogUtilities
Access Methods Nodes / Lists
The Advantages of PostgreSQL 33/43
Performance - AS3AP
The Advantages of PostgreSQL 34/43
Performance - TPC-C
The Advantages of PostgreSQL 35/43
PostgreSQL in the Real
World
The Advantages of PostgreSQL 36/43
Database Migration

Oracle

IBM’s DB2

Microsoft-SQL

Microsoft Access

Informix

Interbase

Dbase/FoxPro

MySQL
The Advantages of PostgreSQL 37/43
Database Languages

C

C++

Embedded C

Java

Perl

Python

Tcl/Tk

Php

Odbc
The Advantages of PostgreSQL 38/43
PostgreSQL Platforms
AIX
BeOS
BSD/OS
FreeBSD
HP-UX
IRIX
Linux
MacOS
NetBSD
OpenBSD
SCO UnixWare
Solaris
SunOS
Tru64
Windows NT/2000
The Advantages of PostgreSQL 39/43
PostgreSQL Usage

Order Entry,Inventory,Billing

Customer Relations Management (CRM)

Data warehouse,data analysis

Medical/Hospital Records Storage

Genetics

Financial Accounting,Banking,Payroll

Military

Government,Social services,Elections

Education

Publishing

Geography (GIS)

Hotel Reservations,Restaurants

Telephone billing,call tracking

Helpdesk problem report tracking

Sales Tracking

Business to business commerce
The Advantages of PostgreSQL 40/43
PostgreSQL Web Site Usage

Product purchase

User preferences

Job listings

Auctions

Real Estate listings

Community building

Dynamic content

Text indexing
The Advantages of PostgreSQL 41/43
Future Directions
The Advantages of PostgreSQL 42/43
Conclusion
http://momjian.us/presentations
The Advantages of PostgreSQL 43/43