Evolution Hosting PostgreSQL Database FAQ
- What is PostgreSQL?
- Where can I find news on PostgreSQL?
- Why use PostgreSQL instead of a large commercial database?
- Why use PostgreSQL instead of MySQL?
- How do I install PostgreSQL on Windows?
- How do I convert schema's to PostgerSQL?
What is PostgreSQL?
is a complete ANSI-SQL relational database that is
available under a BSD-style license. This free/open-source DBMS
provides a quick, reliable, transactional datastore, without the
license and cost restrictions found in larger commercial DBMS's.
Version 7.0 provides the capabilities found in other RDBMS's,
including cursors, views, triggers, native functions and object permission levels. It
also includes user-defined types, table inheritance, foreign keys,
stored procedures, and sophisticated locking. Version 7.1
completes the functionality list to include outer joins, unlimited
row lengths, and the Write-ahead Log (WAL) to maintain database
consistency in the event of an OS crash.
Where can I find news on PostgreSQL?
Visit the PostgreSQL news site.
Why use PostgreSQL instead of a large commercial database?
There are several ways of measuring software: features,
performance, reliability, support, and price.
PostgreSQL has most features present in large commercial DBMS's,
like transactions, subselects, triggers, views, foreign key referential integrity,
and sophisticated locking. PostgreSQL has many features larger commercial databases
don't have, like user-defined types, inheritance, rules, and multi-version concurrency
control to reduce lock contention.
PostgreSQL runs in two modes. Normal fsync mode
flushes every completed transaction to disk, guaranteeing that if
the OS crashes or loses power in the next few seconds, all
data is safely stored on disk. In this mode, PostgreSQL is slower than
most commercial databases, partly because few commercial
DBMS's, do such conservative flushing to disk in
default modes. In PostgreSQL's no-fsync mode, it is usually
faster than commercial databases, though in this mode, an OS crash
could cause data corruption. PostgreSQL is working to provide an
intermediate mode that suffers less performance overhead than full
fsync mode, and will allow data integrity within 30 seconds of an OS crash.
PostgreSQL handles each user connection by creating a Unix process.
Backend processes share data buffers and locking information.
With multiple CPU's, multiple backends can easily run on
A DBMS must be reliable, or it
is worthless. PostgreSQL releases well-tested, stable code that
has a minimum of bugs. Each release has at least one month of
beta testing, and the PostgreSQL release history shows the ability
to provide stable, solid releases that are ready for production use.
PostgreSQL compares favorably to other database software in this
The PostgreSQL mailing list provides a large group of developers and
users to help resolve any problems encountered. While they can not
guarantee a fix, commercial DBMS's don't always
supply a fix either. Direct access to developers, the user
community, manuals, and the source code often make PostgreSQL
support superior to other DBMS's. There is
commercial per-incident support available for those who need it.
PostgreSQL is free for all use, both commercial and non-commercial.
Additionally, customers can add PostgreSQL code to their products with no
limitations, except those outlined in the PostgreSQL BSD-style license.
Why use PostgreSQL instead of MySQL?
Currently, the most popular free database in use is MySQL
. It's small, easy to configure, and fast. For J2EE Development, however,
PostgreSQL has more to offer.
MySQL's transaction implementation and views make it unsuitable
for complex usage.
MySQL only recently added transaction support and
it locks the entire table involved with a transaction. This has major
performance/concurrency ramifications. PostgreSQL, on the other hand,
has MVCC, which only locks the proper rows of a table
(Only Oracle and DB2 have a similiar feature). MySQL is most popular for
websites/applications that do not require transaction support.
MySQL isn't as compliant to ANSI SQL standards as PostgreSQL.
Benchmarks between PostgreSQL and MySQL show that
MySQL has good performance compared to PostgreSQL when looking at
non-transactional updates. PostgreSQL, however, can handle heavier
loads than MySQL, and is more stable under larger numbers of
concurrent connections than MySQL. The latest beta version of
MySQL does provide transaction support using the Berkley DB layer.
However, testing MySQL against PostgreSQL using transactions shows
that PostgreSQL runs faster than MySQL.
PostgreSQL provides a better mechanism to increase performance of SQL queries
than MySQL, and also provides more data-types than any other database on the
market. Additionally, there are utilities that are in the works to provide
hot-backups and clustering ability to PostgreSQL.
Both RDBMS systems are free and easy to use, with MySQL
under the GPL license, and PostgreSQL under a BSD-style license.
PostgreSQL setup is a bit more complicated due to its additional
functionality. This complexity is reduced by packages available to install
PostgreSQL rather than building from source.
In short, when one is looking for a complete, reliable, open-source
RDBMS, PostgreSQL provides the most features, especially around
transactions, that J2EE systems require.
For more information on PostgreSQL Vs. MySQL, explore these links:
How do I install PostgreSQL on Windows?
Read the Evolution Hosting PostgreSQL Windows Setup FAQ.
How do I convert schema's to PostgerSQL?
Here is a guide for porting Oracle schema's to PostgreSQL
and specifically for