One large v. many small

From: Noah Silverman <noah(at)allresearch(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: One large v. many small
Date: 2003-01-30 17:34:36
Message-ID: 1A107C56-3479-11D7-A3A3-000393AA8F3C@allresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

As we continue our evaluation of Postgres, another interesting topic
has come up that I want to run by the group.

In our current model, we have about 3,000 small tables that we use
track data for our clients. Each table is an identical structure, and
holds the data for one client.

Another idea that we are considering is one big table instead of 3,000
smaller ones. We could simply add a numeric field to indicate which
client a particular record was for.

Each table has between 500 and 50,000 records, so the big table could
have up to 10 million rows if we combined everything.

A query on our current system is (for client #4)

Select (*) from client_4 where foo=2;

A query from the new, proposed system would be

Select (*) from big_results where client=4 and foo=2.

The big questions is, WHICH WILL BE FASTER with Postgres. Is there any
performance improvement or cost to switching to this new structure.

ANY AND ALL FEEDBACK/OPINIONS ARE WELCOME!!

Thanks,

Noah

Responses

Browse pgsql-general by date

  From Date Subject
Next Message will trillich 2003-01-30 17:36:40 Re: Documentation needs significant improvement
Previous Message Ron St.Pierre 2003-01-30 17:18:01 Re: is_numeric() or extract_numeric() functions?

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2003-01-30 17:56:56 Re: One large v. many small
Previous Message Rajesh Kumar Mallah. 2003-01-30 10:48:11 Re: Strangae Query Plans