From: Mike Sofen <msofen@runbox.com>
To: pgsql-performance@lists.postgresql.org
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> (raw)
In-Reply-To: <CA+t6e1kGq1Ja-hW88gG7CkGSJBEiCrGrWZc7uOqy3U6Do-G2QA@mail.gmail.com>
References: <CA+t6e1mnD+xP1a0kcuPMRDVUYhJu3rT9fGQ7sPe5=+d5sWykWQ@mail.gmail.com>
<e506bb38aa4ac923709d075cb0e6e7710fe4c269.camel@cybertec.at>
<CA+t6e1kGq1Ja-hW88gG7CkGSJBEiCrGrWZc7uOqy3U6Do-G2QA@mail.gmail.com>
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). In this scenario I’d use a “StartTime” column to anchor the row in a partition. The relatively few rows with a null EndTime don’t need the power of partitioning, just an index to find them.
Mike Sofen
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: msofen@runbox.com, pgsql-performance@lists.postgresql.org
Subject: RE: pg12 - partition by column that might have null values
In-Reply-To: <072201d5791a$0da81ed0$28f85c70$@runbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox