[postgis-users] design problem

[Andy Colson]

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?

-Andy