Re: Largest PostgreSQL 8.x DB someone is running?

What is the the largest PostgreSQL 8.x database that is running in a production environment that you are aware of? We top out at roughly 400 GB but have a need for a new project to go much, much larger (in the several TB range). I am attempting to get a feel for how large one should take a single PostgreSQL database, given all of the operational concerns such as overall performance with a thousand+ concurrent users, times/space requirements for backups and restores, how to upgrade to newer upcoming versions of the software, etc. especially since there are no parallel operations/features in the product.

Any information you can provide would be very helpful.

Thanks,

Keaton

Keaton Adams wrote:
> What is the the largest PostgreSQL 8.x database that is running in a
> production environment that you are aware of? We top out at roughly
> 400 GB but have a need for a new project to go much, much larger (in
> the several TB range). I am attempting to get a feel for how large
> one should take a single PostgreSQL database, given all of the
> operational concerns such as overall performance with a thousand+
> concurrent users, times/space requirements for backups and restores,
> how to upgrade to newer upcoming versions of the software, etc.
> especially since there are no parallel operations/features in the product.
>
> Any information you can provide would be very helpful.
>
See this thread from last month
http://archives.postgresql.org/pgsql-general/2008-08/msg00553.php

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4(at)une(dot)edu(dot)au

Keaton Adams wrote:
> What is the the largest PostgreSQL 8.x database that is running in a
> production environment that you are aware of? We top out at roughly 400
> GB but have a need for a new project to go much, much larger (in the
> several TB range). I am attempting to get a feel for how large one
> should take a single PostgreSQL database, given all of the operational
> concerns such as overall performance with a thousand+ concurrent users,
> times/space requirements for backups and restores, how to upgrade to
> newer upcoming versions of the software, etc. especially since there are
> no parallel operations/features in the product.
>
> Any information you can provide would be very helpful.

I have customers running over a Terabyte.

Joshua D. Drake

>
> Thanks,
>
> Keaton
>
>
>

On Sun, Sep 21, 2008 at 10:14 PM, Keaton Adams <kadams(at)mxlogic(dot)com> wrote:
> What is the the largest PostgreSQL 8.x database that is running in a
> production environment that you are aware of? We top out at roughly 400 GB
> but have a need for a new project to go much, much larger (in the several TB
> range). I am attempting to get a feel for how large one should take a
> single PostgreSQL database, given all of the operational concerns such as
> overall performance with a thousand+ concurrent users, times/space
> requirements for backups and restores, how to upgrade to newer upcoming
> versions of the software, etc. especially since there are no parallel
> operations/features in the product.
>
> Any information you can provide would be very helpful.

First and foremost, don't think of 8.0, 8.1, 8.2, 8.3 and the soon to
be released 8.4 as being the same "main branch". They're not. Each
is a major release in its own right. If you're going to be building a
new system then start with 8.3.4 (due out this week) as it's got major
performance improvements of the previous versions that make scaling
much easier.

Next, size is only important if you're operating on the whole dataset
all the time.

If you've got 1,000 users running "update table set field=field+100
where id=3" you can get by on a lot less horsepower than if you're
running "select avg(field) from table" with no where clause. The
first update updates one row, the select hits the whole table. So
your usage patterns will matter.

Where I work we have older machines with one hard drive running pg 8.1
at work that handle 600 to 1200 connections all alive at once, with
dozens and dozens active at the same time. But they're all like the
simple update above reading, updating, inserting, and deleting single
rows for a session manager.

OTOH, we have 8 CPU machines with lots of memory and hard drives, that
can be expanded, that handle several hundred concurrent operations
which are often hitting dozens to thousands of rows. Those machines
have to be bigger to handle the load.

I fail to see how the (possibly non-)issue you mention above of
parallelism would negatively affect postgresql from handling 1000s of
active backends. Splitting a single query to multiple CPUs is quite
likely to be counterproductive in such an environment.

For backups of very large systems I'd look at either slony replication
slaves for backup, or PITR, or both.

Finally, compared to the commercial products on offer, if you had an 8
or 16 core machine and you had the licenses for all the cool stuff,
you could be looking at a yearly licensing fee well into the hundreds
of thousands of dollars. You can buy a lot of hardware to throw at a
problem for that price.

On Mon, Sep 22, 2008 at 7:14 AM, Keaton Adams <kadams(at)mxlogic(dot)com> wrote:

> What is the the largest PostgreSQL 8.x database that is running in a
> production environment that you are aware of? We top out at roughly 400 GB
> but have a need for a new project to go much, much larger (in the several TB
> range). I am attempting to get a feel for how large one should take a
> single PostgreSQL database, given all of the operational concerns such as
> overall performance with a thousand+ concurrent users, times/space
> requirements for backups and restores, how to upgrade to newer upcoming
> versions of the software, etc. especially since there are no parallel
> operations/features in the product.
>
plProxy can be used in several ways to use more than one server for serving
one database. Our largest OLTP table is over 1 TB in size and we use more
than 10 quite cheap servers to keep it online. We use plProxy for horizontal
splitting data over servers, vertical splitting functionality into separate
servers and clusters of servers and last but not least good old remote calls
between servers.
http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/

regards,
Asko

>
> Any information you can provide would be very helpful.
>
> Thanks,
>
> Keaton
>
>
>
>

Am 2008-09-21 22:14:56, schrieb Keaton Adams:
> What is the the largest PostgreSQL 8.x database that is running in a
> production environment that you are aware of? We top out at roughly
> 400 GB but have a need for a new project to go much, much larger (in
> the several TB range). I am attempting to get a feel for how large
> one should take a single PostgreSQL database, given all of the
> operational concerns such as overall performance with a thousand+
> concurrent users, times/space requirements for backups and restores,
> how to upgrade to newer upcoming versions of the software, etc.
> especially since there are no parallel operations/features in the
> product.
>
> Any information you can provide would be very helpful.

My own "military" database has now arround 1,1 TByte and the largest I
know is one of my customers running three dinosaur from Sun Microsystems
with each more then 500 SCSI drives of 300 GByte.

So it is arround 21-25 TByte but do not ask me about the Electricity bill:-)

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
+49/177/9351947 50, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)