pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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 16:14:22 -0700
Message-ID: <00e301d57977$21b58130$65208390$@runbox.com> (raw)
In-Reply-To: <CA+t6e1mLdjA=PF6PO5HY3XoNPwOeHjb3OVY4QRZ5U5iiy-faVA@mail.gmail.com>
References: <CA+t6e1mnD+xP1a0kcuPMRDVUYhJu3rT9fGQ7sPe5=+d5sWykWQ@mail.gmail.com>
	<e506bb38aa4ac923709d075cb0e6e7710fe4c269.camel@cybertec.at>
	<CA+t6e1kGq1Ja-hW88gG7CkGSJBEiCrGrWZc7uOqy3U6Do-G2QA@mail.gmail.com>
	<072201d5791a$0da81ed0$28f85c70$@runbox.com>
	<CA+t6e1mLdjA=PF6PO5HY3XoNPwOeHjb3OVY4QRZ5U5iiy-faVA@mail.gmail.com>

 

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

>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.  Rarely would there be more updated rows than aged/stable rows…in 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’t always being updated.

 

Mike Sofen

view thread (8+ messages)  latest in thread

Message-ID: <00e301d57977$21b58130$65208390$@runbox.com>
Permalink:  ../00e301d57977$21b58130$65208390$@runbox.com/
Also on:    postgresql.org/message-id/00e301d57977$21b58130$65208390$@runbox.com

 · 

reply
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: <00e301d57977$21b58130$65208390$@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