Best way to handle multiple table query linked to "main" table
Question: I am tracking visitor sessions and have a "master" table that contains the
session start, end and some visitor info.

    
    
    CREATE TABLE "sessions" (
        session_id BIGSERIAL PRIMARY KEY,
        session_start TIMESTAMP NOT NULL,
        session_end TIMESTAMP NOT NULL
        user_ip TEXT NOT NULL,
        user_agent TEXT NOT NULL
    )
    

For each session I have three metrics - pageviews, downloads and video plays.
Each table follows this formula

    
    
    CREATE TABLE "sessions_<x>" (
        session_id BIGINT REFERENCES sessions (session_id),
        data_1 TEXT NOT NULL,
        data_2 TEXT NOT NULL,
        ...
    )
    

The three tables are not identical, but the data itself is not important - the
only thing they share is the `session_id` reference to the `sessions` table.

Data is inserted so, in a single transaction, the relevant
pageview/download/video play record is added and the `session_start` /
`session_end` is updated.

For my code, I wish to extract each session with associated pageviews,
downloads and video plays. In an effort to minimize the number of queries I
figured I'd do the following:

  1. Do a query on the `sessions` table to get the identifying information + `session_id` for each session.
  2. For each of the three metric tables, do a `SELECT * FROM table WHERE session_id IN (<sessions>)`
  3. In code, "collect" the data from each result set from #2 and associate it with the base session information.

I am wondering if this is the best approach. I have some worries that for
large data sets this might be a bad idea (lots of in-memory data at a time),
but is there an alternative where I can in one query get session information +
associated metrics (for all three tables) easily? Or at least in a way where I
can iterate through it in my program one session at a time?

This is not a user-facing web service, so super-fast, sub-second replies are
not the goal, but performance is a factor. The service is being built in
python if it matters and I am using postgresql 9.2.


Answer: Try:

    
    
    SELECT * FROM session s
    LEFT OUTER JOIN session_<x> sx
    ON s.session_id = sx.session_id
    LEFT OUTER JOIN session_<y> sy
    ON s.session_id = sy.session_id;
    

