Re: [PERFORM] partitioning materialized views

[Claudio Freire]

On Fri, Jul 7, 2017 at 10:12 AM, Shaun Thomas
 wrote:
>> I don't think the downstream dependencies will let that work without
>> rebuilding them as well.   The drop fails (without a cascade), and the other
>> views and matviews that are built off of this all simply point to x_old.
>
> Wow, ouch. Yeah, I'd neglected to consider dependent objects. Your
> only "out" at this point is to either add or utilize a "modified_date"
> column of some kind, so you can maintain a different MV with some
> recent window of data, and regularly merge that into a physical local
> copy (not an MV) sort of like a running ETL. Though that won't help
> with deletes, unfortunately.

You have another out: rebuild the dependent views before the drop.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning materialized views

[Shaun Thomas]

> I don't think the downstream dependencies will let that work without
> rebuilding them as well.   The drop fails (without a cascade), and the other
> views and matviews that are built off of this all simply point to x_old.

Wow, ouch. Yeah, I'd neglected to consider dependent objects. Your
only "out" at this point is to either add or utilize a "modified_date"
column of some kind, so you can maintain a different MV with some
recent window of data, and regularly merge that into a physical local
copy (not an MV) sort of like a running ETL. Though that won't help
with deletes, unfortunately.

-- 
Shaun M Thomas - 2ndQuadrant
PostgreSQL Training, Services and Support
shaun.tho...@2ndquadrant.com | www.2ndQuadrant.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning materialized views

[Rick Otten]

>
>
> If you _can't_ do
>> that due to cloud restrictions, you'd actually be better off doing an
>> atomic swap.
>>
>> CREATE MATERIALIZED VIEW y AS ...;
>>
>> BEGIN;
>> ALTER MATERIALIZED VIEW x RENAME TO x_old;
>> ALTER MATERIALIZED VIEW y RENAME TO x;
>> DROP MATERIALIZED VIEW x_old;
>> COMMIT;
>>
>> This is an interesting idea.  Thanks!  I'll ponder that one.
>
>
I don't think the downstream dependencies will let that work without
rebuilding them as well.   The drop fails (without a cascade), and the
other views and matviews that are built off of this all simply point to
x_old.

Re: [PERFORM] partitioning materialized views

[Rick Otten]

On Thu, Jul 6, 2017 at 11:25 AM, Shaun Thomas 
wrote:

> > I'm curious if I'm overlooking other possible architectures or tools
> that might make this simpler to manage.
>
> One of the issues with materialized views is that they are based on
> views... For a concurrent update, it essentially performs a looped
> merge, which can be pretty ugly. That's the price you pay to be
> non-blocking. For this particular setup, I'd actually recommend using
> something like pglogical to just maintain a live copy of the remote
> table or wait for Postgres 10's logical replication.


Unfortunately the foreign database is Hadoop.  (As A Service)



> If you _can't_ do
> that due to cloud restrictions, you'd actually be better off doing an
> atomic swap.
>
> CREATE MATERIALIZED VIEW y AS ...;
>
> BEGIN;
> ALTER MATERIALIZED VIEW x RENAME TO x_old;
> ALTER MATERIALIZED VIEW y RENAME TO x;
> DROP MATERIALIZED VIEW x_old;
> COMMIT;
>
> This is an interesting idea.  Thanks!  I'll ponder that one.



> You could still follow your partitioned plan if you don't want to
> update all of the data at once. Let's face it, 3-4 hours is still a
> ton of data transfer and calculation.
>
>
yup.

Re: [PERFORM] partitioning materialized views

[Shaun Thomas]

> I'm curious if I'm overlooking other possible architectures or tools that 
> might make this simpler to manage.

One of the issues with materialized views is that they are based on
views... For a concurrent update, it essentially performs a looped
merge, which can be pretty ugly. That's the price you pay to be
non-blocking. For this particular setup, I'd actually recommend using
something like pglogical to just maintain a live copy of the remote
table or wait for Postgres 10's logical replication. If you _can't_ do
that due to cloud restrictions, you'd actually be better off doing an
atomic swap.

CREATE MATERIALIZED VIEW y AS ...;

BEGIN;
ALTER MATERIALIZED VIEW x RENAME TO x_old;
ALTER MATERIALIZED VIEW y RENAME TO x;
DROP MATERIALIZED VIEW x_old;
COMMIT;

You could still follow your partitioned plan if you don't want to
update all of the data at once. Let's face it, 3-4 hours is still a
ton of data transfer and calculation.

-- 
Shaun M Thomas - 2ndQuadrant
PostgreSQL Training, Services and Support
shaun.tho...@2ndquadrant.com | www.2ndQuadrant.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning and performance

[Jim Nasby]

On 5/28/15 9:31 AM, Ravi Krishna wrote:

explain select count(*) from tstesting.account where account_row_inst = 101 ;
Aggregate (cost=8.16..8.17 rows=1 width=0)
-> Index Only Scan using account_pkey on account (cost=0.14..8.16
rows=1 width=0)
Index Cond: (account_row_inst = 101)


EXPLAIN only shows what the planner thinks a query will cost. For any 
real testing, you need EXPLAIN ANALYZE.


Also, understand that partitioning isn't a magic bullet. It can make 
some operations drastically faster, but it's not going to help every 
scenario, and will actually slow some other operations down.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning and performance

[Ravi Krishna]

On Thu, May 28, 2015 at 10:31 AM, Ravi Krishna  wrote:
> I am testing partitioning of a large table. I am doing a range

Sorry I forgot to clarify. I am using INHERITS for partioning with
check constraing built for range partitioning.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning performance question

[Robert Klemme]

On Sat, Jun 9, 2012 at 7:58 PM, Kevin Kempter
 wrote:
> Hi All;
>
> We have a client that has a table where large blobs (bytea) are stored. the
> table has a key column that is numbers (like 112362) but unfortunately it's
> a varchar column so the blobs are accessed via queries like:
>
> select * from bigtable where keycol = '217765'
>
> The primary reason we want to partition the table is for maintenance, the
> table is well over 1.2 Terabytes in size and they have never successfully
> vacuumed it. However I don't want to make performance even worse. The table
> does have a serial key, I'm thinking the best options will be to partition
> by range on the serial key, or maybe via the keycol character column via
> using an in statement on the check constraints, thus allowing the planner to
> actually leverage the above sql.  I suspect doing a typecast to integer in
> the check constraints will prove to be a bad idea if the keycol column
> remains a varchar.
>
> Thoughts?
>
> Here's the table:
>
>
>                                        Table "problemchild"
>  Column   |           Type           |                             Modifiers
> ---+--+
>  keycol                | character varying        |
>  blob_data           | bytea                    |
>  removed_date    | timestamp with time zone |
>  alt_key | bigint                   | not null default
> nextval('problemchild_alt_key_seq'::regclass)
> Indexes:
>    "pc_pkey" PRIMARY KEY, btree (alt_key)
>    "key2" btree (keycol)

I find it odd that you have a column "keycol" which is not the PK and
your PK column is named "alt_key".  Is "keycol" always the character
representation of "alt_key"?  Are they unrelated?

It would also help to know how the data in this table changes.  Do you
only ever add data?  Is some data removed from time to time (maybe
based on the "removed_date")?

If the table grows continually then range partitioning sounds good.
However, I think you should really make "keycol" a number type because
otherwise range partitioning will be a pain (you would need to include
the length of the string in the criterion if you want your varchar
ranges to mimic number ranges).

However, if you are deleting from time to time and hence the table
does not grow in the long run then hash partitioning might be a better
idea because then you do not need to create new partitions all the
time.  Example on alt_key

create table problemchild (
  keycol varchar(100),
  blob_data bytea,
  removed_date timestamp with time zone,
  alt_key bigint primary key
);
create table problemchild_00 (
  check ( alt_key % 16 = 0 )
) inherits (problemchild);
create table problemchild_01 (
  check ( alt_key % 16 = 1 )
) inherits (problemchild);
create table problemchild_02 (
  check ( alt_key % 16 = 2 )
) inherits (problemchild);
...

Kind regards

robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning / Strange optimizer behaviour

[Marc Schablewski]

Thanks for pointing me to that article. I totally forgot that the postgres wiki 
existed.

Updating is not an option at the moment, but we'll probably do so in the 
future. Until then I can
live with the workaround.

Kind regards,
Marc


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning / Strange optimizer behaviour

[Tomas Vondra]

On 5 Březen 2012, 16:11, Marc Schablewski wrote:
> We have an optimizer problem regarding partitioned tables on 8.4.11.
...
> gdw=> explain select min( emg_id ) from edifactmsgpart;
>   QUERY PLAN
> --
>  Aggregate  (cost=23521692.03..23521692.04 rows=1 width=8)
>->  Append  (cost=0.00..20944139.42 rows=1031021042 width=8)
>  ->  Seq Scan on edifactmsgpart  (cost=0.00..13.70 rows=370
> width=8)
>  ->  Seq Scan on edifactmsgpart_pact edifactmsgpart
> (cost=0.00..20944125.72 rows=1031020672
> width=8)
>
> I would expect this to run half an hour or so, completely overloading the
> server...
>
> Any Ideas?

This is a well known "feature" of pre-9.1 releases - it simply does not
handle min/max on partitioned tables well. There's even an example of a
workaround on the wiki:
https://wiki.postgresql.org/wiki/Efficient_min/max_over_partitioned_table

Another option is to upgrade to 9.1 which handles this fine.

Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning by status?

[alexandre - aldeia digital]

Em 13-01-2012 17:05, Josh Berkus escreveu:

On 1/13/12 2:44 AM, alexandre - aldeia digital wrote:


Also, (2) only really works if you're going to obsolesce (remove)
archive records after a certain period of time.  Otherwise the
sub-partitioning hurts performance.



Is there any moves to include the "easy" table partitioning in the 9.2
version ?


Nobody has been submitting patches.



I'm sorry hear this. Table partitioning is a very good helper in a large 
number of performance issues. If there was a bounty to help anyone to 
make this, I would be a happy contributor. :)


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning by status?

[Josh Berkus]

On 1/13/12 2:44 AM, alexandre - aldeia digital wrote:
>>
>> Also, (2) only really works if you're going to obsolesce (remove)
>> archive records after a certain period of time.  Otherwise the
>> sub-partitioning hurts performance.
>>
> 
> Is there any moves to include the "easy" table partitioning in the 9.2
> version ?

Nobody has been submitting patches.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning by status?

[alexandre - aldeia digital]

Also, (2) only really works if you're going to obsolesce (remove)
archive records after a certain period of time.  Otherwise the
sub-partitioning hurts performance.



Is there any moves to include the "easy" table partitioning in the 9.2 
version ?



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning by status?

[Josh Berkus]

Mike,

> Is it practical to partition on the status column and, eg, use triggers to
> move a row between the two partitions when status is updated?  Any
> surprises to watch for, given the status column is actually NULL for active
> data and contains a value when archived?

When I've done this before, I've had a setup like the following:

1. One "active" partition

2. Multiple "archive" partitions, also partitioned by time (month or year)

3. stored procedure for archiving a record or records.

I'd recommend against triggers because they'll be extremely inefficient
if you need to archive a large number of rows at once.

Also, (2) only really works if you're going to obsolesce (remove)
archive records after a certain period of time.  Otherwise the
sub-partitioning hurts performance.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning by status?

[Andreas Kretschmer]

Mike Blackwell  wrote:

> We have a set of large tables.  One of the columns is a status indicator
> (active / archived).  The queries against these tables almost always include
> the status, so partitioning against that seems to makes sense from a logical
> standpoint, especially given most of the data is "archived" and most of the
> processes want active records.
> 
> Is it practical to partition on the status column and, eg, use triggers to 
> move
> a row between the two partitions when status is updated?  Any surprises to
> watch for, given the status column is actually NULL for active data and
> contains a value when archived?

If i where you, i would try a partial index where status is null. But
yes, partitioning is an other option, depends on your workload.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Igor Neyman]

 

> -Original Message-
> From: Ben [mailto:midfi...@gmail.com] 
> Sent: Friday, October 29, 2010 12:16 PM
> To: Igor Neyman
> Cc: pgsql-performance@postgresql.org
> Subject: Re: partitioning question 1
> 
> On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote:
> 
> >> is my intuition completely off on this?
> >> 
> >> best regards, ben
> >> 
> > 
> > If your SELECT retrieves substantial amount of records, table scan 
> > could be more efficient than index access.
> > 
> > Now, if while retrieving large amount of records "WHERE clause" of 
> > this SELECT still satisfies constraints on some partition(s), then 
> > obviously one (or few) partition scans will be more efficient than 
> > full table scan of non-partitioned table.
> > 
> > So, yes partitioning provides performance improvements, not only 
> > maintenance convenience.
> 
> my impression was that a *clustered* index would give a lot 
> of the same I/O benefits, in a more flexible way.  if you're 
> clustered on the column in question, then an index scan for a 
> range is much like a sequential scan over a partition (as far 
> as i understand.)
> 
> b
> 

Even with clustered index you still read index+table, which is more
expensive than just table scan (in situation I described above).
PG clustered index is not the same as SQL Server clustered index (which
includes actual table pages on the leaf level).

Igor Neyman

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote:

>> is my intuition completely off on this?
>> 
>> best regards, ben
>> 
> 
> If your SELECT retrieves substantial amount of records, table scan could
> be more efficient than index access.
> 
> Now, if while retrieving large amount of records "WHERE clause" of this
> SELECT still satisfies constraints on some partition(s), then obviously
> one (or few) partition scans will be more efficient than full table scan
> of non-partitioned table.
> 
> So, yes partitioning provides performance improvements, not only
> maintenance convenience.

my impression was that a *clustered* index would give a lot of the same I/O 
benefits, in a more flexible way.  if you're clustered on the column in 
question, then an index scan for a range is much like a sequential scan over a 
partition (as far as i understand.)

b
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Igor Neyman]

> -Original Message-
> From: Ben [mailto:midfi...@gmail.com] 
> Sent: Thursday, October 28, 2010 12:37 PM
> To: pgsql-performance@postgresql.org
> Subject: partitioning question 1
> 
> hello --
> 
> my last email was apparently too long to respond to so i'll 
> split it up into shorter pieces.  my first question :
> 
> my understanding of how range partitioning and constraint 
> exclusion works leads me to believe that it does not buy any 
> query performance that a clustered index doesn't already give 
> you -- the advantages are all in maintainability.  an index 
> is able to eliminate pages just as well as constraint 
> exclusion is able to eliminate table partitions.  the I/O 
> advantages of having queries target small subtables are the 
> same as the I/O advantages of clustering the index : result 
> pages in a small range are very close to each other on disk.
> 
> finally, since constraint exclusion isn't as flexible as 
> indexing (i've seen old mailing list posts that say that 
> constraint exclusion only works with static constants in 
> where clauses, and only works with simple operators like >, < 
> which basically forces btree indexes when i want to use gist) 
> it is indeed likely that partitioning can be slower than one 
> big table with a clustered index.
> 
> is my intuition completely off on this?
> 
> best regards, ben
> 

If your SELECT retrieves substantial amount of records, table scan could
be more efficient than index access.

Now, if while retrieving large amount of records "WHERE clause" of this
SELECT still satisfies constraints on some partition(s), then obviously
one (or few) partition scans will be more efficient than full table scan
of non-partitioned table.

So, yes partitioning provides performance improvements, not only
maintenance convenience.

Regards,
Igor Neyman

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

whoops, didn't see the i=9 (linebreak! linebreak!)

nonetheless that is a static constant constraint on the column i, and i was 
asking if constraint exclusions would work for dynamic constraints (like those 
derived from a table joined against.)  so for example the bar table has only 
0-9 in its histogram for i, but constraint exclusion can't use that to 
eliminate tables foo_2 and foo_3.  this is precisely the kind of information an 
index can use via join selectivity.

i am not going to the pg conference, sorry to say.

b


On Oct 28, 2010, at 1:48 PM, Joshua D. Drake wrote:

> On Thu, 2010-10-28 at 12:59 -0700, Ben wrote:
>> On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
>>> 
>>> My tests show you are incorrect:
>>> 
>>> 
>>> part_test=# explain analyze select * from foo join bar using (i) where
>>> i=9;
>>>   QUERY
>>> PLAN
>>> --
>>> Nested Loop  (cost=34.26..106.76 rows=200 width=20) (actual
>>> time=0.004..0.004 rows=0 loops=1)
>>>  ->  Append  (cost=0.00..68.50 rows=20 width=12) (actual
>>> time=0.004..0.004 rows=0 loops=1)
>>>->  Seq Scan on foo  (cost=0.00..34.25 rows=10 width=12)
>>> (actual time=0.001..0.001 rows=0 loops=1)
>>>  Filter: (i = 9)
>>>->  Seq Scan on foo_1 foo  (cost=0.00..34.25 rows=10 width=12)
>>> (actual time=0.000..0.000 rows=0 loops=1)
>>>  Filter: (i = 9)
>>>  ->  Materialize  (cost=34.26..34.36 rows=10 width=12) (never
>>> executed)
>>>->  Seq Scan on bar  (cost=0.00..34.25 rows=10 width=12) (never
>>> executed)
>>>  Filter: (i = 9)
>>> Total runtime: 0.032 ms
>>> (10 rows)
>> 
>> strange.  my tests don't agree with your tests :
> 
> Do you have constraint_exclusion turned on? You should verify with show
> constraint_exclusion (I saw what you wrote below).
> 
> JD
> 
> P.S. Blatant plug, you coming to http://www.postgresqlconference.org ?
> 
> 
> -- 
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
> 


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Joshua D. Drake]

On Thu, 2010-10-28 at 12:59 -0700, Ben wrote:
> On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
> > 
> > My tests show you are incorrect:
> > 
> > 
> > part_test=# explain analyze select * from foo join bar using (i) where
> > i=9;
> >QUERY
> > PLAN
> > --
> > Nested Loop  (cost=34.26..106.76 rows=200 width=20) (actual
> > time=0.004..0.004 rows=0 loops=1)
> >   ->  Append  (cost=0.00..68.50 rows=20 width=12) (actual
> > time=0.004..0.004 rows=0 loops=1)
> > ->  Seq Scan on foo  (cost=0.00..34.25 rows=10 width=12)
> > (actual time=0.001..0.001 rows=0 loops=1)
> >   Filter: (i = 9)
> > ->  Seq Scan on foo_1 foo  (cost=0.00..34.25 rows=10 width=12)
> > (actual time=0.000..0.000 rows=0 loops=1)
> >   Filter: (i = 9)
> >   ->  Materialize  (cost=34.26..34.36 rows=10 width=12) (never
> > executed)
> > ->  Seq Scan on bar  (cost=0.00..34.25 rows=10 width=12) (never
> > executed)
> >   Filter: (i = 9)
> > Total runtime: 0.032 ms
> > (10 rows)
> 
> strange.  my tests don't agree with your tests :

Do you have constraint_exclusion turned on? You should verify with show
constraint_exclusion (I saw what you wrote below).

JD

P.S. Blatant plug, you coming to http://www.postgresqlconference.org ?


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Reid Thompson]

On Thu, 2010-10-28 at 12:59 -0700, Ben wrote:
> explain analyze select * from foo join bar using (i);
vs
explain analyze select * from foo join bar using (i) where i=9;

Re: [PERFORM] partitioning question 1

[Ben]

On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
> 
> My tests show you are incorrect:
> 
> 
> part_test=# explain analyze select * from foo join bar using (i) where
> i=9;
>QUERY
> PLAN
> --
> Nested Loop  (cost=34.26..106.76 rows=200 width=20) (actual
> time=0.004..0.004 rows=0 loops=1)
>   ->  Append  (cost=0.00..68.50 rows=20 width=12) (actual
> time=0.004..0.004 rows=0 loops=1)
> ->  Seq Scan on foo  (cost=0.00..34.25 rows=10 width=12)
> (actual time=0.001..0.001 rows=0 loops=1)
>   Filter: (i = 9)
> ->  Seq Scan on foo_1 foo  (cost=0.00..34.25 rows=10 width=12)
> (actual time=0.000..0.000 rows=0 loops=1)
>   Filter: (i = 9)
>   ->  Materialize  (cost=34.26..34.36 rows=10 width=12) (never
> executed)
> ->  Seq Scan on bar  (cost=0.00..34.25 rows=10 width=12) (never
> executed)
>   Filter: (i = 9)
> Total runtime: 0.032 ms
> (10 rows)

strange.  my tests don't agree with your tests :

create table foo (i integer not null, j float not null);
create table foo_1 ( check (i >= 0 and i < 10) ) inherits (foo);
create table foo_2 ( check (i >= 10 and i < 20) ) inherits (foo);
create table foo_3 ( check (i >= 20 and i < 30) ) inherits (foo);
create index foo_1_idx on foo_1 (i);
create index foo_2_idx on foo_2 (i);
create index foo_3_idx on foo_3 (i);
insert into foo_1 select generate_series, generate_series from 
generate_series(0,9);
insert into foo_2 select generate_series, generate_series from 
generate_series(10,19);
insert into foo_3 select generate_series, generate_series from 
generate_series(20,29);
create table bar (i integer not null, k float not null);
create index bar_idx on bar (i);
insert into bar select generate_series, -generate_series from 
generate_series(0,9);
vacuum analyze;
explain analyze select * from foo join bar using (i);

QUERY PLAN  
 
--
 Hash Join  (cost=1.23..42.29 rows=98 width=20) (actual time=0.056..0.118 
rows=10 loops=1)
   Hash Cond: (public.foo.i = bar.i)
   ->  Append  (cost=0.00..32.70 rows=1970 width=12) (actual time=0.008..0.043 
rows=30 loops=1)
 ->  Seq Scan on foo  (cost=0.00..29.40 rows=1940 width=12) (actual 
time=0.001..0.001 rows=0 loops=1)
 ->  Seq Scan on foo_1 foo  (cost=0.00..1.10 rows=10 width=12) (actual 
time=0.005..0.008 rows=10 loops=1)
 ->  Seq Scan on foo_2 foo  (cost=0.00..1.10 rows=10 width=12) (actual 
time=0.003..0.006 rows=10 loops=1)
 ->  Seq Scan on foo_3 foo  (cost=0.00..1.10 rows=10 width=12) (actual 
time=0.003..0.006 rows=10 loops=1)
   ->  Hash  (cost=1.10..1.10 rows=10 width=12) (actual time=0.025..0.025 
rows=10 loops=1)
 ->  Seq Scan on bar  (cost=0.00..1.10 rows=10 width=12) (actual 
time=0.005..0.013 rows=10 loops=1)
 Total runtime: 0.205 ms
(10 rows)


i'm running pg 8.4.3 with constraint_exclusion=on (just to be safe.)

best, b
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Joshua D. Drake]

On Thu, 2010-10-28 at 12:25 -0700, Ben wrote:

> i think we are talking about two different things here: the constraints on 
> the table, and the where-clause constraints in a query which may or may not 
> trigger constraint exclusion.  i understand that table constraints have to be 
> constants -- it doesn't make much sense otherwise.  what i am wondering about 
> is, will constraint exclusion be triggered for queries where the column that 
> is being partitioned on is being constrained things that are not static 
> constants, for instance, in a join.  (i'm pretty sure the answer is no, 
> because i think constraint exclusion happens before real query planning.)  a 
> concrete example :
> 
> create table foo (i integer not null, j float not null);
> create table foo_1 (check ( i >= 0 and i < 10) ) inherits (foo);
> create table foo_2 (check ( i >= 10 and i < 20) ) inherits (foo);
> create table foo_3 (check ( i >= 20 and i < 30) ) inherits (foo);
> etc..
> 
> create table bar (i integer not null, k float not null);
> 
> my understanding is that a query like
> 
> select * from foo, bar using (i);
> 
> can't use constraint exclusion, even if the histogram of i-values on table 
> bar says they only live in the range 0-9, and so the query will touch all of 
> the tables.  i think this is not favorable compared to a single foo table 
> with a well-maintained btree index on i.
> 

My tests show you are incorrect:


part_test=# explain analyze select * from foo join bar using (i) where
i=9;
QUERY
PLAN
--
 Nested Loop  (cost=34.26..106.76 rows=200 width=20) (actual
time=0.004..0.004 rows=0 loops=1)
   ->  Append  (cost=0.00..68.50 rows=20 width=12) (actual
time=0.004..0.004 rows=0 loops=1)
 ->  Seq Scan on foo  (cost=0.00..34.25 rows=10 width=12)
(actual time=0.001..0.001 rows=0 loops=1)
   Filter: (i = 9)
 ->  Seq Scan on foo_1 foo  (cost=0.00..34.25 rows=10 width=12)
(actual time=0.000..0.000 rows=0 loops=1)
   Filter: (i = 9)
   ->  Materialize  (cost=34.26..34.36 rows=10 width=12) (never
executed)
 ->  Seq Scan on bar  (cost=0.00..34.25 rows=10 width=12) (never
executed)
   Filter: (i = 9)
 Total runtime: 0.032 ms
(10 rows)



-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

On Oct 28, 2010, at 11:50 AM, Joshua D. Drake wrote:
>>> Yes the constraints have to be static. Not sure about the operator
>>> question honestly.
>> 
>> this seems to severely restrict their usefulness -- our queries are data 
>> warehouse analytical -type  queries, so the constraints are usually 
>> data-driven (come from joining against other tables.)
> 
> Well it does and it doesn't. Keep in mind that the constraint can be:
> 
> date >= '2010-10-01" and date <= '2010-10-31'
> 
> What it can't be is something that contains date_part() or extract() (as
> an example) 

i think we are talking about two different things here: the constraints on the 
table, and the where-clause constraints in a query which may or may not trigger 
constraint exclusion.  i understand that table constraints have to be constants 
-- it doesn't make much sense otherwise.  what i am wondering about is, will 
constraint exclusion be triggered for queries where the column that is being 
partitioned on is being constrained things that are not static constants, for 
instance, in a join.  (i'm pretty sure the answer is no, because i think 
constraint exclusion happens before real query planning.)  a concrete example :

create table foo (i integer not null, j float not null);
create table foo_1 (check ( i >= 0 and i < 10) ) inherits (foo);
create table foo_2 (check ( i >= 10 and i < 20) ) inherits (foo);
create table foo_3 (check ( i >= 20 and i < 30) ) inherits (foo);
etc..

create table bar (i integer not null, k float not null);

my understanding is that a query like

select * from foo, bar using (i);

can't use constraint exclusion, even if the histogram of i-values on table bar 
says they only live in the range 0-9, and so the query will touch all of the 
tables.  i think this is not favorable compared to a single foo table with a 
well-maintained btree index on i.

 is my intuition completely off on this?
>>> 
>>> You may actually want to look into expression indexes, not clustered
>>> ones.
> 
> Take a look at the docs:
> 
> http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html
> 
> It "could" be considered partitioning without breaking up the table,
> just the indexes.

do you mean partial indexes?  i have to confess to not understanding how this 
is relevant -- how could partial indexes give any advantage over a full 
clustered index?

b 
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Joshua D. Drake]

On Thu, 2010-10-28 at 11:44 -0700, Ben wrote:

> > Yes the constraints have to be static. Not sure about the operator
> > question honestly.
> 
> this seems to severely restrict their usefulness -- our queries are data 
> warehouse analytical -type  queries, so the constraints are usually 
> data-driven (come from joining against other tables.)

Well it does and it doesn't. Keep in mind that the constraint can be:

date >= '2010-10-01" and date <= '2010-10-31'

What it can't be is something that contains date_part() or extract() (as
an example) 

> 
> >> is my intuition completely off on this?
> > 
> > You may actually want to look into expression indexes, not clustered
> > ones.

Take a look at the docs:

http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html

It "could" be considered partitioning without breaking up the table,
just the indexes.

Sincerely,

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

thanks for the prompt response.  some comments / questions below :

On Oct 28, 2010, at 10:31 AM, Joshua D. Drake wrote:
>> ...constraint exclusion is able to eliminate table partitions.  the I/O 
>> advantages of having queries target small subtables are the same as the I/O 
>> advantages of clustering the index : result pages in a small range are very 
>> close to each other on disk.
> 
> Not entirely true. One a clustered index will not stay clustered if you
> are still updating data that is in the partition. You shouldn't
> underestimate the benefit of smaller relations in terms of maintenance
> either.

in my situation, the update come in-order (it is timeseries data and the 
clustered index is on time.)  so the table should remain relatively clustered.  
updates also happen relatively infrequently (once a day in one batch.)  so it 
appears that we will continue to get the I/O benefits described above.

are there any other benefits which partitioning provides for query performance 
(as opposed to update performance) besides the ones which i have mentioned?


> Yes the constraints have to be static. Not sure about the operator
> question honestly.

this seems to severely restrict their usefulness -- our queries are data 
warehouse analytical -type  queries, so the constraints are usually data-driven 
(come from joining against other tables.)

>> is my intuition completely off on this?
> 
> You may actually want to look into expression indexes, not clustered
> ones.


what would expression indexes give me?

thanks and best regards, ben


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Joshua D. Drake]

On Thu, 2010-10-28 at 09:36 -0700, Ben wrote:
> hello --
> 
> my last email was apparently too long to respond to so i'll split it up into 
> shorter pieces.  my first question :
> 
> my understanding of how range partitioning and constraint exclusion works 
> leads me to believe that it does not buy any query performance that a 
> clustered index doesn't already give you -- the advantages are all in 
> maintainability.  an index is able to eliminate pages just as well as 
> constraint exclusion is able to eliminate table partitions.  the I/O 
> advantages of having queries target small subtables are the same as the I/O 
> advantages of clustering the index : result pages in a small range are very 
> close to each other on disk.

Not entirely true. One a clustered index will not stay clustered if you
are still updating data that is in the partition. You shouldn't
underestimate the benefit of smaller relations in terms of maintenance
either.

> 
> finally, since constraint exclusion isn't as flexible as indexing (i've seen 
> old mailing list posts that say that constraint exclusion only works with 
> static constants in where clauses, and only works with simple operators like 
> >, < which basically forces btree indexes when i want to use gist) it is 
> indeed likely that partitioning can be slower than one big table with a 
> clustered index.

Yes the constraints have to be static. Not sure about the operator
question honestly.


> is my intuition completely off on this?

You may actually want to look into expression indexes, not clustered
ones.

Sincerely,

Joshua D. Drake

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning max() sql not using index

[Kevin Kempter]

On Wednesday 09 September 2009 07:56:53 Kenneth Cox wrote:
> In case you aren't comfortable running unreleased planner patches from
> pgsql-hackers, a workaround was discussed on this list recently:
>
> http://archives.postgresql.org/pgsql-performance/2009-09/msg00036.php
>
> On Wed, 09 Sep 2009 06:05:22 -0400, Heikki Linnakangas
>
>  wrote:
> > Kevin Kempter wrote:
> >> Hi all I have a large table (>2billion rows) that's partitioned by date
> >> based
> >> on an epoch int value.  We're running a select max(id) where id is the
> >> PK. I
> >> have a PK index on each of the partitions, no indexes at all on the base
> >> table.
> >>
> >> If I hit a partition table directly I get an index scan as expected:
> >
> > The planner isn't smart enough to create the plan you're expecting.
> > There was discussion and even a patch posted recently about that:
> >
> > http://archives.postgresql.org/pgsql-hackers/2009-07/msg01115.php
> >
> > It seems the thread petered out, but the concept seems sane.

Excellent! thanks this is quite helpful

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning max() sql not using index

[Kenneth Cox]

In case you aren't comfortable running unreleased planner patches from  
pgsql-hackers, a workaround was discussed on this list recently:


http://archives.postgresql.org/pgsql-performance/2009-09/msg00036.php

On Wed, 09 Sep 2009 06:05:22 -0400, Heikki Linnakangas  
 wrote:



Kevin Kempter wrote:
Hi all I have a large table (>2billion rows) that's partitioned by date  
based
on an epoch int value.  We're running a select max(id) where id is the  
PK. I

have a PK index on each of the partitions, no indexes at all on the base
table.

If I hit a partition table directly I get an index scan as expected:


The planner isn't smart enough to create the plan you're expecting.
There was discussion and even a patch posted recently about that:

http://archives.postgresql.org/pgsql-hackers/2009-07/msg01115.php

It seems the thread petered out, but the concept seems sane.





--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning max() sql not using index

[Heikki Linnakangas]

Kevin Kempter wrote:
> Hi all I have a large table (>2billion rows) that's partitioned by date based 
> on an epoch int value.  We're running a select max(id) where id is the PK. I 
> have a PK index on each of the partitions, no indexes at all on the base 
> table.
> 
> If I hit a partition table directly I get an index scan as expected:

The planner isn't smart enough to create the plan you're expecting.
There was discussion and even a patch posted recently about that:

http://archives.postgresql.org/pgsql-hackers/2009-07/msg01115.php

It seems the thread petered out, but the concept seems sane.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning: INSERT 0 0 but want INSERT 0 1

[Nikolas Everett]

If I can't find an answer in the next day or so I'll crack open OpenJPA and
disable that check.  Its a very simple, if ugly, hack.

--Nik


On 5/12/08, Neil Peter Braggio <[EMAIL PROTECTED]> wrote:
>
> I have the same problem in PG 8.2
>
> To resolve this issue I had to create a new table with the same
> structure than the partitioned table with a trigger for insert and
> update. All the operations the application have to do are directed to
> this new table.
>
> When a new record is inserted in the new table the trigger insert a
> new record with the same values into the partitioned table and then
> delete all records from this new table. In updates operations the
> trigger redirect the operation to the partitioned table too.
>
> With this _not elegant_ solution our Java application is able to do its
> job.
>
> If you find a better solution please let me know.
>
> 
> Neil Peter Braggio
> [EMAIL PROTECTED]
>
>
> On Tue, May 13, 2008 at 11:48 AM, Nikolas Everett <[EMAIL PROTECTED]>
> wrote:
> > I figure this subject belongs on the performance mailing list because it
> is
> > about partitioning, which is a performance issue.
> >
> > I'm working on partitioning some of the tables used by an application
> that
> > uses OpenJPA.  It turns out that OpenJPA is sensitive to the numbers
> > returned when you do an insert.  So I put together a test and attached
> it.
> > My postgres version is 8.3.1 compiled from source.
> >
> > My problem is that this:
> > test=> INSERT INTO ttt (a, b) VALUES ('5-5-08', 'test11212');
> > INSERT 0 0
> > Time: 21.646 ms
> > needs to show:
> > INSERT 0 1
> >
> > or OpenJPA will not accept it.  The insert works, but OpenJPA does not
> > believe it and aborts the current transaction.
> >
> > Is it possible to have partitioning and have insert show the right
> number of
> > rows inserted?
> >
> > Thanks,
> >
> > --Nik
> >
> >
> >  --
> >  Sent via pgsql-performance mailing list (
> pgsql-performance@postgresql.org)
> >  To make changes to your subscription:
> >  http://www.postgresql.org/mailpref/pgsql-performance
> >
> >
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] Partitioning: INSERT 0 0 but want INSERT 0 1

[Neil Peter Braggio]

I have the same problem in PG 8.2

To resolve this issue I had to create a new table with the same
structure than the partitioned table with a trigger for insert and
update. All the operations the application have to do are directed to
this new table.

When a new record is inserted in the new table the trigger insert a
new record with the same values into the partitioned table and then
delete all records from this new table. In updates operations the
trigger redirect the operation to the partitioned table too.

With this _not elegant_ solution our Java application is able to do its job.

If you find a better solution please let me know.


Neil Peter Braggio
[EMAIL PROTECTED]


On Tue, May 13, 2008 at 11:48 AM, Nikolas Everett <[EMAIL PROTECTED]> wrote:
> I figure this subject belongs on the performance mailing list because it is
> about partitioning, which is a performance issue.
>
> I'm working on partitioning some of the tables used by an application that
> uses OpenJPA.  It turns out that OpenJPA is sensitive to the numbers
> returned when you do an insert.  So I put together a test and attached it.
> My postgres version is 8.3.1 compiled from source.
>
> My problem is that this:
> test=> INSERT INTO ttt (a, b) VALUES ('5-5-08', 'test11212');
> INSERT 0 0
> Time: 21.646 ms
> needs to show:
> INSERT 0 1
>
> or OpenJPA will not accept it.  The insert works, but OpenJPA does not
> believe it and aborts the current transaction.
>
> Is it possible to have partitioning and have insert show the right number of
> rows inserted?
>
> Thanks,
>
> --Nik
>
>
>  --
>  Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-performance
>
>

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitioning in postgres - basic question

[Chris]

Tore Lukashaugen wrote:

Hi,

I am new to postgres having worked with Oracle in the past. I am interested 
in understanding Postgres's table partition functionality better. 
Specifically, I have a third party application running against my postgres 
database, but the database is becoming rather large to maintain. I am 
thinking about partitioning the biggest table.


Would I be able to set-up partitioning on this table with it being seemless 
to the third party app (assuming that it performs pretty standard DML 
statements against the table in question)?


http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION

The examples use rules but some on the list have said triggers work 
better if you have a lot of partitions.


--
Postgresql & php tutorials
http://www.designmagick.com/

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PERFORM] Partitioning

[Rigmor Ukuhe]

Abu Mushayeed wrote:
I have partitioned a table based on period (e.g., cdate >= 
'2007-01-01'::date and cdate<=.2007-03-31':;date).


Now, I am issuing query like cdate >= CURRENT_DATE - 1 and cdate <= 
CURRENT_DATE, it scans all the partitions. But if I do cdate >= 
'2007-01-01'::date and cdate<=.2007-03-31'::date it picks the correct 
partition. Also if I join the cdate field with another table, it does 
not pick the correct partition.


I would like to know if it is possible to pick the correct partition 
using the above example.


from http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html

..."For the same reason, "stable" functions such as CURRENT_DATE must be 
avoided. Joining the partition key to a column of another table will not be 
optimized, either."...



Rigmor



Thanks
Abu


Need Mail bonding?
Go to the Yahoo! Mail Q&A 
 
for great tips from Yahoo! Answers 
 
users. !DSPAM:5,45beea6d287779832115503!



--
Rigmor Ukuhe
Finestmedia Ltd | Software Development Team Manager
gsm : (+372)56467729 | tel : (+372)6558043 | e-mail : [EMAIL PROTECTED]

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Partitioning

[Tomas Vondra]

> Can anybody help me out
>  
> I just wanted to knw what will be the configuraion settings for
> partitioning table so as to make inserts faster on the partitioned tables.

Well, that depends. Many questions are important here. Will you insert
into several partitions or only to a single one? Do you need to enforce
some constraint between several partitioned tables?

If you need to insert into several partitions, it can be faster as you
can place them on different drives. If you need to insert only into the
last one (that's usually the case with 'logging' tables) then this
probably won't give a huge performance benefit.

If you need to enforce some kind of constraint between multiple
partitions (possibly from several tables), you'll have to do that
manually using a plpgsql procedure (for example). This is the case with
UNIQUE constraint on a single table, FOREIGN KEY between multimple
partitioned tables, etc. This can mean a serious performance penalty,
esecially when you do mostly insert/update on that table.

This is mostly about application architecture - if you use partitions
incorrectly it's almost impossible to fix that by changing settings in
postgresql.conf.

Tomas

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Partitioning

[Bernd Helmle]

On Thu, 11 Jan 2007 08:18:39 -0600, "Adam Rich" <[EMAIL PROTECTED]> wrote:

> 
> Subpartitions are just a way to break (parent) partitions up into
> smaller pieces.  Those of course can be moved to other disks
> just like the main partitions.

Ah, didn't know that (i just wondered why i need a subpartition to
change the location of a partition). 

Thanks for your clarification...

Bernd

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Partitioning

[Adam Rich]

Each partition can have its own disk, without using subpartitions.

CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
DATA DIRECTORY = '/var/appdata/95/data'
INDEX DIRECTORY = '/var/appdata/95/idx',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
DATA DIRECTORY = '/var/appdata/96/data'
INDEX DIRECTORY = '/var/appdata/96/idx',
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
DATA DIRECTORY = '/var/appdata/97/data'
INDEX DIRECTORY = '/var/appdata/97/idx',
  PARTITION p2000 VALUES IN (1998, 2002, 2006)
DATA DIRECTORY = '/var/appdata/98/data'
INDEX DIRECTORY = '/var/appdata/98/idx'
);

Subpartitions are just a way to break (parent) partitions up into 
smaller pieces.  Those of course can be moved to other disks 
just like the main partitions.



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Bernd
Helmle
Sent: Thursday, January 11, 2007 6:51 AM
To: Scott Marlowe
Cc: Jim C. Nasby; Jeremy Haile; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Partitioning





On Wed, 10 Jan 2007 15:30:16 -0600, Scott Marlowe
<[EMAIL PROTECTED]> wrote:

[...]

> 
> And I don't think the mysql partition supports tablespaces either.
> 

MySQL supports distributing partitions over multiple disks via the
SUBPARTITION clause [1].
I leave it to you, wether their syntax is cleaner, more powerful or
easier or ;)


Bernd

[1]
http://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Partitioning

[Jeremy Haile]

Well - whether or not MySQL's implementation of partitioning has some
deficiency, it sure is a lot easier to set up than PostgreSQL.  And I
don't think there is any technical reason that setting up partitioning
on Postgres couldn't be very easy and still be robust.

On Thu, 11 Jan 2007 13:59:20 +0100, "Mikael Carneholm"
<[EMAIL PROTECTED]> said:
> > On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> > >> Take a look at the set of partitioning functions I wrote shortly
> after
> > >> the 8.1 release:
> > >>
> > >> http://www.studenter.hb.se/~arch/files/part_functions.sql
> > >>
> > >> You could probably work something out using those functions (as-is,
> or
> > >> as inspiration) together with pgAgent
> > >> (http://www.pgadmin.org/docs/1.4/pgagent.html)
> > >>
> > >> /Mikael
> > >>
> > Those are  some great functions.
> > 
> 
> Well, they're less than optimal in one aspect: they add one rule per
> partition, making them unsuitable for OLTP type applications (actually:
> any application where insert performance is crucial). Someone with time
> and/or energy could probably fix that, I guess...patches are welcome :)
> 
> /Mikael
> 
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Partitioning

[Mikael Carneholm]

> On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> >> Take a look at the set of partitioning functions I wrote shortly
after
> >> the 8.1 release:
> >>
> >> http://www.studenter.hb.se/~arch/files/part_functions.sql
> >>
> >> You could probably work something out using those functions (as-is,
or
> >> as inspiration) together with pgAgent
> >> (http://www.pgadmin.org/docs/1.4/pgagent.html)
> >>
> >> /Mikael
> >>
> Those are  some great functions.
> 

Well, they're less than optimal in one aspect: they add one rule per
partition, making them unsuitable for OLTP type applications (actually:
any application where insert performance is crucial). Someone with time
and/or energy could probably fix that, I guess...patches are welcome :)

/Mikael



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Partitioning

[Bernd Helmle]

On Wed, 10 Jan 2007 15:30:16 -0600, Scott Marlowe <[EMAIL PROTECTED]> wrote:

[...]

> 
> And I don't think the mysql partition supports tablespaces either.
> 

MySQL supports distributing partitions over multiple disks via the SUBPARTITION 
clause [1].
I leave it to you, wether their syntax is cleaner, more powerful or easier or 
;)


Bernd

[1] http://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Partitioning

[Scott Marlowe]

On Wed, 2007-01-10 at 15:15, Jeremy Haile wrote:
> You can do list partitioning in MySQL:
> http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html
> 
> My comment was not meant as a criticism of PostgreSQL's current state -
> I'm glad that it has partitioning.  I'm simply wondering if there are
> any plans of adopting a more user-friendly syntax in the future similar
> to MySQL partitioning support.  Having first-class citizen support of
> partitions would also allow some nice administrative GUIs and views to
> be built for managing them.  

I don't think anyone took it as a negative criticism.  Jim and I were
both more pointing out that the development process of the two projects
is somewhat different.

In MySQL a small group that doesn't necessarily interact with a large
user community sets out to implement a feature in a given time line with
a given set of requirements and they tend to ignore what they see as
esoteric requirements.

In PostgreSQL a large development community that communicates fairly
well with it's large user community put somewhat of the onus of proving
the need and doing the initial proof of concept on those who say they
need a feature, often working in a method where the chief hackers lend a
hand to someone who wants the feature so they can get a proof of concept
up and running.  And example would be the auditing / time travel in the
contrib/spi project.  After several iterations, and given the chance to
learn from the mistakes of the previous incarnations, something often
rises out of that to produce the feature needed.

Generally speaking the postgresql method takes longer, making life
harder today, but produces cleaner more easily maintained solutions,
making life easier in the future.  Meanwhile the mysql method works
faster, making life easier today, but makes compromises that might make
life harder in the future.

Something that embodies that difference is the table handler philosophy
of both databases.  PostgreSQL has the abstraction to have more than one
table handler, but in practice has exactly one table handler.  MySQL has
the ability to have many table handlers, and in fact uses many of them.

With PostgreSQL this means that things like the query parsing /
execution and the table handler are tightly coupled.  This results in
things like transactable DDL.  Sometimes this results in suggestions
being dismissed out of hand because they would have unintended
consequences.

In MySQL, because of the multiple table handlers, many compromises on
the query parsing have to be made.  The most common one being that you
can define constraints / foreign keys in a column item, and they will
simply be ignored with no error or notice.  The fk constraints have to
go at the end of the column list to be parsed and executed.

So, partitioning, being something that will touch a lot of parts of the
database, isn't gonna just show up one afternoon in pgsql.  It will
likely take a few people making proof of concept versions before a
consensus is reached and someone who has the ability codes it up.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Partitioning

[Jeremy Haile]

You can do list partitioning in MySQL:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html

My comment was not meant as a criticism of PostgreSQL's current state -
I'm glad that it has partitioning.  I'm simply wondering if there are
any plans of adopting a more user-friendly syntax in the future similar
to MySQL partitioning support.  Having first-class citizen support of
partitions would also allow some nice administrative GUIs and views to
be built for managing them.  

Jeremy Haile


On Wed, 10 Jan 2007 15:09:31 -0600, "Jim C. Nasby" <[EMAIL PROTECTED]> said:
> On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote:
> > This seems so much more intuitive and simpler than what is required to
> > set it up in PostgreSQL.  Does PostgreSQL's approach to table
> > partitioning have any advantage over MySQL?  Is a "nicer" syntax planned
> > for Postgres?
> 
> The focus was to get the base functionality working, and working
> correctly. Another consideration is that there's multiple ways to
> accomplish the partitioning; exposing the basic functionality without
> enforcing a given interface provides more flexibility (ie: it appears
> that you can't do list partitioning with MySQL, while you can with
> PostgreSQL).
> -- 
> Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] Partitioning

[Scott Marlowe]

On Wed, 2007-01-10 at 15:09, Jim C. Nasby wrote:
> On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote:
> > This seems so much more intuitive and simpler than what is required to
> > set it up in PostgreSQL.  Does PostgreSQL's approach to table
> > partitioning have any advantage over MySQL?  Is a "nicer" syntax planned
> > for Postgres?
> 
> The focus was to get the base functionality working, and working
> correctly. Another consideration is that there's multiple ways to
> accomplish the partitioning; exposing the basic functionality without
> enforcing a given interface provides more flexibility (ie: it appears
> that you can't do list partitioning with MySQL, while you can with
> PostgreSQL).

And I don't think the mysql partition supports tablespaces either.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Partitioning

[Jim C. Nasby]

On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote:
> This seems so much more intuitive and simpler than what is required to
> set it up in PostgreSQL.  Does PostgreSQL's approach to table
> partitioning have any advantage over MySQL?  Is a "nicer" syntax planned
> for Postgres?

The focus was to get the base functionality working, and working
correctly. Another consideration is that there's multiple ways to
accomplish the partitioning; exposing the basic functionality without
enforcing a given interface provides more flexibility (ie: it appears
that you can't do list partitioning with MySQL, while you can with
PostgreSQL).
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Partitioning

[Jeremy Haile]

I really wish that PostgreSQL supported a "nice" partitioning syntax
like MySQL has.  

Here is an example:
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
  PARTITION BY RANGE( YEAR(purchased) ) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (1995),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (2005)
);

And to drop a partition:
ALTER TABLE tr DROP PARTITION p2;


This seems so much more intuitive and simpler than what is required to
set it up in PostgreSQL.  Does PostgreSQL's approach to table
partitioning have any advantage over MySQL?  Is a "nicer" syntax planned
for Postgres?


