4 ms·

I need to store ~50 million records with around 40 columns of strings, integers, decimals, various data. Only needs to be indexed by two string columns, but eve

I need to store ~50 million records with around 40 columns of strings, integers, decimals, various data. Only needs to be indexed by two string columns, but every day I want to "upsert" ~10 million records with data that has potentially changed, plus update a column that represents the date that the row was last updated on every row. With Postgres it seems to be inefficient at storing rows with large amounts of columns, and "upserting" data (using UPDATEs or INSERT ON CONFLICT) results in huge amounts of disk writes, I think because Postgres writes the entire row even if a single column has been updated. I could store some of the regularly updated columns in a separate table, but I'd thought I'd ask, is there a database out there that is more suited for this type of workload? Because I feel like "just use postgres for everything" is not the answer here.

Nican 4y ago
I was going to answer that the answer is column families [1], but I just realized that is a CockroachDB-specific feature. [1] https://www.cockroachlabs.com/docs/stable/column-families.html https://www.cockroachlabs.com/docs/stable/column-families.ht...
deleted 4y ago
[deleted]
inkyoto 4y ago
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.
manigandham 4y ago
Postgres is a perfectly fine answer for the OLTP row-oriented updates that you're doing. It seems you don't need relational features and can probably use simpler key/value datastores but unless you actually have a performance problem, there's no issue here.
civilian 4y ago
I mean, isn't the 40 columns the problem? You might benchmark it against a couple other database flavors, I suspect most dbs would have issues, although maybe not the same issues as postgres
hgamaral 4y ago
> I think because Postgres writes the entire row even if a single column has been updated. You might want to have a look at HOT [0] tuples if you haven't already. [0] - https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/ https://www.cybertec-postgresql.com/en/hot-updates-in-postgr...
- madjam002 4y ago
  Thanks for the pointer, that looks very interesting!
  - jpgvm 4y ago
    If you want to do writes without a ton of amplification on PG this is the only way right now. Eventually maybe PostgreSQL will get a second storage engine that uses an undo log instead, like the somewhat stalled zheap effort for instance. That said the numbers of rows you are talking about are easy peasy for PostgreSQL.
- blast 4y ago
  Thanks. I also found https://medium.com/adyen/fighting-postgresql-write-amplification-with-hot-updates-c8090f329ad6 https://medium.com/adyen/fighting-postgresql-write-amplifica....
unhammer 4y ago
Perhaps a https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS
- hgamaral 4y ago
  It might not be the best tool available, but Timescale's columnar capabalities [0][1] looks great to me. I mean, being able to achieve 90%+ compression on a row-oriented database is not something to be ignored. [0] - https://www.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://www.timescale.com/blog/building-columnar-compression... [1] - https://www.timescale.com/blog/timescaledb-2-3-improving-columnar-compression-for-time-series-on-postgresql/ https://www.timescale.com/blog/timescaledb-2-3-improving-col...
- manigandham 4y ago
  That's not recommended. Column-oriented (relational) databases store data by column instead of by row, which helps in large-scale OLAP reads of data but is extremely slow when writing or updating individual rows (since multiple rows have to be grouped into a column segment). Postgres and other OLTP databases are a better fit.
- madjam002 4y ago
  Yes I've been considering evaluating ScyllaDB, I think it could be a good fit for my use case.
  - manigandham 4y ago
    ScyllaDB (a next-gen Cassandra clone) is a "wide-column" database, or better called "advanced key/value". This is very different from columnar/column-oriented databases which are still (usually) relational and just store data by columns instead of by rows for OLAP (large-scale analytics) usage.
    - PeterCorless 4y ago
      Correct. A wide-column database like ScyllaDB is still a row-based store. Usually described as a "key-key-value" because it has both a partition key for data distribution and a clustering key for sort-ordering within a partition. Not the same-same as a "column-store" like Druid or Clickhouse or Pinot. [Disclosure: I work at ScyllaDB.]
