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