-----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----- ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
