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 1iFdQl-00008u-CV for pgsql-performance@arkaria.postgresql.org; Wed, 02 Oct 2019 12:08:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iFdQk-0002y0-6b for pgsql-performance@arkaria.postgresql.org; Wed, 02 Oct 2019 12:08:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iFdQj-0002xi-Pa for pgsql-performance@lists.postgresql.org; Wed, 02 Oct 2019 12:08:29 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iFdQh-0008LC-37 for pgsql-performance@lists.postgresql.org; Wed, 02 Oct 2019 12:08:29 +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=IRqRCP0IpP0M/nRDwgp2xzZFoLxaIY86rG5jvvwg6XM=; b=M2nb3Nnyhau7aq9KDMkYywzEzT nQ3xnPKkbM9IApE2v8fCVD6xX4+GOYoNvTlx26pvSGA+MYDY0phoSL0tvPkGTjacDotDatZPZ0yDF nT5CA7yzhOv26bCKcfLtD5jYfJy0bsl60jDsISyLry64G1QvCdQ51IV5yofj348n4WLdOipE0WlCl O3wx8oeaY1z5AWZHr1HzqpgDPa3XkULL29LOAUtyYP2OulrF0ZvBYBKVn9BMPZ7lw6nwXdJFsq+4P 56KMnpgiXvmbd4VW+PoDIMXlK2hO48duDHe8QJ1LhioAXbGP3JzGS5QR+6kz6i6yQYPkfI7asqQfB fgDgKFJA==; Received: from [10.9.9.204] (helo=mailfront22.runbox) by mailtransmit03.runbox with esmtp (Exim 4.86_2) (envelope-from ) id 1iFdQg-0006LP-6h for pgsql-performance@lists.postgresql.org; Wed, 02 Oct 2019 14:08:26 +0200 Received: by mailfront22.runbox with esmtpsa [Authenticated alias (300526)] (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) id 1iFdQO-0005Fr-Ll for pgsql-performance@lists.postgresql.org; Wed, 02 Oct 2019 14:08:09 +0200 From: "Mike Sofen" To: References: In-Reply-To: Subject: RE: pg12 - partition by column that might have null values Date: Wed, 2 Oct 2019 05:08:05 -0700 Message-ID: <072201d5791a$0da81ed0$28f85c70$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0723_01D578DF.614A0A20" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIfN2MudS1k9vDwsxzwj3GPWH17ZwIQooH1AReh+VqmmoEOsA== 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_0723_01D578DF.614A0A20 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable From: Mariel Cherkassky =20 Sent: Wednesday, October 02, 2019 12:37 AM Whenever I have a new revision of that object, I update the end_time of = the latest revision to be now() and I add a new record of that object = with end_date null. The null value is used to find most recent revisions of objects.. Thanks for the suggestion of infinity ! I'll try it. =20 My partitioning table design model always uses a partitioning column = that is 100% static since that guarantees that rows are not constantly = moving between partitions (with index update overhead etc). In this = scenario I=E2=80=99d use a =E2=80=9CStartTime=E2=80=9D column to anchor = the row in a partition. The relatively few rows with a null EndTime = don=E2=80=99t need the power of partitioning, just an index to find = them. =20 Mike Sofen ------=_NextPart_000_0723_01D578DF.614A0A20 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

From: Mariel = Cherkassky <mariel.cherkassky@gmail.com>
Sent: = Wednesday, October 02, 2019 12:37 AM
Whenever I have a new revision = of that object, I update the end_time of the latest revision to be now() = and I add a new record of that object with end_date = null.

The null value is used to find most recent = revisions of objects..

Thanks for the suggestion of infinity ! I'll = try it.

 

My partitioning table design model always uses a = partitioning column that is 100% static since that guarantees that rows = are not constantly moving between partitions (with index update overhead = etc). =C2=A0In this scenario I=E2=80=99d use a = =E2=80=9CStartTime=E2=80=9D column to anchor the row in a = partition.=C2=A0 The relatively few rows with a null EndTime = don=E2=80=99t need the power of partitioning, just an index to find = them.

 

Mike Sofen

------=_NextPart_000_0723_01D578DF.614A0A20--