Re: Distributed Table Partitioning

oops! Better example
eg: {note: below is psuedo code}
child {master} (SSD) NO ROWS33
tg_insert_child before insert execute tgf_split_data
child1 (SSD) CONSTRAINT timestamp > {specified time}
child2 (SATA) CONSTRAINT timestamp <= {specified time}

tgf_split_data()
if timestamp > {specified time}
insert into child1
else
insert into child2
endif

On Sat, Mar 12, 2016 at 9:19 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
> On Sat, Mar 12, 2016 at 8:33 PM, Alvaro Aguayo Garcia-Rada <
> aaguayo(at)opensysperu(dot)com> wrote:
>
>> Hi. I think pgpool-II can do that job for you. It's a middleware, so you
>> can use it without even changing your app code(but your postgres
>> configuration). It suppoerts many clustering functions, including
>> replication, failover, and a lot more; it also supports partitioning. so
>> that may be suitable for you. Check the tutorial, it even has some
>> examples: http://www.pgpool.net/docs/latest/tutorial-en.html
>>
>> Alvaro Aguayo
>> Jefe de Operaciones
>> Open Comb Systems E.I.R.L.
>>
>> Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51)
>> 954183248
>> Website: www.ocs.pe
>>
>> ----- Original Message -----
>> From: "Leonardo M. Ramé" <l(dot)rame(at)griensu(dot)com>
>> To: "PostgreSql-general" <pgsql-general(at)postgresql(dot)org>
>> Sent: Saturday, 12 March, 2016 8:25:01 PM
>> Subject: [GENERAL] Distributed Table Partitioning
>>
>> I have this problem: a Master table containing records with a timestamp
>> column registering creation date-time, and one Detail table containing
>> info related to the Master table.
>>
>> As time went by, those tables grew enormously, and I can't afford
>> expanding my SSD VPS. So I'm thinking about storing only NEW data into
>> it, and move OLD data to a cheaper SATA VPS.
>>
>> The goal is using the SSD server as "main", and the other (or others?)
>> as "child", so queries still go to the main server, it somehow detects
>> which records must be fetched from it and what from the child servers,
>> then return the "composed" dataset to the caller.
>>
>> I think this is called Distributed Horizontal Table Partitioning.
>>
>>
>> Is there a way to do this without changing my application code?.
>>
>> Regards,
>> --
>> Leonardo M. Ramé
>> Medical IT - Griensu S.A.
>> Av. Colón 636 - Piso 8 Of. A
>> X5000EPT -- Córdoba
>> Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
>> Cel.: +54 9 (011) 40871877
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
> Why don't you just make use of tablespaces and partition the child
> tablespaces
> so that the newer parttion is on the SSD and the older one is on SATA?
> You will need a trigger and tg function to handle inserts
>
> eg: {note: below is psuedo code}
> child {master} (SSD) NO ROWS33
> child1 (SSD) CONSTRAINT timestamp > {specified time}
> tg_insert_child1 on insert execute tgf_split_data
> child2 (SATA) CONSTRAINT timestamp <= {specified time}
> tg_insert_child2 on insert execute tgf_split_data
>
> tgf_split_data()
> if timestamp > {specified time}
> insert into child1
> else
> insert into child2
> endif
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.