Course contentsShow
← System Design
Lesson 1566 of 1,91938. Real-World System: PastebinFree lesson

Database Schema for Expiration Tracking

Designing tables with expiration timestamps, indexing strategies, and efficient querying of expired content.

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.