Small rows in a database and the content in an object store, and deleting expired pastes when they are read and by a periodic sweeper.
What you will learn
Keep small metadata rows in a database and the paste content in an object store.
Choose between SQL and NoSQL for a lookup by key.
Delete expired pastes, and answer reads of them correctly before the sweeper runs.
Metadata in a database, content in an object store
The primer separates the two:
A relationaldatabase acts "as a large hash table, mapping the generated url to a file server and path containing the paste file".
Rather than run a file server, use a managed object store such as Amazon S3, or a NoSQLdocument store, for the paste content.
The primer's table:
shortlink char(7) NOT NULL
expiration_length_in_minutes int NOT NULL
created_at datetime NOT NULL
paste_path varchar(255) NOT NULL
PRIMARY KEY(shortlink)
The primary key on shortlink creates an index that enforces uniqueness. The primer adds an index on created_at "to speed up lookups (log-time instead of scanning the entire table) and to keep the data in memory". It gives the reason: reading 1 MB sequentially from memory takes about 250 µs, from SSD 4 times longer and from disk 80 times longer.
Keeping content out of the rows keeps those rows small, so far more of the index fits in memory. The object store holds content cheaply and grows without resharding: the primer notes S3 "can comfortably handle" 12.7 GB of new content per month.
A Bit.ly-style shortener needs no object store at all. Its "content" is the long URL, which fits in the row.
SQL or NoSQL for the metadata
The access pattern is a lookup by primary key, which either kind of store handles well. The primer uses the relational approach, suggests a NoSQLkey-value store as the alternative, and points to its SQL-or-NoSQL trade-offs (chapter 5).
At the primer's 4 writes per second, a single primary with replicas is enough. If writes grow, the primer lists federation, sharding, denormalization and SQL tuning, or moving some data to NoSQL. A key-value store can make sharding by shortlink easier. Some also offer built-in expiry; DynamoDB's TTL is one example (below).
Deleting expired pastes
The primer's approach: scan the database for entries whose expiration time has passed, then delete them or mark them expired. The primer's table stores a length in minutes. Storing the absolute time instead, as an expires_at column with an index, lets the scan touch only expired rows:
SELECT shortlink, paste_path FROM pastes
WHERE expires_at <= NOW()
LIMIT 5000; -- delete in batches, then delete each object from the store
This scan is a sweeper, a job that runs periodically, so a paste can outlive its expiry until the next run. Every read must therefore check expiry itself. If the paste has expired, return an error and trigger its deletion right away.
For the error, RFC 9110 offers 410 Gone for a resource that is "intentionally unavailable", which it says is common "for limited-time, promotional services". A server that cannot tell whether the removal is permanent should send 404 Not Found instead.
Redis expires keys the same two ways. A key is removed "passively" when a client tries to access it after it has timed out. Because "there are expired keys that will never be accessed again", Redis also periodically tests random keys that have an expiry and deletes the expired ones.
Managed TTLs work the same way. DynamoDB "automatically deletes expired items within a few days of their expiration time, without consuming write throughput". Until then, expired items can still appear, so AWS advises filtering them out of query and scan results.
Metadata, object storage and expiry
Small rows in the metadata database, the text in an object store, and expired pastes deleted when they are read or by a periodic sweeper.
Clock: T+100s
Database rows
4 active
Text in object store
15.8 KB
Space freed
0.0 KB
New paste:
Stored pastes and their expiryRead checks the expiry first
/cfg-nginxNginx reverse proxy config
Text: pastes/cfg-nginx.txt (1420 B) • Created: T+60s
Expired
/sql-dumpDatabase migration schemaCached
Text: pastes/sql-dump.sql (8450 B) • Created: T+75s
Expires in 20 s
/jwt-helperOAuth2 JWT signature verifierCached
Text: pastes/jwt-helper.ts (2100 B) • Created: T+95s
Never expires
/err-stackCrash dump stack trace
Text: pastes/err-stack.log (4200 B) • Created: T+40s
Expired
What happened
Each paste has a row in the metadata database and its text in the object store.
API servers2× instancesMetadata database2× instancesObject store (pastetext)1× instanceExpiry sweeper1× instanceAPI servers2× instancesMetadata database2× instancesObject store (pastetext)1× instanceExpiry sweeper1× instance
Figure 3
Small rows in a database, the paste text in an object store, and a sweeper that deletes expired pastes from both.
The API servers keep each paste in two places: its row, with the short link, the expiry and where the text is, in the metadata database, and its text in the object store. Every read checks the expiry in the row, and answers 410 Gone for a paste that has expired. The sweeper runs periodically: it finds the rows whose expiry has passed, using the index on the expiry time, deletes them in batches, and deletes each paste’s text from the object store.
API servers: 2 × App server
Metadata database: 2 × Relational database
Object store (paste text): 1 × Object store
Expiry sweeper: 1 × App server
API servers sends requests to Metadata database
API servers sends requests to Object store (paste text)
Expiry sweeper sends requests to Metadata database
Expiry sweeper sends requests to Object store (paste text)
Key takeaways
Small rows keep the index in memory; an object store holds the content cheaply and grows without resharding. A Bit.ly-style shortener needs no object store.
A lookup by primary key suits either kind of store; at 4 writes a second one primary with replicas is enough.
A periodic sweeper leaves expired pastes behind for a while, so every read must check expiry itself and answer 410 Gone (or 404).
Redis deletes an expired key when it is read and in a periodic background sweep; DynamoDB deletes expired items in the background within a few days, so filter them out of reads.