Database Schema for Expiration Tracking
You'll learn how to design database tables and indexes that efficiently track and query expired pastes.
The Core Challenge
When users create pastes with time-to-live (TTL) settings, your system needs to quickly identify which pastes have expired—both to prevent retrieval and to enable cleanup processes. Storing an expiration timestamp isn't enough; you must design your schema and indexing strategy to make expiration queries fast at scale.
Schema Design
Your paste metadata table should include:
CREATE TABLE pastes (
paste_id VARCHAR(10) PRIMARY KEY,
user_id VARCHAR(36),
created_at TIMESTAMP NOT NULL,
expires_at TIMESTAMP, -- NULL means never expires
storage_path VARCHAR(255),
size_bytes INT,
...
);
The expires_at column is your anchor. Set it to NULL for permanent pastes or a future timestamp for temporary ones.
Indexing Strategy
Create a composite index on expiration status:
CREATE INDEX idx_expiration
ON pastes(expires_at, paste_id)
WHERE expires_at IS NOT NULL;
This partial index only covers rows that actually expire, saving space and improving query speed. The index lets you efficiently find all expired pastes:
SELECT paste_id FROM pastes
WHERE expires_at <= CURRENT_TIMESTAMP
AND expires_at IS NOT NULL
LIMIT 1000;
Efficient Querying
When a user retrieves a paste, check expiration inline:
SELECT * FROM pastes
WHERE paste_id = ?
AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP);
For cleanup jobs, batch-process expired pastes periodically using the index, avoiding full table scans.
Key Takeaway: Design your expiration schema with a nullable expires_at timestamp, create a partial index on non-null values, and structure queries to leverage that index for both retrieval validation and efficient batch cleanup operations.