I have a a web app that automatically maps recent tweets and allows you to search for certain keywords. it uses Postgres for the database, geoserver as the server, and openlayers as the cartographic library. Right now tweet keywords are done using the ILIKE query.
This works pretty well if the keyword i'm looking for is a commonly used word, like 'love' , 'friend', 'OMG', etc. but less common words like "geospatial" need to search through a lot more data in order to find the last n instances of the word. This is dead slow.
To combat my slowness I'm building a GIN index on my tweet field in postgres. Two days later and i'm still waiting for the index to complete. Once it's built though, I'll try a few queries using SQL view parameters and hopefully this will dramatically speed things up.
Even if this approach works though, it's not going to be all that useful if the index takes so long to create -- the maintenance of it will not be able to keep up will the incoming flood of data. I think I will try out the GiST index next and see how long it takes as I understand it is much faster to this build index.
Beyond this, what can I do next? Do options like Solr work with Geoserver?? Are there any common approaches to this type of problem and data size/rate?