9 ms·

#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you sho

#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.

googletron 4y ago
Good point. Its often the problem space and other constraints that usually drive these decisions. Its important that you deal with problems when you have them.
dobin 4y ago
I just use files
w0m 4y ago
Is 'Not performance bound, and dot knowing the future shape of your data' a valid reason? Less overhead on initial rollout to just Toss it up there. > choosing something else is rarely the right decision I think this is a little bit of a 'We always did it this way' statement.
- dspillett 4y ago
  There are circumstances where you really don't know the shape of the data, especially when prototyping for proof of concept purposes, but usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. More often than not it is worth sometime thinking and planning to work out at least the core requirements in that area, to save yourself a lot of refactoring (or throwing away and restarting) later, and potentially hitting bugs in production that a relational DB with well-defined constraints could have saved you from while still in dev. Programming is brilliant. Many weeks of it sometimes save you whole hours of up-front design work.
  - spmurrayzzz 4y ago
    > usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. This is a good point and probably correct often enough, but I also think not understanding the entire problem you are solving is not only common, but in fact necessary to most early-stage velocity. There is need to iterate and adapt frequently, sometimes as part of your go-to-market strategy, in order to fully understand the problem space. > a relational DB with well-defined constraints could have saved you from while still in dev This presumes that systems built on top of non-RDBMS are incapable of enforcing similar constraints. This has not been my experience personally. But its possible I don't understand your meaning of constraints in this context. I assumed it to mean, for instance, something like schemas which are fairly common now in the nosql world. Curious what other constraints were you referencing?
    - dspillett 4y ago
      > schemas The confusion there is due to the fact that non-R-DBMS (particular when referred to as noSQL) can mean several different things. In this context I was replying to a comment about not knowing the shape of your data which implies that person was thinking about solutions that are specifically described as schemaless, which is what a lot of people assume (in my experience) if you say non-relational or noSQL. That is the sort of constraints I was meaning: primary & unique keys and foreign keys for enforcing referential integrity and other validity rules enforced at the storage level. There are times when you can't enforce these things immediately with good performance (significantly distributed data stores that need concurrent distributed writes for instance - but the need for those is less common for most developers than the big data hype salespeople might have you believe) so then you have to consider letting go of them (I would advise considering it very carefully).
    - happimess 4y ago
      > There is need to iterate and adapt frequently, sometimes as part of your go-to-market strategy, in order to fully understand the problem space. If you're pivoting so hard that your SQL schema breaks, how is a schemaless system going to help you? You'll still have to either throw out your old data (easy in both cases) or figure out a way to map old records onto new semantics (hard in both cases). I agree with GP that this is a central problem to solve, not something to figure out _after_ you write software. Build your house on stone.
- xmprt 4y ago
  I think the tradeoff is similar to using a weakly typed vs strongly typed language. Strong typing is more up front effort but it will save you down the line because it's more predictable. Similarly, an RDBMS will require more up front planning and design and regular maintenance but that extra planning will save you more time down the line.
- alecfong 4y ago
  I find myself forced to model access pattern when choosing non relational dbs. This often results in a much less flexible model if you didn’t put a lot of thought into it. Ymmv
- gryn 4y ago
  as long as you're willing to write a whole lots of data validation, constraint checking scripts by hand in the future, ETL scripts for non-trivial analytical queries (depending on what NoSQL you chose, but if you chose it for perf this one is usual a price you have to pay). and keep a very rigorous track of the conceptual model of your data somewhere else, or simply don't care about its consistency when different parts of your not-schema have contradicting data (at that point why are you even storing it?) and that you ruled out using a JSON string column(s) as a dump for the uncertain parts, de-normalization and indexing, and the EAV schema as potential solutions to your problems. the point is noting is free, and you have to be sure it's a price your willing to pay. are you ready to give up joins ?, have your data be modeled after the exact queries your going to make ?, for you data to be duplicated across many places ? etc ...
