Postgresql vs. aggregates

[jao]

I have an application with a table that tracks objects with a "size"
attribute. What we want to do is to periodically report on the number
of these objects and the sum of the object sizes. The table will
typically have 1-5 million rows.

I know that the obvious "select count(*), sum(size) ..." is not a good
idea, and we have seen the impact such a query has on performance.

Then we tried creating triggers to maintain a table with object_count
and sum_size columns:

create table summary(object_count bigint, sum_size bigint)

We populate the table with exactly one row, initialized to (0, 0).
A trigger runs an update like this:

update summary
set object_count = object_count + 1,
sum_size = sum_size + new.size

The problem is that our application has periods where objects are
being created at a rapid rate. Updates to the summary table slow down
over time, and the slowdown is accompanied by very low CPU idle% as
reported by vmstat.

I found that if I have a thread which vacuums the summary table every
few seconds (e.g. 30), then update times stay reasonable. We're
starting to reconsider this approach, wondering if the vacuuming or
the buildup of row versions (in those 30 seconds) is behind some
variability in performance that we're seeing. To that end, we are
measuring the impact of more frequent and less frequent
vacuuming. I'll skip the details here since this is not the main point
of this email.

Another idea we had is to maintain object_count and sum_size in
sequences. The trigger would use currval and setval to maintain the
values. The problem with this is that when we try to examine the
sequence on another connection, we can't use currval before bumping
the value with nextval, which would interfere with maintenance of the
totals. (The error message for a sequence s is "ERROR: s.currval
is not yet defined in this session".)

Yet another idea is to write C functions which maintain counts in
shared memory, simulating what we can't quite do with sequences.

I understand why asking for aggregate values computed over rapidly
changing data sets is not a completely meaningful thing to do. Yet we
have the requirement. Users of our product need to be able to look at
object_count and sum_size to get a sense of how an object load is
proceeding.

This shouldn't be so difficult. I've seen, on this mailing list,
the advice "don't do that, use a trigger" when someone want to do
select count(*). But I haven't seen any discussion of the consequences
of using a trigger.

Can anyone recommend a good way to maintain aggregates using a
trigger? Is vacuuming every few seconds really the only way to go?

Jack Orenstein
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Scott Marlowe]

On Wed, 2004-06-09 at 16:32, ja*@geophile.com wrote:

I have an application with a table that tracks objects with a "size"
attribute. What we want to do is to periodically report on the number
of these objects and the sum of the object sizes. The table will
typically have 1-5 million rows.

I know that the obvious "select count(*), sum(size) ..." is not a good
idea, and we have seen the impact such a query has on performance.

Then we tried creating triggers to maintain a table with object_count
and sum_size columns:

create table summary(object_count bigint, sum_size bigint)

We populate the table with exactly one row, initialized to (0, 0).
A trigger runs an update like this:

update summary
set object_count = object_count + 1,
sum_size = sum_size + new.size

The problem is that our application has periods where objects are
being created at a rapid rate. Updates to the summary table slow down
over time, and the slowdown is accompanied by very low CPU idle% as
reported by vmstat.

I'm wondering if these data are time sensitive, and if so, how
sensitive. If they could be up to say a minute old or something, using
a materialized view of some sort might work well here. Or a temp table
to hold all the incoming data until it needs to be dumped into the real
table.

Just a thought, might not work for you.

Also, do you have a lot of parallel updates to the same rows, or are
they all different rows? If you're doing a lot of updating of the same
rows over and over, any database is going to be somewhat slow, either in
vacuuming like for postgresql, or locking as in row level locking
databases.

If they're all different, then this should be no different for
postgresql than for a row locking database, since each tuple will only
have two or three versions, instead of some smaller percentage of rows
having hundreds of versions.

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

[Scott Marlowe]

On Wed, 2004-06-09 at 16:32, ja*@geophile.com wrote:

I have an application with a table that tracks objects with a "size"
attribute. What we want to do is to periodically report on the number
of these objects and the sum of the object sizes. The table will
typically have 1-5 million rows.

I know that the obvious "select count(*), sum(size) ..." is not a good
idea, and we have seen the impact such a query has on performance.

Then we tried creating triggers to maintain a table with object_count
and sum_size columns:

create table summary(object_count bigint, sum_size bigint)

We populate the table with exactly one row, initialized to (0, 0).
A trigger runs an update like this:

update summary
set object_count = object_count + 1,
sum_size = sum_size + new.size

The problem is that our application has periods where objects are
being created at a rapid rate. Updates to the summary table slow down
over time, and the slowdown is accompanied by very low CPU idle% as
reported by vmstat.

I'm wondering if these data are time sensitive, and if so, how
sensitive. If they could be up to say a minute old or something, using
a materialized view of some sort might work well here. Or a temp table
to hold all the incoming data until it needs to be dumped into the real
table.

Just a thought, might not work for you.

Also, do you have a lot of parallel updates to the same rows, or are
they all different rows? If you're doing a lot of updating of the same
rows over and over, any database is going to be somewhat slow, either in
vacuuming like for postgresql, or locking as in row level locking
databases.

If they're all different, then this should be no different for
postgresql than for a row locking database, since each tuple will only
have two or three versions, instead of some smaller percentage of rows
having hundreds of versions.

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

[jao]

Quoting Scott Marlowe <sm******@qwest.net>:

On Wed, 2004-06-09 at 16:32, ja*@geophile.com wrote:

Then we tried creating triggers to maintain a table with object_count
and sum_size columns:

create table summary(object_count bigint, sum_size bigint)

We populate the table with exactly one row, initialized to (0, 0).
A trigger runs an update like this:

update summary
set object_count = object_count + 1,
sum_size = sum_size + new.size

The problem is that our application has periods where objects are
being created at a rapid rate. Updates to the summary table slow down
over time, and the slowdown is accompanied by very low CPU idle% as
reported by vmstat.
I'm wondering if these data are time sensitive, and if so, how
sensitive. If they could be up to say a minute old or something, using
a materialized view of some sort might work well here. Or a temp table
to hold all the incoming data until it needs to be dumped into the real
table.

So once a minute I'd run a stored procedure to compute statistics
and copy rows to the main table? That might be doable, but then
queries would have to be to a view combining the main table and
the temp table.

Can you expand on the use of materialized views to solve this?
Also, do you have a lot of parallel updates to the same rows, or are
they all different rows? If you're doing a lot of updating of the same
rows over and over, any database is going to be somewhat slow, either in
vacuuming like for postgresql, or locking as in row level locking
databases.

If they're all different, then this should be no different for
postgresql than for a row locking database, since each tuple will only
have two or three versions, instead of some smaller percentage of rows
having hundreds of versions.

The summary table has exactly one row, and whenever an object
is added to the main table, this one summary row is updated. An
odd feature of my application is that there is only one object
creation going on at a time. The summary row would not be a concurrency
hotspot in a non-MVCC database system.

But that raises an interesting idea. Suppose that instead of one
summary row, I had, let's say, 1000. When my application creates
an object, I choose one summary row at random (or round-robin) and update
it. So now, instead of one row with many versions, I have 1000 with 1000x
fewer versions each. When I want object counts and sizes, I'd sum up across
the 1000 summary rows. Would that allow me to maintain performance
for summary updates with less frequent vacuuming?

I'd find all this much easier to reason about if I understood how
the versions of a row are organized and accessed. How does postgresql
locate the correct version of a row?

Jack Orenstein

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[jao]

Quoting Scott Marlowe <sm******@qwest.net>:

On Wed, 2004-06-09 at 16:32, ja*@geophile.com wrote:

Then we tried creating triggers to maintain a table with object_count
and sum_size columns:

create table summary(object_count bigint, sum_size bigint)

We populate the table with exactly one row, initialized to (0, 0).
A trigger runs an update like this:

update summary
set object_count = object_count + 1,
sum_size = sum_size + new.size

The problem is that our application has periods where objects are
being created at a rapid rate. Updates to the summary table slow down
over time, and the slowdown is accompanied by very low CPU idle% as
reported by vmstat.
I'm wondering if these data are time sensitive, and if so, how
sensitive. If they could be up to say a minute old or something, using
a materialized view of some sort might work well here. Or a temp table
to hold all the incoming data until it needs to be dumped into the real
table.

So once a minute I'd run a stored procedure to compute statistics
and copy rows to the main table? That might be doable, but then
queries would have to be to a view combining the main table and
the temp table.

Can you expand on the use of materialized views to solve this?
Also, do you have a lot of parallel updates to the same rows, or are
they all different rows? If you're doing a lot of updating of the same
rows over and over, any database is going to be somewhat slow, either in
vacuuming like for postgresql, or locking as in row level locking
databases.

If they're all different, then this should be no different for
postgresql than for a row locking database, since each tuple will only
have two or three versions, instead of some smaller percentage of rows
having hundreds of versions.

The summary table has exactly one row, and whenever an object
is added to the main table, this one summary row is updated. An
odd feature of my application is that there is only one object
creation going on at a time. The summary row would not be a concurrency
hotspot in a non-MVCC database system.

But that raises an interesting idea. Suppose that instead of one
summary row, I had, let's say, 1000. When my application creates
an object, I choose one summary row at random (or round-robin) and update
it. So now, instead of one row with many versions, I have 1000 with 1000x
fewer versions each. When I want object counts and sizes, I'd sum up across
the 1000 summary rows. Would that allow me to maintain performance
for summary updates with less frequent vacuuming?

I'd find all this much easier to reason about if I understood how
the versions of a row are organized and accessed. How does postgresql
locate the correct version of a row?

Jack Orenstein

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

ja*@geophile.com writes:

I'd find all this much easier to reason about if I understood how
the versions of a row are organized and accessed. How does postgresql
locate the correct version of a row?

It doesn't, particularly. A seqscan will of course visit all the
versions of a row, and an indexscan will visit all the versions
matching the indexscan key-column conditions. It's up to the "time
qualification" tests (tqual.c) to accept only the version that is
visible to your transaction.

There are two or three implementation choices in the btree index
routines that are intended to increase the chances that you'll hit the
version you want sooner rather than later. But they're only heuristics.
The bottom line is that we check all the versions till we find the
right one.

regards, tom lane

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

http://archives.postgresql.org

[Tom Lane]

ja*@geophile.com writes:

I'd find all this much easier to reason about if I understood how
the versions of a row are organized and accessed. How does postgresql
locate the correct version of a row?

It doesn't, particularly. A seqscan will of course visit all the
versions of a row, and an indexscan will visit all the versions
matching the indexscan key-column conditions. It's up to the "time
qualification" tests (tqual.c) to accept only the version that is
visible to your transaction.

There are two or three implementation choices in the btree index
routines that are intended to increase the chances that you'll hit the
version you want sooner rather than later. But they're only heuristics.
The bottom line is that we check all the versions till we find the
right one.

regards, tom lane

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

http://archives.postgresql.org

[Richard Huxton]

ja*@geophile.com wrote:

But that raises an interesting idea. Suppose that instead of one
summary row, I had, let's say, 1000. When my application creates
an object, I choose one summary row at random (or round-robin) and update
it. So now, instead of one row with many versions, I have 1000 with 1000x
fewer versions each. When I want object counts and sizes, I'd sum up across
the 1000 summary rows. Would that allow me to maintain performance
for summary updates with less frequent vacuuming?

Perhaps the simplest approach might be to define the summary table as
containing a SERIAL and your count.
Every time you add another object insert (nextval(...), 1)
Every 10s summarise the table (i.e. replace 10 rows all "scored" 1 with
1 row scored 10)
Use sum() over the much smaller table to find your total.
Vacuum regularly.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Richard Huxton]

ja*@geophile.com wrote:

But that raises an interesting idea. Suppose that instead of one
summary row, I had, let's say, 1000. When my application creates
an object, I choose one summary row at random (or round-robin) and update
it. So now, instead of one row with many versions, I have 1000 with 1000x
fewer versions each. When I want object counts and sizes, I'd sum up across
the 1000 summary rows. Would that allow me to maintain performance
for summary updates with less frequent vacuuming?

Perhaps the simplest approach might be to define the summary table as
containing a SERIAL and your count.
Every time you add another object insert (nextval(...), 1)
Every 10s summarise the table (i.e. replace 10 rows all "scored" 1 with
1 row scored 10)
Use sum() over the much smaller table to find your total.
Vacuum regularly.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Nick Barr]

Hi,

----- Original Message -----
From: "Richard Huxton" <de*@archonet.com>
To: <ja*@geophile.com>
Cc: "Scott Marlowe" <sm******@qwest.net>; <pg***********@postgresql.org>
Sent: Thursday, June 10, 2004 8:03 AM
Subject: Re: [GENERAL] Postgresql vs. aggregates

ja*@geophile.com wrote:

But that raises an interesting idea. Suppose that instead of one
summary row, I had, let's say, 1000. When my application creates
an object, I choose one summary row at random (or round-robin) and update it. So now, instead of one row with many versions, I have 1000 with 1000x fewer versions each. When I want object counts and sizes, I'd sum up across the 1000 summary rows. Would that allow me to maintain performance
for summary updates with less frequent vacuuming?

Perhaps the simplest approach might be to define the summary table as
containing a SERIAL and your count.
Every time you add another object insert (nextval(...), 1)
Every 10s summarise the table (i.e. replace 10 rows all "scored" 1 with
1 row scored 10)
Use sum() over the much smaller table to find your total.
Vacuum regularly.

Something along these lines except using a SUM instead of a COUNT.

http://archives.postgresql.org/pgsql...1/msg00059.php
Nick


---------------------------(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

[Nick Barr]

Hi,

----- Original Message -----
From: "Richard Huxton" <de*@archonet.com>
To: <ja*@geophile.com>
Cc: "Scott Marlowe" <sm******@qwest.net>; <pg***********@postgresql.org>
Sent: Thursday, June 10, 2004 8:03 AM
Subject: Re: [GENERAL] Postgresql vs. aggregates

ja*@geophile.com wrote:

But that raises an interesting idea. Suppose that instead of one
summary row, I had, let's say, 1000. When my application creates
an object, I choose one summary row at random (or round-robin) and update it. So now, instead of one row with many versions, I have 1000 with 1000x fewer versions each. When I want object counts and sizes, I'd sum up across the 1000 summary rows. Would that allow me to maintain performance
for summary updates with less frequent vacuuming?

Perhaps the simplest approach might be to define the summary table as
containing a SERIAL and your count.
Every time you add another object insert (nextval(...), 1)
Every 10s summarise the table (i.e. replace 10 rows all "scored" 1 with
1 row scored 10)
Use sum() over the much smaller table to find your total.
Vacuum regularly.

Something along these lines except using a SUM instead of a COUNT.

http://archives.postgresql.org/pgsql...1/msg00059.php
Nick


---------------------------(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

[Karsten Hilbert]

> I understand why asking for aggregate values computed over rapidly

changing data sets is not a completely meaningful thing to do. Yet we
have the requirement. Users of our product need to be able to look at
object_count and sum_size to get a sense of how an object load is
proceeding.

If the customer just needs to know how things are *proceeding*
will it not suffice to be looking at reasonable estimates ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Karsten Hilbert]

> I understand why asking for aggregate values computed over rapidly

changing data sets is not a completely meaningful thing to do. Yet we
have the requirement. Users of our product need to be able to look at
object_count and sum_size to get a sense of how an object load is
proceeding.

If the customer just needs to know how things are *proceeding*
will it not suffice to be looking at reasonable estimates ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org