On Fri, Aug 19, 2016 at 12:44 PM, Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote: > for append-only tables like this consider 9.5 and BRIN-Indexes for > timestamp-searches. But if you deletes after N weeks BRIN shouldn't work > properly because of vacuum and re-use of space within the table. > Do you know BRIN? > > So, in your case, consider partitioning, maybe per month. So you can > also avoid mess with table and index bloat. If done properly he can use both. For 6 weeks I would use seven partition, current+6 previous, drop old partition weekly, so effectively they become append only and he can use BRIN too. Even better, if he normally inserts in batches ( it happens to me with some log-like data, I rotate the file and insert all rotated data periodically ) he can use a staging table ( 1 master, and inheriting from it seven constrained week partition plus one unconstrained staging partition). Insertions go into staging and are moved with a small delay to the corresponding partition, using and ordered select so they go in perfect order into their final resting place and it can be vacuumed just after that ( if they are log lines and the maximum delay is X you just move every row older than that from staging to the partition with whatever period is best). Staging partition is normally small and cached and can be processed quite fast ( with 200k/day an hourly movement will leave staging with less than about 10k rows if distribution is somehow uniform ). Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
