Query Reddit with source-native SQL

Surface

• Records30.7B+ posts + comments
• Canonicalreddit.comments
• FreshnessLive · see note—the Arctic Shift bulk archive advances by monthly dumps, each landing ~6-7 weeks after its month closes; between dumps the live API tail covers thousands of active subreddits through the breadth lander (on the order of 40k distinct subreddits and 4M comments in a trailing day) but a fraction of a bulk month's volume — never compare the thin post-dump tail to bulk-covered months, and read a fresh lag as the thin tail, never archive-wide coverage
• Extentcreated_utc 2005-12-12 to 2026-09-12
• Cadencepublic archive snapshot
• MethodPublic Reddit archive and source-native comment records.
• StatsLast stats snapshot: 2026-09-12T19:30:04Z

Query surfaces

Best for

Fields

Indexed predicates

• PruneshasToken(search_text_lc, '<lowercase-token>')
• PruneshasAllTokens(search_text_lc, ['<t1>','<t2>'])
• PruneshasAnyTokens(search_text_lc, ['<t1>','<t2>'])
• Prunesauthor = '<exact>'
• Pruneslink_id = 't3_<post_id>' AND parent_id = 't1_<comment_id>' (a comment's direct replies: parent_id alone has no index and reads the whole relation — 9.2B rows past a 20 s deadline; paired with the thread's link_id it is the bloom point lookup, 121 ms measured 2026-09-10)
• Pruneslink_id = 't3_<post_id>' (a post's full comment tree in ONE query — a real point lookup since 2026-09-06: composed bloom pair reads ~82 granules where the old index left ~646M rows and blew 20s deadlines; low seconds even under load)
• Prunesid = '<exact>' (the door adds the id's own created_utc window — comment ids are one global monotone counter and reddit.comment_id_hours maps an id to its hour — so the year partition prunes before the two composed id blooms are read: single id 0.9–1.5 s / 0.6 GB measured 2026-09-12; the same lookup written with no time bound and no rewrite read the blooms end to end, 36 GB / 12 s. Up to 8 ids in an IN list get one window each; more share one hull, so keep lists modest (~10 ids) or add your own created_utc bound, which the door then leaves as written.)

Queries

SELECT id, subreddit, author, created_utc, score, body
FROM reddit.comments
WHERE subreddit = 'MachineLearning'
  AND hasAllTokens(search_text_lc, ['mechanistic', 'interpretability'])
ORDER BY created_utc DESC
LIMIT 50;

SELECT subreddit, count() AS comments, max(created_utc) AS latest
FROM reddit.comments
WHERE subreddit IN ('MachineLearning', 'LocalLLaMA')
GROUP BY subreddit
ORDER BY comments DESC
LIMIT 20;

Gaps

• HolesNone declared on the live schema entry for reddit.comments (declared holes are curation, not a measurement of completeness).
• Notesubmissions live in reddit.posts; join comment trees via link_id = concat('t3_', id)
• Notethe archive advances by monthly bulk dumps, each landing about 6–7 weeks after its month closes; between dumps only a thin tail (~300 subreddits, under 1% of full volume) lands, so the newest weeks are never comparable to bulk-covered months — the Freshness line above names the boundary
• Notedeclared coverage gaps are published on the schema entry (known_holes) — read them before treating a quiet period as real
• Notecompleteness is measured against Reddit's own sequential ID counters and published in each relation's coverage note — ~99.6% of all comment ids from 2023-04 onward are held (the capture ceiling; the rest was deleted before any archive saw it), with the weakest band, 2019→early-2023, identified by month

Related proof pages