Thread: Dynamic web sites with PostgreSQL

Hello there!

I'd like to ask the PostgreSQL community for the conseptual thing. We
develop our web sites using MySQL. We like this for its high speed and
fulltext search feature. But nowadays our projects are growing fast and
we afraid our MySQL won't be able to perform large amount of complex
complicated queries. So we get a question about altenative SQL server.
In fact there are not too much from open source SQL servers, I think
only 2 serious: PostgreSQL and MaxDB. May I sak you about words for
advocacy or accusation for each of these database servers?

Our tasks: static content generation but using of complicated search
feature on the web site.

Any suggestions are very appreciated.

Thanks in advance and kind regards,
Nicolay Vasiliev

Nicolay A Vasiliev wrote:
> Hello there!
>
> I'd like to ask the PostgreSQL community for the conseptual thing. We
> develop our web sites using MySQL. We like this for its high speed and
> fulltext search feature.

Cool - how much faster than the other systems you tested against was MySQL?

 > But nowadays our projects are growing fast and
> we afraid our MySQL won't be able to perform large amount of complex
> complicated queries.

Don't be afraid, arm yourself with some facts! MySQL have a lot of
documentation on their website and you can always test the latest
version for free.

Of course - make sure your websites are under an open-source licence or
you are happy paying the licence fee for the business version.

 > So we get a question about altenative SQL server.
> In fact there are not too much from open source SQL servers, I think
> only 2 serious: PostgreSQL and MaxDB. May I sak you about words for
> advocacy or accusation for each of these database servers?

Well, you've come to a PostgreSQL list, so I'll give you two guesses as
to which we prefer? :-)

MaxDB has a lot of history, but the opinions I heard about the code-base
when it was first made open-source were not complimentary. That may of
course change, we'll have to see what Mysql AB do with it.

You've also ignored Firebird, which has been around for a long time as a
Borland DB before becoming open-source. Oh, there's also Ingres recently
set free to roam the plains by C.A.

> Our tasks: static content generation but using of complicated search
> feature on the web site.

You haven't actually provided any information to make a decision. I'm
not sure in what sense you *can* generate static content from a
database. It's also not clear what you mean by a "complicated" search.

In short, the only way you'll know which database suits you is to spend
some time and effort testing. No-one here knows what hardware you will
use, operating-system details, filesystem details, database size,
database structure, number of users, number of concurrent sessions,
usage patterns, client language, application framework, caching
requirements, replication requirements, DBA experience, etc.

--
   Richard Huxton
   Archonet Ltd

Nicolay A. Vasiliev wrote:
> Hello, Richard, and thanks for your answer!
>
> Best of all, sorry for my poor English cause I am not a native English
> speaking man :(.

Your English is fine Nicolay - perfectly fluent AFIACT.

Oh - don't forget to CC: the list too - there are plenty more people who
can help.

>>> we afraid our MySQL won't be able to perform large amount of complex
>>> complicated queries.
>>
>> Don't be afraid, arm yourself with some facts! MySQL have a lot of
>> documentation on their website and you can always test the latest
>> version for free.
>>
>> Of course - make sure your websites are under an open-source licence
>> or you are happy paying the licence fee for the business version.
>
> We are using this dbms cause it was installed on our FreeBSD web server
> we bought from web hoster.

Ah - well there's your first point of comparison.

There are *lots* of web-hosting companies offering cheap and simple
MySQL+PHP setups.

There are *some* offering PostgreSQL/MS-SQL.

There are hardly any offering anything else.

Of course, if you have a (virtual) server then you can install what you
like. FreeBSD is a popular choice for PostgreSQL, so it's well supported
on that OS.

>> > So we get a question about altenative SQL server.
>>
>>> In fact there are not too much from open source SQL servers, I think
>>> only 2 serious: PostgreSQL and MaxDB. May I sak you about words for
>>> advocacy or accusation for each of these database servers?
>>
>> Well, you've come to a PostgreSQL list, so I'll give you two guesses
>> as to which we prefer? :-)
>
> I thought maybe anyone made such comparisons.

