| From: | "Mike Sofen" <msofen(at)runbox(dot)com> |
|---|---|
| To: | <pgsql-performance(at)lists(dot)postgresql(dot)org> |
| Subject: | RE: pg12 - partition by column that might have null values |
| Date: | 2019-10-02 23:14:22 |
| Message-ID: | 00e301d57977$21b58130$65208390$@runbox.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
>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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Mariel Cherkassky | 2019-10-03 07:30:20 | Re: pg12 - partition by column that might have null values |
| Previous Message | Mariel Cherkassky | 2019-10-02 13:44:31 | Re: pg12 - partition by column that might have null values |