Done! Feeder now on PostgreSQL

2018-08-31 20:25  feederpq     [permalink]

Tadaa! Thanks to DataLank, I was able to switch feeder from SQLite to PostgreSQL in roughly one evening. You just need to change ifnull to coalesce, (and SQLiteData.pas for LibPQData.pas ofcourse) and storing Delphi's TDateTime values now map to genuine timestamp values, so it's a little different to do arithmetic with...

But I was getting some 'database locked' errors, so I guess it was the best thing to do, since SQLite works really well but isn't quite designed for use by a website, let alone multiple processes. Sorting by timestamps appears to take a little more processing power than I was expecting, so I may try to change back to storing them as double (since that's also the value TDateTime uses internally), or I may just be missing an index that could speed up the query. It takes some investigating to find out, but that's for another day...

Update 2018-10-12: With some regret, I need to inform you that I have switched back to SQLite, perhaps for the time being. Though operation was acceptable, I noticed quite some performance was lost on queries that depend on the values in the fields of type timestamp without timezone. I've had a look if I could fix this by adding indexes, but wasn't successful. I was also thinking about changing the date-time fields to float, as that's what's actually used in the Delphi code that handles the values, and would probably get better performance in comparing and sorting in PostgreSQL, but the time it would take to switch that around would exceed the time it takes to switch back to SQLite. And switching back to SQLite offers a situation that has shown to perform well in all cases. With this switch I can find some time (somewhere?) to read up on PostgreSQL and timestamp fields, and what I'm missing that was causing some queries to take abnormally long. The query that averages times between posts, prior to checking feeds for new posts would regularly take over 100 seconds, a query that SQLite would only take seconds over.

Update 2019-08-19: Aaaand... we're back on PostgreSQL. This time I decided to use float for all date fields. Not only because Delphi uses this internally (and so there's less conversion to do), but also because PostgreSQL knows what to do with them, for example is able to sort by them.

Update 2020-01-30: I've also added an optional chart that gets generated by the feed reader schedule, that gives a view of the volume of posts your combined subscriptions get each day (yellow) for the last 40 days, and how much of them you've still got marked unread (red). Enable it on the settings page, and use the URL to put into a widget or somewhere online like this:

→