[postgis-users] design problem

[Stephen Woodbridge]

On 3/1/2013 4:44 PM, Andy Colson wrote:
> On 3/1/2013 3:11 PM, Denise Janson wrote:
>> Hi,
>>
>> I have an application that is going to receive lots of georeferenced
>> files
>> every day. Each file has information of several points. Probably in few
>> years my application will have a Tera of points information stored.
>>
>> I think I can do this design  in two ways:
>>
>> 1. Two tables, one of “uploaded_files”, and another of “points” (one
>> uploadedFile to N points). And I'll have to partition the points table,
>> maybe by month …
>> 2. Or I can create one table per file, having thousands of tables in few
>> years.
>>
>> Which case is better for my application?  Is there any better way to do
>> this?
>>
>
> If performance is a concern, and the file's are of any meaningful size,
> you might consider leaving them on the filesystem and have the table
> point to it (full path name sort of thing).
>
> Storing the file in PG is possible, and its nice because everything is
> kept together, but if you have to get to and read the files fast, then
> leave them on the filesystem.
>
> The lots of tables approach is problematic if you ever want to write
> queries that look back in time.  Its much harder to say, give me every
> record from the beginning of time at this point.
>
> With a good index, PG wont have a problem with a single table containing
> billions of rows.  Just try to avoid doing bulk operations (like update
> and delete) on the entire table.
>
>
>  > uploadedFile to N points). And I'll have to partition the points table,
>
>
> Why will you have to partition it?

you might want to consider using inherited tables. This you can have 
something like:

master_table
    - table1 inherits from master_table
    - table2 inherits from master_table
    - etc

This has the advantange that you can set constraints on the sub-tables 
like date_from, data_to or other constraints that you might need in your 
queries.

Then when you make you query on the master_table if will eliminate all 
the tables that fail the constraint test and this is very fast. Also if 
you ever need to make adhoc queries on the master_table you still have a 
structure that supports that.

There might ne some issues with inheriting 10 of 1000s of tables.

The real answer to your design can only be answered by understanding 
what your queries are going to look like with respect to all this data.

-Steve