6 ms·

SQL for Distributed Systems

88 points by babblingfish 5y ago

bobek 5y ago
I am not sure about the "distributed systems" throughout the article. Basically none of the examples is really tight to being a distributed system.
me_im_counting 5y ago
It’s interesting how much SQL, a declarative language, requires thinking about performance. In theory, declarative languages would allow the user to think less about imperative concerns like the query plan. I’ve rewritten the same SQL query and got a 1000x speed up, looks like this author has too.
- jusssi 5y ago
 I find that often the big gains come from applying knowledge about the data. Take a classic, which order should the DB perform the required operations (scan a.foo, scan b.bar, match keys between a and b) for the simple join: SELECT * FROM a INNER JOIN b ON b.id = a.bid WHERE a.foo = 'foo' AND b.bar = 'bar' The correct answer depends on what the tables contain. The query planner makes some educated guesses with what knowledge it has available, but in some cases it guesses consistently wrong.
 - alexisread 5y ago
 Yep agreed, and this is the main argument for using functional query languages (like LINQ) as you can control the eval order explicitly. I'm also a fan of using CTEs in a template, so all template arguments can be grouped together at the top of the query. Additionally, multiple CTEs in a query execute as a single transaction, and in order (well, for Postgres anyway and probably SQL server) which can allow for multiple (different dependent tables) updates in a query without the roundtrips to say the API layer.
 - ako 5y ago
 How do you rewrite your linq query when the data in your tables change, and the eval order is no longer correct?
 - alexisread 5y ago
 Well you switch to a different linq query as your heuristic changes. This isn't a linq vs SQL debate. I'm pointing out that linq allows explicit ordering, and if you know the shape of your data, you can account for changes in it. As with most coding, right tool for the job, YMMV for SQL or linq.
 - BenoitP 5y ago
 RDBMS have statistics to try addressing that. IMHO just like machine learning is statistics on steroids, I would not be surprised to see some neural networks and embeddings try to model statistics for RDBMS in the future. These models could just as well be these that can be used to infer if a user row is bound to add another order row, and which kind of product row to go with that.
 - tomrod 5y ago
 Synthetic data vault is getting there with some of the recent model implementations. A blue ocean project if I ever saw one. Not ready for prod, but is able to learn relationships between tables.
- scythmic_waves 5y ago
 This aspect of SQL is my favorite example of a "leaky abstraction".
 - valenterry 5y ago
 It's not a leaky abstraction, because it doesn't make any promises about performance. If you still call it a "leaky abstraction" then _every_ abstraction is leaky and we can simply alias "leaky abstraction" to "abstraction" and move on.
- jabart 5y ago
 With any language, the more you tell the compiler information it doesn't know the better. Want to create an list of a million items but the default constructor is 50, have to tell the compiler. Same with SQL. I know X is a great start to a filter since it's unique in this one use case I'm working on, for everything else us the stats for the column. SQL has a huge number of ways to read the data, and rewritting the sql query is reprogramming how your code reads and writes data from memory/disk. Every language has the same exact problem.
- mamcx 5y ago
 > It’s interesting how much SQL, a declarative language, I think this is the main mistake. SQL is not declarative language, is a high-level DSL: 1 + 1 = 2 //most PLs SELECT 1 + 1 = 2 //SQL SET 1 + 1 = 2 //TCLish [1, 2] | sum = 2 //Functional 1+1 = NOT 2! //HTML, a TRUE declarative language! The main issues that causes this is that SQL is most of the time a little piece disconnected here and there. You don't see how is so imperative and functional until you write manually a big .sql file. Also, is crippled intentionally, so you don't use it for "regular" programming task (ie, not real way to do print("hello world")!. I start with FoxPro/dBASE and never develop this disconnect because for me, in Fox, SQL was just another sub-dialect of Fox, that was a "full" programming language. So every-time I do: SELECT * FROM customer WHERE code = 1 SCAN customer WHILE code = 1 //Equivalent Fox CMD ?customer ENDSCAN And similar how in Fox you know that your filters and sort depend on indexes then the same with SQL. I still look SQL and see it imperatively and have a good grasp on how everything execute (ie: at least until the query planner disagree with me!).
