PostgreSQL theoretical maximums.

Karen Hill wrote:

How many tables can PostgreSQL theoretically and then practically
handle? What is the largest database size possible? What was the
biggest database you've ever had on PostgreSQL? What were the
challenges and what kind of hardware and OS works best?

Maximum number of tables etc. is a FAQ:
http://www.postgresql.org/docs/faqs.FAQ.html#item4.4

It's been running on unix-like systems for much longer than Windows.
Apart from that, the best system is probably determined by your experience.

What is an effective way to predict database size when designing
tables?

Rule-of-thumb - assume 3-5 times the size of the raw data to allow for
overhead, indexes etc. Other than that, you can find details of on-disk
formats for rows towards the end of the manuals.

--
Richard Huxton
Archonet Ltd

Hi,
Have a look at:
http://www.postgresql.org/about/

/David

Karen Hill wrote:

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

Richard Huxton wrote:

Karen Hill wrote:

How many tables can PostgreSQL theoretically and then
practically handle? What is the largest database size
possible? What was the biggest database you've ever had on
PostgreSQL? What were the challenges and what kind of hardware
and OS works best?

Maximum number of tables etc. is a FAQ:
http://www.postgresql.org/docs/faqs.FAQ.html#item4.4

It's been running on unix-like systems for much longer than
Windows. Apart from that, the best system is probably determined
by your experience.

What is an effective way to predict database size when
designing tables?

Rule-of-thumb - assume 3-5 times the size of the raw data to
allow for overhead, indexes etc. Other than that, you can find
details of on-disk formats for rows towards the end of the
manuals.

I think I've read that there are multi-hundred GB PostgreSQL databases.

Since pg_dump is single-threaded, how long does it take to back up
such a database? (Obviously it would need modern LTO, SuperDLT, etc
drives, either FC or U320.)

Are there any plans of making a multi-threaded, or even
multi-process pg_dump?

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEzUXgS9HxQb37XmcRAq7eAJ9HS4YWB5y/MQ7sGTpAMIvXKMTRhQCfXPEJ
rYSLF4nTKv9AclJ2ZUHLVxE=
=eBsW
-----END PGP SIGNATURE-----

Ron Johnson wrote:

I think I've read that there are multi-hundred GB PostgreSQL
databases.

Since pg_dump is single-threaded, how long does it take to back up
such a database?

The evasive answer is that you probably don't run regular full pg_dump
on such databases.

Are there any plans of making a multi-threaded, or even
multi-process pg_dump?

What do you hope to accomplish by that? pg_dump is not CPU bound.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Peter Eisentraut wrote:

Ron Johnson wrote:

I think I've read that there are multi-hundred GB PostgreSQL
databases.

Since pg_dump is single-threaded, how long does it take to back up
such a database?

The evasive answer is that you probably don't run regular full pg_dump
on such databases.

Hmmm.

Are there any plans of making a multi-threaded, or even
multi-process pg_dump?

What do you hope to accomplish by that? pg_dump is not CPU bound.

Write to multiple tape drives at the same time, thereby reducing the
total wall time of the backup process.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEzhl2S9HxQb37XmcRArRoAKDiVOicD7giL2lWF5zMa9qQanwXjQCfdY6r
0eswQQ5nJb8l5Mbp+ok0ZRs=
=MWSM
-----END PGP SIGNATURE-----

On Mon, 2006-07-31 at 09:53 -0500, Ron Johnson wrote:

The evasive answer is that you probably don't run regular full pg_dump
on such databases.

Hmmm.

You might want to use PITR for incremental backup or maintain a standby
system using Slony-I ( www.slony.info ).

Are there any plans of making a multi-threaded, or even
multi-process pg_dump?

What do you hope to accomplish by that? pg_dump is not CPU bound.

Write to multiple tape drives at the same time, thereby reducing the
total wall time of the backup process.

pg_dump just produces output. You could pretty easily stripe that output
across multiple devices just by using some scripts. Just make sure to
write a script that can reconstruct the data again when you need to
restore. You don't need multi-threaded pg_dump, you just need to use a
script that produces multiple output streams. Multi-threaded design is
only useful for CPU-bound applications.

Doing full backups of that much data is always a challenge, and I don't
think PostgreSQL has limitations that another database doesn't.

Regards,
Jeff Davis

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

Jeff Davis wrote:

On Mon, 2006-07-31 at 09:53 -0500, Ron Johnson wrote:

The evasive answer is that you probably don't run regular full pg_dump
on such databases.

Hmmm.

You might want to use PITR for incremental backup or maintain a standby
system using Slony-I ( www.slony.info ).

We want PITR, Incremental & Full.

Currently do:
Full - every other day, directly to 4 tape drives.
Incremental - on "the other" days, to 1 tape drive.
PITR - every 8 hours, to disk.

Are there any plans of making a multi-threaded, or even
multi-process pg_dump?

What do you hope to accomplish by that? pg_dump is not CPU bound.

Write to multiple tape drives at the same time, thereby reducing the
total wall time of the backup process.

pg_dump just produces output. You could pretty easily stripe that output
across multiple devices just by using some scripts. Just make sure to
write a script that can reconstruct the data again when you need to
restore.

But doesn't that mean that pg_dump must dump to disk?

With a *big* database, that's a whole lot of extra kit (not just
spindles) to buy.

You don't need multi-threaded pg_dump, you just need to use a
script that produces multiple output streams. Multi-threaded design is
only useful for CPU-bound applications.

Doing full backups of that much data is always a challenge, and I don't
think PostgreSQL has limitations that another database doesn't.

I dunno about that. With Rdb/VMS it is trivial to backup a database
directly to multiple tape drives. The Rdb backup utility has an
algorithm with determines which tablespace data is copied to which
tape drive.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE14u3S9HxQb37XmcRAj8xAKC6n4OmHBLeGkGoMz58RFY3FIWf0wCeIlRU
Ott3Uj7/0rpdG7Yb4o+7HPY=
=TdPt
-----END PGP SIGNATURE-----

On Mon, 2006-08-07 at 13:51, Ron Johnson wrote:

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

Jeff Davis wrote:

On Mon, 2006-07-31 at 09:53 -0500, Ron Johnson wrote:

pg_dump just produces output. You could pretty easily stripe that output
across multiple devices just by using some scripts. Just make sure to
write a script that can reconstruct the data again when you need to
restore.

But doesn't that mean that pg_dump must dump to disk?

With a *big* database, that's a whole lot of extra kit (not just
spindles) to buy.

I'm certain you can backup direct to tape, as I've done it in the past
with postgresql. This was in the day of 4 gig 4 mm tape drives...

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

Scott Marlowe wrote:

On Mon, 2006-08-07 at 13:51, Ron Johnson wrote:

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

Jeff Davis wrote:

On Mon, 2006-07-31 at 09:53 -0500, Ron Johnson wrote:

pg_dump just produces output. You could pretty easily stripe that output
across multiple devices just by using some scripts. Just make sure to
write a script that can reconstruct the data again when you need to
restore.

But doesn't that mean that pg_dump must dump to disk?

With a *big* database, that's a whole lot of extra kit (not just
spindles) to buy.

I'm certain you can backup direct to tape, as I've done it in the past
with postgresql. This was in the day of 4 gig 4 mm tape drives...

Sure. That's why tar is named *Tape* ARchive.

Going along with the example of the 4GB 4mm tape drive, and to
simplify, we are *not* using compression (neither on the drive or
s/w gzip or bzip):

Say we have a 7GB database. Is there a way to way to use 2 tape
drives... Argh, I guess not, since the Unix pipe mentality presumes
that the mode of operation will be:
$ pg_dump /some/database | tar cvfz /dev/st0/db.tgz

What would be darned useful (but only, I think, with heavy usage of
tablespaces) is:
$ pg_tapedump /some/database /dev/st0,/dev/st1,/dev/st2,/dev/st3

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE15mwS9HxQb37XmcRAmh/AKDN8ITbeax0+DvKBMGRJ2JBEy3cpACg4/Gd
t4mFuJA+DeAUOEKSztbWHNg=
=Cq3a
-----END PGP SIGNATURE-----

Say we have a 7GB database. Is there a way to way to use 2 tape
drives... Argh, I guess not, since the Unix pipe mentality presumes
that the mode of operation will be:
$ pg_dump /some/database | tar cvfz /dev/st0/db.tgz

What would be darned useful (but only, I think, with heavy usage of
tablespaces) is:
$ pg_tapedump /some/database /dev/st0,/dev/st1,/dev/st2,/dev/st3

Would passing the --multi-volume option be useful to you?

http://www.apl.jhu.edu/Misc/Unix-info/tar/tar_97.html

Regards,

Richard Broersma Jr.

On Mon, 2006-08-07 at 14:51, Ron Johnson wrote:

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

Scott Marlowe wrote:

On Mon, 2006-08-07 at 13:51, Ron Johnson wrote:

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

Jeff Davis wrote:

On Mon, 2006-07-31 at 09:53 -0500, Ron Johnson wrote:

pg_dump just produces output. You could pretty easily stripe that output
across multiple devices just by using some scripts. Just make sure to
write a script that can reconstruct the data again when you need to
restore.

But doesn't that mean that pg_dump must dump to disk?