It's difficult to do well. What works for one person's setup doesn't for
another. You might find the following useful, but be aware that they
will probably be out of date since both have just released new versions.
  http://sql-info.de/mysql/gotchas.html
  http://sql-info.de/postgresql/postgres-gotchas.html

In general MySQL is faster for lots of simple select queries. PostgreSQL
  tends to scale better as you get more complex queries and concurrent
updates. We also take things like data integrity a lot more seriously
than MySQL AB does/has.

>> MaxDB has a lot of history, but the opinions I heard about the
>> code-base when it was first made open-source were not complimentary.
>> That may of course change, we'll have to see what Mysql AB do with it.
>>
>> You've also ignored Firebird, which has been around for a long time as
>> a Borland DB before becoming open-source. Oh, there's also Ingres
>> recently set free to roam the plains by C.A.
>>
> Oh, you're right, this make a sense (I mean Firebird). Maybe we will
> consider it as a candidate for migrating. But I've read a PostgreSQL
> documentation and I liked the PostgreSQL more than firebird (I worked
> with Interbase 5 as Delphi developer). I think PostgreSQL is reacher
> than it essentially.

Well, everyone here is happy with it. Your biggest problem may be
un-learning "MySQL habits" you've picked up.

>>> Our tasks: static content generation but using of complicated search
>>> feature on the web site.
>>
>> You haven't actually provided any information to make a decision. I'm
>> not sure in what sense you *can* generate static content from a database.
>
> If we have web pages without changings for a long time it's more simple
> to make it static basing on database info and regenerate it after this
> base info is changed.
>
>> It's also not clear what you mean by a "complicated" search.
>>
> I meant advanced search with large number of parameters, sorry for this
> trouble in formulating.

Well, our planner is quite good - no problem with multi-table joins with
a mix of parameters. It takes into account the frequency of values in a
column too - so if you were doing a name-search on the UK phone-book it
would know that an index would be good for Vasiliev, but for Smith it
might as well scan the whole table.

>> In short, the only way you'll know which database suits you is to
>> spend some time and effort testing. No-one here knows what hardware
>> you will use, operating-system details, filesystem details, database
>> size, database structure, number of users, number of concurrent
>> sessions, usage patterns, client language, application framework,
>> caching requirements, replication requirements, DBA experience, etc.
>>
> We use FreeBSD OS, our databases are about 5 Gb. Why I asked the
> community - I hoped there is some similar experience and somebody could
> help basing on this.

It's well supported on FreeBSD, and 5GB is tiny compared to some
databases discussed here.

--
   Richard Huxton
   Archonet Ltd

Hello, Richard, and thanks for your answer!

Best of all, sorry for my poor English cause I am not a native English
speaking man :( .

Richard Huxton wrote:

 > Nicolay A Vasiliev wrote:
 >
 >> Hello there!
 >>
 >> I'd like to ask the PostgreSQL community for the conseptual thing.
We develop our web sites using MySQL. We like this for its high speed
and fulltext search feature.
 >
 >
 >
 > Cool - how much faster than the other systems you tested against was
MySQL?


I meant enough fast for our work. No serious comparison was made, we've
got MySQL because it's more popular using by web-developers.

 >
 > > But nowadays our projects are growing fast and
 >
 >> we afraid our MySQL won't be able to perform large amount of complex
complicated queries.
 >
 >
 >
 > Don't be afraid, arm yourself with some facts! MySQL have a lot of
documentation on their website and you can always test the latest
version for free.
 >
 > Of course - make sure your websites are under an open-source licence
or you are happy paying the licence fee for the business version.


We are using this dbms cause it was installed on our FreeBSD web server
we bought from web hoster.

 >
 > > So we get a question about altenative SQL server.
 >
 >> In fact there are not too much from open source SQL servers, I think
only 2 serious: PostgreSQL and MaxDB. May I sak you about words for
advocacy or accusation for each of these database servers?
 >
 >
 >
 > Well, you've come to a PostgreSQL list, so I'll give you two guesses
as to which we prefer? :-)


