StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<ol> <li><p>Record only changes of device positions - most of the time any device will not move - a car will be parked, a person will sit or sleep, a phone will be on unmoving person or charged etc. - this would make you an order of magnitude less data to store.</p></li> <li><p>You'll be generating at most about 1TB a year (even when not implementing point 1), which is not a very big amount of data. This means about 30MB/s of data, which single SATA drive can handle.</p></li> <li><p>Even a simple unpartitioned Postgres database on not too big hardware should manage to handle this. The only problem could be when you'll need to query or backup - this can be resolved by using a <a href="http://www.postgresql.org/docs/9.0/static/hot-standby.html" rel="nofollow noreferrer">Hot Standby</a> mirror using <a href="http://www.postgresql.org/docs/9.0/static/warm-standby.html#STREAMING-REPLICATION" rel="nofollow noreferrer">Streaming Replication</a> - this is a new feature in soon to be released PostgreSQL 9.0. Just query against / backup a mirror - if it is busy it will temporarily and automatically queue changes, and catch up later.</p></li> <li><p>When you really need to partition do it for example on device_id modulo 256 instead of time. This way you'd have writes spread out on every partition. If you partition on time just one partition will be very busy on any moment and others will be idle. <a href="http://www.postgresql.org/docs/current/static/ddl-partitioning.html" rel="nofollow noreferrer">Postgres supports partitioning</a> this way very well. You can then also spread load to several storage devices using <a href="http://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html" rel="nofollow noreferrer">tablespaces</a>, which are also well supported in Postgres.</p></li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload