Recently i had to work with big database, were really big tables like 200gb were not partitioned, and query performance from those tables was getting really slow. So it was decided to partition them in order to speed up database performance. So how exactly do you do this, how do you partition the table? Well i won't get deep into specifics, but basically saying you create or presumably already have a master table from witch one you derive other tables, i'm talking about table inheritance here, then create a trigger on the master table witch redirects data inserts into the right derived table and thus partitioning is born. To be more clear here are some examples. Lets say that we already have a master table 'Users' that contains (id, user_id, login, password, desc) columns. So to derive-create table that inherits all master tables description we do this:

CREATE TABLE users_by_id.user_1 (
    PRIMARY KEY (id, user_id), CHECK ( user_id = '1' )
) INHERITS (public.users);
CREATE INDEX user_1_index ON users_by_id.user_1 (user_id);

So here we created a child table that inherits from its master table 'users', the CHECK is used by postgresql to determinate from witch table to SELECT, UPDATE... data and to unsure that data written to this table is limited to user who's id is 1 . Later we create an INDEX on 'user_id' to increase fields usage speed. Note that 'public' and 'users_by_id' are postgresql schema's, i use them to keep tables separate for better readability. Most likely now after carefully reading what i wrote and in your mind after applying it to an actual application you may have one or few questions. The questions i had were these: 1. What if i have many users and new users are created constantly is there a way to automate the process of creating child tables and indexes instead of doing it manually? 2. How do i direct database INSERT's into the right tables? The answer to these two main questions i had is quite simple. You create a TRIGGER function that on data INSERT dynamically checks if child table is created for user. If it is - you insert data into user table, if not - you create the child table for the user and after that you insert data into that table. For better understanding check code examples. Code examples here:

CREATE OR REPLACE FUNCTION users_data_insert_trigger()
  RETURNS "trigger" AS
$BODY$

DECLARE

data record;

BEGIN

EXECUTE  'SELECT tablename from pg_tables where tablename=''user_'||NEW.user_id||''' AND schemaname=''users_by_id''' INTO data;
IF data.tablename is null THEN

    EXECUTE  'CREATE TABLE users_by_id.coordinates_'||NEW.user_id||' (
    PRIMARY KEY (id, user_id), CHECK ( user_id = '''||NEW.user_id||''' )
    ) INHERITS (public.users);
    CREATE INDEX user_'||NEW.user_id||'_index ON users_by_id.user_'||NEW.user_id||' (user_id);';

    EXECUTE  'INSERT INTO users_by_id.user_'||NEW.user_id||' VALUES('||quote_literal(NEW.id)||','||quote_literal(NEW.user_id)||', '||quote_literal(NEW.login)||', '||quote_literal(NEW.password)||', '||quote_literal(NEW.desc)||' );';

ELSEIF data.tablename is not null THEN
    EXECUTE  'INSERT INTO users_by_id.user_'||NEW.user_id||' VALUES('||quote_literal(NEW.id)||','||quote_literal(NEW.user_id)||', '||quote_literal(NEW.login)||', '||quote_literal(NEW.password)||', '||quote_literal(NEW.desc)||' );';
ELSE

    RAISE NOTICE 'Insert trigger ERROR!';
    RAISE EXCEPTION 'Insert trigger ERROR!';
END IF;

RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Well that wraps it up, i think the code is self explanatory and there is no need to add something.