5 ms·

Since the title is a bit misleading (talks about database choice: SQL vs NoSQL), I comment about the big data/data analytics point of view in case anyone comes

Since the title is a bit misleading (talks about database choice: SQL vs NoSQL), I comment about the big data/data analytics point of view in case anyone comes to this post for the same reason. The only problem I have with SQL (I use Postgres, in the form of Redshift almost daily: it's excellent when it works as expected) is the not-great readability of the code and the abysmal testing/debugging/checking of SQL code. Have had to write lots of boilerplate (Python, mostly) for that, and it feels like we are reinventing a wheel someone else is using, somewhere.

justboxing 10y ago
> is the not-great readability of the code Isn't that more of a poor-SQL-programmer problem, than a SQL-Database-technology problem? > and the abysmal testing/debugging/checking of SQL code I haven't used Postgres, but MS Sql Server has a ton of testing, debugging tools including SQL Trace, Explain Plans, and even a Stored Procedure debugger wherein you can set breakpoints and step through your code. Of course, one cannot expect the same level of rich debugging features that one gets with traditional IDE for programming languages, since SQL programming is a somewhat different beast in that sense.
- ChristianGeek 10y ago
  SQL Server's tools are awesome! Too bad the licensing fees are not.
  - mamcx 10y ago
    All that tools are included for free. You are only limited if you try to use a higher-end feature (like analytics) but the overall tooling is all there.
  - paulddraper 10y ago
    Not true in software, but IMO is in this case: you get what you pay for.
- deleted 10y ago
  [deleted]
- RBerenguel 10y ago
  You can only go so far with SQL as far as readability goes, and is ensuring everyone in the team writes readable queries. Problem is, inheriting a big lump of queries is totally different from inheriting a big lump of code: you can write tests (even if a little black-boxed) and refactor the lump until it makes sense. It is far more convoluted in SQL. I don't mean testing/debugging as in stepping through code that has been just written, but code that has been automatically submitted to the database during the night and for some reason has failed. The goal is to never be in that situation: with SQL you need to be way more careful than with other languages.
  - vog 10y ago
    > You can only go so far with SQL as far as readability goes This. Exactly this is the problem. SQL as a language fails to provide you with simple means of composition. And this is where libraries like Python's SQLAlchemy come into play. Note that it is not necessary to use SQLAlchemy as an ORM. Even if you use it "just" as a query builder, it will simplify a lot. Composition is really where the syntax of SQL fails utterly. This is quite surprising, because the foundation of SQL, relational algebra, excels at composability: You have many operations which all operate on the same type of data (namely, sets of tuples, or in math/cs speak: "relations"). As such, you can combine them in any way you want. For example: * "Filter" takes a relation and returns a relation, just with fewer rows. * "Projection" takes a relations and returns a relation, just with fewer columns * "Cross Join" takes two relations and returns one relation. * "Inner Join" is really just a combination of "Cross Join" and "Filter" * ... and so on. SQL's attempt to be more "human readable" than those nested operations fails to preserve that. Yes, we have sub selects, but can't just stick together the operations we want. For example, assume you have an existing query (perhaps more complex than this one): select a, b from t where a > 0 and want to apply a simple filter "b > 0" on top of it: (select a, b from t where a > 0) where b > 0 This kind of composition is not allowed. You either have to either give up composition (no reuse of the existing query) and combine the filters by hand: select a, b from t where a > 0 and b > 0 Or, you have to write a larger sub select: select * from (select a, b from t where a > 0) as temp where b > 0 In relational algebra, the first statement would have been: Projection[a,b](Filter[a>0](t)) Or, using ">" for nested function calls (function composition): t > Filter[a>0] > Projection[a,b] For the task at hand, you just compose it with your additional filter and be done with it, reusing 100% of your existing query: t > Filter[a>0] > Projection[a,b] > Filter[b>0] However, SQL forces you to either rewrite this query: t > Filter[a>0 and b>0] > Projections[a,b] Or to apply a sub select, which means adding nonsense operations such as naming a purely temporary intermediate result and projecting to all columns: t > Filter[a>0] > Projection[a,b] > Name[temp] > Filter[b>0] > Projection[*] In my view, the task of SQL query builders (such as the one in SQLAlchemy) is to restore the ability for programmers to form their query in relational algebra, without having to worry about the quirks added by the SQL language.
- user5994461 10y ago
  >>> I haven't used Postgres, but MS Sql Server has a ton of testing, debugging tools including SQL Trace, Explain Plans, and even a Stored Procedure debugger wherein you can set breakpoints and step through your code. Let's make it simple. Imagine a database with no tools whatsoever. Welcome to postgre!
mamcx 10y ago
Do you use a ORM? What kind of boilerplate? --- RDBMS have a less-than-ideal programming API, and also is de-coupled from the front-end (good) but also mean it requiere to re-implement some stuff in the client (because is a agnostic api). ORMs make this task harder than it must. -- For testing/debugging not help that a Database is a huge mutable store, and thigh testing was not a thing before. But more of this is a trouble with the disconnect between the database guys and the app guys. For example, Firebird (http://firebirdsql.org/ http://firebirdsql.org/) allow to embed the database similar to sqlite and also use it in a separate server process. It mean is easy to create a test environment easily without docker-alike craziness. Another disconnect is that is impossible to avoid sql. SQL is a poor developer API and is not decoupled so we can use something else. The advantage of some NoSql is that them not recreate SQL (at first) so them can do some nice stuff as post a JSON and call some basic commands.
- RBerenguel 10y ago
  We use it for analytics/data related analysis, so we use straight (or templated) SQL queries. The queries are a bit too thick for using an ORM. The boilerplate is a set of helpers to make sure what the SQL's intention is, is actually fulfilled without problems (lots of sanity checks to make sure the queries are correct before running, column stability, etc). Basically to ensure anything depending on the output still can trust it after the changes.
  - mamcx 10y ago
    So, basically a kind of static type system? Anyway, reporting is a very hard problem. I don't know if exist a truly good way to solve it in a general case. --- Is part of this not solve with views? Any example to showcase the trouble?
    - RBerenguel 10y ago
      Don't have examples (that I can show). It's trying to be a static check (more than type, but it's close enough) system, indeed. With views, we can isolate the used data, as long as we don't modify too much the underlying structure. Also, the biggest chunks of SQL we handle are essentially "computational": take this, join with that, mangle a lot what we have now, repeat several times and get an output we can use. Can't avoid the big part in the middle
ChristianGeek 10y ago
Even though Redshift is based on PostgreSQL 8.0, hasn't it diverged enough that it's not really PostgreSQL anymore?
- grzm 10y ago
  Yes. Nit: PostgreSQL 8 doesn't really describe a particular version. 8.0, 8.1, 8.2, 8.3, and 8.4 are all major PostgreSQL versions. Edited to add: Redshift was based on 8.0.2.
  - ChristianGeek 10y ago
    Edited.
- RBerenguel 10y ago
  It has lots of proprietary extensions and breaks lots of things from Postgres, but it's not far enough (at least for me) to consider it a completely different beast. You can still trust the Postgres documentation when the AWS documentation is lacking
deleted 10y ago
[deleted]

justboxing 10y ago

> is the not-great readability of the code Isn't that more of a poor-SQL-programmer problem, than a SQL-Database-technology problem? > and the abysmal testing/debugging/checking of SQL code I haven't used Postgres, but MS Sql Server has a ton of testing, debugging tools including SQL Trace, Explain Plans, and even a Stored Procedure debugger wherein you can set breakpoints and step through your code. Of course, one cannot expect the same level of rich debugging features that one gets with traditional IDE for programming languages, since SQL programming is a somewhat different beast in that sense.

ChristianGeek 10y ago
SQL Server's tools are awesome! Too bad the licensing fees are not.
- mamcx 10y ago
  All that tools are included for free. You are only limited if you try to use a higher-end feature (like analytics) but the overall tooling is all there.
- paulddraper 10y ago
  Not true in software, but IMO is in this case: you get what you pay for.
deleted 10y ago
[deleted]
RBerenguel 10y ago
You can only go so far with SQL as far as readability goes, and is ensuring everyone in the team writes readable queries. Problem is, inheriting a big lump of queries is totally different from inheriting a big lump of code: you can write tests (even if a little black-boxed) and refactor the lump until it makes sense. It is far more convoluted in SQL. I don't mean testing/debugging as in stepping through code that has been just written, but code that has been automatically submitted to the database during the night and for some reason has failed. The goal is to never be in that situation: with SQL you need to be way more careful than with other languages.
- vog 10y ago
  > You can only go so far with SQL as far as readability goes This. Exactly this is the problem. SQL as a language fails to provide you with simple means of composition. And this is where libraries like Python's SQLAlchemy come into play. Note that it is not necessary to use SQLAlchemy as an ORM. Even if you use it "just" as a query builder, it will simplify a lot. Composition is really where the syntax of SQL fails utterly. This is quite surprising, because the foundation of SQL, relational algebra, excels at composability: You have many operations which all operate on the same type of data (namely, sets of tuples, or in math/cs speak: "relations"). As such, you can combine them in any way you want. For example: * "Filter" takes a relation and returns a relation, just with fewer rows. * "Projection" takes a relations and returns a relation, just with fewer columns * "Cross Join" takes two relations and returns one relation. * "Inner Join" is really just a combination of "Cross Join" and "Filter" * ... and so on. SQL's attempt to be more "human readable" than those nested operations fails to preserve that. Yes, we have sub selects, but can't just stick together the operations we want. For example, assume you have an existing query (perhaps more complex than this one): select a, b from t where a > 0 and want to apply a simple filter "b > 0" on top of it: (select a, b from t where a > 0) where b > 0 This kind of composition is not allowed. You either have to either give up composition (no reuse of the existing query) and combine the filters by hand: select a, b from t where a > 0 and b > 0 Or, you have to write a larger sub select: select * from (select a, b from t where a > 0) as temp where b > 0 In relational algebra, the first statement would have been: Projection[a,b](Filter[a>0](t)) Or, using ">" for nested function calls (function composition): t > Filter[a>0] > Projection[a,b] For the task at hand, you just compose it with your additional filter and be done with it, reusing 100% of your existing query: t > Filter[a>0] > Projection[a,b] > Filter[b>0] However, SQL forces you to either rewrite this query: t > Filter[a>0 and b>0] > Projections[a,b] Or to apply a sub select, which means adding nonsense operations such as naming a purely temporary intermediate result and projecting to all columns: t > Filter[a>0] > Projection[a,b] > Name[temp] > Filter[b>0] > Projection[*] In my view, the task of SQL query builders (such as the one in SQLAlchemy) is to restore the ability for programmers to form their query in relational algebra, without having to worry about the quirks added by the SQL language.
user5994461 10y ago
>>> I haven't used Postgres, but MS Sql Server has a ton of testing, debugging tools including SQL Trace, Explain Plans, and even a Stored Procedure debugger wherein you can set breakpoints and step through your code. Let's make it simple. Imagine a database with no tools whatsoever. Welcome to postgre!

mamcx 10y ago

Do you use a ORM? What kind of boilerplate? --- RDBMS have a less-than-ideal programming API, and also is de-coupled from the front-end (good) but also mean it requiere to re-implement some stuff in the client (because is a agnostic api). ORMs make this task harder than it must. -- For testing/debugging not help that a Database is a huge mutable store, and thigh testing was not a thing before. But more of this is a trouble with the disconnect between the database guys and the app guys. For example, Firebird (http://firebirdsql.org/ http://firebirdsql.org/) allow to embed the database similar to sqlite and also use it in a separate server process. It mean is easy to create a test environment easily without docker-alike craziness. Another disconnect is that is impossible to avoid sql. SQL is a poor developer API and is not decoupled so we can use something else. The advantage of some NoSql is that them not recreate SQL (at first) so them can do some nice stuff as post a JSON and call some basic commands.

RBerenguel 10y ago
We use it for analytics/data related analysis, so we use straight (or templated) SQL queries. The queries are a bit too thick for using an ORM. The boilerplate is a set of helpers to make sure what the SQL's intention is, is actually fulfilled without problems (lots of sanity checks to make sure the queries are correct before running, column stability, etc). Basically to ensure anything depending on the output still can trust it after the changes.
- mamcx 10y ago
  So, basically a kind of static type system? Anyway, reporting is a very hard problem. I don't know if exist a truly good way to solve it in a general case. --- Is part of this not solve with views? Any example to showcase the trouble?
  - RBerenguel 10y ago
    Don't have examples (that I can show). It's trying to be a static check (more than type, but it's close enough) system, indeed. With views, we can isolate the used data, as long as we don't modify too much the underlying structure. Also, the biggest chunks of SQL we handle are essentially "computational": take this, join with that, mangle a lot what we have now, repeat several times and get an output we can use. Can't avoid the big part in the middle

ChristianGeek 10y ago

Even though Redshift is based on PostgreSQL 8.0, hasn't it diverged enough that it's not really PostgreSQL anymore?

grzm 10y ago
Yes. Nit: PostgreSQL 8 doesn't really describe a particular version. 8.0, 8.1, 8.2, 8.3, and 8.4 are all major PostgreSQL versions. Edited to add: Redshift was based on 8.0.2.
- ChristianGeek 10y ago
  Edited.
RBerenguel 10y ago
It has lots of proprietary extensions and breaks lots of things from Postgres, but it's not far enough (at least for me) to consider it a completely different beast. You can still trust the Postgres documentation when the AWS documentation is lacking

deleted 10y ago

[deleted]