Experiences with extensibility

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

On Tue, 08 Jan 2008 16:28:11 -0800
Eric Davies <eric@barrodale.com> wrote:

The existing server extensibilities in modern DBMSs have been
critical in our company's development of software products that
improve database performance for certain scientific computing
applications. We are planning to develop other products that will
utilize an extensible database engine, and so we would be grateful
for feedback on the following questions as they pertain to your
experience in developing PostgreSQL applications:

1. What types of extensibility (i.e., data types,
functions/methods, indexing, etc.) are available and convenient to
use in practice?

Uhhh... all? You can create custom data types and even use them on the
fly via DOMAINS, we have full function support in just about every
language imaginable and full support for hash, btree, ltree, gist and
GIN indexes.

2. What types of extensibility (possibly already available in
other DBMSs) are currently missing in PostgreSQL?

None that I am aware of.

3. To what extent was your choice of PostgreSQL as a development
platform based primarily on its extensibility features?

There is no other open source database that can compare with
PostgreSQL's extensibility, reliability and scalability.

Joshua D. Drake

**********************************************
Eric Davies, M.Sc.
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: http://www.barrodale.com
Email: eric@barrodale.com
**********************************************
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2

Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
**********************************************

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

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

iD8DBQFHhBclATb/zqfZUUQRAkfNAKCheraBon354vPvgRLUdr/oOhozxQCfUNfj
pM1bdri5Zv04B9KQuyaX8qQ=
=EN3c
-----END PGP SIGNATURE-----

On Jan 8, 2008 7:36 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

There is no other open source database that can compare with
PostgreSQL's extensibility, reliability and scalability.

+1000

Joshua D. Drake wrote:

On Tue, 08 Jan 2008 16:28:11 -0800
Eric Davies <eric@barrodale.com> wrote:

3. To what extent was your choice of PostgreSQL as a development
platform based primarily on its extensibility features?

There is no other open source database that can compare with
PostgreSQL's extensibility, reliability and scalability.

AFAIK there is no other database system _at all_ that can compete with
PostgreSQL's extensibility.

On Postgres, you can create your own:
- functions (common functions; "set-returning" functions; aggregate functions)
- types
- operators
- indexing access methods (in particular, indexing for your own types)
- PL languages (i.e. you can create language handler for whatever suits
you). This means there are handlers already available for Python, Tcl,
Perl, PHP, sh, and others.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Joshua D. Drake" <jd@commandprompt.com> writes:

2. What types of extensibility (possibly already available in
other DBMSs) are currently missing in PostgreSQL?

None that I am aware of.

I'm sure there are some options available in some databases which Postgres
doesn't have. Usually Postgres has more choices than any of the others but
that doesn't mean that it includes the union of all of their feature sets.

You'll probably get more useful answers if you ask a more specific question.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

On Tuesday 08 January 2008 21:31, Gregory Stark wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

2. What types of extensibility (possibly already available in
other DBMSs) are currently missing in PostgreSQL?

None that I am aware of.

I'm sure there are some options available in some databases which Postgres
doesn't have. Usually Postgres has more choices than any of the others but
that doesn't mean that it includes the union of all of their feature sets.

I guess one could include synonyms and packages as possible items we don't
have which would make us more extensible, but the uses for those tools that
can't be covered with the tools available in postgres is pretty narrow.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On 08.01.2008, at 17:36, Joshua D. Drake wrote:

2. What types of extensibility (possibly already available in
other DBMSs) are currently missing in PostgreSQL?

None that I am aware of.

Easy multi-master clustering with just two machines.

cug

--
http://www.event-s.net

Guido Neitzer wrote:

On 08.01.2008, at 17:36, Joshua D. Drake wrote:

2. What types of extensibility (possibly already available in
other DBMSs) are currently missing in PostgreSQL?

None that I am aware of.

Easy multi-master clustering with just two machines.

That isn't really an extensibility argument. At least not in my mind.
Further I don't know of anyone that can "easily" do it. You either
suffer the possibility of catastrophic data loss (dolphins) or you
suffer guaranteed bank account drainage (Oracle), or you suffer the
willingness of Monopolies (MSSQL).

None of those equate to "easy".

Joshua D. Drake

On 08.01.2008, at 23:20, Joshua D. Drake wrote:

That isn't really an extensibility argument.

I was thinking about that too - for me, it still is just an
outstanding issue with PostgreSQL. It is incredibly scalable on one
machine but it totally sucks when you want more, but not much more.

Like, I have a situation where I need multi-master just for
availability. Two small servers are good enough for that. But
unfortunately with PostgreSQL the whole setup is a major pain in the ...

