Re: views, queries, and locks

Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
> On Wed, Apr 4, 2012 at 9:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Why aren't you using a standard partitioned table, cf
>> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

> Because I'm adding "scalar" (constant-value) columns to the view like this:
> SELECT * from tableA, DATE 'date string here' as date_column
> UNION ALL
> SELECT * from tableB, DATE 'date string here' as date_column

> for hundreds or even thousands of tables.

[ yawn... ] Premature micro-optimization is the root of all evil.
The actual advantage to what you are doing is not scanning irrelevant
partitions, which constraint exclusion handles perfectly fine. Not
storing the date column is unlikely to be saving anything meaningful.
(How wide are those table rows, anyway?)

More generally, partitioning "hundreds or even thousands" of ways is
costly overkill. Realistically, do you need to manage your data in
a way that allows you to drop less than perhaps 10% at once? I think
the usefulness threshold is probably a lot closer to 10% than 0.01%.

regards, tom lane