Thread: Partitions and work_mem?

I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how work_mem and partitions interact.

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem

The above wiki states that "if a query involves doing merge sorts of 8 tables, that requires 8 times work_mem." If I have a table that is partitioned does each partition count as a "table" and get its on work_mem?

For example, say I have the following table partitioned by the time column:

CREATE TABLE values (time TIMESTAMP, value INTEGER);

If I do the following query will it require 1 work_mem or N work_mem's (where N is the number of partitions)?

SELECT * FROM values ORDER BY time;

On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

BTW, 8.4 is EOL.  Maybe time to upgrade?

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Johansen
Sent: Wednesday, October 15, 2014 4:05 PM
To: Josh Berkus
Cc: pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

 

Thanks for the feedback. That's very helpful.

 

BTW, 8.4 is EOL.  Maybe time to upgrade?

 

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens.

 

 

Postgres 8.4 is EOL (RHEL).

 

 

From: Dave Johansen [mailto:davejohansen@gmail.com]
Sent: Wednesday, October 15, 2014 4:20 PM
To: Igor Neyman
Cc: Josh Berkus; pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 1:08 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Dave Johansen
Sent: Wednesday, October 15, 2014 4:05 PM
To: Josh Berkus
Cc: pgsql-performance
Subject: Re: [PERFORM] Partitions and work_mem?

 

On Wed, Oct 15, 2014 at 10:10 AM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/14/2014 10:08 AM, Dave Johansen wrote:
> I'm running Postgres 8.4 on RHEL 6 64-bit and I had a question about how
> work_mem and partitions interact.
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem
> The above wiki states that "if a query involves doing merge sorts of 8
> tables, that requires 8 times work_mem." If I have a table that is
> partitioned does each partition count as a "table" and get its on work_mem?

In theory, this could happen.  In practice, based on tests I did at Sun
with DBT3 and 8.3, no backend ever used more than 3X work_mem.  This is
partly because the level of parallelism in postgres is extremely
limited, so we can't actually sort 8 partitions at the same time.

 

Thanks for the feedback. That's very helpful.

 

BTW, 8.4 is EOL.  Maybe time to upgrade?

 

RHEL 6 isn't EOLed and we're working on moving to RHEL 7 but it's a slow process that will probably take quite a bit of time, if it ever happens.

 

 

Postgres 8.4 is EOL (RHEL).

 

Sorry I don't understand what you mean by that. My understanding is that RedHat maintains fixes for security and other major issues for packages that have been EOLed. Are you implying that that's not the case? Or something else?

 

I don’t think that RedHat can maintain Postgres version which was EOLed.

Postgres 8.4 is not supported by PostgreSQL community.

On 10/15/2014 01:19 PM, Dave Johansen wrote:
> Sorry I don't understand what you mean by that. My understanding is that
> RedHat maintains fixes for security and other major issues for packages
> that have been EOLed. Are you implying that that's not the case? Or
> something else?

RH probably backpatches our fixes as they come out.  They did in the
past, anyway.

I just had the impression from your original post that this was a new
system; if so, it would make sense to build it on a version of Postgres
which wasn't already EOL.

Magnus Hagander <magnus@hagander.net> writes:
> On Oct 16, 2014 12:58 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> That is in fact exactly what people pay Red Hat to do, and it was my job
>> to do it for Postgres when I worked there.  I don't work there any more,
>> but I'm sure my replacement is entirely capable of back-patching fixes as
>> needed.

> Do they backpatch everything, or just things like security issues? (in sure
> they can do either, but do you know what the policy says?)

Security issues are high priority to fix, otherwise it takes (usually)
complaints from paying customers and/or effective lobbying from the
package's maintainer.  They have finite bandwidth for package updates,
and they also take seriously the idea that a RHEL release series is
supposed to be a stable platform.  When I was there I was usually able
to get them to update to new PG minor releases only when said releases
involved security fixes, otherwise the can got kicked down the road...

> Either way it does also mean that the support requests for such versions
> would need to go to redhat rather than the community lists at some point -
> right now their 8.4 would be almost the same as ours, but down the road
> they'll start separating more and more of course.

If you want a fix in Red Hat's version of 8.4, you need to be talking to
them *now*, not "at some point".  The community lost any input into that
when we stopped updating 8.4.

> For the op - of you haven't already, is suggest you take a look at
> yum.postgresql.org which will get you a modern, supported, postgresql
> version for rhel 6. Regardless of the support, you get all the other
> improvements in postgresql.

Yeah.  Also, Red Hat is shipping a newer version (I think 9.2.something)
as part of their "software collections" packaging initiative.  I do not
know whether that's included in a standard RHEL subscription or costs
extra.