3 ms·

Isn't upsert a bad design smell though? If you have high concurrent updates, you should be dealing with append-only database design and collapsing into a view

Isn't upsert a bad design smell though? If you have high concurrent updates, you should be dealing with append-only database design and collapsing into a view to query, like Datomic. All the locking required for atomic upserts would wreak havoc on performance anyway no?

the_mitsuhiko 13y ago
> If you have high concurrent updates When do you not have concurrency? > you should be dealing with append-only database design and collapsing into a view to query, like Datomic True, but postgres is very bad at that. So you need to work with the tools that postgres gives you. Yes postgres currently does not give you an upsert but an upsert is much closer to postgres' design than an append only database that denormalizes on inserts.
- hcarvalhoalves 13y ago
  > When do you not have concurrency? Highly concurrent updates? By that I understand a workload where you have multiple clients updating the same rows. In my experience it's either rare or the result of doing something inefficiently (e.g., doing analytics as a "view_count" column). Everytime I've faced that I've remodeled the database, both for performance (you avoid invalidating caches for the entire table) and auditing reasons (if you have a bunch of people replacing each other's changes, you might want to retain the history).
- justintocci 13y ago
  very bad at what? i lost you.
justintocci 13y ago
yes, yes(though a view is not necessary) and yes.
einhverfr 13y ago
> Isn't upsert a bad design smell though? It depends. We use upserts for certain kinds of data in LedgerSMB despite the fact that a lot of data is append-only. > If you have high concurrent updates, you should be dealing with append-only database design and collapsing into a view to query, like Datomic. All the locking required for atomic upserts would wreak havoc on performance anyway no? There are many cases beyond high concurrent updates where you want an append-only design. We use a lot of append-only in LedgerSMB but that's for transparency/audit control reasons. The problem is handling corner cases with upserts. The fact is that it is a useful tool in many areas. However you wouldn't use a chasing hammer to drive nails, would you?

the_mitsuhiko 13y ago

> If you have high concurrent updates When do you not have concurrency? > you should be dealing with append-only database design and collapsing into a view to query, like Datomic True, but postgres is very bad at that. So you need to work with the tools that postgres gives you. Yes postgres currently does not give you an upsert but an upsert is much closer to postgres' design than an append only database that denormalizes on inserts.

hcarvalhoalves 13y ago
> When do you not have concurrency? Highly concurrent updates? By that I understand a workload where you have multiple clients updating the same rows. In my experience it's either rare or the result of doing something inefficiently (e.g., doing analytics as a "view_count" column). Everytime I've faced that I've remodeled the database, both for performance (you avoid invalidating caches for the entire table) and auditing reasons (if you have a bunch of people replacing each other's changes, you might want to retain the history).
justintocci 13y ago
very bad at what? i lost you.

justintocci 13y ago

yes, yes(though a view is not necessary) and yes.

einhverfr 13y ago

> Isn't upsert a bad design smell though? It depends. We use upserts for certain kinds of data in LedgerSMB despite the fact that a lot of data is append-only. > If you have high concurrent updates, you should be dealing with append-only database design and collapsing into a view to query, like Datomic. All the locking required for atomic upserts would wreak havoc on performance anyway no? There are many cases beyond high concurrent updates where you want an append-only design. We use a lot of append-only in LedgerSMB but that's for transparency/audit control reasons. The problem is handling corner cases with upserts. The fact is that it is a useful tool in many areas. However you wouldn't use a chasing hammer to drive nails, would you?