My current situation is this: I have 328 million lines of data in a flat
csv file. Not optimal. I want to be able to query this data (I'll explain in more detail).
Grep is getting tired. This data is static and will not change.
I'm new to PostgreSQL and am working with this data on my 2010 MacBook Pro, 2,4 GHz Intel Core 2 Duo, 4 GB 1067 MHz DDR3. Storage size is not really a problem, the data is around 65GB on a 1TB disk, but read / write speed is not great (it's not an SSD unfortunately). From what I remember from my CS studies a few years ago, indexing could help me improve query speeds, and I'm reading up on how best to do that.
Why am I using PostgreSQL? Peer pressure. Not really but let's go with that. I have PostgreSQL 9.3.4 running on OSX 10.9.2.
Now, what I'm trying to do. The data includes date and time info and spans about 10 months. I want, for example, to be able to:
- Find and summarize all activity on, say, all mondays in the data set for a specific user and/or for multiple users
- Look at differences between weeks in the summertime vs weeks in the wintertime
- Between months; and
- Hours of day, etc.
It's mostly timestamp related summaries that I'm interested in.
Each data record includes, besides the
userID and a
servID. In another file (which is stored in another table), each
servID is linked to a location as X and Y values. It's these locations that I'm interested in displaying on a map, so I would be exporting these to plot using
matlab, in aggregate for many users or focusing on specific users at a time.
All values are repeated, i.e. there are about 700.000 unique
userIDs and 1500 unique
servIDs. Timestamps are not unique either, the resolution is only seconds, so there are repeated timestamps.
So far I've created two tables, one with a small subset of test data and the X-Y location table:
create table test (userID varchar, junk1 varchar, date varchar, time varchar, junk2 varchar, junk3 varchar, servID varchar, junk4 varchar, junk5 varchar); copy test from '/Users/path/someuser.csv' delimiter ';' CSV; create table locations (col1 int, servID_DEC int, Col2 varchar, servID_HEX varchar, Locname varchar, Locname2 varchar, SERV_LOCY float8, SERV_LOCX float8, junk1 float8, junk2 float8); copy locations from '/Users/path/locations.csv' delimiter ',' header CSV;
I had to trim the data, one column at a time, which was annoying, but I repeated this for each value:
update test set userID=trim(userID);
Then I added a column for the actual timestamps I had to construct:
alter table test add column timestamp timestamptz; update test set timestamp=to_timestamp(date || '-' || time, 'DD.MM.YYYY-HH24:MI:SS');
Now my question (finally! Sorry about that): How do I create an index for the data in Postgres such that a query similar to this would be fast:
select userID, date, time, servID, timestamp, servID_HEX, SERV_LOCY, SERV_LOCX from test, locations where servID=servID_HEX and userID='<someusers>' and extract(dow from timestamp)=2;
I would also like to be able to quickly summarize the frequency of the
servIDs used by a
userID over a specified timeframe, i.e. he uses servID=1 1000 times, servID=2 600 times, so on (similar to the tabulate command in matlab)
(I renamed some of the variables and path names for reasons, I hope I didn't mess that up).