Type of application that use PostgreSQL

**Richard Huxton** · Nov 12 '05, 12:05 AM

Re: Type of application that use PostgreSQL

On Thursday 02 October 2003 09:10, My Internet wrote:[color=blue]
> Hello,
>
> I am in the process to define if our product can use PostgreSQL.
>
> Do you know what type of application use PostgreSQL, and also what is the
> size of the database for these projects?[/color]

Everything from small-business apps (which I use it for), web-based apps,
large-scale scientific data processing, GIS support, substantial parts of the
DNS system...
[color=blue]
> Our application has a table with more than 300000 rows. There are complexe
> query with many joins. And we must respect some time constraints. Also the
> application is running 24 hours per day, so the product must be stable,
> with good recovery in case of problems.[/color]

People have been saying it's inconvenient to upgrade between versions when
dealing with 100GB+ databases, so if 3million rows is your largest table,
that might put things in perspective.

The query planner generally makes a good job optimising. Three things you
probably want to be aware of though:
- PG is very strict on types - if you have an int2 field compared to an int4
value, you will want to cast the int4 to make sure an index is used.
- Use of the JOIN keyword forces joins in the order you specify them - I
believe this is configurable in 7.4
- Use of IN (...) with many values can be slow. Recommended procedure is to
rewrite using EXISTS, although again I believe the situation is much improved
in 7.4
- count(*) isn't optimised - it has to scan the table.
- you will need to tune the values in the configuration file, they are set
quite small to start with.

Stability is good. If you're feeling cautious, you could use 7.3.4 rather than
the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to get
a valid snapshot) for ages. It has a write-ahead-log to replay active
transactions in case of a crash, and a couple of replication options.

Some of its high points I would say are:
- good proedural language support
- many client interfaces (C,C++,Perl,Pyt hon,PHP,JDBC,OD BC...)
- good compliance with SQL standards
- very helpful mailing lists

In short, I'd recommend spending a couple of days installing and running some
tests - the beauty of open-source is that it costs you nothing to try it out.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Network Administrator** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

Quoting Richard Huxton <dev@archonet.c om>:
[color=blue]
> [stuff deleted][/color]
[color=blue]
> Stability is good. If you're feeling cautious, you could use 7.3.4 rather
> than
> the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to get
>
> a valid snapshot) for ages. It has a write-ahead-log to replay active
> transactions in case of a crash, and a couple of replication options.[/color]
[color=blue]
> [stuff deleted][/color]
[color=blue]
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postg resql.org so that your
> message can get through to the mailing list cleanly
>[/color]

Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the all
of the beta3 docs yet.

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.

http://vcsn.com

_______________ _______________ ______
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Bruno Wolff III** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

On Thu, Oct 02, 2003 at 14:55:04 -0400,
Network Administrator <netadmin@vcsn. com> wrote:[color=blue]
>
> Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the all
> of the beta3 docs yet.[/color]

No. pg_dump still gives you a consistant dump.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Richard Huxton** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

On Thursday 02 October 2003 19:55, Network Administrator wrote:[color=blue]
> Quoting Richard Huxton <dev@archonet.c om>:[color=green]
> > [stuff deleted]
> >
> > Stability is good. If you're feeling cautious, you could use 7.3.4 rather
> > than
> > the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to
> > get
> >
> > a valid snapshot) for ages. It has a write-ahead-log to replay active
> > transactions in case of a crash, and a couple of replication options.[/color][/color]
[color=blue]
> Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the
> all of the beta3 docs yet.[/color]

As Bruno says - no (and I can't imagine why anyone would).

I was merely suggesting the fact that the 7.3 series is at it's fourth minor
revision means it's received a lot more testing, whereas 7.4.0 still has the
paint wet on it.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

**Peter Childs** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

On Fri, 3 Oct 2003, Richard Huxton wrote:
[color=blue]
> On Thursday 02 October 2003 19:55, Network Administrator wrote:[color=green]
> > Quoting Richard Huxton <dev@archonet.c om>:[color=darkred]
> > > [stuff deleted]
> > >
> > > Stability is good. If you're feeling cautious, you could use 7.3.4 rather
> > > than
> > > the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to
> > > get
> > >
> > > a valid snapshot) for ages. It has a write-ahead-log to replay active
> > > transactions in case of a crash, and a couple of replication options.[/color][/color]
>[color=green]
> > Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the
> > all of the beta3 docs yet.[/color]
>
> As Bruno says - no (and I can't imagine why anyone would).
>
> I was merely suggesting the fact that the 7.3 series is at it's fourth minor
> revision means it's received a lot more testing, whereas 7.4.0 still has the
> paint wet on it.
>
>[/color]

Hot backup? Postgres 7.3 can't really do hot-backup! The only way
to backup the database is to dump the entire thing. Two minites later this
backup is only news and not exactly hot. Having to shut your database down
to back it up would be down right nasty! But Since we don't yet have an
update log. Even a dump will not get you back to the good point before
your disk died.
Mind you the truth is the downdate log should work for most cases.
So take regualar backups... and use raid. I mean how often does an entire
raid array fail at once?
I would love to see an Update Log on postgres. That and true
replications (dual master) are the two biggest missing features in
Postgres. If we had them we could run circles round mysql and oracle as
well.
Its not as if an update log is a new idea. Our old system (that we
are replacing with postgres) has an update log. So you can always get from
the last backup to now using it. (Its a TPS Bull System) The update logs
last about 2 weeks if we forget to backup! (Since we have to shutdown to
backup we only do a full backup once a week) but apparently we have never
had to use the update logs in 9 years......
Maybe it would be a good idea to start a mailinglist marked
pgsql-backup since this subject comes up so often.

Peter Childs

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Shridhar Daithankar** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

Peter Childs wrote:[color=blue]
> Hot backup? Postgres 7.3 can't really do hot-backup! The only way
> to backup the database is to dump the entire thing. Two minites later this
> backup is only news and not exactly hot. Having to shut your database down
> to back it up would be down right nasty! But Since we don't yet have an
> update log. Even a dump will not get you back to the good point before
> your disk died.
> Mind you the truth is the downdate log should work for most cases.
> So take regualar backups... and use raid. I mean how often does an entire
> raid array fail at once?[/color]

There was a clever solution posted here for replicating database in near real
time. Use RAID for data directory. For backup break the RAID and pick up the
data image from broken part. That was clever one.
[color=blue]
> I would love to see an Update Log on postgres. That and true
> replications (dual master) are the two biggest missing features in
> Postgres. If we had them we could run circles round mysql and oracle as
> well.[/color]

Postgresql do have update logs in form of WAL. However it can not do PITR with
log archiving if a node in cluster goes down for extended period. WAL does not
handle these situations in current design. Precisely, PITR is the feature that
implements this functonality.

Furthermore, mysql has replication is completely unrelated issue. Mysql didn't
have transactions so implementing replication didn't have so many constraints.
Postgresql has transactions for ages and it's replication has to support them
fully. That's what causing delays in true postgresql replication.

Design a mysql replicated cluster in a heavy concurrent update database and have
one of the node fail it. Let's see how mysql rolls back. ( FWIW, mysql rollback
time is proprotional to data in a transaction. So rolling back a 100MB
transaction isn't exactly so fast)

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Mike Mascari** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

Peter Childs wrote:
[color=blue]
> Hot backup? Postgres 7.3 can't really do hot-backup! The only way
> to backup the database is to dump the entire thing. Two minites later this
> backup is only news and not exactly hot. Having to shut your database down
> to back it up would be down right nasty! But Since we don't yet have an
> update log. Even a dump will not get you back to the good point before
> your disk died.[/color]

Isn't the definition of "hot-backup" the ability to perform a backup
while the database is still on-line engaging in transactions? I think
you're substituting "hot-backup" for point-in-time recovery...
[color=blue]
> Mind you the truth is the downdate log should work for most cases.
> So take regualar backups... and use raid. I mean how often does an entire
> raid array fail at once?[/color]

True...and assuming you're not running just RAID 0. But, as you say,
PITR could recover from such nasty things as an unqualified DELETE or
UPDATE. Disaster recovery from PITR logs being archived over a WAN or
a replicated slave would also be nice, in the event hurricane "Foo"
wipes out the data center. I haven't played around with the
replication options yet, so that might be less of a problem. But
without PITR, there's a window of risk in which a programming error or
a keying error could set you back to the last backup, whenever that was...
[color=blue]
> I would love to see an Update Log on postgres. That and true
> replications (dual master) are the two biggest missing features in
> Postgres. If we had them we could run circles round mysql and oracle as
> well.[/color]

Amen.

Mike Mascari
mascarm@mascari .com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Ron Johnson** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

On Fri, 2003-10-03 at 04:26, Shridhar Daithankar wrote:[color=blue]
> Peter Childs wrote:[/color]
[snip][color=blue]
> Postgresql do have update logs in form of WAL. However it can not do PITR with
> log archiving if a node in cluster goes down for extended period. WAL does not
> handle these situations in current design. Precisely, PITR is the feature that
> implements this functonality.[/color]

For those not in the know, PITR is Point In Time Recovery.

And yes, that capability has been around for 20+ years on mainframes
and minicomputers, but they were/are funded a lot higher than the
PostgreSQL volunteers are!

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@c ox.net
Jefferson, LA USA

"Python is executable pseudocode; Perl is executable line noise"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

**Peter Childs** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

On Fri, 3 Oct 2003, Ron Johnson wrote:
[color=blue]
> On Fri, 2003-10-03 at 04:26, Shridhar Daithankar wrote:[color=green]
> > Peter Childs wrote:[/color]
> [snip][color=green]
> > Postgresql do have update logs in form of WAL. However it can not do PITR with
> > log archiving if a node in cluster goes down for extended period. WAL does not
> > handle these situations in current design. Precisely, PITR is the feature that
> > implements this functonality.[/color][/color]

No it does not. WAL is Down-Date Logs not update logs. WAL will
enable you to rewind to the beginning of all currently running
transactions after a crash. Ie roll-back not roll-forward.
[color=blue]
>
> For those not in the know, PITR is Point In Time Recovery.
>
> And yes, that capability has been around for 20+ years on mainframes
> and minicomputers, but they were/a`re funded a lot higher than the
> PostgreSQL volunteers are!
>
>[/color]

It was planned for 7.4 so when is it going to happern now it has
missed 7.4?

Peter Childs

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

**Shridhar Daithankar** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

Peter Childs wrote:[color=blue]
> On Fri, 3 Oct 2003, Ron Johnson wrote:
>
>[color=green]
>>On Fri, 2003-10-03 at 04:26, Shridhar Daithankar wrote:
>>[color=darkred]
>>>Peter Childs wrote:[/color]
>>
>>[snip]
>>[color=darkred]
>>>Postgresql do have update logs in form of WAL. However it can not do PITR with
>>>log archiving if a node in cluster goes down for extended period. WAL does not
>>>handle these situations in current design. Precisely, PITR is the feature that
>>>implements this functonality.[/color][/color]
> No it does not. WAL is Down-Date Logs not update logs. WAL will
> enable you to rewind to the beginning of all currently running
> transactions after a crash. Ie roll-back not roll-forward.[/color]

Right. But if WAL files from one host are dropped to WAL log dir. of another
host, it will play them provided they have identical structure. IIRC there are
some replication solution which works on this basis.[color=blue][color=green]
>>For those not in the know, PITR is Point In Time Recovery.
>>
>>And yes, that capability has been around for 20+ years on mainframes
>>and minicomputers, but they were/a`re funded a lot higher than the
>>PostgreSQL volunteers are![/color]
> It was planned for 7.4 so when is it going to happern now it has
> missed 7.4?[/color]

7.5. Lots of things have missed 7.4 boat. PITR, nested transactions, native
windows ports etc. So hopefully 7.5 would have very small development cycle as
most of the features already have work going on.

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Tom Lane** · Nov 12 '05, 12:06 AM

Re: Type of application that use PostgreSQL

Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> writes:[color=blue]
> Peter Childs wrote:[color=green][color=darkred]
>>> Postgresql do have update logs in form of WAL.[/color][/color][/color]
[color=blue][color=green]
>> No it does not. WAL is Down-Date Logs not update logs. WAL will
>> enable you to rewind to the beginning of all currently running
>> transactions after a crash. Ie roll-back not roll-forward.[/color][/color]
[color=blue]
> Right.[/color]

Wrong. Peter, don't state something so authoritatively when you
obviously haven't looked at the code. Postgres does not do roll-back,
ever. (We don't need it because of MVCC.) We use WAL for roll
*forward* from the last checkpoint after a crash. Any updates that
didn't make it to disk before the crash are restored from WAL.

All that we basically need for PITR is to provide management code that
lets old WAL segments get archived off to tape (or wherever) rather than
deleted, plus some kind of control that lets the roll-forward process be
stopped at the desired point-in-time rather than necessarily running to
the end of the available WAL data. This isn't a trivial amount of code,
but there's no great conceptual difficulty either.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Network Administrator** · Nov 12 '05, 12:07 AM

Re: Type of application that use PostgreSQL

Quoting Richard Huxton <dev@archonet.c om>:
[color=blue]
> On Thursday 02 October 2003 19:55, Network Administrator wrote:[color=green]
> > Quoting Richard Huxton <dev@archonet.c om>:[color=darkred]
> > > [stuff deleted]
> > >
> > > Stability is good. If you're feeling cautious, you could use 7.3.4[/color][/color]
> rather[color=green][color=darkred]
> > > than
> > > the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to
> > > get
> > >
> > > a valid snapshot) for ages. It has a write-ahead-log to replay active
> > > transactions in case of a crash, and a couple of replication options.[/color][/color]
>[color=green]
> > Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the
> > all of the beta3 docs yet.[/color]
>
> As Bruno says - no (and I can't imagine why anyone would).
>
> I was merely suggesting the fact that the 7.3 series is at it's fourth minor
>
> revision means it's received a lot more testing, whereas 7.4.0 still has the
>
> paint wet on it.
>
> --
> Richard Huxton
> Archonet Ltd[/color]

I agree and I figured that was the case ("hot backups" is one of the reasons I
choose Pg over some other DBs) but I wanted to be clear- more so for the archives.

The other thing is that I suspect there might be some confusion as to the
difference between "hot backups" (dump of data does not require DB to be "down")
and "online backups" (dump of data is not needed because on the disk state of
the DB is current to some "recent" time). To be perfectly honest, I've only had
to use a pg_dump file once in well over 5 years (and that was recent- it was
actually posted here) in conditions where the system with NOT proper shutdown
(at the OS or application layer). PG simply does its "housekeepi ng" when the
backend started up. Of course, running the VACUUM ANALYSE help confirm more
that was indeed true. I would think that is some sort of luck because as far as
I know Pg is not able to do online backups.

In any event, "hot backups" is very important to have and use for more and more
people these days. I had a conversation with a Progress guy yesterday who I
know was grimacing when I told him I didn't need to take Pg down for backup...

Oh what a feeling :)

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.

http://vcsn.com

_______________ _______________ ______
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

**Ron Johnson** · Nov 12 '05, 12:07 AM

online vs. hot backup (was Re: Type of application thatuse PostgreSQL)

On Fri, 2003-10-03 at 13:32, Network Administrator wrote:[color=blue]
> Quoting Richard Huxton <dev@archonet.c om>:
>[color=green]
> > On Thursday 02 October 2003 19:55, Network Administrator wrote:[color=darkred]
> > > Quoting Richard Huxton <dev@archonet.c om>:
> > > > [stuff deleted][/color][/color][/color]
[snip][color=blue]
> The other thing is that I suspect there might be some confusion as to the
> difference between "hot backups" (dump of data does not require DB to be "down")
> and "online backups" (dump of data is not needed because on the disk state of
> the DB is current to some "recent" time). To be perfectly honest, I've only had[/color]

Huh? This is the 1st time that I've ever seen mention that there
is a distinction between "hot" and "on-line" backup.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@c ox.net
Jefferson, LA USA

"Fair is where you take your cows to be judged."
Unknown

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Ron Johnson** · Nov 12 '05, 12:07 AM

PITR (was Re: Type of application that use PostgreSQL)

On Fri, 2003-10-03 at 09:32, Tom Lane wrote:[color=blue]
> Shridhar Daithankar <shridhar_daith ankar@persisten t.co.in> writes:[color=green]
> > Peter Childs wrote:[/color][/color]
[snip][color=blue]
> All that we basically need for PITR is to provide management code that
> lets old WAL segments get archived off to tape (or wherever) rather than
> deleted, plus some kind of control that lets the roll-forward process be
> stopped at the desired point-in-time rather than necessarily running to
> the end of the available WAL data. This isn't a trivial amount of code,
> but there's no great conceptual difficulty either.[/color]

Hope everybody realizes that the amount of WALs will get very big
on active-update systems...

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@c ox.net
Jefferson, LA USA

Great Inventors of our time:
Al Gore -> Internet
Sun Microsystems -> Clusters

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Type of application that use PostgreSQL

Type of application that use PostgreSQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment