Re: scaling a database

"Martin Dillard" <martin(at)edusoftinc(dot)com> writes:

> Good point. I should have elaborated on that. The users would not all
> be connecting concurrently. We store data for all of them but only 10
> connect concurrently right now out of the 20,000 users in the system
> (obviously we hope this increases). This is a separate issue that we
> will address but right now I am interested in knowing if anyone has
> ever had to partition a database or a table as it grew because of the
> resource limits reached.
>
> I am basically looking for examples or case studies to learn from. I
> realize that our application will be unique and that a valid answer
> to my question is "it depends" but I am interested in hearing if
> there are other measures required besides increasing the processing
> power, memory, or disk space allocated to PostgreSQL.

A list of *hard* limitations for PostgreSQL can be found here:

http://www.us.postgresql.org/users-lounge/limitations.html

Limitations of PostgreSQL
Maximum size for a database unlimited (60GB databases exist)
Maximum size for a table 64 TB on all operating systems
Maximum size for a row unlimited in 7.1 and later
Maximum size for a field 1GB in 7.1 and later
Maximum number of rows in a table unlimited
Maximum number of columns in a table 1600
Maximum number of indexes on a table unlimited

Of course, these are not actually unlimited, but limited to
available disk space and memory/swap space. Performance may
suffer when these values get unusually large.

As you can see, PostgreSQL doesn't have any built in restrictions that
would preclude its use in even extremely large datasets. However, my
guess is what you are really looking for is the degree to which
"performance may suffer when these values get unusually large" is
true.

From my own experience I have found that PostgreSQL does just fine
with tables that have millions of rows. This is especially true in
cases like yours where the biggest of your tables are read only. My
biggest problems to date have to do with vacuuming large tables
(between 10 and 20 million rows). If you don't update the tables, on
the other hand, there is no need to vacuum.

Jason