1

I searched responses on "UPDATE vs INSERT + DELETE" in general terms and the consensus is UPDATE is faster, as expected.

My question is centered on a somewhat niche use case. Let's say you have a table of fairly high-frequency ephemeral data. For example, modelling notifications on a social network, those are created very often and you always see the latest N.

Would it make sense to keep the latest N (say 20) and when one comes in, the Nth row is UPDATED into the new one (ID included). Rather than inserting a new one and then trimming old rows (maybe in bulk on an interval)?

Any thoughts?

1
  • 1
    Or possibly, why even consider a database for this? Something like an in memory cache, queue etc seems better suited to such a low number of notifications with such a transient existence – Caius Jard Feb 27 at 21:49
0

Postgres is MVCC. Every write produces a new row-version irrespective of whether the operation is insert or update. So I would not expect the proposed ring buffer style to give performance benefits.

With single-value systems, and fixed length rows I would expect faster performance. I'd like to see some thorough testing before I believed it was worthwhile, though.

-1

The question of performance between an UPDATE statement or a similar INSERT and DELETE statement is too broad of a question to say which one is faster. It'll depend on the specific query used in both cases among other factors such as the schema structure and architecture, including how the table is indexed.

To address your specific example, I'm going to assume you're actually referring to the feed, e.g. the StatusFeed in Facebook, which is only the top N items, and is very ephemeral i.e. it changes on every refresh of the page (since I consider Notifications not very ephemeral and always expect the same Notifications to be waiting for me to view, regardless of how many times I refresh the page).

In such an example a traditional RDBMS could be used, like PostgreSQL, but might be a little overly architected of a choice for something that doesn't require much persistence. As Caius Jard points out, you would likely be better suited to use something designed for high-frequency and low-persistence like an in-memory solution, or possibly even a document data store since the schema is simple and there's not much relational logic needed to support such a use case.

If you were still fixed on (for the theory of it) trying to implement this with a traditional RDBMS then you'll likely generate less transactions and therefor have some slightly less overhead by doing only UPDATES as opposed to INSERTS and DELETES. But again, as far as which methodology will be faster overall or cause the least amount of contention, will very much dependent on how the schema is architected.

4
  • I don't I conveyed the example clearly enough to you. I didn't mean the (news)feed but rather notifications you see in the "bell" section. Like a list of: - 3 people liked your post - New message in a group you are in Stuff like that. It's not THAT ephemeral, for some you might keep those 2 forever but most of the time, they last maybe some days or a week. Also, even if you'd use an in-memory store as a cache, you generally have to keep the in a reliable storage, it might not be data you can re-create if needed – Ariel Flesler Mar 1 at 14:27
  • @ArielFlesler I can only reference existing systems like Facebook which notifications are not ephemeral, and the count of notifications will persist forever until you actually view them. If your system hides unviewed notifications after a specific timeframe, then I still consider them non-ephemeral and would use a table to store those data points, with the date when they're logged. You can use a View or the application itself to implement the logic of what to show and hide by comparing the difference of the current DateTime - the logged date of the notification to the timeframe you... – J.D. Mar 1 at 16:13
  • ...decide is the cutoff. Either way, that use case is not ephemeral, and should be persisted in a table for a multitude of reasons. Then the business logic can be applied on top of it as I just mentioned. So I would never UPDATE an old notification in such a system, only continue to INSERT new ones. I also wouldn't DELETE old ones (unless the user explicitly deletes it in the application). There's no need to worry about a table with more than 20 rows, nor a table with more than 10,000,000 rows from a performance perspective. But eventually you might decide on an archival process... – J.D. Mar 1 at 16:16
  • ...to move old data (perhaps over a year old) outside of the main table, to make management (DDL querying, not performance of DQL queries) of the main table easier. Sorry I misunderstood you, because the context of the newsFeed is an example of ephemeral data that you could use an in-memory solution (or one of the other solutions I mentioned) for without worrying about persisting it on reliable storage, – J.D. Mar 1 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.