Michael Gould wrote:
>> 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
>>
>
> Yes, each location has their own specific location code.
>
>
good!
how many rows you will have per each location code?
how do you use the data? most of your reporting would read from one
location only or from multiple ones?
>> 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)
>>
>
> Everything will be done via inserts either via online entry or from external
> processes such as EDI processing. It would depend on how the partition is
> setup. If we set them up by location, there would be one insert or more for
> each record entered and it would to just the location files. If we did it
> by date range then everyone would be entering data into a single set of
> tables based on date range instead of by location.
>
>
>
do you read tables in the same time when you insert into it?
how many inserts per sec/min/hour you may have? approximate..
>> 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.
>>
>>
>
> >From our current platform, disk space isn't a issue. Our db is highly
> normalized and we've had about 200 locations over the past 10 years and the
> db is currently about 4 gig of total data. That being said, the only way to
> reclaim space with this db is to do a complete unload/reload which we don't
> do very oftern and it appears that the Postgres vaccuum all process is much
> easier to use.
>
ok, in that case why do you want to archive the data in the first place?
you have no space constraints and you still need to read the data from
time to time...
>
>
Regards,
foo