Hi Luiz:
On Thu, Sep 14, 2017 at 11:06 PM, Luiz Hugo Ronqui
<lronqui@tce.sp.gov.br> wrote:
...
> We have a database with data being inserted for almost 10 years and no
> policy defined to get rid of old records, even though we mostly use only the
> current and last year's data. Some etl processes run on older data from time
> to time.
> After this time, some tables have grown to a point where even their indexes
> are bigger than the server's available RAM. Because some queries were
> getting slower, despite optimizations, we started experimenting with table
> partitioning.
> The idea was creating 3 partitions for each table of interest: the "hot",
> the "warm" and the "cold". The first would have the last 2 years. The
> second, data from 3 to 5 years and the third, all the rest.
I would consider using more than one cold partition, and maybe moving
them AND warm to a different schema. Maybe 5 years in each, something
like cold-2000-2009, cold-2010-2019. You can update the constraints
adequately, but the thing is you periodically update your constraints
in the hot, warm and last cold, moving data among them appropiately,
then do a really good backup of warm and colds and you can forget
about them in daily backups, and also if you want to drop "stale" in
the future, or un-inherit them to speed up queries, it is easier to
do.
...
> Then one thing came to mind: Why not to use the "parent" table as the hot
> one, without doing any redirection at all? That way we could:
> 1) Keep the referential integrity of the current model untouched;
> 2) Dismiss the trigger redirection along with the hybernate issue;
> 3) Have a much smaller dataset to use in most of our queries;
> 4) Have all the historic data when needed
You can do it, but remember parent normally does not have constraints,
so it is always scanned ( fastly as it is known empty ). Also select
from only parent is useful to detect when you are missing partitions,
won't work in this case. But you can test it.
...
> I have run some basic tests and all seemed to work as expected, but since I
> couldn't find any use of the parent table besides being the head of the
> hierarchy, I am affraid of doing something that could stop because it wasnt
> designed to work like that to begin with...
Seems fine to me. Never used that because y normally use special
insertion programs for my partitiones tables ( my usage allows thats
), so I insert directly in the appropiate partition always ( so I just
use inheritance, no triggers or rules ).
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general