1

I have an application that saves gps data every second and the user is able to query a variable timespan of data at once. So a select statement potentially needs to return many thousand rows. How is a small EC2 instance going to handle such requests? Would it help if I saved the coordinates of several hundred rows as an array in a single row like this:

instead of single rows:

timestamp, lon, lat
1,11,12
2,11,12
3,11,12
4,11,12
...

batches:

timestamp-start,timestamp-stop,lon,lat
1,300,[11,43,23,...],[11,43,23,...]
301,600,[11,43,23,...],[11,43,23,...]

or is the performance equal for many rows with little data and few rows with a lot of data.

Improve this question
1
  • You speak of GPS data but show integer values? You need to be more specific for a useful answer: Exact table definition? Postgres version? What's the bottleneck in your setup? Is what you describe the whole picture or are there other things going on with that table? Concurrent access? Why did you choose partitions of 300 rows? What's the exact row type you need returned? Arrays may or may not help. There may be other options, depending on missing information. Commented Dec 2, 2016 at 4:44

1 Answer 1

Reset to default
1

Regardless of EC2,

  • do what makes sense for the data
  • don't pre-optimize

Columns are not for similar-data that serve the same purpose. If you're writing entries use rows. If you're describing them use columns. Let aggregates do the voodoo of taking the data from the rows and rolling it up to something useful.

Now for your application, if I was doing GPS stuff, I would not be using separate columns for lat and long but instead I would use

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.