Thread: POSTGRESQL Newbie

On Tue, Mar 20, 2012 at 11:27 PM, Geek Matter <geekmatter@yahoo.com> wrote:
> folks,
>
> i am newbie in prosgretsql i am in midst of making decission of which
> database techology shoould i choose for our large web apps. mysql or
> postgresql?
> could you share larges sites who are implemented Posgtresql successfully in
> term of replication, clustering, scale, and etc. I heard postgresql does not
> have great feature for scale, replication and clustering like mysql, is it
> true?

Skype.  And pgsql has some great replication solutions that actually work

On Tue, Mar 20, 2012 at 11:54 PM, Geek Matter <geekmatter@yahoo.com> wrote:
> any other large sites use postgresql? i need to make right descission coz my
> decision will affect business that is related with $

The people who run the .info and .org domains...  Lots more.  google
is your friend.

On Wed, Mar 21, 2012 at 12:43 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Mar 20, 2012 at 11:54 PM, Geek Matter <geekmatter@yahoo.com> wrote:
>> any other large sites use postgresql? i need to make right descission coz my
>> decision will affect business that is related with $
>
> The people who run the .info and .org domains...  Lots more.  google
> is your friend.

Myyearbook.com: http://www.youtube.com/watch?v=F1dzjU7QC2A

On Wed, Mar 21, 2012 at 12:04 AM, Geek Matter <geekmatter@yahoo.com> wrote:
> scott,
>
> thanks for quick response you mean all  the dowmain .info and .org domains
> are using postgresql?

I am pretty sure he means the top level domains (registration, root
DNS server updates, etc) are all run off PostgreSQL.

In terms of raw performance, I would also recommend reading the
article on CNAF (1 Billion SQL queries a day) at http://pgmag.org/.
It may not be a web site....

Etsy also uses PostgreSQL.

My experience is that PostgreSQL performs quite well under extremely
varied loads.

However, I think you are asking the wrong question.  I think the key
questions are:

1)  What do you want in an RDBMS?

2)  What options are there available in both?

3)  What doors do I want to leave open in the future that one or the
other provides?

Best Wishes,
Chris Travers

Le mercredi 21 mars 2012 à 00:04 -0700, Geek Matter a écrit :

> how about postgresql? does it has free graphical tools for modeling,
> replication ?
>
>

In your original post, you wrote : "for our large web apps"; if that
really is the case, I suggest you invest some time into learning the
command line interface, which gives you very fine control over the
modeling.

on graphical tools : they're around, but I never bothered to learn them,
and I come from an Access background. Google for pgAdmin

on replication : several tools in production use, lots of references
search the list archives; same thing, I doubt you can setup an efficient
replication of a large web app from a graphical tool, you'll have to
learn the commands/configuration settings, etc...

on the business case : I talked less than a week ago to the technical
director at this company

http://www.lusis.fr/SITE/

who tells me they use PostgreSQL.

So if it works for the (very) large monetary transfers between banks, it
should work for you.


--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique

"Geek Matter" wrote:
>> Skype.  And pgsql has some great replication solutions that actually
work

> any other large sites use postgresql? i need to make right descission
coz my decision will affect
> business that is related with $

http://archives.postgresql.org/pgsql-advocacy/2002-08/msg00005.php

Yours,
Laurenz Albe

On Wed, Mar 21, 2012 at 2:46 AM, Geek Matter <geekmatter@yahoo.com> wrote:
> i just wondering, why mysql is more popular than postgresql ?
>

Pretty blonde girls doing marketing

Le mercredi 21 mars 2012 à 02:49 -0600, Abel Abraham Camarillo Ojeda a
écrit :
> On Wed, Mar 21, 2012 at 2:46 AM, Geek Matter <geekmatter@yahoo.com> wrote:
> > i just wondering, why mysql is more popular than postgresql ?
> >
>
> Pretty blonde girls doing marketing

Not sure about that, but it would not be surprising considering who is
backing them.

However, I once read that the real reason is that mysql was available
when ISPs came of existence, circa 1995. It lacked important features of
an RDBMS (you can google the details), but it was enough to satisfy the
needs of php scripts for instance.

First to market, in short.


--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique

2012/3/21 Vincent Veyron <vv.lists@wanadoo.fr>:
> Le mercredi 21 mars 2012 à 02:49 -0600, Abel Abraham Camarillo Ojeda a
> écrit :
>> On Wed, Mar 21, 2012 at 2:46 AM, Geek Matter <geekmatter@yahoo.com> wrote:
>> > i just wondering, why mysql is more popular than postgresql ?
>> >
>>
>> Pretty blonde girls doing marketing
>
> Not sure about that, but it would not be surprising considering who is
> backing them.
>
> However, I once read that the real reason is that mysql was available
> when ISPs came of existence, circa 1995. It lacked important features of
> an RDBMS (you can google the details), but it was enough to satisfy the
> needs of php scripts for instance.
>
> First to market, in short.
>

exactly

Pavel



>
> --
> Vincent Veyron
> http://marica.fr/
> Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On Wed, Mar 21, 2012 at 7:46 PM, Geek Matter <geekmatter@yahoo.com> wrote:
> i just wondering, why mysql is more popular than postgresql ?

PHP and MySQL go together nicely. Both of them allow, even encourage,
sloppinesses of various sorts, and it's really easy to make yourself a
dynamic web page with a standard LAMP/WAMP/OAMP/_AMP stack. We made a
deliberate shift at work a while ago, getting *off* MySQL and onto
Postgres, for a variety of reasons; there are definitely aspects where
PG is less convenient. It's more work to get things right - but it's
worth it if you want to scale "to infinity and beyond".

ChrisA

On Wed, Mar 21, 2012 at 11:10, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
> However, I once read that the real reason is that mysql was available
> when ISPs came of existence, circa 1995. It lacked important features of
> an RDBMS (you can google the details), but it was enough to satisfy the
> needs of php scripts for instance.
>
> First to market, in short.

Let's not forget that PostgreSQL sucked, too, back then.

PostgreSQL's maintenance was absolutely horriffic. And if you got it
wrong, it would bog down all your hardware resources. MySQL lacked
many features, but it "just worked" without maintenance.

E.g. VACUUM/ANALYZE needed to be ran manually and it used to take an
*exclusive* lock on tables, for longish periods, preventing any
queries! Failure to vacuum would cause the files to bloat without
limit and slow down your queries gradually. In the worst case, you hit
XID wraparound and the database would shut down entirely.

Even still in 8.3 (which was newest until 2009) with autovacuum, if
you got max_fsm_pages tuned wrong, vacuum would basically stop
functioning and your tables would bloat.

Regards,
Marti

Am 21.03.2012 12:35, schrieb Marti Raudsepp:
> On Wed, Mar 21, 2012 at 11:10, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
>> However, I once read that the real reason is that mysql was available
>> when ISPs came of existence, circa 1995. It lacked important features of
>> an RDBMS (you can google the details), but it was enough to satisfy the
>> needs of php scripts for instance.
>>
>> First to market, in short.
>
> Let's not forget that PostgreSQL sucked, too, back then.
>
> PostgreSQL's maintenance was absolutely horriffic. And if you got it
> wrong, it would bog down all your hardware resources. MySQL lacked
> many features, but it "just worked" without maintenance.
>
> E.g. VACUUM/ANALYZE needed to be ran manually and it used to take an
> *exclusive* lock on tables, for longish periods, preventing any
> queries! Failure to vacuum would cause the files to bloat without
> limit and slow down your queries gradually. In the worst case, you hit
> XID wraparound and the database would shut down entirely.
>
> Even still in 8.3 (which was newest until 2009) with autovacuum, if
> you got max_fsm_pages tuned wrong, vacuum would basically stop
> functioning and your tables would bloat.

Yepp.. Remmembering back when I started to get in contact with LAMP
mysql just worked. Wasn't fast and didn't had a lot of fancy features
but it just worked in default config for day2day stuff.

Cheers,
Frank

Marti Raudsepp, 21.03.2012 12:35:
> E.g. VACUUM/ANALYZE needed to be ran manually and it used to take an
> *exclusive* lock on tables, for longish periods, preventing any
> queries! Failure to vacuum would cause the files to bloat without
> limit and slow down your queries gradually. In the worst case, you hit
> XID wraparound and the database would shut down entirely.
>
> Even still in 8.3 (which was newest until 2009) with autovacuum, if
> you got max_fsm_pages tuned wrong, vacuum would basically stop
> functioning and your tables would bloat.

I still see the vacuum part as one of the major deficiencies of PostgreSQL (that and the very limited partitioning
features).
I know that all DBMS (that use MVCC) do it in some way or the other, but I know of no other where it is such a
"prominent"maintenance task. 

Thomas

On Wed, Mar 21, 2012 at 01:35:31PM +0200, Marti Raudsepp wrote:
> Let's not forget that PostgreSQL sucked, too, back then.

Well, for some values of "sucked".  But I don't believe VACUUM was the
main issue.  In particular,

> wrong, it would bog down all your hardware resources. MySQL lacked
> many features, but it "just worked" without maintenance.

this "just worked" was only true for some values of "worked".  But the
things it worked at were the things people wanted, whereas the things
that Postgres worked at were things people didn't know they needed.

I recall working with databases in the late 90s and early 2000s.  When
I started using Postgres, 7.0 wasn't out yet.  The 6.x releases had
some really serious problems -- back end crashes were by no means
uncommon.  What Postgres had going for it, however, was either
features or stubs of features that, if you were used to real databases
(or heck, even toys like Paradox or dbase), were things you couldn't
do without.  The problem was that you had to treat the database like
old-line databases were treated: there was maintenance to do, and you
had to plan for it.  Also, you needed to think carefully about your
schema in advance, because schema changes were difficult and tended to
be disruptive.

