why postgresql over other RDBMS

You've addressed cost and performance.
Not much left.

Try it out for yourself and see if it works for you.

On May 24, 2007, at 4:06 AM, Jasbinder Singh Bali wrote:

Tom Allison wrote:

You've addressed cost and performance.
Not much left.

Try it out for yourself and see if it works for you.

+ elephant in logo
- unpronounceable name
+ excellent mailing lists
+ excellent developer community
- you can download as many copies as you like and a salesman still won't 
take you out to lunch

If you want specific vs each DB...

MSSQL obviously integrates nicely with the rest of the MS developer
tools. If you're planning a .NET (TM) deployment over a range of Windows
(TM) systems and have a lot of experiences MS developers in-house then
it's perhaps the default choice.

Oracle has much more experience running on top-end hardware than PG. If
you've got the in-depth knowledge and/or the money then you can push it
further.

On the other hand, I can buy a 2-cpu x 4-core machine with 16GB RAM and
half-a-dozen disks for �5k from dell (not that you necessarily would).
That's a lot of oomph for the money - think what it would have cost five
years ago.

Add Debian + PostgreSQL, total cost=�5k.

Add Windows + SQL Server, total cost = �12k

--
Richard Huxton
Archonet Ltd

On Thursday 24 May 2007 10:06, Jasbinder Singh Bali wrote:

Hi
I was wondering, apart from extensive procedural language support and being
free,
what are other major advantages of Postgresql over other major RDBMS like
oracle and sql server.

Any pointers would be highly appreciated.

Thanks,
~Jas

A free (as in free speech) license. You can still use postgresql if the
developers decide to stop making postgres, or stop making it free, as it is
likely it will be forked and continued by others (like XFree86 forked into
Xorg).

And, you won't run into annoying licence agreements that you can't use it on
machines with two CPU's...

In my experience as sysadmin, I prefer free licenses (open source) software so
much above commercial software.

Something to think about.

Better support!

Where else can you get feedback from the actual programmers (sometimes
within minutes of writing a message) than here?

Ben

On 5/24/07, Richard Huxton <dev@archonet.com> wrote:

Tom Allison wrote:

You've addressed cost and performance.
Not much left.

Try it out for yourself and see if it works for you.

+ elephant in logo
- unpronounceable name
+ excellent mailing lists
+ excellent developer community
- you can download as many copies as you like and a salesman still won't
take you out to lunch

+ Friendly toolset in the box. [1]
+ Transactional DDL. [2]
+ Table inheritance, if you care to use unportable features.
+ Extensibility. [3]
+ PostGIS for spatial extensions.
- Replication offerings suck.
- Warm standby involve a lot of manual labour.
- Point-in-time recovery involve a lot of manual labour.

[1]: psql+readline, pg_dump etc. are a breeze compared to crusty Oracle tools; psql feels distinctly modern compared to MySQL's crummy interpreter.
tools; psql feels distinctly modern compared to MySQL's crummy
interpreter.

[2]: Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a "begin transaction", then issue "create table", "alter table", etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements.
NonStop SQL. This means you can do a "begin transaction", then issue
"create table", "alter table", etc. ad nauseum, and in the mean time
concurrent transactions will just work. Beautiful for atomically
upgrading a production server. Oracle, of course, commits after each
DDL statements.

[3]: PostgreSQL can be extended with new domain types, stored-procedure languages (eg., PL/Python, PL/Perl), functions (eg., dblink, fuzzystrmatch, cube), and indexes (GiST, GIN). Together this allows projects such as TSearch2 and PostGIS to be implemented as separate extensions to PostgreSQL.
languages (eg., PL/Python, PL/Perl), functions (eg., dblink,
fuzzystrmatch, cube), and indexes (GiST, GIN). Together this allows
projects such as TSearch2 and PostGIS to be implemented as separate
extensions to PostgreSQL.

Alexander.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/24/07 10:30, Alexander Staubo wrote:

On 5/24/07, Richard Huxton <dev@archonet.com> wrote:

Tom Allison wrote:

You've addressed cost and performance.
Not much left.

Try it out for yourself and see if it works for you.

+ elephant in logo
- unpronounceable name
+ excellent mailing lists
+ excellent developer community
- you can download as many copies as you like and a salesman still won't
take you out to lunch

+ Friendly toolset in the box. [1]
+ Transactional DDL. [2]
+ Table inheritance, if you care to use unportable features.
+ Extensibility. [3]
+ PostGIS for spatial extensions.
- Replication offerings suck.
- Warm standby involve a lot of manual labour.
- Point-in-time recovery involve a lot of manual labour.

[1] psql+readline, pg_dump etc. are a breeze compared to crusty Oracle
tools; psql feels distinctly modern compared to MySQL's crummy
interpreter.

[2] Nobody else has this, I believe, except possibly Ingres and
NonStop SQL. This means you can do a "begin transaction", then issue
"create table", "alter table", etc. ad nauseum, and in the mean time
concurrent transactions will just work. Beautiful for atomically
upgrading a production server. Oracle, of course, commits after each
DDL statements.

Rdb/VMS and CODASYL DBMS (both Oracle, formerly DEC, products) also
have transactional DDL. Actually, I was quite stunned to discover
that Oracle doesn't do that.

Interbase/Firebird probably also has transactional DDL.

[3] PostgreSQL can be extended with new domain types, stored-procedure
languages (eg., PL/Python, PL/Perl), functions (eg., dblink,
fuzzystrmatch, cube), and indexes (GiST, GIN). Together this allows
projects such as TSearch2 and PostGIS to be implemented as separate
extensions to PostgreSQL.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGVcaiS9HxQb37XmcRAu69AJ0YtBw6zpuR6zAZzEIjVWfisKeM5ACeKz+x
bJGv1Wh7YrUeYglaOHg1n7I=
=YKt9
-----END PGP SIGNATURE-----

For us?

Stability. Pure and simple. It ended up that the site was faster too.
One issue with postgresql is that connection time is slower than
mysql. Otherwise most everything else is just as fast or faster.

So with Ruby on Rails, there is a persistent connections from the
container, and that disadvantage dissapears. If there's anything slow
in the site, its ruby template rendering, database access generally is
10% or less of total time it takes to generate our most popular pages.

Ericson Smith
Developer
http://www.funadvice.com

On 5/24/07, Ron Johnson <ron.l.johnson@cox.net> wrote:

[2] Nobody else has this, I believe, except possibly Ingres and
NonStop SQL. This means you can do a "begin transaction", then issue
"create table", "alter table", etc. ad nauseum, and in the mean time
concurrent transactions will just work. Beautiful for atomically
upgrading a production server. Oracle, of course, commits after each
DDL statements.

Rdb/VMS and CODASYL DBMS (both Oracle, formerly DEC, products) also
have transactional DDL.

Not exactly mainstream databases, and Codasyl isn't even a relational
database as far as I remember.

Interbase/Firebird probably also has transactional DDL.

Apparently. I'm surprised, given how InterBase and Firebird otherwise
feel so awfully ancient.

All right, so not unique, but very rare.

Alexander.

On May 24, 2007, at 4:39 AM, Richard Huxton wrote:

- unpronounceable name

post-gres-queue-el

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/24/07 12:48, Alexander Staubo wrote:

On 5/24/07, Ron Johnson <ron.l.johnson@cox.net> wrote:

[2] Nobody else has this, I believe, except possibly Ingres and
NonStop SQL. This means you can do a "begin transaction", then issue
"create table", "alter table", etc. ad nauseum, and in the mean time
concurrent transactions will just work. Beautiful for atomically
upgrading a production server. Oracle, of course, commits after each
DDL statements.

Rdb/VMS and CODASYL DBMS (both Oracle, formerly DEC, products) also
have transactional DDL.

Not exactly mainstream databases, and Codasyl isn't even a relational
database as far as I remember.

True, they are "legacy systems", but are still actively developed by
Oracle, for Alpha and Itanium VMS. (The VAX products still are
actually maintained, but only for bug-fixes.)

And yes, DBMS uses the network model. (Since both it and Rdb use
the same back end and on-disk structures, it's not that much extra
effort to also keep on actively developing DBMS.)

Interbase/Firebird probably also has transactional DDL.

Apparently. I'm surprised, given how InterBase and Firebird otherwise
feel so awfully ancient.

InterBase was devloped by a man who worked for DEC at the same time
that Rdb was in the works. His DECisms are still apparent in Firebird.

God, I'm going to hate it when we migrate to Oracle...

All right, so not unique, but very rare.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGVdNvS9HxQb37XmcRAkK4AKCxilH8XRM9es1RZ/Vma/AUrLoVnACfaGgP
VSce0lLj/mw2Vj435EDEaII=
=Gkbz
-----END PGP SIGNATURE-----

On Thursday 24. May 2007 19:57, Erik Jones wrote:

On May 24, 2007, at 4:39 AM, Richard Huxton wrote:

- unpronounceable name

post-gres-queue-el

Somebody probably wants to put that pot-grass-kewl thing in his pipe and
smoke it.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

On Thursday 24 May 2007 17:30, Alexander Staubo wrote:

[2] Nobody else has this, I believe, except possibly Ingres and
NonStop SQL. This means you can do a "begin transaction", then issue
"create table", "alter table", etc. ad nauseum, and in the mean time
concurrent transactions will just work. Beautiful for atomically
upgrading a production server. Oracle, of course, commits after each
DDL statements.

If this is such a rare feature, I'm very glad we chose postgresql. I use it all
the time, and wouldn't know what to do without it. We circumvented Ruby on
Rails' migrations, and just implemented them in SQL. Writing migrations is a
breeze this way, and you don't have to hassle with atomicity, or the pain when
you discover the migration doesn't work on the production server.

On May 24, 2007, at 14:29 , Wiebe Cazemier wrote:

On Thursday 24 May 2007 17:30, Alexander Staubo wrote:

[2] Nobody else has this, I believe, except possibly Ingres and
NonStop SQL. This means you can do a "begin transaction", then issue
"create table", "alter table", etc. ad nauseum, and in the mean time
concurrent transactions will just work. Beautiful for atomically
upgrading a production server. Oracle, of course, commits after each
DDL statements.

If this is such a rare feature, I'm very glad we chose postgresql.
I use it all
the time, and wouldn't know what to do without it. We circumvented
Ruby on
Rails' migrations, and just implemented them in SQL. Writing
migrations is a
breeze this way, and you don't have to hassle with atomicity, or
the pain when
you discover the migration doesn't work on the production server.

Indeed. Wouldn't it be a cool feature to persists transaction states
across connections so that a new connection could get access to a sub-
transaction state? That way, you could make your schema changes and
test them with any number of test clients (which designate the state
to connect with) and then you would commit when everything works.

Unfortunately, the postgresql architecture wouldn't lend itself well
to this. Still, it seems like a basic extension of the notion of sub-
transactions.

Cheers,
M

A.M. wrote:

On May 24, 2007, at 14:29 , Wiebe Cazemier wrote:

On Thursday 24 May 2007 17:30, Alexander Staubo wrote:

[2] Nobody else has this, I believe, except possibly Ingres and
NonStop SQL. This means you can do a "begin transaction", then issue
"create table", "alter table", etc. ad nauseum, and in the mean time
concurrent transactions will just work. Beautiful for atomically
upgrading a production server. Oracle, of course, commits after each
DDL statements.

If this is such a rare feature, I'm very glad we chose postgresql.
I use it all
the time, and wouldn't know what to do without it. We circumvented
Ruby on
Rails' migrations, and just implemented them in SQL. Writing
migrations is a
breeze this way, and you don't have to hassle with atomicity, or
the pain when
you discover the migration doesn't work on the production server.

Indeed. Wouldn't it be a cool feature to persists transaction states
across connections so that a new connection could get access to a sub-
transaction state? That way, you could make your schema changes and
test them with any number of test clients (which designate the state
to connect with) and then you would commit when everything works.

Unfortunately, the postgresql architecture wouldn't lend itself well
to this. Still, it seems like a basic extension of the notion of sub-
transactions.

Hmm, doesn't this Just Work with two-phase commit?

--
Alvaro Herrera Valdivia, Chile ICBM: S 39� 49' 18.1", W 73� 13' 56.4"
Oh, oh, las chicas galacianas, lo har�n por las perlas,
�Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, �Prueba una hija de Caladan! (Gurney Halleck)

On May 24, 2007, at 15:57 , Alvaro Herrera wrote:

A.M. wrote:

Indeed. Wouldn't it be a cool feature to persists transaction states
across connections so that a new connection could get access to a
sub-
transaction state? That way, you could make your schema changes and
test them with any number of test clients (which designate the state
to connect with) and then you would commit when everything works.

Unfortunately, the postgresql architecture wouldn't lend itself well
to this. Still, it seems like a basic extension of the notion of sub-
transactions.

Hmm, doesn't this Just Work with two-phase commit?

2PC requires that the modifications already be in concrete. What I
suggest is a method for a new connection to insert itself into an
existing (sub-)transaction SQL stream, make changes, and commit to
the root or parent transaction.

In the scenario where changes are pending, only one connection can
test the changes- it must be the connection that opened the
transaction. Concurrency issues cannot be tested before committing,
for example.

The implementation could be as simple as decoupling connections from
transactions- then a connection could make serialized requests to
other backends. A proof-of-concept could certainly be cobbled
together with pipes and pl/perl, but the real beef would be the
ability to "pass" responsibility for transactions from one connection
to another.

Cheers,
M

We're derailing the thread, but...

On 5/24/07, A.M. <agentm@themactionfaction.com> wrote:

2PC requires that the modifications already be in concrete. What I
suggest is a method for a new connection to insert itself into an
existing (sub-)transaction SQL stream, make changes, and commit to
the root or parent transaction.

The problem with long-running transactions is that they need to avoid
locking the resources they touch. Short-running transactions are bad
enough as they stand -- until fairly recently (8.1? 8.2?), merely
inserting or updating a row that had a foreign-key reference to
another table row would lock the referenced row until the end of the
transaction, by issuing an implicit "select ... for update".

Although a mechanism whereby multiple connections can share a single
session/transaction is probably easy to implement, using long-running
transactions to isolate DDL changes is not feasible at the moment
because PostgreSQL currently acquires an AccessExclusiveLock on the
modified table until the transaction ends, which means that concurrent
transactions would be blocked from even querying the table.

I don't know PostgreSQL's internals, so I can only postulate that this
locking occurs because PostgreSQL holds a single copy of the schema
and related bookeeping structures in memory.

Alexander.

Indeed. Wouldn't it be a cool feature to persists transaction states
across connections so that a new connection could get access to a sub-
transaction state? That way, you could make your schema changes and
test them with any number of test clients (which designate the state
to connect with) and then you would commit when everything works.

Actually you can hack this by starting your webserver with only 1 thread,
use persistent connections, and disable all commits in the application.
But I'd call that "a very hackish hack".

On May 24, 2007, at 18:12 , PFC wrote:

Indeed. Wouldn't it be a cool feature to persists transaction states
across connections so that a new connection could get access to a
sub-
transaction state? That way, you could make your schema changes and
test them with any number of test clients (which designate the state
to connect with) and then you would commit when everything works.

Actually you can hack this by starting your webserver with only 1
thread, use persistent connections, and disable all commits in the
application.
But I'd call that "a very hackish hack".

Not really- then I can't use any transactions, in which case, I might
as well use MySQL. I would like to be able to pass transaction state
between connections.

-M

agentm@themactionfaction.com ("A.M.") writes:

On May 24, 2007, at 14:29 , Wiebe Cazemier wrote:

On Thursday 24 May 2007 17:30, Alexander Staubo wrote:

[2] Nobody else has this, I believe, except possibly Ingres and
NonStop SQL. This means you can do a "begin transaction", then issue
"create table", "alter table", etc. ad nauseum, and in the mean time
concurrent transactions will just work. Beautiful for atomically
upgrading a production server. Oracle, of course, commits after each
DDL statements.

If this is such a rare feature, I'm very glad we chose postgresql.
I use it all
the time, and wouldn't know what to do without it. We circumvented
Ruby on
Rails' migrations, and just implemented them in SQL. Writing
migrations is a
breeze this way, and you don't have to hassle with atomicity, or
the pain when
you discover the migration doesn't work on the production server.

Indeed. Wouldn't it be a cool feature to persists transaction states
across connections so that a new connection could get access to a sub-
transaction state? That way, you could make your schema changes and
test them with any number of test clients (which designate the state
to connect with) and then you would commit when everything works.

Unfortunately, the postgresql architecture wouldn't lend itself well
to this. Still, it seems like a basic extension of the notion of sub-
transactions.

Jan Wieck had a proposal to a similar effect, namely to give some way
to get one connection to duplicate the state of another one.

This would permit doing a neat parallel decomposition of pg_dump: you
could do a 4-way parallelization of it that would function something
like the following:

- connection 1 opens, establishes the usual serialized mode transaction

- connection 1 dumps the table metadata into one or more files in a
specified directory

- then it forks 3 more connections, and seeds them with the same
serialized mode state

- it then goes thru and can dump 4 tables concurrently at a time,
one apiece to a file in the directory.

This could considerably improve speed of dumps, possibly of restores,
too.

Note that this isn't related to subtransactions...
--
output = reverse("ofni.secnanifxunil" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/internet.html
"Unless there really are chrono-synclastic infundibula. (Roll on the
Church of God the Utterly Indifferent...)" -- Will Deakin