Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iFnpd-0007vf-Ai for pgsql-performance@arkaria.postgresql.org; Wed, 02 Oct 2019 23:14:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iFnpb-0004q2-VD for pgsql-performance@arkaria.postgresql.org; Wed, 02 Oct 2019 23:14:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iFnpb-0004po-Fg for pgsql-performance@lists.postgresql.org; Wed, 02 Oct 2019 23:14:51 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iFnpX-0007au-SC for pgsql-performance@lists.postgresql.org; Wed, 02 Oct 2019 23:14:50 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=runbox.com; s=rbselector1; h=Content-Type:MIME-Version:Message-ID:Date:Subject: In-Reply-To:References:To:From; bh=KqnY5Q29IxWrXC6jvIUpLDkaOvkgZewMjMAwQ02Sk/4=; b=Eh43u+RKY22dMXcdjMISYfiJVt UF4C3ESTaX2I7e1tIdlCPBNK3KZNGBP/H9fpXqkcaqHanfCCQ8bNbAesYR/cVy6tuY5dTSExER1+B hcQiuSFZDfDedgM54Aly0XaoV6ek8pqS6ESL3NNTXoTcEJtFmtgzP3kT/LfuCv/9hqYubOEuhTEae +LiqXQ5Q4QiJfMmlmdBf3QlQ5OAPo78VILkC/IwBLxrZSgsCxVIe23kpSJKnCV62DBwyC8uHIRtFC lfJktv0y2QPMhGPOJK9+GN6CUhm5uBdHJuw0mT3CqfAJG/9IXFqwOFvWBw0SNGgGbphpkKNBsgqGd Qiyv3YyA==; Received: from [10.9.9.202] (helo=mailfront20.runbox) by mailtransmit03.runbox with esmtp (Exim 4.86_2) (envelope-from ) id 1iFnpV-00020S-4r for pgsql-performance@lists.postgresql.org; Thu, 03 Oct 2019 01:14:45 +0200 Received: by mailfront20.runbox with esmtpsa [Authenticated alias (300526)] (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) id 1iFnpB-0000cJ-PC for pgsql-performance@lists.postgresql.org; Thu, 03 Oct 2019 01:14:26 +0200 From: "Mike Sofen" To: References: <072201d5791a$0da81ed0$28f85c70$@runbox.com> In-Reply-To: Subject: RE: pg12 - partition by column that might have null values Date: Wed, 2 Oct 2019 16:14:22 -0700 Message-ID: <00e301d57977$21b58130$65208390$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00E4_01D5793C.7559B670" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIfN2MudS1k9vDwsxzwj3GPWH17ZwIQooH1AReh+VoBlr/jhAEm6hQVpoVN1EA= Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00E4_01D5793C.7559B670 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable =20 >but the start time doesnt indicates that the object is the most recent, = it just indicates when the object was added to your table=E2=80=A6 >on each partition I'll create range partition on the end_date so that I = can search for revisions faster. =20 I believe you are confusing data storage with query optimization. = Rarely would there be more updated rows than aged/stable rows=E2=80=A6in = the normal system, having even 3% of the data in churn (updateable) = state would be unusual and your description of the data dynamics on this = table said that a row updated once, gets the end_date set and then a new = row is created. =20 To me, that says, put an index on end_date so you can find/query them = quickly, and create partitions on a static date so the rows (and = indexes) aren=E2=80=99t always being updated. =20 Mike Sofen ------=_NextPart_000_00E4_01D5793C.7559B670 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

 

>but the start time = doesnt indicates that the object is the most recent, it just indicates = when the object was added to your table=E2=80=A6

>on each = partition I'll create range partition on the end_date so that I can = search for revisions faster.

 

I believe = you are confusing data storage with query optimization.=C2=A0 Rarely = would there be more updated rows than aged/stable rows=E2=80=A6in the = normal system, having even 3% of the data in churn (updateable) state = would be unusual and your description of the data dynamics on this table = said that a row updated once, gets the end_date set and then a new row = is created.

 

To me, that says, put an index on end_date so you can = find/query them quickly, and create partitions on a static date so the = rows (and indexes) aren=E2=80=99t always being updated.

 

Mike = Sofen

------=_NextPart_000_00E4_01D5793C.7559B670--