MySQL had exactly the opposite plan.  The early MySQL releases were
little more than a SQL gloss on flat files.  The only reason to use
MySQL was that you got a subset of SQL functionality.  This was much
easier to develop with than rolling your own back end support for your
CGI or early PHP web pages.  All you needed was to put some SQL calls
in instead of figuring out how to write safely on the filesystem (from
inside your web server, remember).  And since the back end had very
weak typing anyway, schema changes didn't need to happen that often
(at the occasional expense of strange data, such as the famous date,
'0000-00-00').  Anything that wasn't fast, cheap, and easy was a thing
that didn't work and, quite likely, was listed in the manual as
something stupid that no real programmer would ever even possibly need.

Programmers with no experience whatever in database systems were
perfectly prepared for messages like "foreign keys are stupid", "only
morons use transactions", and "check data consistency in your
application", because they were used to doing everything in the
application anyway.  (This disease is still with us, I am sad to say
-- I cannot count the number of people I _still_ have to teach that
round trips are evil and that two application servers that don't use
transactions will inevitably get into a race and render the data
corrupt, without a lot of extra work.)  Moreover, because many people
(I include myself) had grown up with systems that measured stability
in hours and which randomly puked on the hard drive from time to time
(thanks, Windows!), Linux seemed like a paragon of stability, and
checking your database for corruption occasionally didn't seem like a
strange thing to do.

In short, MySQL offered the appearance of ease of use, which meant you
didn't need a DBA or even, really, to read the manual.  For most
people it was good enough.  It turned out that once you started trying
to scale it, you really did need all those features that the MySQL
3.2.3 and earlier manuals said nobody would ever actually need; but by
the time you found that out, you already had a sunk cost in your
development so far, and MySQL's dialect of SQL was even stranger than
Oracle's so it was hard to move away from MySQL.

At least, that's my explanation for its popularity.  As a disclaimer,
however, I will note that explaining the popularity of products is
almost always unsatisfying.  Consumer behaviour, whatever a certain
strain of economics says, is not obviously rationally maximizing.

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

> In short, MySQL offered the appearance of ease of use, which meant you
> didn't need a DBA or even, really, to read the manual.  For most
> people it was good enough.  It turned out that once you started trying
> to scale it, you really did need all those features that the MySQL
> 3.2.3 and earlier manuals said nobody would ever actually need; but by
> the time you found that out, you already had a sunk cost in your
> development so far, and MySQL's dialect of SQL was even stranger than
> Oracle's so it was hard to move away from MySQL.

I think that's a lot of it.  A majority of my experience with it is
from the Internet "boom" of the late 90's - we used it because it was
sloppy.  Because it was sloppy, it was easy - it let you concentrate
a lot more on the *quantity* of the code, rather than the *quality*.
I don't know if it's still as sloppy (forgiving or loose), but I do
know that data integrity had/has taken a back seat to speed and ease of
use for a long time.  Or at least it had when I was using a lot
more of it (really?  i can do an INSERT statement, and because the
type is wrong it'll just say it was successful but silently fail?
really?).

Now, I'll usually replace a piece of application software that
requires MySQL, rather than use MySQL over PostgreSQL.  Shame on
open-source app developers that write only for MySQL.

Benny


--
"The problem with quotes on the internet is that it's very hard to
verify their authenticity."       -- Abraham Lincoln

On Wed, Mar 21, 2012 at 2:04 AM, Geek Matter <geekmatter@yahoo.com> wrote:
> scott,
>
> thanks for quick response you mean all  the dowmain .info and .org domains
> are using postgresql?
> my background from SQL server which has powerful graphical tools for data
> modeling, replication, and etc.
> how about postgresql? does it has free graphical tools for modeling,
> replication ?

The biggest difference you'll find between postgres and a product like
sql server is that postgres is less tool driven (although there are
many great tools) but provides a richer programming environment.  If
database development is your objective as opposed to high level design
and administration, you'll find postgres to be the best product in the
market.  The price is right and the community support is fantastic.

merlin

I didn't catch this when it went by the first time, so sorry to have
missed it (and for the deep quoting):

On Wed, Mar 21, 2012 at 09:20:02AM -0500, Merlin Moncure wrote:
> On Wed, Mar 21, 2012 at 2:04 AM, Geek Matter <geekmatter@yahoo.com> wrote:
> > scott,
> >
> > thanks for quick response you mean all  the dowmain .info and .org domains
> > are using postgresql?

I want to clear this up lest some claim gets misinterpreted and
repeated elsewhere.  The domain name registry for .info and .org --
that is, the system that makes sure that at most one person in the
world has control of a domain name in either of those domains -- uses
PostgreSQL under the hood.  I was fortunate enough to have had a small
role in the launch of both of those systems and several others at
Afilias.  Unless something significant has changed (and I have reason
to believe it hasn't), the real-time changes to the DNS information in
those registries and a number of others are also pumped through
PostgreSQL.  I do not speak for Afilias, and I am not associated with
them in any way any more.

The domain registries do not have any control over what software
people use in sites using names in that registry: if you register
example.info, Afilias has nothing to do with what software you use.
I'm sure to this audience that is self-evident, but having just
returned from an ICANN meeting I am not at all sure it is self-evident
to everyone in the world.

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca