PostgreSQL RSS Feed: Build the XML Straight From SQL

Short answer: PostgreSQL can build the XML of an RSS feed for you. Store a status, a slug and a publication date with time zone for every article, select the newest published rows whose date has passed, wrap each row with the XML functions so that text is escaped automatically, and serve the result from a small route with the RSS content type and an ETag. Add a partial index for speed, cache the document for a few minutes and use a notification to refresh it when an article is published. Test the live address, then connect it to an auto-poster.

Many sites keep their articles in PostgreSQL, whether they use Django, Rails, Laravel, Node or plain SQL scripts. A feed is just a query with a particular shape, and the database has functions that build well-formed XML and escape every special character. Using them removes a whole class of errors, such as a stray ampersand in a title, and keeps the logic close to the data. This guide shows the table, the index, the query and a short Node route, and explains how to deal with dates, drafts, scheduled posts and caching. The XML functions need a server that was built with the XML library, which is the case for the common packages, but check yours with a quick test.

Design the table for a feed

A feed needs only a few columns, but they must be right. The status tells the query whether an article is public, the slug builds the address and the publication date decides the order. Use a timestamp with time zone so that the date is unambiguous, and set it once, when the article goes live.

CREATE TABLE articles (
  id           bigserial PRIMARY KEY,
  slug         text NOT NULL UNIQUE,
  title        text NOT NULL,
  summary      text NOT NULL,
  status       text NOT NULL DEFAULT 'draft',
  published_at timestamptz
);

CREATE INDEX articles_feed_idx
  ON articles (published_at DESC)
  WHERE status = 'published';

The partial index covers only published rows, so it stays small and the feed query reads a handful of entries. Do not use an updated-at column for the feed. It changes every time someone fixes a typo, and an auto-poster would see a changed item as a new one. The causes of repeated posts are explained in why a feed auto-posts the same article twice.

Build the XML in SQL

PostgreSQL has functions to create elements and attributes, to join many elements into one and to serialize the result as text. The query below selects the newest twenty published articles and builds the channel around them.

WITH latest AS (
  SELECT slug, title, summary, published_at
  FROM articles
  WHERE status = 'published' AND published_at <= now()
  ORDER BY published_at DESC
  LIMIT 20
)
SELECT xmlserialize(document
  xmlelement(name rss, xmlattributes('2.0' AS version),
    xmlelement(name channel,
      xmlelement(name title, 'Example Journal'),
      xmlelement(name link, 'https://example.com'),
      xmlelement(name description, 'News and guides from Example Journal.'),
      (SELECT xmlagg(
         xmlelement(name item,
           xmlelement(name title, title),
           xmlelement(name link, 'https://example.com/' || slug || '/'),
           xmlelement(name guid, xmlattributes('true' AS "isPermaLink"),
                      'https://example.com/' || slug || '/'),
           xmlelement(name "pubDate",
                      to_char(published_at AT TIME ZONE 'UTC', 'Dy, DD Mon YYYY HH24:MI:SS') || ' GMT'),
           xmlelement(name description, summary))
         ORDER BY published_at DESC)
       FROM latest)))
  AS text) AS feed;

The element functions escape the content, so an ampersand or an angle bracket in a title becomes a safe entity without any code of yours. Names that contain capital letters must be quoted, as in the pubDate element, otherwise PostgreSQL folds them to lower case and the feed becomes invalid. The date format uses English abbreviations for day and month, which is what the format requires, and the conversion to UTC makes the date exact. The condition on the publication date lets editors schedule an article: it is hidden until the moment passes and then appears without any other action.

Serve the result from a small route

The database returns the body of the feed, and the route adds the XML declaration, the headers and the validator. This example uses Node 18 or later with the pg package and Express.

import express from "express";
import pg from "pg";
import { createHash } from "node:crypto";
import { readFileSync } from "node:fs";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const SQL = readFileSync("feed.sql", "utf8");
let cache = { xml: null, etag: null, at: 0 };

async function refresh() {
  const { rows } = await pool.query(SQL);
  const xml = '<?xml version="1.0" encoding="UTF-8"?>' + rows[0].feed;
  cache = { xml, etag: '"' + createHash("sha1").update(xml).digest("hex") + '"', at: Date.now() };
}

const app = express();
app.get("/feed.xml", async (req, res) => {
  try {
    if (!cache.xml || Date.now() - cache.at > 300000) await refresh();
  } catch (err) {
    if (!cache.xml) return res.status(503).send("Feed temporarily unavailable");
  }
  res.set({
    "Content-Type": "application/rss+xml; charset=utf-8",
    "Cache-Control": "public, max-age=300",
    ETag: cache.etag,
  });
  if (req.get("If-None-Match") === cache.etag) return res.status(304).end();
  res.send(cache.xml);
});
app.listen(3000);

The query lives in its own file, so a person who knows SQL can review it without reading the route. The route keeps the last good copy and serves it when the database is unavailable, and it answers with an error only when it has nothing to serve. An empty but valid feed would be worse, because an auto-poster may read it as a sign that nothing changed. The mechanism of validators is described in the guide to ETags and conditional requests.

