How to convert postgresql tabular data to a numpy array?

Question

I'm completely new to data science and I have a problem that I need help in resolving. I have time series data (with 87 million rows currently, though that will grow) with x, y coordinates, a timestamp (using date_trunc('hour') to enable better comparisons), and the value, stored in a Postgresql table. The analyses I need to perform on this data (find the value with the closest timestamp for one or more x,y coordinates, average the values in different ways, etc.) do not perform in Postgres at the speed I need (ideally sub 5 second response time). So I'm investigating using a multidimensional Numpy array. My problem is multiple: first, that I have no idea if this is a good idea, but I'm willing to try it and find out. More importantly, and the specific issue I need help with, is how to convert the data from the 2D tabular Postgres format to a 3D numpy array.

The data looks similar to this:

x_id    y_id    approx_time           value
 4       26     2022-10-14 08:00:00    0.01
 4       26     2022-09-03 08:00:00    0.02
...

Any suggestions on how to convert this to an array that will allow me to perform the analyses I need to perform, and secondarily, any suggestions on better paths forward if an array won't get me where I need to be? Thanks in advance

Answer 1

You could export the data from the database (for example to a csv file) and load that into memory using numpy (or pandas, which may be easier as it gives you extra functions and allows you to refer to the columns using the column names instead of indices). I am not sure how much memory you have available but 87 millions rows might be than what can fit into memory at once. In this case you could use chunking, or try to optimize the performance of your SQL query (which might be a good first step nevertheless depending on the exact logic you're looking for). Other python libraries that might be interesting to look at when working with large datasets are polars and dask.