4 ms·

It sounds like you are dealing with a denormalised data model. Relational databases do not handle the denormalised data models well, and 50 million records is a

It sounds like you are dealing with a denormalised data model. Relational databases do not handle the denormalised data models well, and 50 million records is a small dataset. Consider remodelling the data model and going up to 3NF [0], or to a higher normal form if that is more appropriate for your use case. Throwing different relational database engine types at the problem might help with bandaiding the problem for a while, but one day it will come back and bite you again. Wikipedia has a good starting point [1] for the 1NF ↝ 2NF ↝ 3NF ↝ … design steps. Depending on the nature of your data, you might have to consider the Boyce–Codd normal form (3NF++ so to speak). Maybe not. Once you have remodelled the data model, selects and upserts will be very efficient, quick, and they won't result in the constant index update/rebuild for the entire single main table. You will have to rewrite your queries, but if the data model is normalised, joins will be straightforward, fun to write and will only update what has actually has changed in each, separate table. If the data model normalisation is absolutely impossible (e.g. you don't own the data model but rather your customer does), consider replacing upserts with the MERGE statement. [0] https://en.wikipedia.org/wiki/Third_normal_form https://en.wikipedia.org/wiki/Third_normal_form [1] https://en.wikipedia.org/wiki/Database_normalization#Example_of_a_step_by_step_normalization https://en.wikipedia.org/wiki/Database_normalization#Example...

fuy 4y ago
Why would you want to replace upserts (I.e INSERT with ON CONFLICT) with MERGE? Without additional context I'd say it's an antipattern, for a few reasons. First is that MERGE is a much more versatile operator (not a compliment if you need a specific narrow functionality like upsert) whereas INSERT / ON CONFLICT was specifically added to make thread safe upsert simple. How would you do thread safe MERGE in Postgres, off the top of your head? It's something that ON CONFLICT solves for you. Secondly, MERGE is only available starting with Postgres 15, which has just been released. So no, don't replace upserts with MERGE unless there's a gery specific reason.
- inkyoto 4y ago
  I have suggested MERGE as the last resort (due to the the main table having 40(!) circa columns), not as the preferred option – please refer to the last paragraph in my comment. Yes, it is difficult indeed to recommend something more specific without knowing exact details. MERGE does have a number of its own peculiarities that are database engine specific and the MERGE behaviour is not portable across RDBMS's. For instance, Oracle simply does not care about the transaction context width in which a MERGE is used, and the transaction will either succeed or fail no matter how long the transaction takes. Whereas MS SQL Server is very, very touchy and will kill off the transaction if, say, an index update caused by a MERGE is taking too long (from the SQL Server perspective). So it is better to minimise the transaction context containing a MERGE for the MS SQL Server and carefully assess the index update performance. I would expect Postgres to have the behaviour closer to that of Oracle, but I have not looked into it. More generally speaking (and if we ignore MERGE implementation specific details for a moment), remember that the UPSERT pattern has come about as a workaround for the missing MERGE functionality which was added into the specification very late, and vendors were slow to implement it. MERGE covers a few very useful use cases, especially for complex UPSERT scenarios, but it does not obviate UPSERT's in simple scenarios. Both are useful.
  - fuy 4y ago
    My main gripe is the idea that MERGE is somehow better than INSERT ON CONFLICT for upserts, which I don't think is ever true. I don't think I agree that it was added as a workaround - it was added rather as a proper solution, but for a more limited use case which is popular in OLTP workloads: simple thread-safe upsert. MERGE definitely has its place in complex ETL-style workloads, where you may also want to delete stuff, or change data in the source table as well as in the target table. Regarding SQL Server - it's not true that SQL Server will kill transactions based on some timeout (unless there's a deadlock detected). I worked with SQL Server extensively, including using MERGE for upserts (btw, you have to use serializable isolation to make MERGE thread-safe, so it's not so easy to get rid of transactions altogether). SQL Server doesn't kill transactions, except when there's a deadlock. FWIW, in SQL Server community there's a well-known notion of MERGE being buggy (even though it's been supported for like 15 years!) and hard to reason about (especially when there's triggers on the involved columns), see for example: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... I agree with you that Postgres' MERGE is most likely modeled after Oracle, but haven't looked into it either.

fuy 4y ago

Why would you want to replace upserts (I.e INSERT with ON CONFLICT) with MERGE? Without additional context I'd say it's an antipattern, for a few reasons. First is that MERGE is a much more versatile operator (not a compliment if you need a specific narrow functionality like upsert) whereas INSERT / ON CONFLICT was specifically added to make thread safe upsert simple. How would you do thread safe MERGE in Postgres, off the top of your head? It's something that ON CONFLICT solves for you. Secondly, MERGE is only available starting with Postgres 15, which has just been released. So no, don't replace upserts with MERGE unless there's a gery specific reason.

inkyoto 4y ago
I have suggested MERGE as the last resort (due to the the main table having 40(!) circa columns), not as the preferred option – please refer to the last paragraph in my comment. Yes, it is difficult indeed to recommend something more specific without knowing exact details. MERGE does have a number of its own peculiarities that are database engine specific and the MERGE behaviour is not portable across RDBMS's. For instance, Oracle simply does not care about the transaction context width in which a MERGE is used, and the transaction will either succeed or fail no matter how long the transaction takes. Whereas MS SQL Server is very, very touchy and will kill off the transaction if, say, an index update caused by a MERGE is taking too long (from the SQL Server perspective). So it is better to minimise the transaction context containing a MERGE for the MS SQL Server and carefully assess the index update performance. I would expect Postgres to have the behaviour closer to that of Oracle, but I have not looked into it. More generally speaking (and if we ignore MERGE implementation specific details for a moment), remember that the UPSERT pattern has come about as a workaround for the missing MERGE functionality which was added into the specification very late, and vendors were slow to implement it. MERGE covers a few very useful use cases, especially for complex UPSERT scenarios, but it does not obviate UPSERT's in simple scenarios. Both are useful.
- fuy 4y ago
  My main gripe is the idea that MERGE is somehow better than INSERT ON CONFLICT for upserts, which I don't think is ever true. I don't think I agree that it was added as a workaround - it was added rather as a proper solution, but for a more limited use case which is popular in OLTP workloads: simple thread-safe upsert. MERGE definitely has its place in complex ETL-style workloads, where you may also want to delete stuff, or change data in the source table as well as in the target table. Regarding SQL Server - it's not true that SQL Server will kill transactions based on some timeout (unless there's a deadlock detected). I worked with SQL Server extensively, including using MERGE for upserts (btw, you have to use serializable isolation to make MERGE thread-safe, so it's not so easy to get rid of transactions altogether). SQL Server doesn't kill transactions, except when there's a deadlock. FWIW, in SQL Server community there's a well-known notion of MERGE being buggy (even though it's been supported for like 15 years!) and hard to reason about (especially when there's triggers on the involved columns), see for example: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... I agree with you that Postgres' MERGE is most likely modeled after Oracle, but haven't looked into it either.