I thought maybe anyone made such comparisons.

 >
 > MaxDB has a lot of history, but the opinions I heard about the
code-base when it was first made open-source were not complimentary.
That may of course change, we'll have to see what Mysql AB do with it.
 >
 > You've also ignored Firebird, which has been around for a long time
as a Borland DB before becoming open-source. Oh, there's also Ingres
recently set free to roam the plains by C.A.
 >
Oh, you're right, this make a sense (I mean Firebird). Maybe we will
consider it as a candidate for migrating. But I've read a PostgreSQL
documentation and I liked the PostgreSQL more than firebird (I worked
with Interbase 5 as Delphi developer). I think PostgreSQL is reacher
than it essentially.

 >> Our tasks: static content generation but using of complicated search
feature on the web site.
 >
 >
 >
 > You haven't actually provided any information to make a decision. I'm
not sure in what sense you *can* generate static content from a database.


If we have web pages without changings for a long time it's more simple
to make it static basing on database info and regenerate it after this
base info is changed.

 > It's also not clear what you mean by a "complicated" search.
 >
I meant advanced search with large number of parameters, sorry for this
trouble in formulating.

 > In short, the only way you'll know which database suits you is to
spend some time and effort testing. No-one here knows what hardware you
will use, operating-system details, filesystem details, database size,
database structure, number of users, number of concurrent sessions,
usage patterns, client language, application framework, caching
requirements, replication requirements, DBA experience, etc.
 >
We use FreeBSD OS, our databases are about 5 Gb. Why I asked the
community - I hoped there is some similar experience and somebody could
help basing on this.

 > --
 >   Richard Huxton
 >   Archonet Ltd
 >
 >
Thanks again Richard for your answer!

Nicolay

On Nov 10, 2005, at 2:50 AM, Nicolay A Vasiliev wrote:

> I'd like to ask the PostgreSQL community for the conseptual thing.
> We develop our web sites using MySQL. We like this for its high
> speed and fulltext search feature. But nowadays our projects are
> growing fast and we afraid our MySQL won't be able to perform large
> amount of complex complicated queries. So we get a question about
> altenative SQL server. In fact there are not too much from open
> source SQL servers, I think only 2 serious: PostgreSQL and MaxDB.
> May I sak you about words for advocacy or accusation for each of
> these database servers?
>
> Our tasks: static content generation but using of complicated
> search feature on the web site.
>
> Any suggestions are very appreciated.


My suggestion would be to try a content management system like Drupal
(http://drupal.org). It supports both MySQL and PostgreSQL, so you
could design a system that works with both databases and choose the
one that best fits your requirements. It has lots of powerful
features and a nice modular architecture which makes it easy to
extend and customize.

See the link below for some information about installing Drupal with
PostgreSQL and some early discussion about integrating tsearch2 into
Drupal.

http://pgedit.com/install_drupal


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Le Jeudi 10 Novembre 2005 19:30, John DeSoi a écrit :
> My suggestion would be to try a content management system like Drupal
> (http://drupal.org). It supports both MySQL and PostgreSQL, so you
> could design a system that works with both databases and choose the
> one that best fits your requirements. It has lots of powerful
> features and a nice modular architecture which makes it easy to
> extend and customize.

May I add that our http://www.PostgreSQLFr.org french speaking PostgreSQL
community website runs under Drupal since summer 2004.

We are very happy with it, it completely respond our needs.

You'll just have to install pgpool and set on internal "Drupal cache" to have
a professional CMS running.

My 2 cents.

--
Jean-Paul Argudo
Trésorier de PostgreSQLFr
http://www.PostgreSQLFr.org

Run as fast as you can away from MaxDB.  I've looked at the source in detail, and my money is that mysql ab will never really do anything with it.  It's just a huge mess.  The documentation is probably the worst I have ever seen, the command line utilities have so many options and are so poorly documented that it takes forever to figure out how to do the most basic stuff.  Exporting and importing data is also a pain.  I really can't say enough bad things about MaxDB.  Maybe it was good a few years ago, but right now it's a huge step backwards from most anything else you could use, including mysql.  

Chris