From: Joshua D. Drake <jd@commandprompt.com>
To: Benjamin Arai <me@benjaminarai.com>
Cc: Brandon Shalton <brandon@cydataservices.com>
Cc: PostgreSQL <pgsql-general@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views
Date: Sat, 25 Aug 2007 09:18:18 -0700
Message-ID: <46D0564A.7030405@commandprompt.com> (raw)
In-Reply-To: <F6B156AD-BE6D-4300-871A-E3DE0491AA11@benjaminarai.com>
References: <3147EF26-2649-4FDF-BD86-2DB0AF8460FC@benjaminarai.com>
<0be901c7e6cb$a2b13320$c901a8c0@blaptop>
<46CF85EE.5040805@commandprompt.com>
<F6B156AD-BE6D-4300-871A-E3DE0491AA11@benjaminarai.com>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Benjamin Arai wrote:
> This kind of disappointing, I was hoping there was more that could be done.
>
> There has to be another way to do incremental indexing without loosing
> that much performance.
What makes you think you are loosing performance by using partitioning?
Joshua D. Drake
>
> Benjamin
>
> On Aug 24, 2007, at 6:29 PM, Joshua D. Drake wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>
>> Brandon Shalton wrote:
>>> Benjamin,
>>>
>>>
>>>>
>>>> In order to avoid the re-indexing I was thinking of instead creating
>>>> a new
>>>> table each month (building its indexes and etc) and accessing them all
>>>> through a view. This way I only have to index the new data each month.
>>>>
>>>
>>> Take a look at bizgres.org (based on postgres).
>>>
>>> They have a parent-child structure.
>>>
>>> The way i use it, is I have about 30M records a day that are inserted
>>> into the database.
>>>
>>> Each day is a "child" table to the "parent".
>>>
>>> so example:
>>>
>>> the parent table is called "logfile"
>>>
>>> each day, is a child, with the structure like "logfile_YYMMDD"
>>>
>>> the "child" inherits the table structure of the parent, such that you
>>> could query the child table name directly, or you run the query against
>>> the parent (ie. logfile table) and get all the data.
>>>
>>> the indexes are done on a per table basis, so new data that comes in, is
>>> a lesser amount, and doesn't require re-indexing.
>
>
>> PostgreSQL can do all of this too.
>
>> Sincerely,
>
>> Joshua D. Drake
>
>>>
>>>
>>> example:
>>>
>>> select * from logfile_070825 where datafield = 'foo'
>>>
>>> if i knew i wanted to specifically go into that child, or:
>>>
>>> select * from logfile where datafield = 'foo'
>>>
>>> and all child tables are searched and results merged. You can perform
>>> any kind of sql query and field structures are you normally do.
>>>
>>> the downside is that the queries are run sequentially.
>>>
>>> so if you had 100 child tables, each table is queried via indexes, then
>>> results are merged.
>>>
>>> but, this approach does allow me to dump alot of data in, without having
>>> the re-indexing issues you are facing.
>>>
>>> at some point, you could roll up the days, in to weekly child tables,
>>> then monthly tables, etc.
>>>
>>> I believe Bizgres has a new version of their system that does parallel
>>> queries which would certainly speed things up.
>>>
>>> For your documents, you can do it by the day it was checked in, or maybe
>>> you have some other way of logically grouping, but the parent/child
>>> table structure really helped to solve my problem of adding in millions
>>> of records each day.
>>>
>>> The closest thing in mysql is using merge tables, which is not really
>>> practical when it comes time to do the joins to the tables.
>>>
>>> -brandon
>>>
>>> http://www.t3report.com - marketing intelligence for online marketing
>>> and affiliate programs
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: explain analyze is your friend
>>>
>
>
>> - --
>
>> === The PostgreSQL Company: Command Prompt, Inc. ===
>> Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
>> PostgreSQL solutions since 1997 http://www.commandprompt.com/
>> UNIQUE NOT NULL
>> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>> PostgreSQL Replication: http://www.commandprompt.com/products/
>
>>
- ---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFG0FZKATb/zqfZUUQRAsfRAJ4mjQP+1ltG7pqLFQ+Ru52LA5e7XACcDqKr
PIihth2x3gx3qTEI8WfWNjo=
=AhJx
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jd@commandprompt.com, me@benjaminarai.com, brandon@cydataservices.com, pgsql-general@postgresql.org
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views
In-Reply-To: <46D0564A.7030405@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox