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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
