Re: Distributed Table Partitioning

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.