Why is truncate DDL

You seem to be misinterpreting a part of my advice to your previous question:

so I can not Truncate and rename the temp_tables since the system should be on all the time.

There was no renaming involved. After TRUNCATE you run an INSERT. The only blocking operation is the TRUNCATE. I quote the manual:

TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table. (...) If concurrent access to a table is required, then the DELETE command should be used instead.

INSERT may still work, if it is not time-critical and allowed to wait until the lock is released. TRUNCATE is usually very fast, if you run it in a separate transaction, it should only block for a couple of milliseconds. Note: separate transaction, not separate session! Your temporary table lives and dies with the session.

The drawback of a separate transaction: if you lose your session before you are able to re-insert from the temporary table, you loose data. To be sure, you could use a plain table instead. (Which would offer the alternative path to drop the old and rename the new table.)

Either way, your updated question makes clear that you want to run this repeatedly, accumulating old rows in the same table. In this case, TRUNCATE is not a good option anyway. You can always just use a plain DELETE. Considerably slower with big tables, but concurrent INSERT is not blocked at all. The autovacuuming daemon will have to do some more work, too.

It's mostly a trade-off between speed and security.

SQL

The DELETE command for the slow and sure method could look like this:

DELETE FROM TABLE tbl t
USING (
   SELECT created_at FROM tbl WHERE created_at < now() - (interval '3 month')
   ) d
LEFT   JOIN (
   SELECT min(created_at) AS created_at
   FROM   tbl
   GROUP  BY date_trunc('minute', created_at)
   WHERE  created_at < now() - (interval '3 month')
   ) x USING (created_at)
WHERE  x.created_at IS NULL
AND    d.created_at = t.created_at;

There is more than one possibility here.

One is what Peter Eisentraut suggested in the post you linked, using a writable CTE which deletes the rows from the table which will be processed or inserted elsewhere in the same query.

This approach may be viable with small datasets. However, there are chances that some table bloat will occur with time, or concurrent inserts and deletes clash on the disk I/O level.

Another way to solve this is using transaction blocks, as you also suggested. If there are open transactions (that do the inserts, for example) on new, the TRUNCATE command will wait until these are closed, then it will remove everything. This means that the problem you anticipate really exists :)

Consider the following:

BEGIN;

LOCK new; -- takes an exclusive lock

INSERT INTO staging_table SELECT * FROM new;

TRUNCATE new;

COMMIT;

In this case, the exclusive lock will make all concurrent INSERT statements wait until the COMMIT. As the INSERT in this block should be relatively fast, the waiting time won't be terribly long, while your existing data is safely moved to an other table. Then comes the TRUNCATE, finally the COMMIT will release the lock and the waiting inserts can be done.

This way you can move the data processing from new to the newly introduced table.