With a *big* database, that's a whole lot of extra kit (not just
spindles) to buy.

I'm certain you can backup direct to tape, as I've done it in the past
with postgresql. This was in the day of 4 gig 4 mm tape drives...

Sure. That's why tar is named *Tape* ARchive.

Going along with the example of the 4GB 4mm tape drive, and to
simplify, we are *not* using compression (neither on the drive or
s/w gzip or bzip):

Say we have a 7GB database. Is there a way to way to use 2 tape
drives... Argh, I guess not, since the Unix pipe mentality presumes
that the mode of operation will be:
$ pg_dump /some/database | tar cvfz /dev/st0/db.tgz

What would be darned useful (but only, I think, with heavy usage of
tablespaces) is:
$ pg_tapedump /some/database /dev/st0,/dev/st1,/dev/st2,/dev/st3

Oh, I see where you were headed.

I've found a bit here and there googling about for multiplex and tar,
and found an IO:Multiplex module for perl.

I don't see why someone couldn't make use of it to create some kind of
system where you'd do:

pg_dump dbname | perl mulitiplexscript device1,device2,device3,devicen

No need for postgresql to support it directly. Restoring would likewise
just be a reverse operation.

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

Richard Broersma Jr wrote:

Say we have a 7GB database. Is there a way to way to use 2 tape
drives... Argh, I guess not, since the Unix pipe mentality presumes
that the mode of operation will be:
$ pg_dump /some/database | tar cvfz /dev/st0/db.tgz

What would be darned useful (but only, I think, with heavy usage of
tablespaces) is:
$ pg_tapedump /some/database /dev/st0,/dev/st1,/dev/st2,/dev/st3

Would passing the --multi-volume option be useful to you?

http://www.apl.jhu.edu/Misc/Unix-info/tar/tar_97.html

Mostly no, because it serializes access. Fill up one tape, go to
the next, then the 3rd, etc.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE152FS9HxQb37XmcRAiLuAJwIsMlCfguvti4z/2WNozCFjT0ZYQCgv8d2
LnwIypkK00EVRx/yMKzggy4=
=rdW2
-----END PGP SIGNATURE-----

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

Scott Marlowe wrote:

On Mon, 2006-08-07 at 14:51, Ron Johnson wrote:

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

Scott Marlowe wrote:

On Mon, 2006-08-07 at 13:51, Ron Johnson wrote:

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

Jeff Davis wrote:

On Mon, 2006-07-31 at 09:53 -0500, Ron Johnson wrote:
pg_dump just produces output. You could pretty easily stripe that output
across multiple devices just by using some scripts. Just make sure to
write a script that can reconstruct the data again when you need to
restore.

But doesn't that mean that pg_dump must dump to disk?

With a *big* database, that's a whole lot of extra kit (not just
spindles) to buy.

I'm certain you can backup direct to tape, as I've done it in the past
with postgresql. This was in the day of 4 gig 4 mm tape drives...

Sure. That's why tar is named *Tape* ARchive.

Going along with the example of the 4GB 4mm tape drive, and to
simplify, we are *not* using compression (neither on the drive or
s/w gzip or bzip):

Say we have a 7GB database. Is there a way to way to use 2 tape
drives... Argh, I guess not, since the Unix pipe mentality presumes
that the mode of operation will be:
$ pg_dump /some/database | tar cvfz /dev/st0/db.tgz

What would be darned useful (but only, I think, with heavy usage of
tablespaces) is:
$ pg_tapedump /some/database /dev/st0,/dev/st1,/dev/st2,/dev/st3

Oh, I see where you were headed.

I've found a bit here and there googling about for multiplex and tar,
and found an IO:Multiplex module for perl.

I don't see why someone couldn't make use of it to create some kind of
system where you'd do:

pg_dump dbname | perl mulitiplexscript device1,device2,device3,devicen

No need for postgresql to support it directly. Restoring would likewise
just be a reverse operation.

Interesting. Many thanks. Scary though that it hasn't been touched
in 30 months.

With multiple SCSI cards, each with it's own tape drive, this
immensely speeds up the backup operation.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE16ABS9HxQb37XmcRAg3jAJ4nCT6vaytOuPnk/zG2QYSeulRirgCgmVkT
iM+FYqrPwGX2as2fYs67lE4=
=YVML
-----END PGP SIGNATURE-----

On Mon, 2006-08-07 at 15:18, Ron Johnson wrote:

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

Scott Marlowe wrote:

Oh, I see where you were headed.

I've found a bit here and there googling about for multiplex and tar,
and found an IO:Multiplex module for perl.

I don't see why someone couldn't make use of it to create some kind of
system where you'd do:

pg_dump dbname | perl mulitiplexscript device1,device2,device3,devicen

No need for postgresql to support it directly. Restoring would likewise
just be a reverse operation.

Interesting. Many thanks. Scary though that it hasn't been touched
in 30 months.

With multiple SCSI cards, each with it's own tape drive, this
immensely speeds up the backup operation.

Yeah, I read the description, and I think it's just a fancy name for
tee. sigh. Not REAL multiplexing, but stream duplication.

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

Scott Marlowe wrote:

On Mon, 2006-08-07 at 15:18, Ron Johnson wrote:

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

Scott Marlowe wrote:

Oh, I see where you were headed.

I've found a bit here and there googling about for multiplex and tar,
and found an IO:Multiplex module for perl.

I don't see why someone couldn't make use of it to create some kind of
system where you'd do:

pg_dump dbname | perl mulitiplexscript device1,device2,device3,devicen

No need for postgresql to support it directly. Restoring would likewise
just be a reverse operation.

Interesting. Many thanks. Scary though that it hasn't been touched
in 30 months.

With multiple SCSI cards, each with it's own tape drive, this
immensely speeds up the backup operation.

Yeah, I read the description, and I think it's just a fancy name for
tee. sigh. Not REAL multiplexing, but stream duplication.

This is where a multi-threaded pg_tapedump would be more effective,
since it would be able to, for example, have 4 threads reading
(different parts of) the database and writing to 4 separate tape drives.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE16wiS9HxQb37XmcRAvi0AJ0eHuurG7cC3HC9A1lOMXvcBDJ7QACeMlyB
bS3ozQ69gFgrM70oHGJr8Zk=
=8N8c
-----END PGP SIGNATURE-----

On Mon, 2006-08-07 at 14:51 -0500, Ron Johnson wrote:

What would be darned useful (but only, I think, with heavy usage of
tablespaces) is:
$ pg_tapedump /some/database /dev/st0,/dev/st1,/dev/st2,/dev/st3

I must be missing something. What is stopping you from doing something
along the lines of:

// mux.script psuedocode
// X is the size of a stripe of data
top:
read X bytes from stdin or exit if EOF
asynchronously write those X bytes to /dev/st0
read X bytes from stdin or exit if EOF
asynchronously write those X bytes to /dev/st1
goto top

And then make an inverse script called demux.script.

Then:
$ pg_dump somedatabase | gzip -c | mux.script
To restore:
$ demux.script | gunzip -c | psql somedatabase

Would that work? Obviously you'd have to document the process well to
make sure the someone didn't get confused 12 months later trying to
restore.

You may have to do something a little more sophisticated to make it work
more generally, like adding header information to each tape that says "I
am the 2nd tape of 3".

Regards,
Jeff Davis

Ron Johnson wrote:

This is where a multi-threaded pg_tapedump would be more effective,
since it would be able to, for example, have 4 threads reading
(different parts of) the database and writing to 4 separate tape drives.

It will be difficult to have a consistent dump though. You can't do
that with separate transactions. (And you can't have multiple
simultaneous readers without separate transactions.)

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

On Mon, 2006-08-07 at 16:09, Ron Johnson wrote:

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

Scott Marlowe wrote:

On Mon, 2006-08-07 at 15:18, Ron Johnson wrote:

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

Scott Marlowe wrote:

Oh, I see where you were headed.

I've found a bit here and there googling about for multiplex and tar,
and found an IO:Multiplex module for perl.

I don't see why someone couldn't make use of it to create some kind of
system where you'd do:

pg_dump dbname | perl mulitiplexscript device1,device2,device3,devicen

No need for postgresql to support it directly. Restoring would likewise
just be a reverse operation.

Interesting. Many thanks. Scary though that it hasn't been touched
in 30 months.

With multiple SCSI cards, each with it's own tape drive, this
immensely speeds up the backup operation.

Yeah, I read the description, and I think it's just a fancy name for
tee. sigh. Not REAL multiplexing, but stream duplication.

This is where a multi-threaded pg_tapedump would be more effective,
since it would be able to, for example, have 4 threads reading
(different parts of) the database and writing to 4 separate tape drives.

Actually, I kinda prefer the idea of creating multiple streams using
something like RAID, where if you have two streams, it's a mirror, if
you have three or more then you rotate around a parity stripe like RAID
5. Then, any error on any one tape drive could be recovered. Veritas
has something like that for tape drives.

Heck, the more I think about it, the more I think it would be an
interesting project for a device driver that was like /dev/mdt or
something, and re-used the md libs from the hard drive universe.

Attach X tape drive, put in a bunch of tapes, and just pg_dump > mdt0
and you're backing up. Restore the other way around.