2 ms·

Absolutely store the raw data. For the reasons you state. But also store derived data. Titles, authors, dates, article texts. You need those for whatever yo

Absolutely store the raw data. For the reasons you state. But also store derived data. Titles, authors, dates, article texts. You need those for whatever your application does. You don't want your application logic to be working with the raw text. > how will you handle updates to the feed? When polling, consider using HTTP HEAD to check for changes before GET. What you do when an article ID reappears with different content, that up to you. I think readers usually replace the old entry with the new content, silently. But it's not the only option.

vivzkestrel 2mo ago
so how do you store raw xml data in postgres?
- paulryanrogers 2mo ago
  As text or XML column type. IME a document store or columnar backend is significantly cheaper if you're not querying it from SQL.
  - vivzkestrel 2mo ago
    - there is definitely one dilemma here - if you split the rss document into its "items" i am not sure if you can store each item separately inside postgres using that XML data type - if you store the whole document, you end up with a problem when say the feed partially updates after a few minutes - one suggestion i hear from r/PostgreSQL is that you store the XML blob somewhere else like hstore or something and somehow index it back to postgres. I wonder how that works conceptually / architecturally speaking
    - paulryanrogers 2mo ago
      Pg can do array columns, though I don't think it's a good fit for common use cases
    - zaptheimpaler 2mo ago
      The data is probably small enough and infrequently updated such that almost anything will work. If you split the items then you would have a separate episodes table with one row per episode joined to the podcasts table. If you store the whole XML in a row then you can overwrite the row when the feed changes. Doing the parsing and splitting into episodes in DB sounds better to me because your clients will otherwise constantly be repeating that work.
- SahAssar 2mo ago
  There is a datatype, if the XML is correct: https://www.postgresql.org/docs/current/datatype-xml.html https://www.postgresql.org/docs/current/datatype-xml.html

vivzkestrel 2mo ago

so how do you store raw xml data in postgres?

paulryanrogers 2mo ago
As text or XML column type. IME a document store or columnar backend is significantly cheaper if you're not querying it from SQL.
- vivzkestrel 2mo ago
  - there is definitely one dilemma here - if you split the rss document into its "items" i am not sure if you can store each item separately inside postgres using that XML data type - if you store the whole document, you end up with a problem when say the feed partially updates after a few minutes - one suggestion i hear from r/PostgreSQL is that you store the XML blob somewhere else like hstore or something and somehow index it back to postgres. I wonder how that works conceptually / architecturally speaking
  - paulryanrogers 2mo ago
    Pg can do array columns, though I don't think it's a good fit for common use cases
  - zaptheimpaler 2mo ago
    The data is probably small enough and infrequently updated such that almost anything will work. If you split the items then you would have a separate episodes table with one row per episode joined to the podcasts table. If you store the whole XML in a row then you can overwrite the row when the feed changes. Doing the parsing and splitting into episodes in DB sounds better to me because your clients will otherwise constantly be repeating that work.
SahAssar 2mo ago
There is a datatype, if the XML is correct: https://www.postgresql.org/docs/current/datatype-xml.html https://www.postgresql.org/docs/current/datatype-xml.html