At least not in my mind. Further I don't know of anyone that can
"easily" do it. You either suffer the possibility of catastrophic
data loss (dolphins) or you suffer guaranteed bank account drainage
(Oracle), or you suffer the willingness of Monopolies (MSSQL).

FrontBase. It has an incredibly easy to configure replication and
multi master clustering support, is very reliable and can also handle
really big databases. The only problem is that the query planner is
not as good as PostgreSQL's so you might end up with much worse
performance. Depends a bit on the complexity of the database and how
"special" your queries are.

But if you need something easy to setup, multi-master with just two
machines, easy fail-over (done in the JDBC driver) without your
application even noticing it - try it. It's free, but not open source.
And it's a good product. I use it for some stuff and PostgreSQL for
other projects. Just depends on the requirements.

cug

--
http://www.event-s.net

Guido Neitzer wrote:

On 08.01.2008, at 23:20, Joshua D. Drake wrote:

That isn't really an extensibility argument.

I was thinking about that too - for me, it still is just an outstanding
issue with PostgreSQL. It is incredibly scalable on one machine but it
totally sucks when you want more, but not much more.

There are OS level things you can do here.

But if you need something easy to setup, multi-master with just two
machines, easy fail-over (done in the JDBC driver) without your
application even noticing it - try it.

http://www.continuent.org/HomePage

It's free, but not open source.
And it's a good product. I use it for some stuff and PostgreSQL for
other projects. Just depends on the requirements.

Sincerely,

Joshua D. Drake

That isn't really an extensibility argument. At least not in my mind.
Further I don't know of anyone that can "easily" do it. You either
suffer the possibility of catastrophic data loss (dolphins) or you
suffer guaranteed bank account drainage (Oracle), or you suffer the
willingness of Monopolies (MSSQL).

None of those equate to "easy".

That's a load of FUD. When looking at feature-sets that are available or not
available in an open source product, you can't throw out all the things that a
commercial, closed source project has because it isn't open source and it costs
money.

The reason companies go with the closed source, expensive solutions is because
they are better products.

When evaluating a database for your company, it is better to look at what the
closed source products offer that cause companies to shell out tons of money and
decide if it is worth locking yourself into an expensive and/or exclusive agreement.

On 08.01.2008, at 23:40, Joshua D. Drake wrote:

There are OS level things you can do here.

They are normally not really easier and, more important, I don't have
them on my deployment environment.

http://www.continuent.org/HomePage

When I'm talking about two cheap machines you recommend a solution
where I need four machines (Or can I use the uni/cluster machines also
as db nodes?) and licenses for a couple of thousands bucks? Sorry, no
option.

And, I have my option ...

cug

--
http://www.event-s.net

Sim Zacks wrote:

That isn't really an extensibility argument. At least not in my mind.
Further I don't know of anyone that can "easily" do it. You either
suffer the possibility of catastrophic data loss (dolphins) or you
suffer guaranteed bank account drainage (Oracle), or you suffer the
willingness of Monopolies (MSSQL).

None of those equate to "easy".

That's a load of FUD. When looking at feature-sets that are available or
not
available in an open source product, you can't throw out all the things
that a
commercial, closed source project has because it isn't open source and
it costs
money.

You obviously didn't read my post.

The reason companies go with the closed source, expensive solutions is
because
they are better products.

Sometimes, sometimes not. It depends on your needs.

When evaluating a database for your company, it is better to look at
what the
closed source products offer that cause companies to shell out tons of
money and
decide if it is worth locking yourself into an expensive and/or
exclusive agreement.

The only thing this post could possibly be is a Troll. Please go back
under the bridge.

Sincerely,

Joshua D. rake

Guido Neitzer wrote:

On 08.01.2008, at 23:40, Joshua D. Drake wrote:

There are OS level things you can do here.

They are normally not really easier and, more important, I don't have
them on my deployment environment.

http://www.continuent.org/HomePage

When I'm talking about two cheap machines you recommend a solution where
I need four machines (Or can I use the uni/cluster machines also as db
nodes?) and licenses for a couple of thousands bucks? Sorry, no option.

Did you even bother to read the page?

http://sequoia.continuent.org/HomePage

Open Source... Free...

Sequoia is a transparent middleware solution offering clustering, load
balancing and failover services for any database. Sequoia is the
continuation of the C-JDBC project.

It can be downloaded here:

https://forge.continuent.org/frs/?group_id=6

And, I have my option ...

Great! I was just trying to show you that there was a JDBC layer
available for multi-mastering with PostgreSQL.

Sincerely,

Joshua D. Drake

On Tue, 2008-01-08 at 23:37 -0700, Guido Neitzer wrote:

On 08.01.2008, at 23:20, Joshua D. Drake wrote:

Like, I have a situation where I need multi-master just for
availability. Two small servers are good enough for that. But
unfortunately with PostgreSQL the whole setup is a major pain in the ...

Isn't that the reason they hire DB admins and not the run of the mill
guy.

I've not played with multimaster (sync/async) and I doubt I will since
there's no requirement for it., (yet)

In any case, based on my research there's lots of FOSS and (not-so)FOSS
based solutions and of course, each comes with their own learning curve
and also depends on the complexity of the requirements. (Mind you, even
MSSQL with all it's polished point and click interface, you still have
times when you pull hairs out)

I've done a simple master/slave configuration which is faring well, so
that's fine (for me)

On 09.01.2008, at 00:08, Joshua D. Drake wrote:

Did you even bother to read the page?

Actually I tried but typed it in the browser and it resolved directly
to continuent.com (which I have as a bookmark) and I wasn't aware of
the Sequoia stuff anymore and combined Contiuent with uni/cluster
directly in my mind. That was the main problem here.

After reading again a bit now, I haven't seen mentioned how many nodes
are required to have a minimum more or less safe fail-over setup.

Great! I was just trying to show you that there was a JDBC layer
available for multi-mastering with PostgreSQL.

When I find some time, I might dig a bit deeper in the Sequoia stuff
again. We will see. Thanks for that hint.

cug

--
http://www.event-s.net

On Tue, 2008-01-08 at 23:05 -0800, Joshua D. Drake wrote:

Sim Zacks wrote:

The reason companies go with the closed source, expensive solutions is
because they are better products.

Sometimes, sometimes not. It depends on your needs.

This is total FUD. Everything has a place. And besides, as what I read, nobody ever gets fired
for recommending an expensive solution that comes with expensive support contracts and what not.
(wish I could google and insert the link to where I read that)

When evaluating a database for your company, it is better to look at
what the
closed source products offer that cause companies to shell out tons of
money and
decide if it is worth locking yourself into an expensive and/or
exclusive agreement.

The only thing this post could possibly be is a Troll. Please go back
under the bridge.

No, it's better to evaluate if the features which are being provided
will fit your needs. This is akin to buying a lamborghini only to drive
it down to the local 7-11, down the (same) road to buy some bread.

Take a walk instead, save my ears, save some petrol, save some money.

Otherwise, you end up paying X amount more for features you don't need.
(Me remembers vividly an episode of Simpsons where Homer was given free
rein to design the ultimate American Dream Car.)

On Wed, 2008-01-09 at 00:21 -0700, Guido Neitzer wrote:

On 09.01.2008, at 00:08, Joshua D. Drake wrote:

Great! I was just trying to show you that there was a JDBC layer
available for multi-mastering with PostgreSQL.

When I find some time, I might dig a bit deeper in the Sequoia stuff
again. We will see. Thanks for that hint.

IIRC, there was a presentation on avaiable replication solutions in one
of the pgcons. Perhaps you can search there.

On 09.01.2008, at 00:14, Ow Mun Heng wrote:

Like, I have a situation where I need multi-master just for
availability. Two small servers are good enough for that. But
unfortunately with PostgreSQL the whole setup is a major pain in
the ...

Isn't that the reason they hire DB admins and not the run of the mill
guy.

Isn't that more the situation where it is preferred to have a working
fail-over with as less money and work as possible?

There is just no way I (personally) can afford hiring someone to set
that up as I'm talking about something that hasn't brought a dollar
yet and will probably not for the next time ... and it is my own
project, but there is still some need for a reliable service to come
to a point where I can maybe hire someone.

cug

--
http://www.event-s.net

You wrote that either it is not implemented well (catastrophic data losss) or is
expensive (Oracle) or it is a monopoly (MSSQL). None of those are easy.
Expensive and monopoly don't seem to me to be non-easy, rather undesirable if
you don't need to get into it.

When someone asks a question about a feature found in a commercial product and
the answer is that the feature is not available unless you accept on yourself
horrid possibilities, that is similar to Microsoft saying that sure you can use
open source, but there is no support, it is unreliable, ... Pure FUD. You can
call it reverse FUD, but it is FUD nonetheless.

We use postgresql because it is open source, we have in-house experience to deal
with it so we don't have any extra support costs and we don't need the features
that are offered in commercial products that PostGreSQL does not have. We also
don't need the speed that commercial products offer that is missing in PostgreSQL.

Sim

Joshua D. Drake wrote: