On 01/13/2014 01:38 PM, Francisco Olarte wrote:
Hi:
On Mon, Jan 13, 2014 at 5:26 PM, Tirthankar Barari <tbarari@xxxxxxxxxxx> wrote:
On 01/10/2014 07:06 AM, Francisco Olarte wrote:
Not related to your vacuum problem, but if your pattern is something
like deleting everything inserted 15 days ago you may want to think of
using partitioning or simple inheritance. Make the scheduled task
create a new partition/child table, redirect insertions to it, drop
the oldest partition.
...
Thanks for your input. The rentention window was supposed to be variable and
dynamically changeable. So, partitioning is our last resort. Will try the
vacuum after delete instead of autovacuum.
The ability to use partition/inheritance does not depend on an static
window, but on wether your deletion pattern is as described. Supose
you do it daily. You can name your partitions / child_tables as
child_YYYYMMDD. Then to delete data that is >N days old you just build
the cutoff date, select from the system catalog relations whos name is
like child_\d\d\d\d\d\d\d\d , whose name is greater than
child_12345678 ( substitute the curoff date ) and whose parent is the
appropiate table and drop all of them. If the retention window just
grew ( say from 15 to 20 ), the first 5 days you'll find no child
table ( same as when starting, this looks like the window grew from 0
to N ). If it shrank from 15 to 10 the first day you'll drop 10
tables. Depending on how you change the retention window you can also
delete just the appropiate partition, ignoring error in case it does
not exists ( to acomodate window growing cases, you can even use just
a drop if exists ) and when the window shrinks you can zap extra
tables manually or on the procedure which shrinks the window. The
advantage of this is avoiding system catalog query, but I personally
would use first alternative. The logic is much the same as a deleting,
just using partition drops.
Regards.
Francisco Olarte.
My tables are:
table test_metric (
id varchar(255) not null, // just auto generated uuid from app
timestamp timestamp not null,
version int4,
entity_id varchar(255) not null,
primary key (id, timestamp)
);
Indexes:
"test_metric_pkey1" PRIMARY KEY, btree (id)
"test_metric_entity_id_timestamp_key" UNIQUE CONSTRAINT, btree (entity_id, "timestamp")
AND
table test_metric_metrics (
metric_id varchar(255) not null,
metrics float8,
metrics_key varchar(255) not null,
primary key (metric_id, metrics_key)
);
Indexes:
"test_metric_metrics_pkey" PRIMARY KEY, btree (metric_id, metrics_key)
Foreign-key constraints:
"fk3b8e13abb63406d5" FOREIGN KEY (metric_id) REFERENCES test_metric(id)
Basically, test_metric holds the timestamp and some metadata and test_metric_metrics holds the set of key/value pairs for the give entity and timestamp in the parent table.
Is it possible to partition the second table by timestamp field from first table?
We are using postgres 9.2.2
Thanks,
- Tirthankar
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
