[postgis-users] Choosing a database scheme for huge amount of points

[Stephen Woodbridge]

Alexander Cohagen wrote:
> Hi,
> 
> I have the following data in the system:
> 1. Points - retrieved from users GPS devices every 10 seconds. (x,y,z - 
> lon,lat,alt)
> 2. timestamp for each point
> 
> 
> So my amount could potentially grow to:
> 1 day, 1 user = 8640 coordinates
> 1 day, 10 000 users = 8 640 000 coordinates
> A year, 10 000 users = 3 153 600 000
> 
> How could i store such amount of data using postgis in the most 
> effective way?

"effective" with respect to what? It might be "effective" to dump them 
all into a table if you never need to access them. You have only define 
one half of your problem. Lets say we have them all stored in the most 
"effective" possible way, now what do you need to do with all these 
points? How do you need to access them? what is the most common query 
you will need to do? What is the most obscure query? what performance 
criteria do you have? etc

-Steve

> Breaking into several little qustions:
> 1. Should i store all points in 1 table or in several tables each for 
> exact time period(all inheriting 1 main table with no data)?
> 2. Should i create the GIST index and make VACUUM ANALYZE?
> 3. What other performance tips could be helpful? (if you point me to 
> information about clustering tables or computers with postgis, to best 
> practises of other people handling such huge amount of data - i 
> appreciate it).
> 
> Anticipating some questions:
> 1. Yes, I can afford a computing cloud or own computer cluster if 
> needed(But I dont know how to cluster the postegre)
> 2. Yes, there is some more data for each coordinate - user_id, timestamp.
> 3. Yes, there will be enough writting and quiering this database. The 
> queries will extract points with distance not more than X from a given 
> point for a given user_id and timestamp.
> 
> 
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users