On Wed, 10 Jan 2007 14:20:06 -0600, "Jim C. Nasby" <[EMAIL PROTECTED]> said:
> BTW, someone coming up with a set of functions to handle partitioning
> for the general 'partition by time' case would make a GREAT project on
> pgFoundry.
> 
> On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> > Take a look at the set of partitioning functions I wrote shortly after
> > the 8.1 release:
> > 
> > http://www.studenter.hb.se/~arch/files/part_functions.sql
> > 
> > You could probably work something out using those functions (as-is, or
> > as inspiration) together with pgAgent
> > (http://www.pgadmin.org/docs/1.4/pgagent.html)
> > 
> > /Mikael
> > 
> > > -Original Message-
> > > From: [EMAIL PROTECTED]
> > [mailto:pgsql-performance-
> > > [EMAIL PROTECTED] On Behalf Of Arnau
> > > Sent: den 5 januari 2007 12:02
> > > To: pgsql-performance@postgresql.org
> > > Subject: [PERFORM] Partitioning
> > > 
> > > Hi all,
> > > 
> > >I'm not sure if this question fits in the topic of this list.
> > > 
> > >I'm interested in partitioning and it's the first time I'd use it.
> > > There is an issue I don't know how you handle it. Lets say I'm
> > > interested in store monthly based statistical data like the example of
> > > http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html. What
> > I
> > > don't like of this approach is that the monthly tables, rules... must
> > be
> > > created "manually" or at least I haven't found any other option.
> > > 
> > >My question is how do you manage this? do you have a cron task that
> > > creates automatically these monthly elements (tables, rules, ... ) or
> > > there is another approach that doesn't require external things like
> > cron
> > >   only PostgreSQL.
> > > --
> > > Arnau
> > > 
> > > ---(end of
> > broadcast)---
> > > TIP 5: don't forget to increase your free space map settings
> > 
> > 
> > ---(end of broadcast)---
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >choose an index scan if your joining column's datatypes do not
> >match
> > 
> 
> -- 
> Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)
> 
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Partitioning

[Erik Jones]

On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:

Take a look at the set of partitioning functions I wrote shortly after
the 8.1 release:

http://www.studenter.hb.se/~arch/files/part_functions.sql

You could probably work something out using those functions (as-is, or
as inspiration) together with pgAgent
(http://www.pgadmin.org/docs/1.4/pgagent.html)

/Mikael


Those are  some great functions.

--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] Partitioning

[Jim C. Nasby]

BTW, someone coming up with a set of functions to handle partitioning
for the general 'partition by time' case would make a GREAT project on
pgFoundry.

On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> Take a look at the set of partitioning functions I wrote shortly after
> the 8.1 release:
> 
> http://www.studenter.hb.se/~arch/files/part_functions.sql
> 
> You could probably work something out using those functions (as-is, or
> as inspiration) together with pgAgent
> (http://www.pgadmin.org/docs/1.4/pgagent.html)
> 
> /Mikael
> 
> > -Original Message-
> > From: [EMAIL PROTECTED]
> [mailto:pgsql-performance-
> > [EMAIL PROTECTED] On Behalf Of Arnau
> > Sent: den 5 januari 2007 12:02
> > To: pgsql-performance@postgresql.org
> > Subject: [PERFORM] Partitioning
> > 
> > Hi all,
> > 
> >I'm not sure if this question fits in the topic of this list.
> > 
> >I'm interested in partitioning and it's the first time I'd use it.
> > There is an issue I don't know how you handle it. Lets say I'm
> > interested in store monthly based statistical data like the example of
> > http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html. What
> I
> > don't like of this approach is that the monthly tables, rules... must
> be
> > created "manually" or at least I haven't found any other option.
> > 
> >My question is how do you manage this? do you have a cron task that
> > creates automatically these monthly elements (tables, rules, ... ) or
> > there is another approach that doesn't require external things like
> cron
> >   only PostgreSQL.
> > --
> > Arnau
> > 
> > ---(end of
> broadcast)---
> > TIP 5: don't forget to increase your free space map settings
> 
> 
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
> 

-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Partitioning

[Mikael Carneholm]

Take a look at the set of partitioning functions I wrote shortly after
the 8.1 release:

http://www.studenter.hb.se/~arch/files/part_functions.sql

You could probably work something out using those functions (as-is, or
as inspiration) together with pgAgent
(http://www.pgadmin.org/docs/1.4/pgagent.html)

/Mikael

> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:pgsql-performance-
> [EMAIL PROTECTED] On Behalf Of Arnau
> Sent: den 5 januari 2007 12:02
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Partitioning
> 
> Hi all,
> 
>I'm not sure if this question fits in the topic of this list.
> 
>I'm interested in partitioning and it's the first time I'd use it.
> There is an issue I don't know how you handle it. Lets say I'm
> interested in store monthly based statistical data like the example of
> http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html. What
I
> don't like of this approach is that the monthly tables, rules... must
be
> created "manually" or at least I haven't found any other option.
> 
>My question is how do you manage this? do you have a cron task that
> creates automatically these monthly elements (tables, rules, ... ) or
> there is another approach that doesn't require external things like
cron
>   only PostgreSQL.
> --
> Arnau
> 
> ---(end of
broadcast)---
> TIP 5: don't forget to increase your free space map settings


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Partitioning / constrain exlusion not working with %-operator

[Martin Lesser]

Tom Lane <[EMAIL PROTECTED]> writes:

> It's usually better to use partitioning rules that have something to
> do with the WHERE-clauses you'd be using anyway.  For instance, try
> to partition on ranges.

I agree and tried to create new partitioned tables. But now I ran into
some other performance-related trouble when inserting (parts of) the old
(unpartioned) table into the new one:

CREATE TABLE t_unparted (id1 int, cont varchar);
-- Populate table with 1000 records with id1 from 1 to 1000 and ANALYZE

CREATE TABLE t_parted (id1 int, cont varchar);
CREATE TABLE t_parted_000 (check (id1 >=0 AND id1 < 100)) INHERITS (t_parted);
CREATE RULE ins_000 AS ON INSERT TO t_parted  WHERE id1 >= 0 AND id1 < 100 DO 
INSTEAD INSERT INTO t_parted_000 VALUES (new.*);
-- ... 8 more tables + 8 more rules
CREATE TABLE t_parted_900 (check (id1 >=900 AND id1 < 1000)) INHERITS 
(t_parted);
CREATE RULE ins_900 AS ON INSERT TO t_parted  WHERE id1 >= 900 AND id1 < 1000 
DO INSTEAD INSERT INTO t_parted_900 VALUES (new.*);

And now:

EXPLAIN INSERT INTO t_parted SELECT * FROM t_parted WHERE id1>=0 AND id1<100;

 Result  (cost=0.00..170.80 rows=12 width=36)
   ->  Append  (cost=0.00..170.80 rows=12 width=36)
 ->  Seq Scan on t_parted  (cost=0.00..85.40 rows=6 width=36)
   Filter: ((id1 >= 0) AND (id1 < 100) AND (((id1 >= 0) AND (id1 < 
100)) IS NOT TRUE) AND (((id1 >= 100) AND (id1 < 200)) IS NOT TRUE) AND (((id1 
>= 200) AND (id1 < 300)) IS NOT TRUE) AND (((id1 >= 300) AND (id1 < 400)) IS 
NOT TRUE) AND (((id1 >= 400) AND (id1 < 500)) IS NOT TRUE) AND (((id1 >= 500) 
AND (id1 < 600)) IS NOT TRUE) AND (((id1 >= 600) AND (id1 < 700)) IS NOT TRUE) 
AND (((id1 >= 700) AND (id1 < 800)) IS NOT TRUE) AND (((id1 >= 800) AND (id1 < 
900)) IS NOT TRUE) AND (((id1 >= 900) AND (id1 < 1000)) IS NOT TRUE))
 ->  Seq Scan on t_parted_000 t_parted  (cost=0.00..85.40 rows=6 
width=36)
   Filter: ((id1 >= 0) AND (id1 < 100) AND (((id1 >= 0) AND (id1 < 
100)) IS NOT TRUE) AND (((id1 >= 100) AND (id1 < 200)) IS NOT TRUE) AND (((id1 
>= 200) AND (id1 < 300)) IS NOT TRUE) AND (((id1 >= 300) AND (id1 < 400)) IS 
NOT TRUE) AND (((id1 >= 400) AND (id1 < 500)) IS NOT TRUE) AND (((id1 >= 500) 
AND (id1 < 600)) IS NOT TRUE) AND (((id1 >= 600) AND (id1 < 700)) IS NOT TRUE) 
AND (((id1 >= 700) AND (id1 < 800)) IS NOT TRUE) AND (((id1 >= 800) AND (id1 < 
900)) IS NOT TRUE) AND (((id1 >= 900) AND (id1 < 1000)) IS NOT TRUE))

 Result  (cost=0.00..66.40 rows=12 width=36)
   ->  Append  (cost=0.00..66.40 rows=12 width=36)
 ->  Seq Scan on t_parted  (cost=0.00..33.20 rows=6 width=36)
   Filter: ((id1 >= 0) AND (id1 < 100) AND (id1 >= 0) AND (id1 < 
100))
 ->  Seq Scan on t_parted_000 t_parted  (cost=0.00..33.20 rows=6 
width=36)
   Filter: ((id1 >= 0) AND (id1 < 100) AND (id1 >= 0) AND (id1 < 
100))
 ...
 Result  (cost=0.00..33.20 rows=6 width=36)
   ->  Append  (cost=0.00..33.20 rows=6 width=36)
 ->  Seq Scan on t_parted  (cost=0.00..33.20 rows=6 width=36)
   Filter: ((id1 >= 0) AND (id1 < 100) AND (id1 >= 900) AND (id1 < 
1000))
(58 rows)

The filters appended by the planner do not make any sense and cost too
much time if the old table is huge. (constraint_exclusion was ON)

Is there a better way to partition an existing table with a large
number of rows (>100 mio)?

TIA, Martin

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Partitioning / constrain exlusion not working with %-operator

[Tom Lane]

Martin Lesser <[EMAIL PROTECTED]> writes:
> I try to partition a large table (~ 120 mio. rows) into 50 smaller
> tables but using the IMO immutable %-function constraint exclusion
> does not work as expected:

The constraint exclusion mechanism is not as bright as you think.
There are some very limited cases where it can make a deduction that
a WHERE clause implies a CHECK constraint that's not an exact textual
equivalent ... but all those cases have to do with related b-tree
operators, and % is not one.

It's usually better to use partitioning rules that have something to
do with the WHERE-clauses you'd be using anyway.  For instance, try
to partition on ranges of id1 instead of id1 % 50.  That works because
the CHECK clauses will be like "id1 >= x and id1 < y" and those
operators are btree-related to the "id1 = z" clauses you'll have in
the query.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] partitioning

[Jim C. Nasby]

On Sun, Mar 19, 2006 at 01:31:42PM +0100, Antoine wrote:
> Hi,
> Is there any work on the cards for implementing other partitioning
> strategies? I see mysql 5.1 will have support for hashes and stuff but
> didn't see anything in the todos for postgres.

You'd have to provide a pretty convincing argument for providing hash
partitioning I think. I can't really think of any real-world scenarios
where it's better than other forms.

In any case, the next logical step on the partitioning front is to add
some 'syntactic sugar' to make it easier for people to work with
partitions. I seem to remember some discussion about that, but I don't
recall where it lead to.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] partitioning and locking problems

[Simon Riggs]

On Tue, 2006-02-07 at 18:59 -0600, Jim C. Nasby wrote:

> I'm honestly somewhat surprised someone hasn't run into this problem
> with partitioning yet; or maybe everyone who needs to do long
> transactions just shoves those off to slony slaves...

All DDL takes locks, on all DBMS.

Best Regards, Simon Riggs


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] partitioning and locking problems

[Jim C. Nasby]

On Tue, Feb 07, 2006 at 10:09:02PM +, Simon Riggs wrote:
> On Thu, 2006-02-02 at 11:27 -0500, Marc Morin wrote:
> 
> > > > 1- long running report is running on view
> > > > 2- continuous inserters into view into a table via a rule
> > > > 3- truncate or rule change occurs, taking an exclusive lock.
> > > > Must wait for #1 to finish.
> > > > 4- new reports and inserters must now wait for #3.
> > > > 5- now everyone is waiting for a single query in #1.   Results
> > > > in loss of insert data granularity (important for our application).
> 
> > Using a separate lock table is what we've decided to do in this
> > particular case to serialize #1 and #3.  Inserters don't take this lock
> > and as such will not be stalled. 
> 
> Would it not be simpler to have the Inserters change from one table to
> another either upon command, on a fixed timing cycle or even better
> based upon one of the inserted values (Logdate?) (or all 3?). (Requires
> changes in the application layer: 3GL or db functions).

Unfortunately, AFAIK rule changes would suffer from the exact same
problem, which will be a serious issue for table partitioning. If you
try and add a new partition while a long report is running you'll end up
blocking everything.

ALso, IIRC the OP was trying *not* to have the locking system impose
scheduling. I believe the intention is that either 1 not block 3 or 3
not block 4.

I'm honestly somewhat surprised someone hasn't run into this problem
with partitioning yet; or maybe everyone who needs to do long
transactions just shoves those off to slony slaves...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] partitioning and locking problems

[Marc Morin]

All good ideas, unfortunately, we can't change the inserting applicatin
code easily. 

> -Original Message-
> From: Simon Riggs [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, February 07, 2006 5:09 PM
> To: Marc Morin
> Cc: Markus Schaber; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] partitioning and locking problems
> 
> On Thu, 2006-02-02 at 11:27 -0500, Marc Morin wrote:
> 
> > > > 1- long running report is running on view
> > > > 2- continuous inserters into view into a table 
> via a rule
> > > > 3- truncate or rule change occurs, taking an 
> exclusive lock.
> > > > Must wait for #1 to finish.
> > > > 4- new reports and inserters must now wait for #3.
> > > > 5- now everyone is waiting for a single query 
> in #1.   Results
> > > > in loss of insert data granularity (important for our 
> application).
> 
> > Using a separate lock table is what we've decided to do in this 
> > particular case to serialize #1 and #3.  Inserters don't take this 
> > lock and as such will not be stalled.
> 
> Would it not be simpler to have the Inserters change from one 
> table to another either upon command, on a fixed timing cycle 
> or even better based upon one of the inserted values 
> (Logdate?) (or all 3?). (Requires changes in the application 
> layer: 3GL or db functions).
> 
> The truncates can wait until the data has stopped being used.
> 
> I'd be disinclined to using the locking system as a scheduling tool.
> 
> Best Regards, Simon Riggs
> 
> 
> 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] partitioning and locking problems

[Ron]

At 05:09 PM 2/7/2006, Simon Riggs wrote:


I'd be disinclined to using the locking system as a scheduling tool.
I Agree with Simon.  Using the locking system for scheduling feels 
like a form of Programming by Side Effect.


Ron 




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PERFORM] partitioning and locking problems

[Simon Riggs]

On Thu, 2006-02-02 at 11:27 -0500, Marc Morin wrote:

> > >   1- long running report is running on view
> > >   2- continuous inserters into view into a table via a rule
> > >   3- truncate or rule change occurs, taking an exclusive lock.
> > > Must wait for #1 to finish.
> > >   4- new reports and inserters must now wait for #3.
> > >   5- now everyone is waiting for a single query in #1.   Results
> > > in loss of insert data granularity (important for our application).

> Using a separate lock table is what we've decided to do in this
> particular case to serialize #1 and #3.  Inserters don't take this lock
> and as such will not be stalled. 

Would it not be simpler to have the Inserters change from one table to
another either upon command, on a fixed timing cycle or even better
based upon one of the inserted values (Logdate?) (or all 3?). (Requires
changes in the application layer: 3GL or db functions).

The truncates can wait until the data has stopped being used.

I'd be disinclined to using the locking system as a scheduling tool.

Best Regards, Simon Riggs



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] partitioning and locking problems

[Jim C. Nasby]

On Wed, Feb 01, 2006 at 10:20:21AM -0500, Tom Lane wrote:
> "Marc Morin" <[EMAIL PROTECTED]> writes:
> > Do you mean it would be impossible to change the code so that existing
> > selects continue to use the pre-truncated table until they commit?
> 
> Yes, because that table won't exist any more (as in the file's been
> unlinked) once the TRUNCATE commits.
 
Is there a reason the truncate must happen in 'real time'? If TRUNCATE
marked a table as "truncated as of tid, cid" and created a new set of
empty objects to be used by all transactions after that, then it should
be possible to truncate without waiting on existing selects.
Unfortunately, I can't think of any way to avoid blocking new inserters,
but in the partitioning case that shouldn't matter.

> > The update/insert rule change appears to be more more doable? No? 
> 
> You've still got race conditions there: do onlooker transactions see the
> old set of rules, or the new set, or some unholy mixture?  Removing the
> lock as you suggest would make it possible for the rule rewriter to pick
> up non-self-consistent data from the system catalogs, leading to
> arbitrarily bad behavior ... if you're lucky, it'll just crash, if
> you're not lucky the incorrect rule will do a fandango on your data.

Where can one read about why the catalogs can't/don't use MVCC (I'm
assuming that's why this won't work...)
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] partitioning and locking problems

[Marc Morin]

Using a separate lock table is what we've decided to do in this
particular case to serialize #1 and #3.  Inserters don't take this lock
and as such will not be stalled. 

> -Original Message-
> From: Markus Schaber [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, February 02, 2006 7:44 AM
> To: Marc Morin
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] partitioning and locking problems
> 
> Hi, Marc,
> 
> Marc Morin wrote:
> 
> > 1- long running report is running on view
> > 2- continuous inserters into view into a table via a rule
> > 3- truncate or rule change occurs, taking an exclusive lock.
> > Must wait for #1 to finish.
> > 4- new reports and inserters must now wait for #3.
> > 5- now everyone is waiting for a single query in #1.   Results
> > in loss of insert data granularity (important for our application).
> 
> Apart from having two separate views (one for report, one for 
> insert) as Richard suggested:
> 
> If you have fixed times for #3, don't start any #1 that won't 
> finish before it's time for #3.
> 
> You could also use the LOCK command on an empty lock table at 
> the beginning of each #1 or #3 transaction to prevent #3 from 
> getting the view lock before #1 is finished.
> 
> 
> HTH,
> Markus
> 
> --
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf. | Software Development GIS
> 
> Fight against software patents in EU! www.ffii.org 
> www.nosoftwarepatents.org
> 
> 

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] partitioning and locking problems

[Markus Schaber]

Hi, Marc,

Marc Morin wrote:

>   1- long running report is running on view
>   2- continuous inserters into view into a table via a rule
>   3- truncate or rule change occurs, taking an exclusive lock.
> Must wait for #1 to finish.
>   4- new reports and inserters must now wait for #3.
>   5- now everyone is waiting for a single query in #1.   Results
> in loss of insert data granularity (important for our application).

Apart from having two separate views (one for report, one for insert) as
Richard suggested:

If you have fixed times for #3, don't start any #1 that won't finish
before it's time for #3.

You could also use the LOCK command on an empty lock table at the
beginning of each #1 or #3 transaction to prevent #3 from getting the
view lock before #1 is finished.


HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] partitioning and locking problems

[Tom Lane]

"Marc Morin" <[EMAIL PROTECTED]> writes:
> Do you mean it would be impossible to change the code so that existing
> selects continue to use the pre-truncated table until they commit?

Yes, because that table won't exist any more (as in the file's been
unlinked) once the TRUNCATE commits.

> The update/insert rule change appears to be more more doable? No? 

You've still got race conditions there: do onlooker transactions see the
old set of rules, or the new set, or some unholy mixture?  Removing the
lock as you suggest would make it possible for the rule rewriter to pick
up non-self-consistent data from the system catalogs, leading to
arbitrarily bad behavior ... if you're lucky, it'll just crash, if
you're not lucky the incorrect rule will do a fandango on your data.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] partitioning and locking problems

[Marc Morin]

Tom,

Do you mean it would be impossible to change the code so that existing
selects continue to use the pre-truncated table until they commit? Or
just require a more extensive change?

The update/insert rule change appears to be more more doable? No? 

> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 01, 2006 12:50 AM
> To: Marc Morin
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] partitioning and locking problems 
> 
> "Marc Morin" <[EMAIL PROTECTED]> writes:
> > Would like to understand the implications of changing postgres'
> > code/locking for rule changes and truncate to not require 
> locking out 
> > select statements?
> 
> It won't work...
> 
>   regards, tom lane
> 
> 

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] partitioning and locking problems

[Richard Huxton]

Marc Morin wrote:

Under both these circumstances (truncate and create / replace rule) the
locking behaviour of these commands can cause locking problems for us.
The scenario is best illustrated as a series of steps:
 


1- long running report is running on view
2- continuous inserters into view into a table via a rule
3- truncate or rule change occurs, taking an exclusive lock.
Must wait for #1 to finish.
4- new reports and inserters must now wait for #3.
5- now everyone is waiting for a single query in #1.   Results
in loss of insert data granularity (important for our application).


How much would you get from splitting the view into two: reporting and 
inserting?


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PERFORM] partitioning and locking problems

[Tom Lane]

"Marc Morin" <[EMAIL PROTECTED]> writes:
> Would like to understand the implications of changing postgres'
> code/locking for rule changes and truncate to not require locking out
> select statements?  

It won't work...

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] partitioning

[Marc Cousin]

Yes, that's how I solved it... and I totally agree that it's hard for the 
planner to guess what to do on the partitions. But maybe there should be 
something in the docs explaining the limitations ...

I'm only asking for the biggest 100 ids from the table, so I thought maybe the 
planner would take the 100 biggest from all partitions or something like that 
and return me the 100 biggest from those results. It didn't and that's quite 
logical.

What I meant is that I understand why the planner chooses this plan, but maybe 
it should be written somewhere in the docs that some plans will be worse 
after partitionning.

Le Mardi 13 Décembre 2005 12:50, vous avez écrit :
> I just saw that there is no where clause in the query, that you had
> fed to explain plan.
> you need to include a where clause based on id_machine column to see the
> effect.
>
> On 12/13/05, Pandurangan R S <[EMAIL PROTECTED]> wrote:
> > Did you set constraint_exclusion = true in postgresql.conf file?
> >
> > On 12/13/05, Marc Cousin <[EMAIL PROTECTED]> wrote:
> > > Hi,
> > >
> > > I've been working on trying to partition a big table (I've never
> > > partitioned a table in any other database till now).
> > > Everything went ok, except one query that didn't work afterwards.
> > >
> > > I've put the partition description, indexes, etc ..., and the explain
> > > plan attached.
> > >
> > > The query is extremely fast without partition (index scan backards on
> > > the primary key)
> > >
> > > The query is : "select * from logs order by id desc limit 100;"
> > > id is the primary key.
> > >
> > > It is indexed on all partitions.
> > >
> > > But the explain plan does full table scan on all partitions.
> > >
> > > While I think I understand why it is doing this plan right now, is
> > > there something that could be done to optimize this case ? Or put a
> > > warning in the docs about this kind of behaviour. I guess normally
> > > someone would partition to get faster queries :)
> > >
> > > Anyway, I thought I should mention this, as it has been quite a
> > > surprise.
> > >
> > >
> > >
> > > ---(end of
> > > broadcast)--- TIP 1: if posting/reading through
> > > Usenet, please send an appropriate subscribe-nomail command to
> > > [EMAIL PROTECTED] so that your message can get through to the
> > > mailing list cleanly
> >
> > --
> > Regards
> > Pandu

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] partitioning

[Pandurangan R S]

I just saw that there is no where clause in the query, that you had
fed to explain plan.
you need to include a where clause based on id_machine column to see the effect.

On 12/13/05, Pandurangan R S <[EMAIL PROTECTED]> wrote:
> Did you set constraint_exclusion = true in postgresql.conf file?
>
> On 12/13/05, Marc Cousin <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I've been working on trying to partition a big table (I've never 
> > partitioned a
> > table in any other database till now).
> > Everything went ok, except one query that didn't work afterwards.
> >
> > I've put the partition description, indexes, etc ..., and the explain plan
> > attached.
> >
> > The query is extremely fast without partition (index scan backards on the
> > primary key)
> >
> > The query is : "select * from logs order by id desc limit 100;"
> > id is the primary key.
> >
> > It is indexed on all partitions.
> >
> > But the explain plan does full table scan on all partitions.
> >
> > While I think I understand why it is doing this plan right now, is there
> > something that could be done to optimize this case ? Or put a warning in the
> > docs about this kind of behaviour. I guess normally someone would partition
> > to get faster queries :)
> >
> > Anyway, I thought I should mention this, as it has been quite a surprise.
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >subscribe-nomail command to [EMAIL PROTECTED] so that your
> >message can get through to the mailing list cleanly
> >
> >
> >
> >
>
>
> --
> Regards
> Pandu
>

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] partitioning

[Pandurangan R S]

Did you set constraint_exclusion = true in postgresql.conf file?

On 12/13/05, Marc Cousin <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I've been working on trying to partition a big table (I've never partitioned a
> table in any other database till now).
> Everything went ok, except one query that didn't work afterwards.
>
> I've put the partition description, indexes, etc ..., and the explain plan
> attached.
>
> The query is extremely fast without partition (index scan backards on the
> primary key)
>
> The query is : "select * from logs order by id desc limit 100;"
> id is the primary key.
>
> It is indexed on all partitions.
>
> But the explain plan does full table scan on all partitions.
>
> While I think I understand why it is doing this plan right now, is there
> something that could be done to optimize this case ? Or put a warning in the
> docs about this kind of behaviour. I guess normally someone would partition
> to get faster queries :)
>
> Anyway, I thought I should mention this, as it has been quite a surprise.
>
>
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
>
>
>
>


--
Regards
Pandu

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Partitioning / Clustering

[PFC]

If you make the assertion that you are transferring equal or less
session data between your session server (lets say an RDBMS) and the
app server than you are between the app server and the client, an out
of band 100Mb network for session information is plenty of bandwidth.
	So if you count on a mean page size of 6-8 kbytes gzipped, that will  
prevent you from caching the N first results of the Big Slow Search Query  
in a native object in the user session state (say, a list of integers  
indicating which rows match), so you will have to redo the Big Slow Search  
Query everytime the user clicks on Next Page instead of grabbing a set of  
cached row id's and doing a fast SELECT WHERE id IN ...
	This is the worst case ... I'd gzip() the row id's and stuff them in the  
session, that's always better than blowing up the database with the Big  
Slow Search Query everytime someone does Next Page...

This also represents OLTP style traffic, which postgresql is pretty
good at.  You should easily be able to get over 100Tps.  100 hits per
second is an awful lot of traffic, more than any website I've managed
will ever see.
	On the latest anandtech benchmarks, 100 hits per second on a blog/forum  
software is a big bi-opteron server running dotNET, at 99% load... it's a  
lot if you count only dynamic page hits.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [PERFORM] Partitioning / Clustering

[Josh Berkus]

Ross,

> Memcached is a PG memory store, I gather,

Nope.  It's a hyperfast resident-in-memory hash that allows you to stash stuff 
like user session information and even materialized query set results.  
Thanks to SeanC, we even have a plugin, pgmemcached.

> but...what is squid, lighttpd? 
> anything directly PG-related?

No.   These are all related to making the web server do more.   The idea is 
NOT to hit the database every time you have to serve up a web page, and 
possibly not to hit the web server either.  For example, you can use squid 3 
for "reverse" caching in front of your web server, and serve far more page 
views than you could with Apache alone.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Partitioning / Clustering

[Alex Stapleton]

On 12 May 2005, at 18:33, Josh Berkus wrote:
People,

In general I think your point is valid. Just remember that it  
probably
also matters how you count page views. Because technically images  
are a
separate page (and this thread did discuss serving up images). So if
there are 20 graphics on a specific page, that is 20 server hits just
for that one page.

Also, there's bots and screen-scrapers and RSS, web e-mails, and  
web services
and many other things which create hits but are not "people".  I'm  
currently
working on clickstream for a site which is nowhere in the top 100,  
and is
getting 3 million real hits a day ... and we know for a fact that  
at least
1/4 of that is bots.
I doubt bots are generally Alexa toolbar enabled.
Regardless, the strategy you should be employing for a high traffic  
site is
that if your users hit the database for anything other than direct
interaction (like filling out a webform) then you're lost.Use  
memcached,
squid, lighttpd caching, ASP.NET caching, pools, etc.   Keep the  
load off the
database except for the stuff that only the database can do.
This is the aproach I would take as well. There is no point storing  
stuff in a DB, if your only doing direct lookups on it and it isn't  
the sort of data that you care so much about the integrity of.


--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of  
broadcast)---
TIP 8: explain analyze is your friend



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [PERFORM] Partitioning / Clustering

[Josh Berkus]

People,

> In general I think your point is valid. Just remember that it probably
> also matters how you count page views. Because technically images are a
> separate page (and this thread did discuss serving up images). So if
> there are 20 graphics on a specific page, that is 20 server hits just
> for that one page.

Also, there's bots and screen-scrapers and RSS, web e-mails, and web services 
and many other things which create hits but are not "people".  I'm currently 
working on clickstream for a site which is nowhere in the top 100, and is 
getting 3 million real hits a day ... and we know for a fact that at least 
1/4 of that is bots.

Regardless, the strategy you should be employing for a high traffic site is 
that if your users hit the database for anything other than direct 
interaction (like filling out a webform) then you're lost.Use memcached, 
squid, lighttpd caching, ASP.NET caching, pools, etc.   Keep the load off the 
database except for the stuff that only the database can do.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] Partitioning / Clustering

[PFC]

100 hits a second = 8,640,000 hits a day. I work on a site which does  >  
100 million dynamic pages a day. In comparison Yahoo probably does  >  
100,000,000,000 (100 billion) views a day
  if I am interpreting Alexa's charts correctly. Which is about  
1,150,000 a second.

Read the help on Alexa's site... ;)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Partitioning / Clustering

[John A Meinel]

Alex Turner wrote:
Ok - my common sense alarm is going off here...
There are only 6.446 billion people worldwide.  100 Billion page views
would require every person in the world to view 18 pages of yahoo
every day.  Not very likely.
http://www.internetworldstats.com/stats.htm
suggests that there are around 1 billion people actualy on the internet.
That means each and every person on the internet has to view 100 pages
per day of yahoo.
pretty unlikely IMHO.  I for one don't even use Yahoo ;)
100 million page views per day suggests that 1 in 100 people on the
internet each viewed 10 pages of a site.  Thats a pretty high
percentage if you ask me.
In general I think your point is valid. Just remember that it probably
also matters how you count page views. Because technically images are a
separate page (and this thread did discuss serving up images). So if
there are 20 graphics on a specific page, that is 20 server hits just
for that one page.
I could easily see an image heavy site getting 100 hits / page. Which
starts meaning that if 1M users hit 10 pages, then you get 1M*10*100 = 1G.
I still think 100G views on a single website is a lot, but 100M is
certainly possible.
John
=:->


signature.asc
Description: OpenPGP digital signature

Re: [PERFORM] Partitioning / Clustering

[Alex Turner]

Ok - my common sense alarm is going off here...

There are only 6.446 billion people worldwide.  100 Billion page views
would require every person in the world to view 18 pages of yahoo
every day.  Not very likely.

http://www.internetworldstats.com/stats.htm
suggests that there are around 1 billion people actualy on the internet.

That means each and every person on the internet has to view 100 pages
per day of yahoo.

pretty unlikely IMHO.  I for one don't even use Yahoo ;)

100 million page views per day suggests that 1 in 100 people on the
internet each viewed 10 pages of a site.  Thats a pretty high
percentage if you ask me.

If I visit 20 web sites in a day, and see an average of 10 pages per
site. that means only about 2000 or so sites generate 100 million page
views in a day or better.

100 million pageviews averages to 1157/sec, which we'll double for
peak load to 2314.

I can easily see a system doing 2314 hash lookups per second.  Hell I
wrote a system that could do a thousand times that four years ago on a
single 1Ghz Athlon.  Heck - you can get 2314 lookups/sec on a 486 ;)

Given that session information doesn't _have_ to persist to storage,
and can be kept in RAM.  A single server could readily manage session
information for even very large sites (of course over a million
concurrent users could really start chewing into RAM, but if you are
Yahoo, you can probably afford a box with 100GB of RAM ;).

We get over 1000 tps on a dual opteron with a couple of mid size RAID
arrays on 10k discs with fsync on for small transactions.  I'm sure
that could easily be bettered with a few more dollars.

Maybe my number are off, but somehow it doesn't seem like that many
people need a highly complex session solution to me.

Alex Turner
netEconomist

On 5/12/05, Alex Stapleton <[EMAIL PROTECTED]> wrote:
> 
> On 12 May 2005, at 15:08, Alex Turner wrote:
> 
> > Having local sessions is unnesesary, and here is my logic:
> >
> > Generaly most people have less than 100Mb of bandwidth to the
> > internet.
> >
> > If you make the assertion that you are transferring equal or less
> > session data between your session server (lets say an RDBMS) and the
> > app server than you are between the app server and the client, an out
> > of band 100Mb network for session information is plenty of bandwidth.
> > This also represents OLTP style traffic, which postgresql is pretty
> > good at.  You should easily be able to get over 100Tps.  100 hits per
> > second is an awful lot of traffic, more than any website I've managed
> > will ever see.
> >
> > Why solve the complicated clustered sessions problem, when you don't
> > really need to?
> 
> 100 hits a second = 8,640,000 hits a day. I work on a site which does
>  > 100 million dynamic pages a day. In comparison Yahoo probably does
>  > 100,000,000,000 (100 billion) views a day
>   if I am interpreting Alexa's charts correctly. Which is about
> 1,150,000 a second.
> 
> Now considering the site I work on is not even in the top 1000 on
> Alexa, theres a lot of sites out there which need to solve this
> problem I would assume.
> 
> There are also only so many hash table lookups a single machine can
> do, even if its a Quad Opteron behemoth.
> 
> 
> > Alex Turner
> > netEconomist
> >
> > On 5/11/05, PFC <[EMAIL PROTECTED]> wrote:
> >
> >>
> >>
> >>
> >>> However, memcached (and for us, pg_memcached) is an excellent way to
> >>> improve
> >>> horizontal scalability by taking disposable data (like session
> >>> information)
> >>> out of the database and putting it in protected RAM.
> >>>
> >>
> >> So, what is the advantage of such a system versus, say, a
> >> "sticky
> >> sessions" system where each session is assigned to ONE application
> >> server
> >> (not PHP then) which keeps it in RAM as native objects instead of
> >> serializing and deserializing it on each request ?
> >> I'd say the sticky sessions should perform a lot better,
> >> and if one
> >> machine dies, only the sessions on this one are lost.
> >> But of course you can't do it with PHP as you need an app
> >> server which
> >> can manage sessions. Potentially the savings are huge, though.
> >>
> >> On Google, their distributed system spans a huge number of
> >> PCs and it has
> >> redundancy, ie. individual PC failure is a normal thing and is a
> >> part of
> >> the system, it is handled gracefully. I read a paper on this
> >> matter, it's
> >> pretty impressive. The google filesystem has nothing to do with
> >> databases
> >> though, it's more a massive data store / streaming storage.
> >>
> >> ---(end of
> >> broadcast)---
> >> TIP 1: subscribe and unsubscribe commands go to
> >> [EMAIL PROTECTED]
> >>
> >>
> >
> >
> 
>

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Partitioning / Clustering

