Thread: Better Features document?
One thing that confused me when I started seriously looking at PostgreSQL was the features it had relative to other competitors. We have so many powerful features that are often underused by new users: * procedural languages * triggers * rules * views * custom aggregate functions * ... and more and so on. The documentation does a good job (& gets better all the time!) at explaining this, but many users never read that far into the documentation, and, of course, many people never get to the documentation at all -- they're evaluating software by a 10-minute glance through the web site. We have a features document at http://www.postgresql.org/features.html but this covers the architecture of the system (postgres / postmaster, etc), and very little about some of our other competitive advantages. My fear is that users & potential users come to PG w/o learning what a view is, how triggers can be helpful in designing database systems, why custom aggregates are so great, etc. (Those of us w/CS backgrounds do well to remember how many web database designers don't have that background!) Therefore, people compare us sometimes w/other database systems (mostly MySQL simply as 'MySQL seems faster and easier to install, but PostgreSQL has some features, like transactions, that may be useful to complicated databases', completely missing how many PG features are important to everyone that is designing databases, simple or large. I started writing a 'Features+' document a few months ago, but it got sat aside during a busy work time. I'd like to restart that work. I don't want to recreate the manuals -- I envision something like a 5-page 'product datasheet' that explains just enough about what a trigger is so that users have no excuse for not digging into that chapter, and that people understand how fantasic procedural languages are. Before I start digging into that, does anyone know if there exists a short- or medium- length (2-5 p) document that explains, for ordinary database mortals, about the sophisticated features of PG? Does anyone want to help put this together? -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Sounds great.
I have collected some comments from people about PostgreSQL vs. other
databases at:
ftp://candle.pha.pa.us/pub/postgresql/comparison.mbox
This may help. You can also link to the docs and my book at:
http://www.postgresql.org/docs/awbook.html
to give people more detailed information about the features. I think
such a page would be a great idea. I can help too.
>
> One thing that confused me when I started seriously looking at PostgreSQL
> was the features it had relative to other competitors. We have so many
> powerful features that are often underused by new users:
>
> * procedural languages
> * triggers
> * rules
> * views
> * custom aggregate functions
> * ... and more
>
> and so on. The documentation does a good job (& gets better all the
> time!) at explaining this, but many users never read that far into the
> documentation, and, of course, many people never get to the documentation
> at all -- they're evaluating software by a 10-minute glance through the
> web site.
>
> We have a features document at
>
> http://www.postgresql.org/features.html
>
> but this covers the architecture of the system (postgres / postmaster,
> etc), and very little about some of our other competitive advantages.
>
> My fear is that users & potential users come to PG w/o learning what a
> view is, how triggers can be helpful in designing database systems, why
> custom aggregates are so great, etc. (Those of us w/CS backgrounds do well
> to remember how many web database designers don't have that background!)
>
> Therefore, people compare us sometimes w/other database systems (mostly
> MySQL simply as 'MySQL seems faster and easier to install, but PostgreSQL
> has some features, like transactions, that may be useful to complicated
> databases', completely missing how many PG features are important to
> everyone that is designing databases, simple or large.
>
> I started writing a 'Features+' document a few months ago, but it got sat
> aside during a busy work time. I'd like to restart that work.
>
> I don't want to recreate the manuals -- I envision something like a 5-page
> 'product datasheet' that explains just enough about what a trigger is so
> that users have no excuse for not digging into that chapter, and that
> people understand how fantasic procedural languages are.
>
> Before I start digging into that, does anyone know if there
> exists a short- or medium- length (2-5 p) document that explains, for
> ordinary database mortals, about the sophisticated features of PG?
>
> Does anyone want to help put this together?
>
>
> --
> Joel Burton <jburton@scw.org>
> Director of Information Systems, Support Center of Washington
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Sat, Apr 07, 2001 at 03:52:00PM -0400, Joel Burton wrote:
> My fear is that users & potential users come to PG w/o learning what a
> view is, how triggers can be helpful in designing database systems, why
> custom aggregates are so great, etc. (Those of us w/CS backgrounds do well
> to remember how many web database designers don't have that background!)
Wholeheartedly agreed! I see this happen many times here at our
university. People look at PG's website, then look at MySQL's site and see
MySQL's (very misleading) "benchmarks", and since they don't have the db
background and are looking for an esay to use DB, they go with MySQL.
> Does anyone want to help put this together?
Let me know how can I be of help. It should only take you one day to
write a good document listing PG's features.
-Roberto
--
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
Sorry, "Quit" not available in unregistered versions.
Joel Burton writes: > I started writing a 'Features+' document a few months ago, but it got sat > aside during a busy work time. I'd like to restart that work. > > I don't want to recreate the manuals -- I envision something like a 5-page > 'product datasheet' that explains just enough about what a trigger is so > that users have no excuse for not digging into that chapter, and that > people understand how fantasic procedural languages are. If you can make it a little less than 5 pages (like 2), we can put it into the preface of the documentation. I'd imagine it stuck somewhere near the following URL: http://www.de.postgresql.org/devel-corner/docs/postgres/preface.html (main web server down right now) You will notice that the section "What is PostgreSQL?" at the cited location is already a feeble attempt to be a feature advertisement. However, the set and the focus of the features have clearly changed over time, so it would be time to update this section. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Hi Joel,
Since you've got a 5 page document already, maybe you'd like to put what
you've got on the net somewhere (I can put it on techdocs.postgresql.org
if you'd like), then massage it into shape for an updated 2 page intro
Peter is suggesting?
Don't want to lose 3 pages of what may just be really insightful
work... :-)
Regards and best wishes,
Justin Clift
Peter Eisentraut wrote:
<snip>
> If you can make it a little less than 5 pages (like 2), we can put it into
> the preface of the documentation. I'd imagine it stuck somewhere near the
> following URL:
<snip>
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
Ok, so here's the draft I wrote a few months ago up the updated features
document.
--
pg_advocates of the world, unite!
As I've recommended it to other people, I've found myself at a loss to
explain all of
the features that recommend the system. Digging around, almost all of
these can be
identified through the various manuals and FAQs.
I'm envisioning a document that could be posted on the postgresql.org web
site
that gives more information about the server's capabilities than the
introductory
information on the site, without having to dig around in different
manuals. I think
it shouldn't attempt to be completely comprehensive (many features are
wonderful,
but obscure for first-time buyers :-) ), but should list the features,
along with
a short explanation of what they mean and why they might be useful. With
the explosion
of new Unix/Linux users, I constant come across new database
administrators that
come from Windows-database backgrounds, and have no idea what a trigger
is,
let alone what our more unique features are, and how the might be helpful.
I've written out the features I see, in some order, with some attempts
at explanation. I'd be grateful for any corrections or
suggestions. Embedded throughout are XXX for areas that need immediate
help; however, the entire document could use a critical eye for
suggestions and improvements.
At this point, its just a text document, but as it improves, I'll
translate it into
an attractive HTML document suitable for posting or framing.
Suggestions/improvements should be sent to jburton@scw.org. I will
post the list improved versions of the document.
----------------------------------------------------------
PostgreSQL is the world's most advanced Open Source database server.
-------------
INSTALLATION:
Supported Platforms:
* AIX - IBM on AIX 3.2.5 or 4.x
* BeOS - x86
* Alpha - DEC Alpha AXP on Digital Unix 2.0, 3.2, 4.0
* BSD44_derived - OSs derived from 4.4-lite BSD (NetBSD, FreeBSD)
* BSDI - BSD/OS 2.x, 3.x, 4.x
* HP-UX - HP PA-RISC on HP-UX 9.*, 10.*
* IRIX - SGI MIPS on IRIX 5.3
* Linux - Intel i86 Alpha SPARC PPC M68k
* Mac OS X - PPC
* SCO Unix - SCO 3.2v5 Unixware
* Solaris - i386, SPARC
* SunOS - SUN SPARC on SunOS 4.1.3
* SVR4 - Intel x86 on Intel SVR4 and MIPS
* Tru64 - Compaq Alpha
* ultrix4 - DEC MIPS on Ultrix 4.4
* UnixWare
* Windows NT/2000 (using Cygwin)
* (I got this list from the FAQ, is it missing anything?)
Unsupported Platforms: (previous versions have worked, not tested
recently)
* DG/UX - m88k
* MkLinux - PPC750
* NextStep - x86
* QNX - x86
* System V R4 - m88k MIPS
* Ultrix - MIPS VAX
Easy installation:
* Uses standard configure and make options for building from source.
* Binary RPMs are available for most popular Linux distributions.
* Can be installed without root permissions.
* Included in most popular Linux and BSD distributions
-----------
HISTORY, DEVELOPERS & LICENSE:
* Descended from Ingres and Postgres, cutting-edge academic database
projects
* Maintained, improved, and supported by a stable Global Development
Group, and a large number of contributors
* Source code available for download, and anonymous CVS access is
available
* Licensed under BSD license, allowing truly free modifications and
redistribution of binaries
-----------
DATA FEATURES
Rich support for data types:
* Includes most standard and exotic SQL92 and SQL3 data types,
including INT, BOOL, CHAR, VARCHAR, DATE, INTERVAL, TIMESTAMP.
* Additional types for geometric constructs (points, lines, etc.),
TCP/IP network addresses, Ethernet card IDs, and more.
* New types can be defined, along with neccessary supporting functions
and operators.
Support for BLOB / Large Objects:
* PostgreSQL supports storage of binary large objects, including
pictures, sounds, or video.
* These objects can be retrieved (in whole or part) by client
applications.
Multibyte and LOCALE-aware settings
* For storage and ordering of international characters and character
sets.
Foreign key / Referential integrity support:
* Supports CREATE TABLE ... FOREIGN KEY syntax for referential
integrity
* Supports different actions for updating and deleting, including
cascading, restricting, and restoring to default or NULL values. These
features are critical for data integrity in many applications.
* Foreign keys can be detected by database modeling tools, such as
Computer Associates' ERwin, to ease database design and documentation.
-----------
SQL FEATURES:
SELECT FEATURES:
* Support for standard SQL conditions, such as CASE WHEN THEN,
COALESCE, and NULLIF.
* Support for joins, including INNER, OUTER (LEFT and FULL) and NATURAL
JOINS.
* Support for subselects:
* Subqueries are nested queries that allow complex questions to be
answered entirely through the database
* Using subqueries can simplify and speed up database applications
* XXXAn example subquery?
* Support for SELECT DISTINCT and SELECT DISTINCT ON ( column ), to
show only unique rows, and matching data for unique values.
* Full support for GROUP BY and aggregate (domain) functions, including
COUNT(), SUM(), AVG(), MIN(), MAX(), STDDEV() and VARIANCE().
* New aggregate functions can easily be created in C or any procedural
language PostgreSQL supports.
* Sub-selects in FROM clause, such as SELECT COUNT(x), AVG(x),
SUM(x) FROM (SELECT SUM(weight) AS x FROM Items GROUP BY manufacturer) AS
Items
* Support for UNION and UNION ALL.
* Support for extensions INTERSECTS and EXCEPT, which are used like
UNION, to join two or more SELECT statements,
but find only intersecting records or missing records.
* Extensions for LIMIT and OFFSET to allow for the selection of any
arbitrary number of records, ie SELECT * FROM Items
ORDER BY cost LIMIT 5;
OTHER SQL FEATURES:
Procedural Languages:
* Procedural languages can be used to create user-defined functions and
procedures, and are used to create triggers.
* PostgreSQL currently supports several procedural languages:
* C XXXIs it fair to call C a PL?
* SQL
* PL/PGSQL (very similar to Oracle's PL/SQL)
* PL/Tcl
* PL/Perl
* PL/Python (alpha)
* Procedural languages functions can be used by and defined by
untrusted users.
Comprehensive Library of Functions:
* Hundreds of built-in functions in its library, including rich support
for mathematical, date/time, and string manipulation.
* Additional functions: ISBN/ISSN handling, SOUNDEX sounds-like
matching
* Support functions for full-text indexing
* Online library of user functions at http://techdocs.postgresql.org
* ODBC functions
User-defined functions:
* User defined functions allow designers to create new functions.
* These can be programmed in C, or in any of the procedural languages
PostgreSQL supports.
* User-defined functions allow database designer to encapsulate
business logic in the database, rather than in the front-end.
Comprehensive operators:
* Inequalities and logical conditions
* Standard SQL LIKE matching and case-insensitive LIKE matching
* Full regular expression matching and case-insensitive regular
expression matching
* Time/date and interval comparisons
* Many more
* New operators can be defined using procedures written in C or any of
the procedural languages PostgreSQL supports.
Views:
Views are stored SQL SELECT statements that are used to abstract complex
views. For example, a common operation
that joins four tables, with subselects, a WHERE clause, an ORDER BY
clause, etc., can be given
the name EMPLOYEE_INFO, and treated as a virtual table--including (if
appropriate)
INSERTS, UPDATES, and DELETEs. This allow database users to work more
easily with the data,
and requires less sophistication in query writing for most users.
* Supports views
Triggers:
Triggers are procedures that are called by the database on defined
actions. For example, a trigger could be used to log every time a
record in inserted or deleted to or from a table, or to update a field
every time another field has been changed.
* Supports triggers before and/or after actions
* Supports triggers for every row or every action
* Trigger functions can be written in C or any of the procedural
languages PostgreSQL supports.
Rules:
Rules are an advanced feature of PostgreSQL that allows a database
designer to substitute any
SQL table operation (SELECT, INSERT, DELETE, etc.) for another
operation. Rules can be used
to return different sets based on permissions, turn an attempted update
into a delete, etc.
Transactions:
Transactions allow all data modifications to be encapsulated in a
transaction. This allows the
database to rollback and restore all original data if there is an error in
the transaction
or if the database server crashes. Transactions are a critical feature for
ensuring
data integrity in many applications.
* Support for COMMIT or ROLLBACK
* Works transparently with transaction features of interface programs,
such as Perl's DBI, Zope, JDBC and ODBC transactions, etc.
* XXXSomeone want to explain MVCC for the masses?
Inheritance:
* XXXI never use this--anyone want to explain?
-----------
EXTENSIONS:
Replication:
Contact Management:
(XXXI don't use either of these. Can anyone at pgsql inc, or anyone at
all, provide some bullets of the advantages?)
-----------
DOCUMENTATION & SUPPORT:
Documentation:
* Comes with four comprehensive manuals (online or in print formats)
* Tutorial manual
* Excellent commercial, full length book (available in print form, or
online at http://www.postgresql.org/docs/awbook.html)
Online support:
* Support is available through 13 online discussion lists.
* Comprehensive installation instructions and FAQ included in
distribution.
* Online resource listings at http://techdocs.postgresql.org
Commercial contracts:
* Commercial contracts are available from several companies, and
consulting from hundreds of companies and independent consultants.
* Commercially-packaged versions of PostgreSQL are available from
GreatBridge LLC, http://www.greatbridge.org.
-----------
SERVER ADMINISTRATION:
Backup and Recovery tools:
* PostgreSQL includes a tool (pg_dump) to handle backup of all database
data. These backups, which can be performed while the database is being
used, are platform-independent, human-readable, and can be stored as tar
archives or in compressed formats.
* Database can be restored as a whole, or (using
pg_restore) selectively to recover individual database elements.
Security:
* PostgreSQL offers a user/group security model that can restrict
operations by user and/or group.
* Privileges can be assigned for viewing and modifying data.
* Access to the database server itself can be restricted based on host,
username, database.
* Database passwords can be sent encrypted, and database connections
can be encrypted using SSH or SSL.
Temporary tables
* Temporary tables (which can be created by CREATE TABLE or SELECT
INTO) are automatically dropped at the end of a database connection.
* Easier maintenance than removing normal table manually.
GUI Administration:
* pgaccess: a platform-independent Tcl/Tk program for database
maintenance and report-writing
* pgAdmin: a comprehensive, Windows-based PostgreSQL administration
program
* XXXAnd some web programs I don't know about
-----------
CLIENT INTERFACES
Client programming languages/interfaces:
* Perl (through Pg and DBD::Pg)
* Python (through Pygres or PoPy)
* PHP (support built-in to PHP)
* Tcl
* ODBC, and therefore many ODBC client programs, including Microsoft
Access, StarOffice, Applix, etc.
* JDBC (Java Database Connectivity)
* C (through libpq and libpqeasy)
* Can use embedded SQL statements in C
* C++ (through libpq++)
* Emacs LISP
* R (Open Source statistical package)
* Zope (Open Source web application server)
* XXXand othhers, I'm sure--what's missing? ]
Web Publishing Solutions:
With its support for transactions, defined functions, and views,
PostgreSQL offers web developers
a safe and structured programming environment. Almost all web development
systems that uses Perl,
Python, ODBC, PHP, or Tcl can use PostgreSQL.
* Zope
* OpenACS, an Open Source port of ArsDigita's ACS
* ColdFusion
* Mason
* EmbPerl
* mod_perl and DBI
* XXXAnd others, no doubt
* Web groupware packages supporting PostgreSQL: Twig
(www.screwdriver.net/twig), XXXand no doubt many others
Query monitor:
* Comes with psql, a featured-filled text console-based interactive
query monitor.
* Includes full support for history and history editing,
customization, local/remote database access, and importing and exporting
of data.
* In addition, graphic query monitors available for many operating
systems and desktops, including Windows, KDE, and GNOME.
Event notification
* LISTEN and NOTIFY can be used to pass messages or notify different
clients of an event in the database.
* Can be used to coordinate different front-end clients (even across
different front-end systems).
--------
PERFORMANCE & STABILITY
Performance:
* Very competitive performance, especially for multi-user applications
and larger databases.
* Sophisticated locking models permits high scalability for concurrent
writes and reads.
Stability:
[ any info on large dbs used by PG? ]
---------
USERS
* SourgeForge
* Large open source database-backed collaborative web site
* OpenACS
* Fully open source version of ArsDigita's community-based web
system, ACS
* XXXBruce, surely you know of others.
-----
AND MORE
And More (Optional Contributions)
Distributed with PostgreSQL are many optional contributions, such as
SOUNDEX() functions
for 'sounds-like' string matching, full-text indexing, cryptographic hash
functions (including
SHA1 and MD5), user-handled long-term cooperative locking, ISBN/ISSN
number types, and more. In
addition, there are many additional functions that can be downloaded from
web sites or from the
PostgreSQL discussion lists.
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
Great! You guys do it and I'll put it on the website. Vince. On Sun, 8 Apr 2001, Bruce Momjian wrote: > Sounds great. > > I have collected some comments from people about PostgreSQL vs. other > databases at: > > ftp://candle.pha.pa.us/pub/postgresql/comparison.mbox > > This may help. You can also link to the docs and my book at: > > http://www.postgresql.org/docs/awbook.html > > to give people more detailed information about the features. I think > such a page would be a great idea. I can help too. > > > > > One thing that confused me when I started seriously looking at PostgreSQL > > was the features it had relative to other competitors. We have so many > > powerful features that are often underused by new users: > > > > * procedural languages > > * triggers > > * rules > > * views > > * custom aggregate functions > > * ... and more > > > > and so on. The documentation does a good job (& gets better all the > > time!) at explaining this, but many users never read that far into the > > documentation, and, of course, many people never get to the documentation > > at all -- they're evaluating software by a 10-minute glance through the > > web site. > > > > We have a features document at > > > > http://www.postgresql.org/features.html > > > > but this covers the architecture of the system (postgres / postmaster, > > etc), and very little about some of our other competitive advantages. > > > > My fear is that users & potential users come to PG w/o learning what a > > view is, how triggers can be helpful in designing database systems, why > > custom aggregates are so great, etc. (Those of us w/CS backgrounds do well > > to remember how many web database designers don't have that background!) > > > > Therefore, people compare us sometimes w/other database systems (mostly > > MySQL simply as 'MySQL seems faster and easier to install, but PostgreSQL > > has some features, like transactions, that may be useful to complicated > > databases', completely missing how many PG features are important to > > everyone that is designing databases, simple or large. > > > > I started writing a 'Features+' document a few months ago, but it got sat > > aside during a busy work time. I'd like to restart that work. > > > > I don't want to recreate the manuals -- I envision something like a 5-page > > 'product datasheet' that explains just enough about what a trigger is so > > that users have no excuse for not digging into that chapter, and that > > people understand how fantasic procedural languages are. > > > > Before I start digging into that, does anyone know if there > > exists a short- or medium- length (2-5 p) document that explains, for > > ordinary database mortals, about the sophisticated features of PG? > > > > Does anyone want to help put this together? > > > > > > -- > > Joel Burton <jburton@scw.org> > > Director of Information Systems, Support Center of Washington > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Sun, 8 Apr 2001, Bruce Momjian wrote: > Sounds great. > > I have collected some comments from people about PostgreSQL vs. other > databases at: > > ftp://candle.pha.pa.us/pub/postgresql/comparison.mbox > > This may help. You can also link to the docs and my book at: > > http://www.postgresql.org/docs/awbook.html > > to give people more detailed information about the features. I think > such a page would be a great idea. I can help too. Thanks for the links. Your book, of course, is probably the first thing that new users should be reading; I added a link in the document. One area that you could help is the Who's Using PostgreSQL section. If this features document has a datasheet/sales tool feeling, I think it should provide some information about the scalability and real-world use of PG. I posted a draft start of the time to pgsql-docs. Thanks! -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
On Sat, 7 Apr 2001, Roberto Mello wrote: > On Sat, Apr 07, 2001 at 03:52:00PM -0400, Joel Burton wrote: > > Wholeheartedly agreed! I see this happen many times here at our > university. People look at PG's website, then look at MySQL's site and see > MySQL's (very misleading) "benchmarks", and since they don't have the db > background and are looking for an esay to use DB, they go with MySQL. > > Let me know how can I be of help. It should only take you one day to > write a good document listing PG's features. I posted a draft to pgsql-docs this morning. It needs a critical eye for what may have been left out. It also needs some more coverage of PG-interfaces, especially in the web world. (You're involved in the OpenACS project, right? Perhaps you know of other things like that to cover.) Thanks for the enouragement, -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
On Sun, 8 Apr 2001, Joel Burton wrote:
> One area that you could help is the Who's Using PostgreSQL section.
> If this features document has a datasheet/sales tool feeling, I think it
> should provide some information about the scalability and real-world use
> of PG.
Have you seen the gallery?
http://www.pgsql.com/user_gallery/
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================