SQL for Distributed Systems | Hacker News Reader

SQL for Distributed Systems

babbling.fish

88 pointsbabblingfish37 comments

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

In my opinion BETWEEN should not be used because it is not clear whether the boundaries are open or closed.

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.

> 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

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.

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.

Oh I was thinking the opposite. I work between Monday and Friday, that’s a closed boundary. I have between 5 and 10 oranges, another closed boundary.

Maybe it's a temporal vs spatial vs cardinal issue. I'm sitting "between or in between" two people ( open boundary or exclusive or non-inclusive ). Seems spatial is always open boundary. Park your car between those two trucks. I walked between two clowns.

Ah good explanation. You’re right.

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.

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.

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.

How do you rewrite your linq query when the data in your tables change, and the eval order is no longer correct?

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.

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.

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.

This aspect of SQL is my favorite example of a "leaky abstraction".

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.

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.

> 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
    <span>1</span><span>+</span><span>1</span> = 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!).

> 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.

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).

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.

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.

I clicked at wrong tab when responding, sorry.

Neither "TIMESTAMP WITH TIME ZONE" nor "TIMESTAMP WITHOUT TIME ZONE" stores a time zone. The difference is in the meaning of the value: a "TIMESTAMP WITH TIME ZONE" value represents an absolute time in history; "TIMESTAMP WITHOUT TIME ZONE" represents a time relative to some unspecified time zone.

The types are so named because values of type "TIMESTAMP WITH TIME ZONE" are always displayed with a time zone (corresponding to the system's time zone) -- and the numeric value adjusted accordingly -- so the time they represent is unambiguous. Values of type "TIMESTAMP WITHOUT TIME ZONE" are displayed as-is without a time zone.

The types behave differently when used with the "AT TIME ZONE" operator as well, which converts between the two. When presented with a "TIMESTAMP WITH TIME ZONE" value, "AT TIME ZONE" returns a "TIMESTAMP WITHOUT TIME ZONE" value which numerically is equal to the time in the specified time zone. When presented with a "TIMESTAMP WITHOUT TIME ZONE" value, "AT TIME ZONE" interprets the given time as relative to the indicated time zone, and returns a "TIMESTAMP WITH TIME ZONE" value representing that absolute point in time.

Consequently, in cases where you would use UTC in other systems, you should use "TIMESTAMP WITH TIME ZONE" in PostgreSQL. In cases where you would use a local time in other systems, you should use "TIMESTAMP WITHOUT TIME ZONE" along with a "text" value naming the local time zone when appropriate.

Unfortunately, my colleagues and me had the displeasure to look at that distinction between timestamp and timestamptz in Postgres very deeply. All that is written above is correct from my understanding, but it is not the complete picture. Even in the case of a column of type `TIMESTAMP WITHOUT TIME ZONE`, the Postgresql JDBC driver may convert time to UTC if your session time zone is not UTC. I write "may" because it depends on whether the binary protocol or the text base protocol is used and that depends on whether prepared statements are actually used between the Postgres client library and the server.

We had the requirement to store dates and times coming it without changing them at all cost, but still using the space efficient epoch format. And we didn't know the time zone of the time when we had to store it.

Ugh, sounds like a bug in the JDBC driver. I've found that type translation in some drivers is... incomplete let's say. I seem to recall working around similar issues in psycopg2/3(?).

The PostgreSQL server itself though I've found to be very consistent handling these types.

Yes, PostgreSQL seems always to be consistent and very well documented.

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

I started reading and… yikes.

https://imgur.com/gallery/LVkDKeV

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.

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.

> 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!

This meme is completely unrelated to your article but it brought a smile to my face, and I clicked the article as a result. From there I found your web scraping article, which is timely given the recent discussions around web scraping on HN. Thanks!

I’ve never thought of using a template engine to generate code like that so thanks.

Been pondering on how to update something like the IMDB csv files once imported into a db and I think a diff -> generated script -> db update might do the trick. Haven’t really looked into it too much (and there’s probably a better way) because I don’t really need the movie db for anything other than playing around with.

Reply on news.ycombinator.com