Storage: SQL or NoSQL, object storage and expiry

Metadata in a database, content in an object store

The primer separates the two:

• A relational database 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 NoSQL document 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 NoSQL key-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

The sweeper 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.