-----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/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGz4XuATb/zqfZUUQRAukhAJ9b2x4PLPZsoPmtm3O/Ze4AobDXngCgq+rl X2j2ePDyjYxRajfGCVmjnYU= =pIjb -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