LittlePeter 5y ago
In my opinion BETWEEN should not be used because it is not clear whether the boundaries are open or closed. See also https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...
- adeelk93 5y ago
 I thought the BETWEEN boundaries are always closed. Isn’t that pretty clear? If you’re worried about date part precision in that comparison, you can always TRUNC before you BETWEEN.
 - williamdclt 5y ago
 > Isn’t that pretty clear? Nop :) That would be my guess too, but I definitely would not be certain about it, and the dozens of people that are going to read the code won't be either, some of them might make the wrong assumption, and bad things happen. I much prefer to be explicit with `>= <=` too
 - marcosdumay 5y ago
 On Postgres, yes. I wouldn't count on every DBMS correctly implement the standard. Specifically, I would expect Oracle to have some crazy concept of "closed" on specific contexts that don't align with anybody's. Anyway, in my opinion it shouldn't be used because the boundaries are both closed, and that's an incredibly bad choice. Even when it doesn't matter you have to go and confirm it doesn't matter.
 - LittlePeter 5y ago
 In the real-world world between almost always implies open boundary. Ask a hundred people to draw a point between two lines. I think none will draw a point on the line. When I enter the programming world, I carry over real-world concepts with me, and this where BETWEEN being closed gets confusing to me. Since I'm not a special snowflake, there must be other people like me who could be confused. Hence why I prefer not to use BETWEEN.
drran 5y ago
> Storing UTC values in a timestamp without time zone column is, unfortunately, a practice commonly inherited from other databases that lack usable timezone support. WTF? Timestamp is not a user-facing value, so it doesn't need to have user time zone. Timestamps are always in UTC.
- pc86 5y ago
  To second the sibling comment, "UTC" doesn't appear in the article at all. "timezone" occurs in code samples only. "timestamp" appears exactly once in this sentence: > Let’s say we define a python function `convert_to_eastern` that takes a timestamp and converts it to the eastern time zone. It seems unlikely this 2-week old article was updated within the last 2 hours so I'm not sure which article you're quoting.
  - drran 5y ago
    I clicked at wrong tab when responding, sorry.
- atombender 5y ago
  The sentence you quoted does not occur in the article, so I'm not sure about the context. But generally speaking, the problem is that an app might accidentally do: db.exec("INSERT INTO values (t) VALUES (?)", now()) and now your timezoneless column t probably contains the local time. A lot of code (including ORMs) assumes local time zones everywhere. It's simply hard to avoid, and too easy to accidentally insert bad data. But the above bug does not happen if your column is declared as something like "TIMESTAMP WITH TIME ZONE" (PostgreSQL, BigQuery, etc., or just "TIMESTAMP" in MySQL).
  - drran 5y ago
    Sorry, I clicked on wrong tab, when responding. It was a sane choice, to use local timezone for dates, in the past, but it's no longer a sane choice in today's world, because a database can be at another continent, while users can be all around the world. «Timestamp» is somewhat different from «date». Timestamps can be represented by different dates in different time zones or different chronicle. «Local timestamps» with time zone are prone to error when time zone shifts.
andogreenz88 5y ago
https://coincircle.com/l/2yhhyJLPGV https://coincircle.com/l/2yhhyJLPGV
crorella 5y ago
Missed some basic info about distributed systems: Partitioning, bucketing, sorting, bloom filters, the fact that you normally create specific logic to backfill rather than run the same 'daily' process N times. If you come from Dataswarm/Airflow then you can use JINJA for the templating, UPSERTS are better when you want to save space by not keeping the same data for each snapshot you take day after day (on multi terabyte tables the space savings are considerable). More topics that are usually prevalent in distributed systems: - Join order - Type optimization for smaller tables (ones you can fit in memory to do broadcast joins) - Field codification for extra compression/smaller intermediate shuffles when processing data. - Field sorting for extra compression - Caching tables that participate in several parts of the process. - Rolling accumulating pre-aggregates for multi-day processing - T-Digest for performant aggs / cubes - HLL for performant aggs - etc
eyelidlessness 5y ago
I started reading and… yikes.
- babblingfish 5y ago
  https://imgur.com/gallery/LVkDKeV https://imgur.com/gallery/LVkDKeV
  - eyelidlessness 5y ago
    I owe you an apology. I saw templating and what looks like string concatenation in the first example, referencing unfamiliar APIs in an ecosystem I don’t keep up with. I found that alarming but didn’t dig deep enough to really understand the way that templating is applied. I’m sorry, that was a knee-jerk reaction and not a helpful contribution to discussion.
    - babblingfish 5y ago
      No worries, thank you for taking the time to write this comment. I must agree that this article is talking about a niche and surrounding ecosystem that may not be familiar to many readers. I try to write the articles in a way that is approachable to people who do not work in data analytics, but there is certainly a lot more I could do, especially for this article. It's a difficult tradeoff of making it accessible but also digging deep enough for those who have some familiarity. I probably get it wrong more than I get it right. Anyways thank you for your thoughtful feedback and have a great day.
      - eyelidlessness 5y ago
        > It's a difficult tradeoff of making it accessible but also digging deep enough for those who have some familiarity. I probably get it wrong more than I get it right. FWIW, when I came back and read further, I thought you did quite well making it accessible. It’s not your fault I quickly formed a knee-jerk opinion!
  - throwawaysea 5y ago

88 points by babblingfish 5y ago

I am not sure about the "distributed systems" throughout the article. Basically none of the examples is really tight to being a distributed system.

me_im_counting 5y ago

It’s interesting how much SQL, a declarative language, requires thinking about performance. In theory, declarative languages would allow the user to think less about imperative concerns like the query plan. I’ve rewritten the same SQL query and got a 1000x speed up, looks like this author has too.

jusssi 5y ago
I find that often the big gains come from applying knowledge about the data. Take a classic, which order should the DB perform the required operations (scan a.foo, scan b.bar, match keys between a and b) for the simple join: SELECT * FROM a INNER JOIN b ON b.id = a.bid WHERE a.foo = 'foo' AND b.bar = 'bar' The correct answer depends on what the tables contain. The query planner makes some educated guesses with what knowledge it has available, but in some cases it guesses consistently wrong.
- alexisread 5y ago
  Yep agreed, and this is the main argument for using functional query languages (like LINQ) as you can control the eval order explicitly. I'm also a fan of using CTEs in a template, so all template arguments can be grouped together at the top of the query. Additionally, multiple CTEs in a query execute as a single transaction, and in order (well, for Postgres anyway and probably SQL server) which can allow for multiple (different dependent tables) updates in a query without the roundtrips to say the API layer.
  - ako 5y ago
    How do you rewrite your linq query when the data in your tables change, and the eval order is no longer correct?
    - alexisread 5y ago
      Well you switch to a different linq query as your heuristic changes. This isn't a linq vs SQL debate. I'm pointing out that linq allows explicit ordering, and if you know the shape of your data, you can account for changes in it. As with most coding, right tool for the job, YMMV for SQL or linq.
- BenoitP 5y ago
  RDBMS have statistics to try addressing that. IMHO just like machine learning is statistics on steroids, I would not be surprised to see some neural networks and embeddings try to model statistics for RDBMS in the future. These models could just as well be these that can be used to infer if a user row is bound to add another order row, and which kind of product row to go with that.
  - tomrod 5y ago
    Synthetic data vault is getting there with some of the recent model implementations. A blue ocean project if I ever saw one. Not ready for prod, but is able to learn relationships between tables.
scythmic_waves 5y ago
This aspect of SQL is my favorite example of a "leaky abstraction".
- valenterry 5y ago
  It's not a leaky abstraction, because it doesn't make any promises about performance. If you still call it a "leaky abstraction" then _every_ abstraction is leaky and we can simply alias "leaky abstraction" to "abstraction" and move on.
jabart 5y ago
With any language, the more you tell the compiler information it doesn't know the better. Want to create an list of a million items but the default constructor is 50, have to tell the compiler. Same with SQL. I know X is a great start to a filter since it's unique in this one use case I'm working on, for everything else us the stats for the column. SQL has a huge number of ways to read the data, and rewritting the sql query is reprogramming how your code reads and writes data from memory/disk. Every language has the same exact problem.
mamcx 5y ago
> It’s interesting how much SQL, a declarative language, I think this is the main mistake. SQL is not declarative language, is a high-level DSL: 1 + 1 = 2 //most PLs SELECT 1 + 1 = 2 //SQL SET 1 + 1 = 2 //TCLish [1, 2] | sum = 2 //Functional 1+1 = NOT 2! //HTML, a TRUE declarative language! The main issues that causes this is that SQL is most of the time a little piece disconnected here and there. You don't see how is so imperative and functional until you write manually a big .sql file. Also, is crippled intentionally, so you don't use it for "regular" programming task (ie, not real way to do print("hello world")!. I start with FoxPro/dBASE and never develop this disconnect because for me, in Fox, SQL was just another sub-dialect of Fox, that was a "full" programming language. So every-time I do: SELECT * FROM customer WHERE code = 1 SCAN customer WHILE code = 1 //Equivalent Fox CMD ?customer ENDSCAN And similar how in Fox you know that your filters and sort depend on indexes then the same with SQL. I still look SQL and see it imperatively and have a good grasp on how everything execute (ie: at least until the query planner disagree with me!).

LittlePeter 5y ago

In my opinion BETWEEN should not be used because it is not clear whether the boundaries are open or closed. See also https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...

adeelk93 5y ago
I thought the BETWEEN boundaries are always closed. Isn’t that pretty clear? If you’re worried about date part precision in that comparison, you can always TRUNC before you BETWEEN.
- williamdclt 5y ago
 > Isn’t that pretty clear? Nop :) That would be my guess too, but I definitely would not be certain about it, and the dozens of people that are going to read the code won't be either, some of them might make the wrong assumption, and bad things happen. I much prefer to be explicit with `>= <=` too
- marcosdumay 5y ago
 On Postgres, yes. I wouldn't count on every DBMS correctly implement the standard. Specifically, I would expect Oracle to have some crazy concept of "closed" on specific contexts that don't align with anybody's. Anyway, in my opinion it shouldn't be used because the boundaries are both closed, and that's an incredibly bad choice. Even when it doesn't matter you have to go and confirm it doesn't matter.
- LittlePeter 5y ago
 In the real-world world between almost always implies open boundary. Ask a hundred people to draw a point between two lines. I think none will draw a point on the line. When I enter the programming world, I carry over real-world concepts with me, and this where BETWEEN being closed gets confusing to me. Since I'm not a special snowflake, there must be other people like me who could be confused. Hence why I prefer not to use BETWEEN.

drran 5y ago

> Storing UTC values in a timestamp without time zone column is, unfortunately, a practice commonly inherited from other databases that lack usable timezone support. WTF? Timestamp is not a user-facing value, so it doesn't need to have user time zone. Timestamps are always in UTC.

pc86 5y ago
To second the sibling comment, "UTC" doesn't appear in the article at all. "timezone" occurs in code samples only. "timestamp" appears exactly once in this sentence: > Let’s say we define a python function `convert_to_eastern` that takes a timestamp and converts it to the eastern time zone. It seems unlikely this 2-week old article was updated within the last 2 hours so I'm not sure which article you're quoting.
- drran 5y ago
  I clicked at wrong tab when responding, sorry.
atombender 5y ago
The sentence you quoted does not occur in the article, so I'm not sure about the context. But generally speaking, the problem is that an app might accidentally do: db.exec("INSERT INTO values (t) VALUES (?)", now()) and now your timezoneless column t probably contains the local time. A lot of code (including ORMs) assumes local time zones everywhere. It's simply hard to avoid, and too easy to accidentally insert bad data. But the above bug does not happen if your column is declared as something like "TIMESTAMP WITH TIME ZONE" (PostgreSQL, BigQuery, etc., or just "TIMESTAMP" in MySQL).
- drran 5y ago
  Sorry, I clicked on wrong tab, when responding. It was a sane choice, to use local timezone for dates, in the past, but it's no longer a sane choice in today's world, because a database can be at another continent, while users can be all around the world. «Timestamp» is somewhat different from «date». Timestamps can be represented by different dates in different time zones or different chronicle. «Local timestamps» with time zone are prone to error when time zone shifts.

andogreenz88 5y ago

https://coincircle.com/l/2yhhyJLPGV https://coincircle.com/l/2yhhyJLPGV

crorella 5y ago

Missed some basic info about distributed systems: Partitioning, bucketing, sorting, bloom filters, the fact that you normally create specific logic to backfill rather than run the same 'daily' process N times. If you come from Dataswarm/Airflow then you can use JINJA for the templating, UPSERTS are better when you want to save space by not keeping the same data for each snapshot you take day after day (on multi terabyte tables the space savings are considerable). More topics that are usually prevalent in distributed systems: - Join order - Type optimization for smaller tables (ones you can fit in memory to do broadcast joins) - Field codification for extra compression/smaller intermediate shuffles when processing data. - Field sorting for extra compression - Caching tables that participate in several parts of the process. - Rolling accumulating pre-aggregates for multi-day processing - T-Digest for performant aggs / cubes - HLL for performant aggs - etc

eyelidlessness 5y ago

I started reading and… yikes.

babblingfish 5y ago
https://imgur.com/gallery/LVkDKeV https://imgur.com/gallery/LVkDKeV
- eyelidlessness 5y ago
  I owe you an apology. I saw templating and what looks like string concatenation in the first example, referencing unfamiliar APIs in an ecosystem I don’t keep up with. I found that alarming but didn’t dig deep enough to really understand the way that templating is applied. I’m sorry, that was a knee-jerk reaction and not a helpful contribution to discussion.
  - babblingfish 5y ago
    No worries, thank you for taking the time to write this comment. I must agree that this article is talking about a niche and surrounding ecosystem that may not be familiar to many readers. I try to write the articles in a way that is approachable to people who do not work in data analytics, but there is certainly a lot more I could do, especially for this article. It's a difficult tradeoff of making it accessible but also digging deep enough for those who have some familiarity. I probably get it wrong more than I get it right. Anyways thank you for your thoughtful feedback and have a great day.
    - eyelidlessness 5y ago
      > It's a difficult tradeoff of making it accessible but also digging deep enough for those who have some familiarity. I probably get it wrong more than I get it right. FWIW, when I came back and read further, I thought you did quite well making it accessible. It’s not your fault I quickly formed a knee-jerk opinion!
- throwawaysea 5y ago