Thread: PostgreSQL database design for a large company

On Wed, Feb 23, 2011 at 9:59 PM, Kalai R <softlinne.kv@gmail.com> wrote:
> Is it possible to maintain in a single database for all years of data?

Yes it is possible.  But nobody can answer that for your specific
situation without knowing the amount of data and how you plan to use
it.

On 24/02/2011 10:59 AM, Kalai R wrote:
> We are going to design database for a large company with multi branches.
> I am using PostgreSQL
>
> For example,
>
> I create a single database for all branches. After 10 years, database
> size is too large.

What is "too large?"

If you keep PostgreSQL up to date, have periodic hardware updates, and
properly maintain & index the database, it won't necessarily become
"too" large at all.

If it does, your best option is usually going to be partitioning tables
into live- and history- sections, or partitioning by some
query-appropriate key like date. It is a good idea to think about these
issues when designing the app and database, rather than trying to hack
them in afterwards. You can get particularly significant benefits at low
effort cost by consistently using partial indexes to keep index sizes
down for queries that only touch "live" data.

> I want to know ,at that time, the performance of the database is reduced
> or not.

If you add more data, then all other things being equal the database
will get slower. How much? Hard to know, it depends so much on your
query patterns, your maintenance, etc. In any case, you would be crazy
to keep on running on 10 year old hardware with a 10 year old release of
PostgreSQL. In reality you must plan periodic updates, in which case
it's quite likely that the growth of hardware and software performance
will keep up with the data growth.

> Is it possible to maintain in a single database for all years of data?

How much data do you expect to have? How fast will it grow?

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

On 24/02/2011 10:59 AM, Kalai R wrote:
> We are going to design database for a large company with multi branches.
> I am using PostgreSQL
>
> For example,
>
> I create a single database for all branches. After 10 years, database
> size is too large.

Oh yeah, for what it's worth: my employer has 10-year-old database
stored in a shared-access ISAM-variant database last updated in 1983,
running on a scary old SCO OpenServer 5.0.5 machine that runs the
software in a Microsoft Xenix (1895) personality. Despite the total
inability to maintain the database or the runtime its self, the app's
performance has IMPROVED over time because the hardware has become so
much faster. Most recently, I moved it from physical hardware into a
VMWare container, at which point it began benefiting from Linux's
efficient use of lots of RAM for caching (something SCO is terrible at)
and more than quadrupled in speed. Operations that used to take an hour
when the system was first deployed in 1991* now take less than a minute.

About the only significant scaling design choice made in the original
software was the decision to partition the biggest table into history-
and live- sections. That alone has preserved performance to a more than
satisfactory level.

If we can get that kind of result with a scary ancient monster, it's
possible you'll be OK. On the other hand, said scary ancient monster
just doesn't collect that much data... and that makes a big difference.

* Yes it's insane to write a mission-critical system in 1991 using a
closed source runtime that was abandoned and dead in 1983. It was before
my time at the company, which just means I get to suffer through
maintaining the damn thing while trying to replace it.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

On Feb 25, 2011, at 6:24 PM, Craig Ringer wrote:

> In reality you must plan periodic updates, in which case it's quite likely that the growth of hardware and software
performancewill keep up with the data growth. 

I've been in a lovely little niche that has fit that description since 1993 ;-)


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice