Automatically expire rows in Postgres
source link: https://schinckel.net/2021/09/09/automatically-expire-rows-in-postgres/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Automatically expire rows in Postgres
Here’s a fun idea: how to make a database table where the data is kept only for a certain period of time?
One solution could be to have a column updated_at
, which is set to the current timestamp each time a row is updated. Then you need a scheduled task that periodically clears out rows older than the threshold.
We can do this in a single trigger function:
CREATE OR REPLACE FUNCTION keep_for() RETURNS TRIGGER AS $$
DECLARE
primary_key_name TEXT = (
SELECT attname
FROM pg_index
JOIN pg_attribute ON
attrelid = indrelid
AND attnum = ANY(indkey)
WHERE indrelid = TG_RELID AND indisprimary
);
primary_key_value TEXT;
BEGIN
IF TG_WHEN <> 'BEFORE' THEN
RAISE EXCEPTION 'keep_for() may only run as a BEFORE trigger';
END IF ;
IF TG_ARGV[0]::INTERVAL IS NULL THEN
RAISE EXCEPTION 'keep_for() must be installed with an INTERVAL to keep data for';
END IF;
NEW.updated_at = now();
IF TG_OP = 'INSERT' THEN
EXECUTE 'DELETE FROM ' || quote_ident(TG_TABLE_NAME) || ' WHERE updated_at < now() - INTERVAL ' || quote_literal(TG_ARGV[0]::TEXT) || ';';
ELSIF TG_OP = 'UPDATE' THEN
EXECUTE 'DELETE FROM ' || quote_ident(TG_TABLE_NAME)
|| ' WHERE updated_at < now() - INTERVAL ' || quote_literal(TG_ARGV[0]::TEXT)
|| ' AND ' || quote_ident(primary_key_name) || ' <> ' || quote_literal(row_to_json(NEW) ->> primary_key_name) || ';';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Now, we can install this on a table (that has a single primary key column):
CREATE TRIGGER keep_for_one_week
BEFORE UPDATE OR INSERT ON <target_table>
FOR EACH ROW
EXECUTE PROCEDURE keep_for('1 week');
This only works on tables that are updated somewhat regularly, and that are not too big. You’ll also want an index on that updated_by
column.
This was a thought experiment (which I did implement), until my co-worker pointed out that there was no reason to actually store this data in the database to begin with, since we already use Redis we can just set the TTL on the key when we set it, and it will automatically expire without us having to do extra work.
Recommend
-
6
100,000 Bitcoin Options Contracts Set to Expire – Trustnodes100,000 Bitcoin Options Contracts Set to Expire – Trustnodes“29JAN, the monster expiry, will be the biggest expiry Deribit has seen to date!” So said the bitcoin and ethere...
-
6
Discussion (6) CollapseExpandI have a web site with ".com" as the publicized domain. Many years ago, before they started expanding TLDs a lot, I had registered the matching .net, .org, .info, .ws and a couple...
-
1
April 30, 2021
-
3
Redis源码剖析之数据过期(expire) | XINDOO我之前统计过我们线上某redis数据被访问的时间分布,大概90%的请求只会访问最新15分钟的数据,99%的请求访问最新1小时的数据,只有不到千分之一的请求会访问超过1天的数据。我们之前这份数据存了两天(近500g内存数据...
-
6
$1.8 Billion Bitcoin Options to Expire – TrustnodesIt’s the last Friday of the month with $1.8 billion worth of bitcoin options to expire tomorrow on Deribit, a futures and options platform. That makes it one of the biggest expiries...
-
1
Industry Lowest SMX Convert rates expire Saturday Book by this Saturday, July 31 to get your All Access p...
-
7
Let's Encrypt's Root Certificate is About To Expire, and It Might Break Your Devices Try the CryptoTab Browser. It works...
-
2
SEO Best SMX rates expire this Saturday… book now and save! Don’t miss your opportunity to learn brand-safe, actionable ta...
-
2
Users recommended to update as soon as possible Windows 11 is projected to start rolling out to the very first non-Insider devices as soon as the next week, but needless to say, Microsoft will conti...
-
2
How Postgres Stores RowsPosted at — Feb 7, 2022Out of curiosity, I was trying to understand how PostgreSQL stores the data onto the disk and there are a few interesting things that I have noticed that might be u...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK