ClickHouse RSS Feed: Build It From a Fast Query

Short answer: If your articles, links or news items already live in ClickHouse, you can build an RSS feed from it with a small script that runs a read-only query for the newest rows, renders the XML and writes the file atomically on a schedule. Use a table design that handles updates, add a time limit and a row limit to the query, filter on published status and serve the finished file as a static address. PostRSS can then read that address and post each new item to your social networks.

ClickHouse is built for fast analytical queries over very large tables, so it is not the first database most people think of for an RSS feed. Yet it is increasingly the place where aggregated content ends up: news monitoring systems, link collectors, price trackers, changelog pipelines and event databases. When the interesting data is already there, building a feed directly from it is more reliable than copying it to another database just to serve a few dozen rows. This guide explains how to model the table, write the query, generate valid XML, schedule the job and avoid the traps that are specific to ClickHouse, such as eventual deduplication and the cost of careless queries.

When a ClickHouse feed makes sense

A feed from ClickHouse fits when your pipeline already stores items there: headlines collected from sources, press releases, product updates, job listings, security advisories or records produced by your own application. It is a poor fit for a normal blog with a few hundred posts, where the content system already produces a feed. Do not move content into ClickHouse only to generate XML. Use it when it is the system of record for the items you want to publish.

Design a table that can be fed to a feed

An RSS item needs a stable identifier, a title, a link, a publication time, a summary and optionally an image. ClickHouse tables are append-oriented, so updates are modeled as new rows. A ReplacingMergeTree table with a version column keeps the latest version of each item after background merges.

CREATE TABLE news_items
(
    item_id     UInt64,
    title       String,
    link        String,
    summary     String,
    image_url   String,
    published_at DateTime('UTC'),
    status      LowCardinality(String),
    version     UInt32
)
ENGINE = ReplacingMergeTree(version)
ORDER BY item_id;

The identifier is the sort key, which is what the engine uses to find duplicates. The status column lets you hide an item by inserting a newer version with the status set to hidden, instead of trying to delete data. The publication time is stored in UTC so that the feed code does not have to guess time zones.

Understand eventual deduplication

The most important ClickHouse detail for a feed is that duplicates are removed only when background merges happen. Right after an update, both the old and the new version of an item can exist, and a plain query may return both. For a feed, that means the same article could appear twice in the XML, which an auto-poster might treat as two different items if the identifiers differ, or at least as noise.

There are two sound ways to handle it. The first is to add the FINAL modifier to the query, which merges versions at read time. The second is to group by the identifier and take the row with the highest version using an aggregate function such as argMax. For a query that returns only the newest thirty items, FINAL is simple and fast enough. For very large tables, the grouped form can be cheaper because it avoids merging everything.

Write the query

The query should return a small, ordered set of rows and nothing else. Always filter on the status and on a publication time that is not in the future, always order by publication time descending and always limit the rows.

SELECT item_id, title, link, summary, image_url, published_at
FROM news_items FINAL
WHERE status = 'published'
  AND published_at <= now()
ORDER BY published_at DESC
LIMIT 30

Thirty items are plenty for a feed, and most readers and auto-posters only look at the newest few. If your table is partitioned by month, a condition on the publication time also lets ClickHouse skip old partitions, which keeps the query fast no matter how large the table grows.

Use a read-only account with limits

The feed job should connect with a dedicated user that can only read the tables it needs. In addition, set query limits for that user: a maximum execution time, a maximum number of result rows and a memory limit. These settings are your seatbelt. If someone edits the query and forgets the limit, or the table grows and the plan changes, the database refuses to run away with resources. Never use an administrative account for a public feed job.

Run the job with a small script

A short Python script with the clickhouse-connect client is enough. It runs the query, renders the XML and writes the file.

import clickhouse_connect, os, tempfile
from xml.sax.saxutils import escape
from email.utils import format_datetime

client = clickhouse_connect.get_client(
    host="clickhouse", username="feed_reader", password=os.environ["CH_PASSWORD"]
)

QUERY = """
SELECT item_id, title, link, summary, image_url, published_at
FROM news_items FINAL
WHERE status = 'published' AND published_at <= now()
ORDER BY published_at DESC
LIMIT 30
"""

def build():
    rows = client.query(QUERY).named_results()
    items = []
    for r in rows:
        items.append(
            "<item><title>%s</title><link>%s</link>"
            "<guid isPermaLink=\"false\">%s</guid>"
            "<pubDate>%s</pubDate><description>%s</description></item>"
            % (escape(r["title"]), escape(r["link"]), r["item_id"],
               format_datetime(r["published_at"]), escape(r["summary"]))
        )
    return "".join(items)

This sample builds only the item list. The surrounding channel element needs a title, a link, a description and ideally a last build date. Use a real XML library or a tested template for the full document instead of string concatenation if your content can contain unusual characters, and always escape text.

Make dates and identifiers correct

