| Lists: | pgsql-hackers |
|---|
| From: | Leonardo Francalanci <m_lists(at)yahoo(dot)it> |
|---|---|
| To: | tgl(at)sss(dot)pgh(dot)pa(dot)us, simon(at)2ndquadrant(dot)com |
| Cc: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: Unlogged tables, persistent kind |
| Date: | 2011-04-25 07:36:35 |
| Message-ID: | 991879.62936.qm@web29012.mail.ird.yahoo.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
The only data we can't rebuild it's the heap. So what about an option for UNlogged indexes on a LOGged table? It would always preserve data, and it would 'only' cost a rebuilding of the indexes in case of an unclean shutdown. I think it would give a boost in performance for all those cases where the IO (especially random IO) is caused by the indexes, and it doesn't look too complicated (but maybe I'm missing something).
I proposed the unlogged to logged patch (BTW has anyone given a look at it?) because we partition data based on a timestamp, and we can risk loosing the last N minutes of data, but after N minutes we want to know data will always be there, so we would like to set a partition table to 'logged'.
Leonardo
| From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
|---|---|
| To: | Leonardo Francalanci <m_lists(at)yahoo(dot)it> |
| Cc: | tgl <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: Unlogged tables, persistent kind |
| Date: | 2011-04-25 08:54:45 |
| Message-ID: | BANLkTin72VMykDBjMsD4Md7BhO4FmcjrLw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
On Mon, Apr 25, 2011 at 8:36 AM, Leonardo Francalanci <m_lists(at)yahoo(dot)it> wrote:
> The only data we can't rebuild it's the heap. So what about an option for UNlogged indexes on a LOGged table? It would always preserve data, and it would 'only' cost a rebuilding of the indexes in case of an unclean shutdown. I think it would give a boost in performance for all those cases where the IO (especially random IO) is caused by the indexes, and it doesn't look too complicated (but maybe I'm missing something).
>
> I proposed the unlogged to logged patch (BTW has anyone given a look at it?) because we partition data based on a timestamp, and we can risk loosing the last N minutes of data, but after N minutes we want to know data will always be there, so we would like to set a partition table to 'logged'.
I agree that unlogged indexes on a logged heap are better for
resilience and are likely to be the best first step.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
| From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
|---|---|
| To: | Leonardo Francalanci <m_lists(at)yahoo(dot)it> |
| Cc: | tgl(at)sss(dot)pgh(dot)pa(dot)us, simon(at)2ndquadrant(dot)com, pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: Unlogged tables, persistent kind |
| Date: | 2011-04-25 12:45:53 |
| Message-ID: | BANLkTikX=TFyyTYw5sG5_mM4FXav7P275A@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
On Mon, Apr 25, 2011 at 3:36 AM, Leonardo Francalanci <m_lists(at)yahoo(dot)it> wrote:
> The only data we can't rebuild it's the heap. So what about an option for UNlogged indexes on a LOGged table? It would always preserve data, and it would 'only' cost a rebuilding of the indexes in case of an unclean shutdown. I think it would give a boost in performance for all those cases where the IO (especially random IO) is caused by the indexes, and it doesn't look too complicated (but maybe I'm missing something).
+1.
> I proposed the unlogged to logged patch (BTW has anyone given a look at it?) because we partition data based on a timestamp, and we can risk loosing the last N minutes of data, but after N minutes we want to know data will always be there, so we would like to set a partition table to 'logged'.
That approach is something I had also given some thought to, and I'm
glad to hear that people are thinking about doing it in the real
world. I'm planning to look at your patch, but I haven't gotten to it
yet, because I'm giving priority to anything that must be done to get
9.1beta1 out the door.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company