Re: COPY command details - Mailing list pgsql-general

I have one system which I have used partitioning.  For this particular
case I have tons of data over about (50 years).  What I did is wrote
small loader that breaks data in tables based on date, so I have tables
like abc_2000, abc_2001 etc.  The loading script is only a couple
hundred lines of code.  The only part that was a little bit of work was
to allow for easy access to the data for the data for the devs.  I did
this by writing a few PL functions to automatically union  the tables
and produce results. So the function like getData(startData,enddate)
would run a union query for the respective date ranges.

Benjamin

P.S. If I am doing anything that can be improved for the data access
portion please let me know and feel free to voice your opinions.  I am
always looking for new ways to make this particular database faster.

Gerald Timothy G Quimpo wrote:
> On Thu, 2007-03-29 at 21:30 -0700, Benjamin Arai wrote:
>
>> Rebuilding an index can't be the PostgreSQL solution for all
>> cases. I am dealing with databases in the hundreds of gigs
>> range and I am adding about 10gigs of data a week.  At
>> some point its going to take longer than a week to rebuild
>> all of the indexes in the database.
>>
>> On the other hand, if I am to partition the data into
>> several tables then it might not be such a big deal since
>> I am only adding and never deleting... This makes it a
>> little more of a pain in the ass.
>>
>
> I am leaning toward a policy of always partitioning large
> tables.  I haven't found the time to do it properly yet,
> thinking about it, hoping that someone who'se done it will
> chime in with their rules of thumb.
>
> Like Benjamin, I have a database that is close to 600GB
> for 2.25 years of data, and if I were to add the other
> 4 years of data that we have archived away, will easily go
> into the terabyte range.  There are a few individual tables
> which approach 100GB all by themselves.
>
> As it is, I can't afford to do reindex or even backup
> (pg_dump or any other method) or other administrative tasks
> on those tables since the processes take too long (there are
> workarounds, i could backup single tables at slack times,
> which would allow me to do a complete backup (but not
> self-consistent as a set) over the course of a week or so.
>
> So I'm leaning toward partitioning, perhaps selecting
> partition rules so that no table will be larger than
> around 5GB, at which point, reindex or admin procedures
> that take exclusive locks now become only minor
> inconveniences rather than showstoppers.
>
> How do people take consistent backups of very large
> databases on Linux/FreeBSD?  I'm aware of PITR, but
> might not be able to set aside a box with enough
> drives for it.  LVM Snapshot? performance issues with
> LVM, etc?
>
> tiger
>
>