The publication date in RSS follows the format of an internet message date, with a time zone. Because ClickHouse returns a timestamp in UTC, convert it with a standard library function and do not invent your own format. Use the item identifier as the GUID and keep it stable forever. If the identifier changes when an item is updated, auto-posters will see a new item and may post it again.

Write the file atomically

As with every generated feed, write to a temporary file in the target directory and rename it over the final name. The rename is atomic on the same filesystem, so a reader never sees a half-written document. If you publish to object storage, upload the complete object in a single request.

def write_atomic(path, xml):
    fd, tmp = tempfile.mkstemp(dir=os.path.dirname(path), suffix=".tmp")
    with os.fdopen(fd, "w", encoding="utf-8") as handle:
        handle.write(xml)
    os.chmod(tmp, 0o644)
    os.replace(tmp, path)

Schedule it sensibly

Analytical databases are happy to answer small queries, but there is no reason to run one every second. A schedule of one to five minutes suits most pipelines. If items arrive in bursts, for example after a crawler finishes a batch, you can trigger the job at the end of the batch in addition to the timer. Keep the timer as a safety net, so that a missed trigger never leaves the feed stale for long.

Protect against empty or broken results

A temporary connection problem could produce an empty result, and a naive script would then overwrite a good feed with an empty one. Add a guard: if the query returns no rows, or fewer rows than a sensible minimum, keep the old file and log a warning. Also validate the generated XML by parsing it before the rename. These two checks take a few lines and prevent the most embarrassing failures.

Monitor the freshness

Measure the age of the feed file and the age of its newest item, and compare the newest item with the newest published row in the table. If the file is fresh but the newest item is old, the pipeline that fills the table may be stuck. If the file itself is old, the job is not running. Alert on both. A simple external check that downloads the public feed and parses it is the most reliable test, because it exercises the whole chain.

Write a useful summary and image

Rows from an analytical pipeline often have terse titles and long raw text. The summary of a feed item becomes the caption of a social post, so it deserves care. Store a short, human-readable summary in its own column, ideally one or two sentences, and keep it free of markup. If the raw text is long, cut it at a sentence boundary when you write the row, not at render time, so every consumer sees the same summary. For the image, store a full public address to a picture that opens without a login, and prefer a size that looks good as a preview. Items without an image still work, but posts with a picture get more attention.

Test with a copy of real data

Before you publish the address, run the job against a copy of production data and check the result by hand. Look at the first five items: are the titles readable, are the links absolute and correct, are the dates in the right order and does the image load? Insert a new version of one item and confirm that the feed shows it once, not twice. Insert a hidden item and confirm that it does not appear. Finally, run the XML through a standard validator. This half hour reveals the problems that would otherwise surface in public, on a social network, in front of your audience.

Plan for growth

ClickHouse tables grow fast, and a feed query that is fine today may be slow in a year if it scans too much. Partition by month and filter on the publication time so old partitions are skipped. Keep the sort key aligned with the columns you filter on. Review the query plan when the table passes a hundred million rows, and keep the row limit small. Because the feed asks for only thirty items, the right design keeps its cost almost constant as the table grows.

Common mistakes to avoid

• Querying a ReplacingMergeTree table without handling duplicates.
• Forgetting the row limit and the time limit on a public feed query.
• Using an account with write access for a read-only job.
• Overwriting a good feed with an empty result after a temporary failure.
• Changing the item identifier when a row is updated.
• Including hidden or future-dated rows because the filter was left out.

How PostRSS works with a ClickHouse-built feed

PostRSS only needs a valid RSS or Atom address that can be opened without a login. It checks the address on the schedule of your plan, finds items it has not posted before and publishes them, with image and link, to the networks you connect. The features page lists the networks and options such as keyword filters, which are handy when the table holds items of many kinds and you want to post only some of them. The pricing page shows which plan fits the number of targets you need.

Bài đọc liên quan

• Cloudflare Workers RSS feed
• Vercel Functions RSS feed
• Cloudflare blocking your RSS feed: fixing 403 errors

The bottom line

ClickHouse can serve as the source of an RSS feed when it already holds your items. Model updates with a replacing table, handle duplicates with FINAL or a grouped query, filter on status and time, limit rows and execution time, use a read-only account, write the file atomically and guard against empty results. Monitor the freshness of the file and of its newest item. Then connect the address to an auto-poster so new items reach your audience without manual work.

FAQ

Is ClickHouse a good database for an RSS feed?

It is a good source when your items already live there. For an ordinary blog, the content system usually produces a feed, and moving data just for XML is unnecessary.

Why can my query return the same item twice?

In a replacing table, old versions are removed only during background merges. Use the FINAL modifier or group by the identifier and take the highest version.

How often should the feed job run?

Every one to five minutes suits most pipelines. Trigger it after a batch of new rows as well, and keep the timer as a safety net.

How do I avoid publishing an empty feed after an error?

Keep the previous file if the query returns no rows or too few, and validate the XML before replacing it.

Can PostRSS read a feed generated from ClickHouse?

Yes. PostRSS reads any valid RSS or Atom address that is reachable without a login, no matter which system produces the file.