Re: [GENERAL] Querying a Large Partitioned DB

2009-04-12 Thread Simon Riggs 

On Fri, 2009-04-10 at 09:15 -0500, Justin Funk wrote:

 I need to be able to do full text searches on the message field, and
 of course, it needs to be reasonably fast.
 
 The table is partitioned daily and has this structure:

 My typical query looks like this:
 SELECT * FROM SystemEvents WHERE message_index_col @@
 to_tsquery('english', 'Term')  LIMIT 25 OFFSET 0;

The partitioning does nothing to improve your typical query.

Loop through the tables from first to last until you have returned 25
records. That way you won't have to wait to search every table.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Querying a Large Partitioned DB

2009-04-10 Thread Tom Lane 
Justin Funk fun...@iastate.edu writes:
 Can you give me any tips and suggestions about how to speed this up?

Use fewer partitions --- 180 is a lot.  Maybe weekly partitioning would
be about right.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Querying a Large Partitioned DB

2009-04-10 Thread A.M. 

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On Apr 10, 2009, at 10:15 AM, Justin Funk wrote:


Team Amazing,

I am building a massive database for storing the syslogs of a room of
servers. The database gets about 25 million entries a day, and need  
to keep

them for 180 days. So the total size of the database will be about 4.5
billion records.

I need to be able to do full text searches on the message field, and  
of

course, it needs to be reasonably fast.


You could use pg-pool II or your own middleware to execute the search  
query in parallel across all the partitions (maybe not all 180 at  
once, though).


Cheers,
M
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.10 (Darwin)

iEYEARECAAYFAknfaVAACgkQqVAj6JpR7t65DQCgsN51pMWoY8WXyxss6cXRPHug
4h8An2IufbKuhrw4fyki4gBbjrkkQD0M
=5PRb
-END PGP SIGNATURE-

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general