- emerongi 4y ago
  With Postgres, you can always just have a JSONB column for data whose shape you're unsure of. Personally, I'd rather start with Postgres and dump data into there and retain the powers of RDBMS for the future, rather than the other way around and end up finding out that I really would like to have features that come out of the box with relational databases. I think a valid reason for not choosing a relational database is if your business plan requires that you grow to be a $100B+ company with hundreds of millions of users. Otherwise, you will probably be fine with RDBMS, even if it will require some optimizing in the future.
  - packetlost 4y ago
    Postgres' JSON implementation perfectly adheres to the JSON spec, which actually sucks if you need to support things like NaNs, Inf, etc. It's a good option, but it doesn't work for all datasets.
    - slaymaker1907 4y ago
      Wow, TIL that NaN isn't valid JSON.
  - mslot 4y ago
    Most $100B+ companies (e.g. Google, Meta, Amazon) were built primarily using relational databases for the first 10-15 years.
  - qaq 4y ago
    No longer an issue with things like Spanner, CockroachDB etc
- adrianmonk 4y ago
  > future shape of your data Contrary to what people seem to assume, you actually can change the schema of a database and migrate the existing data to the new schema. There's a learning curve, but it's doable. If you go schema-less, you run into another problem: not knowing the past shape of your data. When you try to load old records (from previous years), you may find that they don't look like the ones you wrote recently. And, if your code was changed, it may fail to handle them. This makes it hard to safely change code that handles stored data. You can avoid changing that code, you can accept breakage, or you can do a deep-dive research project before making a change. If you have a schema, you have a contract about what the data looks like, and you can have guarantees that it follows that contract.
  - grogers 4y ago
    Maintaining backwards compatibility for reading old records in code is not hard. You can always rewrite all rows to the newer format if you want to remove the conpat code, or if the structure changes in an incompatible way. It's pretty comparable to what you have to do to evolve the code/schema safely together. Having schema is much better for ad-hoc queries though, doubly so if your schemaless types aren't JSON (e.g. protobufs).
- marcosdumay 4y ago
  > Is 'Not performance bound, and dot knowing the future shape of your data' a valid reason? That's a very good reason for going with a RDBMS even if looks like it's not the clearest winner for your use case. If you invert any of those conditions, it may become interesting to study alternatives.
- roflyear 4y ago
  No absolutely not. 1 hr spent making a schema and a few hours of migrations is way less than the headaches you'll have by going nosql first.
jimnotgym 4y ago
The other day I said to a junior dev, when you started planning a locking scheme to handle concurrency in you file based system it is time to swap to a db
spmurrayzzz 4y ago
The premise here (I think, correct me if I'm mistaken) is that there are net-negative tradeoffs to using nosql/non-rdbms. If that assumption is true, then it follows that the same argument used in the last statement also applies— that if you're not at massive scale, then its likely the aforementioned tradeoff of not using RDBMS is likely de minimis. (This assumes that the tradeoffs are of the magnitude that they only manifest impact at scale, hard to address that without concrete examples though)
- deleted 4y ago
  [deleted]
- deleted 4y ago
  [deleted]
- bcrosby95 4y ago
  > (This assumes that the tradeoffs are of the magnitude that they only manifest impact at scale, hard to address that without concrete examples though) The tradeoff is usually flexibility. You run into flexibility problems anytime requirements change. Scale doesn't factor in.
aoms 4y ago
You're speaking my language. After more than 20 years of custom software dev, this statement has so much merit.
corpMaverick 4y ago
Similarly. People don't use Object Modeling/Entity relation-ship diagrams anymore. Every day, I see people struggling with problems that would be easy to understand if you had one. You don't even need to have an RDBMs. They are good just to model how things are related to each other.
randomdata 4y ago
> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work well enough. But those additional layers come with a lot of added complexity and undesirable dependencies that most would prefer to see live in the DBMS itself instead. At least among the RDBMSes we've heard of, there does not seem to be much interest in improving the APIs at the service level to make them more compelling to use natively like alternative offerings outside of the relational space have done.
- swagasaurus-rex 4y ago
  EdgeDB looks promising. Postgres under the hood so you know it's stable.
- paulryanrogers 4y ago
  What is a pleasant API? For what kind of data?
  - Yhippa 4y ago
    I wonder if parent post meant something other than SQL?
    - lwouis 4y ago
      That's also my reading. And I agree that SQL is a nice DSL yet way behind general programming languages we enjoy today. I'm dealing with some pretty involved ETL at work these days, and it's really hard and ugly to do it in SQL to "stay in the DB" and keep things fast. It's tempting to read, transform in a high-level language (think pandas) then write back to the DB, but then you give up so much perf. I would love another API than SQL like what Sparq is doing. You can keep a query optimizer, an engine to execute queries efficiently physically, etc, but it could be controlled with a nice general language API. Another way to put it is that SQL became the standard and alternative haven't been developed much in comparison. Imagine like the ML world being stuck with Prolog or some DSL. It would be annoying to say the least. Instead people have a variety of APIs and the popular and most developed ones are actually using high-level languages to drive optimized ML engines. These convert function calls and chaining into efficient transformation that use memory, computing units (CPU, GPU, etc) efficiently. The same for DB would be wonderful.
throwamon 4y ago
Isn't this like saying you can solve every programming problem you have with <insert your favorite Turing-complete language here>? Of course you can, but aren't there any cases where the tradeoffs outweigh the benefits, even if it's about something selfish like ergonomics or, dare I say, fun?
- _the_inflator 4y ago
  I agree with you. However, conversely I don't see anything that proves him wrong. Databases are not like programming languages. There is a reason why we don't use punch cards anymore.
nlnn 4y ago
I've found it's not just scale, but also down to query patterns across the data being stored. I'm with you on using an RDBMS for almost everything, but worked on quite a few projects where alternatives were needed. One involved a lot of analytics queries (aggregations, filters, grouping etc.) on ~100-200GB of data. No matter what we tried, we couldn't get enough performance from Postgres (column-based DBs / Parquet alternatives gave us 100x speedups for many queries). Another was for storing ~100M rows of data in a table with ~70 columns or so of largely text based data. Workload was predominantly random reads of subsets of 1M rows and ~20 columns at a time. Performance was also very poor in Postgres/MySQL. We ended up using a key/value store, heavily compressing everything before storing, and got a 30x speedup compared to using an RDBMS using a far smaller instance host size. I wouldn't call either of them massive scale, more just data with very specific query needs.
- snarfy 4y ago
  It's pretty old problem as they are competing ideas. It's OLTP vs OLAP. Postgres is designed for OLTP.
  - nlnn 4y ago
    Yeah, I think some of the problems are when both or needed on the same data, or when the use case changes over time. E.g. our customers are stored in Postgres, so let's also log their actions there linked to the user table. 5 years on someone decides we need to run analytical queries across years of 200M logged actions, joined with other data in the DB. So now we either have to live with horrible performance, migrate the logs to something suitable for OLAP (and lose all the benefits of a solid RDBMS), or have some syncing/export process to duplicate somewhere suitable for querying.
- giovannibonetti 4y ago
  > Another was for storing ~100M rows of data in a table with ~70 columns or so of largely text based data. Workload was predominantly random reads of subsets of 1M rows and ~20 columns at a time. Kimball's dimensional modelling helps a lot in cases like this, since probably there is a lot of repeated data in these columns.
VirusNewbie 4y ago
It is very frustrating to work with engineers who don't understand the nuances of RDBMS and assume they can solve all the things. The small company I work for has 3B rows. We have a high write volume. Can you use an RDBMs database to solve this? Sure, but it would be a terrible waste of engineering effort.
- qaq 4y ago
  ? as opposed to buggy reimplementation of subset of RDBMS functionality on the Application side?
- mulmen 4y ago
  Is that hard? Sounds like it might be a little expensive in terms of server resources but I don’t see why the engineering would be hard. I’m curious how you handle this with less engineering effort without using an RDBMS.
- HelloNurse 4y ago
  A "high write volume" requires fast disks, and billions of rows require large disks. Two simple requirements that are the same for any relational or less relational database. What's interesting is query performance, and a RDBMS supports explicit control over indexing (usually including analyzing execution plans to find out which queries are going to work well). Where do you see "a terrible waste of engineering effort"?
jpdb 4y ago
Hard disagree. The operational overhead of RDBMS and specifically their inherent reliance on a single primary node makes them, in my opinion, a bad place to start your architecture. I want to be able to treat the servers in my database tier as cattle instead of pets and RDBMSs don't fit this paradigm well. Either NoSQL or NewSQL databases are, in my opinion, a much better place to start. I feel like RDBMSs being the "default" option is because most people have worked with them in the past and already understand them. It doesn't mean they are the best tool for the job or even the tool most likely to solve the unknown problems you'll encounter in the future.
- imachine1980_ 4y ago
  You can have multi tb postgree database, that are fast and usable whit limited number of cache layers for speed, but you probably don need it. mediums migrate from single postress in 2020.
  - funcDropShadow 4y ago
    I don't understand why this comment is down voted. I've been part of a project that uses a dozen different PostgreSQL databases from different services, one of those databases is multi TB with an OLAP usage patterns. And it beats performance-wise all the previous attempts to solve that specific problem.
    - mulmen 4y ago
      Postgres is the Post-Ingres database. Originally it used QUEL (as in PostQUEL as in libpq) as a query language. When SQL support was added the product name was changed to PostgreSQL Postgree isn’t a thing. Postgres or PostgreSQL are acceptable. Postgre or postgree are not. https://www.postgresql.org/docs/current/history.html https://www.postgresql.org/docs/current/history.html
- anonymousDan 4y ago
  What is your go to NewSQL database these days (and why) out of interest?
roflyear 4y ago
Very frequently polled queues come to mind, but usually I'll use a db first anyway as there are benefits to it.
jsiaajdsdaa 4y ago
Who wants to shard MySQL once a week at Amazon levels of scale? I prefer a managed service with consistent hashing.
FlyingSnake 4y ago
This rings true in my experience. SQL knowledge has consistently helped me over my career. A simple exercise in designing the relational data model vastly improves the system architecture.
eikenberry 4y ago
Pretty easy. RDBMs have a shit API (SQL is terrible) and the largest (PostgreSQL) have a shit HA story. IMO you should think why you are using an RDBMs. I have used both and have never regretted NOT using an RDBMs. Maybe its a taste thing but I'd rather use a simple K/V database than a relational database any day.

googletron 4y ago

Good point. Its often the problem space and other constraints that usually drive these decisions. Its important that you deal with problems when you have them.

dobin 4y ago

I just use files

w0m 4y ago

Is 'Not performance bound, and dot knowing the future shape of your data' a valid reason? Less overhead on initial rollout to just Toss it up there. > choosing something else is rarely the right decision I think this is a little bit of a 'We always did it this way' statement.

dspillett 4y ago
There are circumstances where you really don't know the shape of the data, especially when prototyping for proof of concept purposes, but usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. More often than not it is worth sometime thinking and planning to work out at least the core requirements in that area, to save yourself a lot of refactoring (or throwing away and restarting) later, and potentially hitting bugs in production that a relational DB with well-defined constraints could have saved you from while still in dev. Programming is brilliant. Many weeks of it sometimes save you whole hours of up-front design work.
- spmurrayzzz 4y ago
  > usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. This is a good point and probably correct often enough, but I also think not understanding the entire problem you are solving is not only common, but in fact necessary to most early-stage velocity. There is need to iterate and adapt frequently, sometimes as part of your go-to-market strategy, in order to fully understand the problem space. > a relational DB with well-defined constraints could have saved you from while still in dev This presumes that systems built on top of non-RDBMS are incapable of enforcing similar constraints. This has not been my experience personally. But its possible I don't understand your meaning of constraints in this context. I assumed it to mean, for instance, something like schemas which are fairly common now in the nosql world. Curious what other constraints were you referencing?
  - dspillett 4y ago
    > schemas The confusion there is due to the fact that non-R-DBMS (particular when referred to as noSQL) can mean several different things. In this context I was replying to a comment about not knowing the shape of your data which implies that person was thinking about solutions that are specifically described as schemaless, which is what a lot of people assume (in my experience) if you say non-relational or noSQL. That is the sort of constraints I was meaning: primary & unique keys and foreign keys for enforcing referential integrity and other validity rules enforced at the storage level. There are times when you can't enforce these things immediately with good performance (significantly distributed data stores that need concurrent distributed writes for instance - but the need for those is less common for most developers than the big data hype salespeople might have you believe) so then you have to consider letting go of them (I would advise considering it very carefully).
  - happimess 4y ago
    > There is need to iterate and adapt frequently, sometimes as part of your go-to-market strategy, in order to fully understand the problem space. If you're pivoting so hard that your SQL schema breaks, how is a schemaless system going to help you? You'll still have to either throw out your old data (easy in both cases) or figure out a way to map old records onto new semantics (hard in both cases). I agree with GP that this is a central problem to solve, not something to figure out _after_ you write software. Build your house on stone.
xmprt 4y ago
I think the tradeoff is similar to using a weakly typed vs strongly typed language. Strong typing is more up front effort but it will save you down the line because it's more predictable. Similarly, an RDBMS will require more up front planning and design and regular maintenance but that extra planning will save you more time down the line.
alecfong 4y ago
I find myself forced to model access pattern when choosing non relational dbs. This often results in a much less flexible model if you didn’t put a lot of thought into it. Ymmv
gryn 4y ago
as long as you're willing to write a whole lots of data validation, constraint checking scripts by hand in the future, ETL scripts for non-trivial analytical queries (depending on what NoSQL you chose, but if you chose it for perf this one is usual a price you have to pay). and keep a very rigorous track of the conceptual model of your data somewhere else, or simply don't care about its consistency when different parts of your not-schema have contradicting data (at that point why are you even storing it?) and that you ruled out using a JSON string column(s) as a dump for the uncertain parts, de-normalization and indexing, and the EAV schema as potential solutions to your problems. the point is noting is free, and you have to be sure it's a price your willing to pay. are you ready to give up joins ?, have your data be modeled after the exact queries your going to make ?, for you data to be duplicated across many places ? etc ...
emerongi 4y ago
With Postgres, you can always just have a JSONB column for data whose shape you're unsure of. Personally, I'd rather start with Postgres and dump data into there and retain the powers of RDBMS for the future, rather than the other way around and end up finding out that I really would like to have features that come out of the box with relational databases. I think a valid reason for not choosing a relational database is if your business plan requires that you grow to be a $100B+ company with hundreds of millions of users. Otherwise, you will probably be fine with RDBMS, even if it will require some optimizing in the future.
- packetlost 4y ago
  Postgres' JSON implementation perfectly adheres to the JSON spec, which actually sucks if you need to support things like NaNs, Inf, etc. It's a good option, but it doesn't work for all datasets.
  - slaymaker1907 4y ago
    Wow, TIL that NaN isn't valid JSON.
- mslot 4y ago
  Most $100B+ companies (e.g. Google, Meta, Amazon) were built primarily using relational databases for the first 10-15 years.
- qaq 4y ago
  No longer an issue with things like Spanner, CockroachDB etc
adrianmonk 4y ago
> future shape of your data Contrary to what people seem to assume, you actually can change the schema of a database and migrate the existing data to the new schema. There's a learning curve, but it's doable. If you go schema-less, you run into another problem: not knowing the past shape of your data. When you try to load old records (from previous years), you may find that they don't look like the ones you wrote recently. And, if your code was changed, it may fail to handle them. This makes it hard to safely change code that handles stored data. You can avoid changing that code, you can accept breakage, or you can do a deep-dive research project before making a change. If you have a schema, you have a contract about what the data looks like, and you can have guarantees that it follows that contract.
- grogers 4y ago
  Maintaining backwards compatibility for reading old records in code is not hard. You can always rewrite all rows to the newer format if you want to remove the conpat code, or if the structure changes in an incompatible way. It's pretty comparable to what you have to do to evolve the code/schema safely together. Having schema is much better for ad-hoc queries though, doubly so if your schemaless types aren't JSON (e.g. protobufs).
marcosdumay 4y ago
> Is 'Not performance bound, and dot knowing the future shape of your data' a valid reason? That's a very good reason for going with a RDBMS even if looks like it's not the clearest winner for your use case. If you invert any of those conditions, it may become interesting to study alternatives.
roflyear 4y ago
No absolutely not. 1 hr spent making a schema and a few hours of migrations is way less than the headaches you'll have by going nosql first.

jimnotgym 4y ago

The other day I said to a junior dev, when you started planning a locking scheme to handle concurrency in you file based system it is time to swap to a db

spmurrayzzz 4y ago

The premise here (I think, correct me if I'm mistaken) is that there are net-negative tradeoffs to using nosql/non-rdbms. If that assumption is true, then it follows that the same argument used in the last statement also applies— that if you're not at massive scale, then its likely the aforementioned tradeoff of not using RDBMS is likely de minimis. (This assumes that the tradeoffs are of the magnitude that they only manifest impact at scale, hard to address that without concrete examples though)

deleted 4y ago
[deleted]
deleted 4y ago
[deleted]
bcrosby95 4y ago
> (This assumes that the tradeoffs are of the magnitude that they only manifest impact at scale, hard to address that without concrete examples though) The tradeoff is usually flexibility. You run into flexibility problems anytime requirements change. Scale doesn't factor in.

aoms 4y ago

You're speaking my language. After more than 20 years of custom software dev, this statement has so much merit.

corpMaverick 4y ago

Similarly. People don't use Object Modeling/Entity relation-ship diagrams anymore. Every day, I see people struggling with problems that would be easy to understand if you had one. You don't even need to have an RDBMs. They are good just to model how things are related to each other.

randomdata 4y ago

> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work well enough. But those additional layers come with a lot of added complexity and undesirable dependencies that most would prefer to see live in the DBMS itself instead. At least among the RDBMSes we've heard of, there does not seem to be much interest in improving the APIs at the service level to make them more compelling to use natively like alternative offerings outside of the relational space have done.

swagasaurus-rex 4y ago
EdgeDB looks promising. Postgres under the hood so you know it's stable.
paulryanrogers 4y ago
What is a pleasant API? For what kind of data?
- Yhippa 4y ago
  I wonder if parent post meant something other than SQL?
  - lwouis 4y ago
    That's also my reading. And I agree that SQL is a nice DSL yet way behind general programming languages we enjoy today. I'm dealing with some pretty involved ETL at work these days, and it's really hard and ugly to do it in SQL to "stay in the DB" and keep things fast. It's tempting to read, transform in a high-level language (think pandas) then write back to the DB, but then you give up so much perf. I would love another API than SQL like what Sparq is doing. You can keep a query optimizer, an engine to execute queries efficiently physically, etc, but it could be controlled with a nice general language API. Another way to put it is that SQL became the standard and alternative haven't been developed much in comparison. Imagine like the ML world being stuck with Prolog or some DSL. It would be annoying to say the least. Instead people have a variety of APIs and the popular and most developed ones are actually using high-level languages to drive optimized ML engines. These convert function calls and chaining into efficient transformation that use memory, computing units (CPU, GPU, etc) efficiently. The same for DB would be wonderful.

throwamon 4y ago

Isn't this like saying you can solve every programming problem you have with <insert your favorite Turing-complete language here>? Of course you can, but aren't there any cases where the tradeoffs outweigh the benefits, even if it's about something selfish like ergonomics or, dare I say, fun?

_the_inflator 4y ago
I agree with you. However, conversely I don't see anything that proves him wrong. Databases are not like programming languages. There is a reason why we don't use punch cards anymore.

nlnn 4y ago

I've found it's not just scale, but also down to query patterns across the data being stored. I'm with you on using an RDBMS for almost everything, but worked on quite a few projects where alternatives were needed. One involved a lot of analytics queries (aggregations, filters, grouping etc.) on ~100-200GB of data. No matter what we tried, we couldn't get enough performance from Postgres (column-based DBs / Parquet alternatives gave us 100x speedups for many queries). Another was for storing ~100M rows of data in a table with ~70 columns or so of largely text based data. Workload was predominantly random reads of subsets of 1M rows and ~20 columns at a time. Performance was also very poor in Postgres/MySQL. We ended up using a key/value store, heavily compressing everything before storing, and got a 30x speedup compared to using an RDBMS using a far smaller instance host size. I wouldn't call either of them massive scale, more just data with very specific query needs.

snarfy 4y ago
It's pretty old problem as they are competing ideas. It's OLTP vs OLAP. Postgres is designed for OLTP.
- nlnn 4y ago
  Yeah, I think some of the problems are when both or needed on the same data, or when the use case changes over time. E.g. our customers are stored in Postgres, so let's also log their actions there linked to the user table. 5 years on someone decides we need to run analytical queries across years of 200M logged actions, joined with other data in the DB. So now we either have to live with horrible performance, migrate the logs to something suitable for OLAP (and lose all the benefits of a solid RDBMS), or have some syncing/export process to duplicate somewhere suitable for querying.
giovannibonetti 4y ago
> Another was for storing ~100M rows of data in a table with ~70 columns or so of largely text based data. Workload was predominantly random reads of subsets of 1M rows and ~20 columns at a time. Kimball's dimensional modelling helps a lot in cases like this, since probably there is a lot of repeated data in these columns.

VirusNewbie 4y ago

It is very frustrating to work with engineers who don't understand the nuances of RDBMS and assume they can solve all the things. The small company I work for has 3B rows. We have a high write volume. Can you use an RDBMs database to solve this? Sure, but it would be a terrible waste of engineering effort.

qaq 4y ago
? as opposed to buggy reimplementation of subset of RDBMS functionality on the Application side?
mulmen 4y ago
Is that hard? Sounds like it might be a little expensive in terms of server resources but I don’t see why the engineering would be hard. I’m curious how you handle this with less engineering effort without using an RDBMS.
HelloNurse 4y ago
A "high write volume" requires fast disks, and billions of rows require large disks. Two simple requirements that are the same for any relational or less relational database. What's interesting is query performance, and a RDBMS supports explicit control over indexing (usually including analyzing execution plans to find out which queries are going to work well). Where do you see "a terrible waste of engineering effort"?

jpdb 4y ago

Hard disagree. The operational overhead of RDBMS and specifically their inherent reliance on a single primary node makes them, in my opinion, a bad place to start your architecture. I want to be able to treat the servers in my database tier as cattle instead of pets and RDBMSs don't fit this paradigm well. Either NoSQL or NewSQL databases are, in my opinion, a much better place to start. I feel like RDBMSs being the "default" option is because most people have worked with them in the past and already understand them. It doesn't mean they are the best tool for the job or even the tool most likely to solve the unknown problems you'll encounter in the future.

imachine1980_ 4y ago
You can have multi tb postgree database, that are fast and usable whit limited number of cache layers for speed, but you probably don need it. mediums migrate from single postress in 2020.
- funcDropShadow 4y ago
  I don't understand why this comment is down voted. I've been part of a project that uses a dozen different PostgreSQL databases from different services, one of those databases is multi TB with an OLAP usage patterns. And it beats performance-wise all the previous attempts to solve that specific problem.
  - mulmen 4y ago
    Postgres is the Post-Ingres database. Originally it used QUEL (as in PostQUEL as in libpq) as a query language. When SQL support was added the product name was changed to PostgreSQL Postgree isn’t a thing. Postgres or PostgreSQL are acceptable. Postgre or postgree are not. https://www.postgresql.org/docs/current/history.html https://www.postgresql.org/docs/current/history.html
anonymousDan 4y ago
What is your go to NewSQL database these days (and why) out of interest?

roflyear 4y ago

Very frequently polled queues come to mind, but usually I'll use a db first anyway as there are benefits to it.

jsiaajdsdaa 4y ago

Who wants to shard MySQL once a week at Amazon levels of scale? I prefer a managed service with consistent hashing.

FlyingSnake 4y ago

This rings true in my experience. SQL knowledge has consistently helped me over my career. A simple exercise in designing the relational data model vastly improves the system architecture.

eikenberry 4y ago

Pretty easy. RDBMs have a shit API (SQL is terrible) and the largest (PostgreSQL) have a shit HA story. IMO you should think why you are using an RDBMs. I have used both and have never regretted NOT using an RDBMs. Maybe its a taste thing but I'd rather use a simple K/V database than a relational database any day.