5

I currently have a large and growing database of geo-social media. I'm using Postgres with postgis, and so far it's been quite good.

My use case thus far has been to query the database for all posts within specific areas, such as every tweet within each post/zip-code in the US/Canada (or other boundaries -- the posts are not geo-coded to the post/zip code. selection is through intersection of coordinates).

In the past this was relatively quick and easy to do, however, now that my data table has grown to over 350m posts, my queries take forever to complete. Each post code can take up to a day to retrieve the tweets from within its geometry.

I am currently debating what to do next. I know of a few solutions, but I'm curious what you think of them and if I'm missing anything.

  1. throw more memory at postgres, and reduce disk searching. I'm currently working on doing this, but I wonder if it is a good long term solution.

  2. find a better way to partition data into more tables. Currently, all of my posts are in the same table, with each column indexed. Given that I need to search through all of the posts to find the ones in the given areas, is this the wrong way to be doing it?

  3. create a hybrid of Cassandra-PostGIS. Bulk store my data into Cassandra, and then pull out roughly the data I need (say, one state/province at a time) to a staging data table in PostGIS, then query individual post/zip codes using PostGIS. Is this overkill/horribly inefficient? I have looked into Postgres-XL, but it has been kiboshed by others in this project for cost issues.

closed as too broad by PolyGeo Mar 17 at 21:59

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Might be better to ask at dba.stackexchange.com – Mapperz Jul 17 '14 at 20:49
  • Are you sure it is the selection that is slow and not just the output of all the data. I mean an index search on a integer field like postal codes on 350 mill rows shouldn't be that slow I think. – Nicklas Avén Jul 17 '14 at 21:26
  • i'm slelecting posts by post-code via the geometry intersections, as each post is not geocoded. Additionally, i use other boundaries than post codes, i.e. disemination areas, CMA districts, etc so I don't think a mass geo-code is terribly effective – mikedotonline Jul 17 '14 at 21:33
  • 1
    Ok, but still. If you have a working spatial index on the geometries I cannot understand that it should take that long time. 350 mill records is not that much. – Nicklas Avén Jul 17 '14 at 21:56
  • BTW, you say that you do the selection spatially, but you are also talking about many indexed columns. Why do you have many indexed columns if you don't use them for the selection? – Nicklas Avén Jul 17 '14 at 21:58
1

use "explain" (http://www.postgresql.org/docs/9.3/static/sql-explain.html) to see if your spatial index is being used, and make sure you've run "analyze" (http://www.postgresql.org/docs/9.3/static/sql-analyze.html) to make sure that the db has collected sufficient statistics on your indices.

It sounds to me like you don't know what you're doing with respect to planning for queries. It is usually not good practice to index every column. For example, if you know that spatial queries will drop the number of items to a small number, then just use space. If time will do a better job than space, then just use that. Then use sub-selects or with statements to drill down even further.

But really, spending some time playing with "explain" will greatly improve your understanding of what the engine is doing.

0

I agree with the other comments, for it to be this slow you need to not have setup indexing correctly. How are you storing the data ? Are you using JSON ? You need to have spatial columns or indexes to get fast queries working.

Check out the links below for examples of spatially indexing JSON twitter data.

http://bibhas.in/blog/postgresql-swag-json-data-type-and-working-with-twitter-data/ http://bibhas.in/blog/postgresql-swag-part-2-indexing-json-data-type-and-full-text-search/

Not the answer you're looking for? Browse other questions tagged or ask your own question.