[Alex Stapleton]

On 12 May 2005, at 15:08, Alex Turner wrote:
Having local sessions is unnesesary, and here is my logic:
Generaly most people have less than 100Mb of bandwidth to the  
internet.

If you make the assertion that you are transferring equal or less
session data between your session server (lets say an RDBMS) and the
app server than you are between the app server and the client, an out
of band 100Mb network for session information is plenty of bandwidth.
This also represents OLTP style traffic, which postgresql is pretty
good at.  You should easily be able to get over 100Tps.  100 hits per
second is an awful lot of traffic, more than any website I've managed
will ever see.
Why solve the complicated clustered sessions problem, when you don't
really need to?
100 hits a second = 8,640,000 hits a day. I work on a site which does  
> 100 million dynamic pages a day. In comparison Yahoo probably does  
> 100,000,000,000 (100 billion) views a day
 if I am interpreting Alexa's charts correctly. Which is about  
1,150,000 a second.

Now considering the site I work on is not even in the top 1000 on  
Alexa, theres a lot of sites out there which need to solve this  
problem I would assume.

There are also only so many hash table lookups a single machine can  
do, even if its a Quad Opteron behemoth.


Alex Turner
netEconomist
On 5/11/05, PFC <[EMAIL PROTECTED]> wrote:


However, memcached (and for us, pg_memcached) is an excellent way to
improve
horizontal scalability by taking disposable data (like session
information)
out of the database and putting it in protected RAM.
So, what is the advantage of such a system versus, say, a  
"sticky
sessions" system where each session is assigned to ONE application  
server
(not PHP then) which keeps it in RAM as native objects instead of
serializing and deserializing it on each request ?
I'd say the sticky sessions should perform a lot better,  
and if one
machine dies, only the sessions on this one are lost.
But of course you can't do it with PHP as you need an app  
server which
can manage sessions. Potentially the savings are huge, though.

On Google, their distributed system spans a huge number of  
PCs and it has
redundancy, ie. individual PC failure is a normal thing and is a  
part of
the system, it is handled gracefully. I read a paper on this  
matter, it's
pretty impressive. The google filesystem has nothing to do with  
databases
though, it's more a massive data store / streaming storage.

---(end of  
broadcast)---
TIP 1: subscribe and unsubscribe commands go to  
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Partitioning / Clustering

[Alex Turner]

Having local sessions is unnesesary, and here is my logic:

Generaly most people have less than 100Mb of bandwidth to the internet.

If you make the assertion that you are transferring equal or less
session data between your session server (lets say an RDBMS) and the
app server than you are between the app server and the client, an out
of band 100Mb network for session information is plenty of bandwidth. 
This also represents OLTP style traffic, which postgresql is pretty
good at.  You should easily be able to get over 100Tps.  100 hits per
second is an awful lot of traffic, more than any website I've managed
will ever see.

Why solve the complicated clustered sessions problem, when you don't
really need to?

Alex Turner
netEconomist

On 5/11/05, PFC <[EMAIL PROTECTED]> wrote:
> 
> 
> > However, memcached (and for us, pg_memcached) is an excellent way to
> > improve
> > horizontal scalability by taking disposable data (like session
> > information)
> > out of the database and putting it in protected RAM.
> 
> So, what is the advantage of such a system versus, say, a "sticky
> sessions" system where each session is assigned to ONE application server
> (not PHP then) which keeps it in RAM as native objects instead of
> serializing and deserializing it on each request ?
> I'd say the sticky sessions should perform a lot better, and if one
> machine dies, only the sessions on this one are lost.
> But of course you can't do it with PHP as you need an app server which
> can manage sessions. Potentially the savings are huge, though.
> 
> On Google, their distributed system spans a huge number of PCs and it 
> has
> redundancy, ie. individual PC failure is a normal thing and is a part of
> the system, it is handled gracefully. I read a paper on this matter, it's
> pretty impressive. The google filesystem has nothing to do with databases
> though, it's more a massive data store / streaming storage.
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Partitioning / Clustering

[PFC]

machines. Which has it's own set of issues entirely. I am not entirely  
sure that memcached actually does serialize data when it's comitted into
	I think it does, ie. it's a simple mapping of [string key] => [string  
value].

memcached either, although I could be wrong, I have not looked at the  
source. Certainly if you can ensure that a client always goes back to  
the same machine you can simplify the whole thing hugely. It's generally  
not that easy though, you need a proxy server of some description  
capable of understanding the HTTP traffic and maintaining a central
	Yes...
	You could implement it by mapping servers to the hash of the user session  
id.
	Statistically, the servers would get the same numbers of sessions on each  
of them, but you have to trust statistics...
	It does eliminate the lookup table though.

idea, I would like to hear a way of implementing them cheaply (and on  
PHP) as well. I may have to give that some thought in fact. Oh yeah, and  
load balancers software often sucks in annoying (if not always  
important) ways.
	You can use lighttpd as a load balancer, I believe it has a stick  
sessions plugin (or you could code one in, it's open source after all). It  
definitely support simple round-robin load balancing, acting as a proxy to  
any number of independent servers.


matter, it's pretty impressive. The google filesystem has nothing to do  
with databases though, it's more a massive data store / streaming  
storage.
Since when did Massive Data stores have nothing to do with DBs? Isn't  
Oracle Cluster entirely based on forming an enormous scalable disk array  
to store your DB on?
	Um, well, the Google Filesystem is (like its name implies) a filesystem  
designed to store huge files in a distributed and redundant manner. Files  
are structured as a stream of records (which are themselves big in size)  
and it's designed to support appending records to these stream files  
efficiently and without worrying about locking.

	It has no querying features however, that is why I said it was not a  
database.

	I wish I could find the whitepaper, I think the URL was on this list some  
day, maybe it's on Google's site ?

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [PERFORM] Partitioning / Clustering

[Alex Stapleton]

On 11 May 2005, at 23:35, PFC wrote:


However, memcached (and for us, pg_memcached) is an excellent way  
to improve
horizontal scalability by taking disposable data (like session  
information)
out of the database and putting it in protected RAM.

So, what is the advantage of such a system versus, say, a  
"sticky sessions" system where each session is assigned to ONE  
application server (not PHP then) which keeps it in RAM as native  
objects instead of serializing and deserializing it on each request ?
I'd say the sticky sessions should perform a lot better, and if  
one machine dies, only the sessions on this one are lost.
But of course you can't do it with PHP as you need an app  
server which can manage sessions. Potentially the savings are huge,  
though.
Theres no reason it couldn't be done with PHP to be fair as long as  
you could ensure that the client was always routed back to the same  
machines. Which has it's own set of issues entirely. I am not  
entirely sure that memcached actually does serialize data when it's  
comitted into memcached either, although I could be wrong, I have not  
looked at the source. Certainly if you can ensure that a client  
always goes back to the same machine you can simplify the whole thing  
hugely. It's generally not that easy though, you need a proxy server  
of some description capable of understanding the HTTP traffic and  
maintaining a central session lookup table to redirect with. Which  
isn't really solving the problem so much as moving it somewhere else.  
Instead of needing huge memcached pools, you need hardcore  
loadbalancers. Load Balancers tend to cost $ in comparison.  
Distributed sticky sessions are a rather nice idea, I would like to  
hear a way of implementing them cheaply (and on PHP) as well. I may  
have to give that some thought in fact. Oh yeah, and load balancers  
software often sucks in annoying (if not always important) ways.

On Google, their distributed system spans a huge number of PCs  
and it has redundancy, ie. individual PC failure is a normal thing  
and is a part of the system, it is handled gracefully. I read a  
paper on this matter, it's pretty impressive. The google filesystem  
has nothing to do with databases though, it's more a massive data  
store / streaming storage.

Since when did Massive Data stores have nothing to do with DBs? Isn't  
Oracle Cluster entirely based on forming an enormous scalable disk  
array to store your DB on?

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Partitioning / Clustering

[PFC]

However, memcached (and for us, pg_memcached) is an excellent way to  
improve
horizontal scalability by taking disposable data (like session  
information)
out of the database and putting it in protected RAM.
	So, what is the advantage of such a system versus, say, a "sticky  
sessions" system where each session is assigned to ONE application server  
(not PHP then) which keeps it in RAM as native objects instead of  
serializing and deserializing it on each request ?
	I'd say the sticky sessions should perform a lot better, and if one  
machine dies, only the sessions on this one are lost.
	But of course you can't do it with PHP as you need an app server which  
can manage sessions. Potentially the savings are huge, though.

	On Google, their distributed system spans a huge number of PCs and it has  
redundancy, ie. individual PC failure is a normal thing and is a part of  
the system, it is handled gracefully. I read a paper on this matter, it's  
pretty impressive. The google filesystem has nothing to do with databases  
though, it's more a massive data store / streaming storage.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Partitioning / Clustering

[Jim C. Nasby]

On Wed, May 11, 2005 at 08:57:57AM +0100, David Roussel wrote:
> For an interesting look at scalability, clustering, caching, etc for a
> large site have a look at how livejournal did it.
> http://www.danga.com/words/2004_lisa/lisa04.pdf
> 
> They have 2.6 Million active users, posting 200 new blog entries per
> minute, plus many comments and countless page views.

Neither of which is that horribly impressive. 200 TPM is less than 4TPS.
While I haven't run high transaction rate databases under PostgreSQL, I
suspect others who have will say that 4TPS isn't that big of a deal.

> Although this system is of a different sort to the type I work on it's
> interesting to see how they've made it scale.
> 
> They use mysql on dell hardware! And found single master replication did
> not scale.  There's a section on multimaster replication, not sure if
Probably didn't scale because they used to use MyISAM.

> they use it.  The main approach they use is to parition users into
> spefic database clusters.  Caching is done using memcached at the
Which means they've got a huge amount of additional code complexity, not
to mention how many times you can't post something because 'that cluster
is down for maintenance'.

> application level to avoid hitting the db for rendered pageviews.
Memcached is about the only good thing I've seen come out of
livejournal.

> It's interesting that the solution livejournal have arrived at is quite
> similar in ways to the way google is set up.

Except that unlike LJ, google stays up and it's fast. Though granted, LJ
is quite a bit faster than it was 6 months ago.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Partitioning / Clustering

[Simon Riggs]

On Wed, 2005-05-11 at 17:13 +0800, Christopher Kings-Lynne wrote:
> > Alex Stapleton wrote
> > Be more helpful, and less arrogant please. 
> 
> Simon told you all the reasons clearly and politely.

Thanks Chris for your comments.

PostgreSQL can always do with one more developer and my sole intent was
to encourage Alex and other readers to act themselves. If my words seem
arrogant, then I apologise to any and all that think so.

Best Regards, Simon Riggs 



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] Partitioning / Clustering

[Greg Stark]

Alex Stapleton <[EMAIL PROTECTED]> writes:

> Acceptable Answers to 'So, when/is PG meant to be getting a decent
> partitioning system?':
...
>  3. Your welcome to take a stab at it, I expect the community  would
> support your efforts as well.

As long as we're being curt all around, this one's not acceptable on the basis
that it's not grammatical.

-- 
greg


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Partitioning / Clustering

[Josh Berkus]

David,

> It's interesting that the solution livejournal have arrived at is quite
> similar in ways to the way google is set up.

Yes, although again, they're using memcached as pseudo-clustering software, 
and as a result are limited to what fits in RAM (RAM on 27 machines, but it's 
still RAM).  And due to limitations on memcached, the whole thing blows 
whenever a server goes out (the memcached project is working on this).  But 
any LJ user could tell you that it's a low-availability system.

However, memcached (and for us, pg_memcached) is an excellent way to improve 
horizontal scalability by taking disposable data (like session information) 
out of the database and putting it in protected RAM.  On some websites, 
adding memcached can result is as much as a 60% decrease in database traffic.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] Partitioning / Clustering

[Tom Lane]

Mischa Sandberg <[EMAIL PROTECTED]> writes:
> So, simplicity dictates something like:

> table pg_remote(schemaname text, connectby text, remoteschema text)

Previous discussion of this sort of thing concluded that we wanted to
follow the SQL-MED standard.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] Partitioning / Clustering

[Christopher Kings-Lynne]

Acceptable Answers to 'So, when/is PG meant to be getting a decent  
partitioning system?':

1. Person X is working on it I believe.
2. It's on the list, but nobody has done anything about it yet
3. Your welcome to take a stab at it, I expect the community  would 
support your efforts as well.
4. If you have a huge pile of money you could probably buy the  
Moon. Thinking along those lines, you can probably pay someone to  write 
it for you.
5. It's a stupid idea, and it's never going to work, and heres  
why..

