Skip site navigation (1) Skip section navigation (2)

many tables vs large tables

From: Kevin Neufeld <kneufeld(at)refractions(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: many tables vs large tables
Date: 2008-11-04 00:29:22
Message-ID: 490F9762.4030603@refractions.net (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-generalpgsql-performance
What is the general consensus around here ... to have many smaller tables, or have a few large tables?

I'm looking at a db model where a client has around 5500 spatial (PostGIS) tables, where the volume of each one varies 
greatly ... from a few hundred rows to over 250,000.

Speed is of the utmost importance. I'm investigating various options, like grouping the tables based on a common 
attribute or spatial type (POINT, LINESTRING, etc) into many multi-million tuple tables.

Or, table inheritance could be my friend here, in terms of performance. Ie. Using inheritance and constraint exclusion, 
the query planner could quickly isolate the tables of interest.  It's orders of magnitude faster to perform a sequential 
scan through a relatively small table than it is to do an index scan on a large, likely unclustered table.  The question 
is, can the underlying OS handle thousands of tables in a tablespace?  Would it overwhelm the parser to perform 
constraint exclusion on 50-100 tables?  Can it be done relatively quickly?

Clearly, serious testing is in order, but I just wanted to get a feel for things before I dive in.

Cheers,
Kevin

Responses

pgsql-performance by date

Next:From: Tory M BlueDate: 2008-11-04 01:30:51
Subject: Re: Index bloat, reindex weekly, suggestions etc?
Previous:From: David ReesDate: 2008-11-01 00:14:28
Subject: Re: Occasional Slow Commit

pgsql-admin by date

Next:From: Laszlo NagyDate: 2008-11-04 07:42:44
Subject: rebellious postgres process
Previous:From: Milen A. RadevDate: 2008-11-03 21:47:19
Subject: Re: connect to psql without passwd

pgsql-general by date

Next:From: Jason LongDate: 2008-11-04 00:41:46
Subject: JDBC and setting statement_timeout
Previous:From: Tom LaneDate: 2008-11-03 23:51:02
Subject: Re: INSERT with RETURNING clause inside SQL function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group