crazygringo 4y ago
I mean, you could certainly try MySQL to see how it compares. It's hard to guess in advance because it seems like this is a very specific scenario. But 40 columns should be a cakewalk for any RDBMS to handle. And I assume you're not storing KB's of data in the different columns? Because if your strings are large enough to be allocated as references to separate blob storage, rather than in the row, that could be a performance problem. Bulk operations with millions of rows can also sometimes take way longer if you're doing them as a transaction that can be rolled back. If it's acceptable to disable that, that could be a huge improvement. You can also sometimes find massive speedups in using bulk SQL statements (upserting 1000 rows per query, rather than 1 row per query) or CSV file import rather than SQL. And obviously make sure you're using indexes wherever appropriate. Because generally speaking, upserting ~10 million reasonably-sized records is the kind of thing that should only take a few minutes on an SSD. You're not going to do it in seconds, but it shouldn't be taking an hour or anything either.
- madjam002 4y ago
  The issue isn't so much the speed as yes I use bulk SQL statements and can upsert like 4-5k rows per second, it's more the disk writes which I find unsettling as after a few months I've noticed several 10s of TBs of disk writes from Postgres onto the SSDs which seems like unnecessary wear.
  - qeternity 4y ago
    Several 10s of TBs of writes over a few months is literally nothing to be concerned about for any recently modern enterprise ssd in a production setting. This seems like a bizarre rationale to make a database choice.
  - crazygringo 4y ago
    Then it seems your setup is totally fine. If you're upserting 10 million rows a day with lots of columns, then of course you're going to be seeing TB's of disk writes. And remember that SSD's can't even write individual bytes the way HDD's can, they necessarily write a whole page at a time, which might be 4K or 16K, even if you're just updating a single integer. Your database and SSD are functioning totally normally, as designed.
tremon 4y ago
This sounds like a data lake or delta lake type of situation. If all you need is daily bulk updates but don't care about individual row updates nor concurrent access, I would probably not use a full-fledged database in the first place. e.g. https://docs.delta.io/latest/delta-standalone.html https://docs.delta.io/latest/delta-standalone.html
Izkata 4y ago
> I think because Postgres writes the entire row even if a single column has been updated. This is because of the consistency model postgres uses: Roughly, the old row remains in existence as long as transactions started before the UPDATE are still running, and the new row exists alongside it for that duration. Transactions started before the UPDATE can't see the new row, and transactions started after the update can't see the old row, because the transaction id (txid) is added to the query and is compared to hidden columns on each table/row (xmin and xmax). This is one of the things VACUUM cleans up, actually deleting those old rows once all the old transactions have ended.

Nican 4y ago

I was going to answer that the answer is column families [1], but I just realized that is a CockroachDB-specific feature. [1] https://www.cockroachlabs.com/docs/stable/column-families.html https://www.cockroachlabs.com/docs/stable/column-families.ht...

deleted 4y ago

[deleted]

inkyoto 4y ago

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.

manigandham 4y ago

Postgres is a perfectly fine answer for the OLTP row-oriented updates that you're doing. It seems you don't need relational features and can probably use simpler key/value datastores but unless you actually have a performance problem, there's no issue here.

civilian 4y ago

I mean, isn't the 40 columns the problem? You might benchmark it against a couple other database flavors, I suspect most dbs would have issues, although maybe not the same issues as postgres

hgamaral 4y ago

> I think because Postgres writes the entire row even if a single column has been updated. You might want to have a look at HOT [0] tuples if you haven't already. [0] - https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/ https://www.cybertec-postgresql.com/en/hot-updates-in-postgr...

madjam002 4y ago
Thanks for the pointer, that looks very interesting!
- jpgvm 4y ago
  If you want to do writes without a ton of amplification on PG this is the only way right now. Eventually maybe PostgreSQL will get a second storage engine that uses an undo log instead, like the somewhat stalled zheap effort for instance. That said the numbers of rows you are talking about are easy peasy for PostgreSQL.
blast 4y ago
Thanks. I also found https://medium.com/adyen/fighting-postgresql-write-amplification-with-hot-updates-c8090f329ad6 https://medium.com/adyen/fighting-postgresql-write-amplifica....

unhammer 4y ago

Perhaps a https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS

hgamaral 4y ago
It might not be the best tool available, but Timescale's columnar capabalities [0][1] looks great to me. I mean, being able to achieve 90%+ compression on a row-oriented database is not something to be ignored. [0] - https://www.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://www.timescale.com/blog/building-columnar-compression... [1] - https://www.timescale.com/blog/timescaledb-2-3-improving-columnar-compression-for-time-series-on-postgresql/ https://www.timescale.com/blog/timescaledb-2-3-improving-col...
manigandham 4y ago
That's not recommended. Column-oriented (relational) databases store data by column instead of by row, which helps in large-scale OLAP reads of data but is extremely slow when writing or updating individual rows (since multiple rows have to be grouped into a column segment). Postgres and other OLTP databases are a better fit.
madjam002 4y ago
Yes I've been considering evaluating ScyllaDB, I think it could be a good fit for my use case.
- manigandham 4y ago
  ScyllaDB (a next-gen Cassandra clone) is a "wide-column" database, or better called "advanced key/value". This is very different from columnar/column-oriented databases which are still (usually) relational and just store data by columns instead of by rows for OLAP (large-scale analytics) usage.
  - PeterCorless 4y ago
    Correct. A wide-column database like ScyllaDB is still a row-based store. Usually described as a "key-key-value" because it has both a partition key for data distribution and a clustering key for sort-ordering within a partition. Not the same-same as a "column-store" like Druid or Clickhouse or Pinot. [Disclosure: I work at ScyllaDB.]

crazygringo 4y ago

I mean, you could certainly try MySQL to see how it compares. It's hard to guess in advance because it seems like this is a very specific scenario. But 40 columns should be a cakewalk for any RDBMS to handle. And I assume you're not storing KB's of data in the different columns? Because if your strings are large enough to be allocated as references to separate blob storage, rather than in the row, that could be a performance problem. Bulk operations with millions of rows can also sometimes take way longer if you're doing them as a transaction that can be rolled back. If it's acceptable to disable that, that could be a huge improvement. You can also sometimes find massive speedups in using bulk SQL statements (upserting 1000 rows per query, rather than 1 row per query) or CSV file import rather than SQL. And obviously make sure you're using indexes wherever appropriate. Because generally speaking, upserting ~10 million reasonably-sized records is the kind of thing that should only take a few minutes on an SSD. You're not going to do it in seconds, but it shouldn't be taking an hour or anything either.

madjam002 4y ago
The issue isn't so much the speed as yes I use bulk SQL statements and can upsert like 4-5k rows per second, it's more the disk writes which I find unsettling as after a few months I've noticed several 10s of TBs of disk writes from Postgres onto the SSDs which seems like unnecessary wear.
- qeternity 4y ago
  Several 10s of TBs of writes over a few months is literally nothing to be concerned about for any recently modern enterprise ssd in a production setting. This seems like a bizarre rationale to make a database choice.
- crazygringo 4y ago
  Then it seems your setup is totally fine. If you're upserting 10 million rows a day with lots of columns, then of course you're going to be seeing TB's of disk writes. And remember that SSD's can't even write individual bytes the way HDD's can, they necessarily write a whole page at a time, which might be 4K or 16K, even if you're just updating a single integer. Your database and SSD are functioning totally normally, as designed.

tremon 4y ago

This sounds like a data lake or delta lake type of situation. If all you need is daily bulk updates but don't care about individual row updates nor concurrent access, I would probably not use a full-fledged database in the first place. e.g. https://docs.delta.io/latest/delta-standalone.html https://docs.delta.io/latest/delta-standalone.html

Izkata 4y ago

> I think because Postgres writes the entire row even if a single column has been updated. This is because of the consistency model postgres uses: Roughly, the old row remains in existence as long as transactions started before the UPDATE are still running, and the new row exists alongside it for that duration. Transactions started before the UPDATE can't see the new row, and transactions started after the update can't see the old row, because the transaction id (txid) is added to the query and is compared to hidden columns on each table/row (xmin and xmax). This is one of the things VACUUM cleans up, actually deleting those old rows once all the old transactions have ended.