Unacceptable Answers to the same question:
1. Yours.
Be more helpful, and less arrogant please. Everyone else who has  
contributed to this thread has been very helpful in clarifying the  
state of affairs and pointing out what work is and isn't being done,  
and alternatives to just waiting for PG do it for you.
Please YOU be more helpful and less arrogant.  I thought your inital 
email was arrogant, demanding and insulting.  Your followup email has 
done nothing to dispel my impression.  Simon (one of PostgreSQL's major 
contributors AND one of the very few people working on partitioning in 
PostgreSQL, as you requested) told you all the reasons clearly and politely.

Chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [PERFORM] Partitioning / Clustering

[Alex Stapleton]

On 11 May 2005, at 09:50, Alex Stapleton wrote:
On 11 May 2005, at 08:57, David Roussel wrote:

For an interesting look at scalability, clustering, caching, etc  
for a
large site have a look at how livejournal did it.
http://www.danga.com/words/2004_lisa/lisa04.pdf

I have implemented similar systems in the past, it's a pretty good  
technique, unfortunately it's not very "Plug-and-Play" as you have  
to base most of your API on memcached (I imagine MySQLs NDB tables  
might work as well actually) for it to work well.


They have 2.6 Million active users, posting 200 new blog entries per
minute, plus many comments and countless page views.
Although this system is of a different sort to the type I work on  
it's
interesting to see how they've made it scale.

They use mysql on dell hardware! And found single master  
replication did
not scale.  There's a section on multimaster replication, not sure if
they use it.  The main approach they use is to parition users into
spefic database clusters.  Caching is done using memcached at the
application level to avoid hitting the db for rendered pageviews

I don't think they are storing pre-rendered pages (or bits of) in  
memcached, but are principally storing the data for the pages in  
it. Gluing pages together is not a hugely intensive process usually :)
The only problem with memcached is that the clients clustering/ 
partitioning system will probably break if a node dies, and  
probably get confused if you add new nodes onto it as well. Easily  
extensible clustering (no complete redistribution of data required  
when you add/remove nodes) with the data distributed across nodes  
seems to be nothing but a pipe dream right now.


It's interesting that the solution livejournal have arrived at is  
quite
similar in ways to the way google is set up.

Don't Google use indexing servers which keep track of where data  
is? So that you only need to update them when you add or move data,  
deletes don't even have to be propagated among indexes immediately  
really because you'll find out if data isn't there when you visit  
where it should be. Or am I talking crap?
That will teach me to RTFA first ;) Ok so LJ maintain an index of  
which cluster each user is on, kinda of like google do :)


David
---(end of  
broadcast)---
TIP 8: explain analyze is your friend



---(end of  
broadcast)---
TIP 4: Don't 'kill -9' the postmaster



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [PERFORM] Partitioning / Clustering

[Alex Stapleton]

On 11 May 2005, at 08:57, David Roussel wrote:
For an interesting look at scalability, clustering, caching, etc for a
large site have a look at how livejournal did it.
http://www.danga.com/words/2004_lisa/lisa04.pdf
I have implemented similar systems in the past, it's a pretty good  
technique, unfortunately it's not very "Plug-and-Play" as you have to  
base most of your API on memcached (I imagine MySQLs NDB tables might  
work as well actually) for it to work well.

They have 2.6 Million active users, posting 200 new blog entries per
minute, plus many comments and countless page views.
Although this system is of a different sort to the type I work on it's
interesting to see how they've made it scale.
They use mysql on dell hardware! And found single master  
replication did
not scale.  There's a section on multimaster replication, not sure if
they use it.  The main approach they use is to parition users into
spefic database clusters.  Caching is done using memcached at the
application level to avoid hitting the db for rendered pageviews
I don't think they are storing pre-rendered pages (or bits of) in  
memcached, but are principally storing the data for the pages in it.  
Gluing pages together is not a hugely intensive process usually :)
The only problem with memcached is that the clients clustering/ 
partitioning system will probably break if a node dies, and probably  
get confused if you add new nodes onto it as well. Easily extensible  
clustering (no complete redistribution of data required when you add/ 
remove nodes) with the data distributed across nodes seems to be  
nothing but a pipe dream right now.

It's interesting that the solution livejournal have arrived at is  
quite
similar in ways to the way google is set up.
Don't Google use indexing servers which keep track of where data is?  
So that you only need to update them when you add or move data,  
deletes don't even have to be propagated among indexes immediately  
really because you'll find out if data isn't there when you visit  
where it should be. Or am I talking crap?

David
---(end of  
broadcast)---
TIP 8: explain analyze is your friend



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Partitioning / Clustering

[Alex Stapleton]

On 11 May 2005, at 08:16, Simon Riggs wrote:
On Tue, 2005-05-10 at 11:03 +0100, Alex Stapleton wrote:
So, when/is PG meant to be getting a decent partitioning system?
ISTM that your question seems to confuse where code comes from.  
Without
meaning to pick on you, or reply rudely, I'd like to explore that
question. Perhaps it should be a FAQ entry.

All code is written by someone, and those people need to eat. Some
people are fully or partly funded to perform their tasks on this  
project
(coding, patching, etc). Others contribute their time for a variety of
reasons where involvement has a positive benefit.

You should ask these questions:
- Is anyone currently working on (Feature X)?
- If not, Can I do it myself?
- If not, and I still want it, can I fund someone else to build it for
me?
Asking "when is Feature X going to happen" is almost certainly  
going to
get the answer "never" otherwise, if the initial development is large
and complex. There are many TODO items that have lain untouched for
years, even though adding the feature has been discussed and agreed.

Best Regards, Simon Riggs
Acceptable Answers to 'So, when/is PG meant to be getting a decent  
partitioning system?':

1. Person X is working on it I believe.
2. It's on the list, but nobody has done anything about it yet
3. Your welcome to take a stab at it, I expect the community  
would support your efforts as well.
4. If you have a huge pile of money you could probably buy the  
Moon. Thinking along those lines, you can probably pay someone to  
write it for you.
5. It's a stupid idea, and it's never going to work, and heres  
why..

Unacceptable Answers to the same question:
1. Yours.
Be more helpful, and less arrogant please. Everyone else who has  
contributed to this thread has been very helpful in clarifying the  
state of affairs and pointing out what work is and isn't being done,  
and alternatives to just waiting for PG do it for you.

---(end of  
broadcast)---
TIP 8: explain analyze is your friend



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Partitioning / Clustering

[David Roussel]

For an interesting look at scalability, clustering, caching, etc for a
large site have a look at how livejournal did it.
http://www.danga.com/words/2004_lisa/lisa04.pdf

They have 2.6 Million active users, posting 200 new blog entries per
minute, plus many comments and countless page views.

Although this system is of a different sort to the type I work on it's
interesting to see how they've made it scale.

They use mysql on dell hardware! And found single master replication did
not scale.  There's a section on multimaster replication, not sure if
they use it.  The main approach they use is to parition users into
spefic database clusters.  Caching is done using memcached at the
application level to avoid hitting the db for rendered pageviews.

It's interesting that the solution livejournal have arrived at is quite
similar in ways to the way google is set up.

David

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] Partitioning / Clustering

[Neil Conway]

Josh Berkus wrote:
The other problem, as I was told it at OSCON, was that these were not 
high-availability clusters; it's impossible to add a server to an existing 
cluster
Yeah, that's a pretty significant problem.
a server going down is liable to take the whole cluster down.
That's news to me. Do you have more information on this?
-Neil
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [PERFORM] Partitioning / Clustering

[Simon Riggs]

On Tue, 2005-05-10 at 11:03 +0100, Alex Stapleton wrote:
> So, when/is PG meant to be getting a decent partitioning system?  

ISTM that your question seems to confuse where code comes from. Without
meaning to pick on you, or reply rudely, I'd like to explore that
question. Perhaps it should be a FAQ entry.

All code is written by someone, and those people need to eat. Some
people are fully or partly funded to perform their tasks on this project
(coding, patching, etc). Others contribute their time for a variety of
reasons where involvement has a positive benefit.

You should ask these questions:
- Is anyone currently working on (Feature X)?
- If not, Can I do it myself?
- If not, and I still want it, can I fund someone else to build it for
me?

Asking "when is Feature X going to happen" is almost certainly going to
get the answer "never" otherwise, if the initial development is large
and complex. There are many TODO items that have lain untouched for
years, even though adding the feature has been discussed and agreed.

Best Regards, Simon Riggs



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] Partitioning / Clustering

[Josh Berkus]

Neil,

> Sure, but that hardly makes it not "usable". Considering the price of
> RAM these days, having enough RAM to hold the database (distributed over
> the entire cluster) is perfectly acceptable for quite a few people.

The other problem, as I was told it at OSCON, was that these were not 
high-availability clusters; it's impossible to add a server to an existing 
cluster, and a server going down is liable to take the whole cluster down.  
Mind you, I've not tried that aspect of it myself; once I saw the ram-only 
rule, we switched to something else.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Partitioning / Clustering

[Neil Conway]

Joshua D. Drake wrote:
Neil Conway wrote:
Oh? What's wrong with MySQL's clustering implementation?
Ram only tables :)
Sure, but that hardly makes it not "usable". Considering the price of 
RAM these days, having enough RAM to hold the database (distributed over 
the entire cluster) is perfectly acceptable for quite a few people.

(Another deficiency is in 4.0, predicates in queries would not be pushed 
down to storage nodes -- so you had to stream the *entire* table over 
the network, and then apply the WHERE clause at the frontend query node. 
That is fixed in 5.0, though.)

-Neil
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [PERFORM] Partitioning / Clustering

[Bruno Wolff III]

On Tue, May 10, 2005 at 08:02:50 -0700,
  Adam Haberlach <[EMAIL PROTECTED]> wrote:
> 
> 
> With all the Opteron v. Xeon around here, and talk of $30,000 machines,
> perhaps it would be worth exploring the option of buying 10 cheapass
> machines for $300 each.  At the moment, that $300 buys you, from Dell, a
> 2.5Ghz Pentium 4 w/ 256mb of RAM and a 40Gb hard drive and gigabit ethernet.
> The aggregate CPU and bandwidth is pretty stupendous, but not as easy to
> harness as a single machine.

That isn't going to be ECC ram. I don't think you really want to use
non-ECC ram in a critical database.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Partitioning / Clustering

[Joshua D. Drake]

Neil Conway wrote:
Josh Berkus wrote:
Don't hold your breath.   MySQL, to judge by their first "clustering" 
implementation, has a *long* way to go before they have anything usable.

Oh? What's wrong with MySQL's clustering implementation?
Ram only tables :)
-Neil
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [PERFORM] Partitioning / Clustering

[Neil Conway]

Josh Berkus wrote:
Don't hold your breath.   MySQL, to judge by their first "clustering" 
implementation, has a *long* way to go before they have anything usable.
Oh? What's wrong with MySQL's clustering implementation?
-Neil
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [PERFORM] Partitioning / Clustering

[Mischa Sandberg]

Quoting "Jim C. Nasby" <[EMAIL PROTECTED]>:

> To the best of my knowledge no such work has been done. There is a
> project (who's name escapes me) that lets you run queries against a
> remote postgresql server from a postgresql connection to a different
> server, which could serve as the basis for what you're proposing.

Okay, if the following looks right to the powerthatbe, I'd like to start
a project. Here's the proposition:

"servername.dbname.schema.object" would change RangeVar, which would
affect much code. "dbname.schema.object" itself is not implemented in
8.0. So, simplicity dictates something like:

table pg_remote(schemaname text, connectby text, remoteschema text)

The pg_statistic info from a remote server cannot be cached in local
pg_statistic, without inventing pseudo reloids as well as a
pseudoschema. Probably cleaner to cache it somewhere else. I'm still
reading down the path that puts pg_statistic data where costsize can get
at it.

First step: find out whether one can link libpq.so to postmaster :-)


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] Partitioning / Clustering

[Mischa Sandberg]

Quoting Christopher Kings-Lynne <[EMAIL PROTECTED]>:

> 
> >>*laff*
> >>Yeah, like they've been working on views for the last 5 years, and
> >>still haven't released them :D :D :D
> > 
> > ? 
> > http://dev.mysql.com/doc/mysql/en/create-view.html
> > ...for MySQL 5.0.1+ ?
> 
> Give me a call when it's RELEASED.


:-) Touche'



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Partitioning / Clustering

[Christopher Kings-Lynne]

*laff*
Yeah, like they've been working on views for the last 5 years, and
still haven't released them :D :D :D

? 
http://dev.mysql.com/doc/mysql/en/create-view.html
...for MySQL 5.0.1+ ?
Give me a call when it's RELEASED.
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Partitioning / Clustering

[Joshua D. Drake]

Mischa Sandberg wrote:
Quoting Christopher Kings-Lynne <[EMAIL PROTECTED]>:

This is why I mention partitioning. It solves this issue by storing
different data sets on different machines under the same schema. 
These seperate chunks of the table can then be replicated as well for 
data redundancy and so on. MySQL are working on these things
*laff*
Yeah, like they've been working on views for the last 5 years, and
still haven't released them :D :D :D

? 
http://dev.mysql.com/doc/mysql/en/create-view.html
...for MySQL 5.0.1+ ?
Yes but MySQL 5 isn't out yet (considered stable).
Sincerely,
Joshua D. Drake


---(end of broadcast)---
TIP 6: Have you searched our list archives?
   http://archives.postgresql.org

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [PERFORM] Partitioning / Clustering

[Mischa Sandberg]

Quoting Christopher Kings-Lynne <[EMAIL PROTECTED]>:

> > This is why I mention partitioning. It solves this issue by storing
> > different data sets on different machines under the same schema. 
> > These seperate chunks of the table can then be replicated as well for 
> > data redundancy and so on. MySQL are working on these things
> *laff*
> Yeah, like they've been working on views for the last 5 years, and
> still haven't released them :D :D :D

? 
http://dev.mysql.com/doc/mysql/en/create-view.html
...for MySQL 5.0.1+ ?


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org