Refresh the feed when an article is published

A cache of five minutes is a good default, but you can make updates almost instant with a notification. A trigger on the table sends a message whenever a row becomes published, and the Node process listens and clears its copy.

CREATE FUNCTION notify_feed() RETURNS trigger AS $$
BEGIN
  IF NEW.status = 'published' THEN
    PERFORM pg_notify('feed_changed', NEW.slug);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_feed_notify
  AFTER INSERT OR UPDATE OF status ON articles
  FOR EACH ROW EXECUTE FUNCTION notify_feed();

On the listening side, open one dedicated connection, run the listen command for the channel and set the cache time to zero when a message arrives. Keep the lifetime of five minutes as a safety net, because a connection can drop without anyone noticing. Scheduled articles do not trigger a message at the moment their date passes, so the safety net is what makes them appear, which is another reason to keep it short.

Dates, slugs and drafts in practice

Three habits keep the feed stable. First, store dates with a time zone and convert them to UTC only when you write the XML. Second, make slugs permanent, for example with a rule that forbids changing a slug after publication, because a new address makes the same article look new. Third, never publish from a join that can produce duplicate rows, such as a join with tags, without grouping, or the same article will appear twice with the same identifier. Test with a draft, a scheduled article and an article with a special character in its title, and confirm the result for each.

Performance, permissions and security

The feed query is cheap with the partial index, because it reads a few rows and returns a few kilobytes. Check it once with the explain command on a copy of your data and confirm that it uses the index. Give the application a database role that can only read the articles table and listen for notifications, and keep the connection string in an environment variable. Do not select columns that you do not need, and never pass user input into the query text. If the archive grows to millions of rows, the plan stays the same, because the limit applies after the ordered index scan. Monitor the route for slow answers, since a slow feed can lead to timeouts in the tools that read it.

Languages and categories

If your table covers several languages, add a column for the language and create one route for each, so that every language has its own feed and its own social channel. A feed that mixes translations makes an auto-poster share the same story several times, and followers see it as spam. The same applies to categories. A join to a category table and a condition on its slug give you one feed per topic. See category filtering for auto-posting dan the guide to multilingual feeds.

Test the feed

Run the query in a database console first and read the output, then test the public address from outside.

curl -i https://example.com/feed.xml
curl -i -H 'If-None-Match: "paste-the-etag-here"' https://example.com/feed.xml

• The first request returns 200, the RSS content type and a body that starts with an XML declaration.
• The second returns 304 with no body when the value matches.
• A draft and a future article do not appear, and an article with an ampersand in its title does.
• Every link is absolute, uses https and opens the right page.
• The address passes the W3C Feed Validation Service without errors.

A longer routine is in the feed validation guide, and a shorter one in debugging a feed with curl.

Common mistakes to avoid

• An updated-at column as the item date. A correction moves the article to the top.
• Unquoted element names. The server folds pubDate to lower case.
• Joins that duplicate rows. The same identifier appears twice.
• No index. The query slows down as the archive grows.
• An empty feed on errors. Serve the last good copy or an error status.
• Only a console test. The live address passes through hosting and proxies.

How PostRSS works with a feed built from PostgreSQL

PostRSS has been publishing RSS and Atom feeds to social networks since 2014. Paste the address of your feed, connect your accounts and choose how each post is built from the title, the description or the page title. PostRSS checks the feed as often as every minute and shares each new entry, with its image and link, to the channels you choose among 66 supported networks, messengers, team chats and blogs, including LinkedIn, X, Bluesky, Mastodon and Telegram. Keyword filters, UTM parameters and posting windows let you tune each channel. See the PostRSS features page and the plan limits on the pricing page. More developer guides are under the Technical Guide tag.

Bacaan Berkaitan

• PHP and MySQL RSS feed
• Supabase RSS feed with an edge function
• RSS feed pagination

The bottom line

A PostgreSQL feed is one query and one route. Store a status, a slug and a publication date with time zone, use the XML functions so that text is escaped for you, hide drafts and future dates in the condition, keep a partial index, send the right headers and cache the result with a fallback. Refresh it with a notification when an article goes live, test the live address from outside and then connect it to an auto-poster, so that every article you publish reaches your social channels without extra work.

FAQ

Can PostgreSQL generate RSS XML itself?

Yes. Its XML functions create elements, attributes and aggregated lists, and they escape special characters, so the query can return a complete feed body.

Which date column should the feed use?

A publication date with time zone that you set once when the article goes live. An updated-at column changes with every correction and can make an auto-poster share the article again.

How do I schedule an article?

Store a future publication date and add a condition that the date must not be later than the current time. The article appears when the moment passes.

How can the feed update at once when an article is published?

Use a trigger that sends a notification and let the application clear its cached copy, while keeping a short cache lifetime as a safety net.

Can PostRSS read a feed built from a PostgreSQL database?

Yes. PostRSS reads any valid RSS or Atom address that is reachable without a login. Paste the address, connect your channels and new articles are shared as soon as they appear.