Michael Gould wrote:
I am considering whether or not to partition a few tables in our system.
The guide lines I read said that this could help when the size of a table
exceeds the amount of system memory. I'm wondering if this statement should
be table or database.
The way our system operates is that each individual location is actually a
separate operation owned by another party. In most cases the data from one
location should not be seen by others. There are several tables which are
global in nature which hold mainly lookup information, but the actual
processing tables are by location.
I am wondering if partitioning would be a good way to isolate the
information for each location. Each database would be created by location
number. Out db servers is a dual quad Intel with 4 Gigs of RAM running
Windows 2000 Enterprise Server. All Client machines are running Quad core
servers with 8-16 gig of RAM partitioned using Windows 2003 and accessed by
Citrix.
The total size of our database with 5 years worth of data is about 3.4 gig.
In the business we are in, we open about 5-7 new locations each year and
close 2-3. I was also thinking that if each partition was by location it
would be easier to disconnect the partion tables to use for historial use
when we close a location. We probably would get 10 or so queries on the
closed locations 6 months after closing.
Does this seem like an appropriate use of table partitioning?
Hi - partitioning by definition should be used to get your data faster
and, in most of the cases, should be transparent to your client.
Questions I'd suggest to ask first:
1. do you have performance issues reading from your db?
2. can you come up with a pattern you can use to segment your data into
smaller chunks (like weeks, months, ids) to create partitions reasonably
big but not huge
3. how do you populate your db? inserts? copy? if ever you create
partitions, will you write to many or just one (the most recent one)
Yes, it can be useful archiving old 'locations'. In that case I'd
suggest to create dedicated datafile and put it on (slower=cheaper)
disks and move your older partitions there... but again, it's not the
main reason why you could consider partitioning.
Best Regards
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
Regards,
foo
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
