5 ms·

I'm a fan of relational databases, but I think we should concede three points: 1. Databases should accept queries in a structured machine-readable format rathe

I'm a fan of relational databases, but I think we should concede three points: 1. Databases should accept queries in a structured machine-readable format rather than a plaintext language. 2. SQL in particular is a poorly designed language: it isn't very composable, it has lots of annoying edge cases (like NULLs), and it has a number of annoying limitations (in particular, its historically limited support for structured data within fields). 3. Given how most RDBMSs are designed, you often need to handle denormalization and caching manually. This requires doing a lot of excess data management in a middleware layer--for instance, querying a cache before accessing the DB, or storing data in multiple places for denormalization. Some of this can be done in SQL (e.g. denormalization through TRIGGERs), but since SQL is not a very good language (see (2)) that can be tough.

asalahli 4y ago
Also, caching can be done with materialized views
- jasonhansel 4y ago
  IIRC in Postgres you need to refresh an entire materialized view all at once, effectively recreating the entire table; you can't just have it update incrementally whenever the underlying data changes. I think SQL Server can do this, but...then you have to use SQL Server.
  - mnahkies 4y ago
    I think it's being worked on for postgres but probably another major release or two away, quick Google came up with this https://pgconf.ru/en/2021/288667 https://pgconf.ru/en/2021/288667 but I'm sure I've come across discussion in postgres mailing lists / wiki in the past. Would definitely be a nice feature to have, without it I find the main use case I have for materialised views is batch processing where you want to prepare a complex result set and then stream process it in a Cronjob or similar
    - aidos 4y ago
      There’s also info on this page about it (incremental materialised views). https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... In the meantime my preferred technique is to have a column where I stamp the last generation time for each row and then I rebuild anything that’s changed since then (assuming all your source data has some sort of last updated stamp).
  - branko_d 4y ago
    > I think SQL Server can do this, but...then you have to use SQL Server. Yes, SQL Server can update indexed views incrementally, but there are severe limitations: https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16#additional-requirements https://learn.microsoft.com/en-us/sql/relational-databases/v... If memory servers, indexed views have been in SQL Server for 20-odd years, and haven't seen meaningful improvements in all that time. We still can't do a LEFT JOIN, or join the same table more than once or MAX etc... The same story with T-SQL, which is firmly stuck in the '80s (not that other databases are better). There are some extremely powerful features in SQL Server that can be used effectively with some pain, but they could be so much better if Microsoft invested in fully fleshing-out their potential instead of chasing the latest buzzword. Sorry for the rant.
  - tshaddox 4y ago
    Yep, which is why materialized views don't tend to work great for a lot of tasks where they initially seem to be the most natural implementation, particularly almost any view that's a feed or aggregate of data in the system over all time. It's so easy to just play with a simple SQL select query until you get it working, then throw it into a materialized view. It'll probably even work for a long time! But as soon as the data in the system grows and that refresh starts getting slower, you're stuck with a (potentially tricky or at least frustrating) migration to another implementation (maybe something like event sourcing).
- cwillu 4y ago
  Also computed columns in a covering index.
jrumbut 4y ago
The thing about #2, is poorly designed in comparison to what? Chances are high the application layer is written in JavaScript, PHP, Ruby, or Python. We don't even talk about nasty edge cases in those languages because they are uncountable.
- jasonhansel 4y ago
  Those languages all have first-class functions and OOP-style encapsulation. In SQL stored procedures (at least in Postgres), you can't have a variable that holds multiple records. You can't even define a variable inside of a block.
  - ttfkam 4y ago
    DECLARE foo record[];
  - nhinck2 4y ago
    Temp tables?
- lmm 4y ago
  None of those languages is as unpleasant as SQL. Not even PHP.
  - billythemaniam 4y ago
    For me, I would much rather SQL especially when using DBT too to manage the queries like any other language. I thought the same way as you and SQL certainly has its warts, but I have grown to appreciate it's elegance. I think over the long-term it is easier to maintain because it is so concise and the most common second language among programmers.
  - ttfkam 4y ago
    Once you start thinking in sets instead of collections of independent statements as per PHP et al, SQL starts looking a lot better. If you try to fit SQL into the general purpose programming language box, you're gonna have a bad time. A really bad time. SQL is a DSL for set theory. Nothing more. Nothing less.
goto11 4y ago
> in particular, its historically limited support for structured data within fields This is not particular to SQL though, and is the rationale behind the first normal form. Codd argued that any complex data structure could be represented in the form of relations, so adding non-relational structures would just complicate things for no additional power.
- jasonhansel 4y ago
  The issue is that, to put things in 1NF, you need to fully normalize everything, which has a big performance penalty since every query now has to JOIN a large number of tables together. Of course, an RDBMS could be designed to do that without a performance penalty, by storing data in a denormalized form and automatically translating queries for the normalized data accordingly. But SQL doesn't have the features you'd need to control and manage that sort of transparent denormalization. So you'd end up having to extend SQL to support it properly so that the performance penalty in question could be mitigated in all cases. edit: Rather than "you need to fully normalize everything," I should have said "you need to split all your data across multiple tables to eliminate the need for structured data within records." The performance penalty happens when you need to do this everywhere for sufficiently complex datasets.
  - dragonwriter 4y ago
    > The issue is that, to put things in 1NF, you need to fully normalize everything Not unless you redefine “fully normalize” you don’t.
  - goto11 4y ago
    I doubt querying JSON or XML or some other embedded structured format would be faster than querying normalized data. It might be true in some special cases, but certainly not in the general case of ad-hoc queries across nested data structures. But I totally agree SQL could be improved to make normalization feel like less of a burden. It really highlights a problem when it feels like its more convenient to just dump a JSON array into a field rather than extract to a separate table.
- 4y ago
Kwpolska 4y ago
SQL being a plaintext, human-friendly language is a good thing. SQL is a common skill transferrable between languages and environments, and it is also easily usable by non-developers. If we replaced SQL with some abstract language, what language would you use when talking to the database directly (via psql, sqlplus, or whatever)? Would you need to learn the PythonQuery, JavaQuery, and C#Query languages separately? Would the language used in ETL tools be different still? What language would you write database views, triggers, functions in?
- bb88 4y ago
  Have you looked at Quel? We have multiple languages to run our code on generic CPUs (C, Python, Java, Ada, Go, Rust, Lisp, etc.) Why must we have only one database language that doesn't do a very good job at Codd's relational calculus? So many people have drank the kool-aid that SQL is the answer. Maybe it's time to change this. The success of ORM's could be thought as the market voting against SQL. https://www.holistics.io/blog/quel-vs-sql/ https://www.holistics.io/blog/quel-vs-sql/
  - Kwpolska 4y ago
    The blogspam post you linked has zero examples of QUEL. Looking at Wikipedia [0], it seems much uglier and less readable than SQL. I am not a database theory person. I'm a developer who does not care about Codd's relational calculus. As for ORMs, they are great at solving simpler problems and CRUD data access, and they make the developer’s life easier by giving them nice objects to work with as opposed to raw database rows. However, any advanced analytics/reporting/summary queries tend to look awful with an ORM. [0]: https://en.wikipedia.org/wiki/QUEL_query_languages https://en.wikipedia.org/wiki/QUEL_query_languages
    - bb88 4y ago
      I was hoping you would address the points about composability rather than turning it into a beauty contest between the two languages.
- jasonhansel 4y ago

asalahli 4y ago

Also, caching can be done with materialized views

jasonhansel 4y ago
IIRC in Postgres you need to refresh an entire materialized view all at once, effectively recreating the entire table; you can't just have it update incrementally whenever the underlying data changes. I think SQL Server can do this, but...then you have to use SQL Server.
- mnahkies 4y ago
  I think it's being worked on for postgres but probably another major release or two away, quick Google came up with this https://pgconf.ru/en/2021/288667 https://pgconf.ru/en/2021/288667 but I'm sure I've come across discussion in postgres mailing lists / wiki in the past. Would definitely be a nice feature to have, without it I find the main use case I have for materialised views is batch processing where you want to prepare a complex result set and then stream process it in a Cronjob or similar
  - aidos 4y ago
    There’s also info on this page about it (incremental materialised views). https://wiki.postgresql.org/wiki/Incremental_View_Maintenance https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc... In the meantime my preferred technique is to have a column where I stamp the last generation time for each row and then I rebuild anything that’s changed since then (assuming all your source data has some sort of last updated stamp).
- branko_d 4y ago
  > I think SQL Server can do this, but...then you have to use SQL Server. Yes, SQL Server can update indexed views incrementally, but there are severe limitations: https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16#additional-requirements https://learn.microsoft.com/en-us/sql/relational-databases/v... If memory servers, indexed views have been in SQL Server for 20-odd years, and haven't seen meaningful improvements in all that time. We still can't do a LEFT JOIN, or join the same table more than once or MAX etc... The same story with T-SQL, which is firmly stuck in the '80s (not that other databases are better). There are some extremely powerful features in SQL Server that can be used effectively with some pain, but they could be so much better if Microsoft invested in fully fleshing-out their potential instead of chasing the latest buzzword. Sorry for the rant.
- tshaddox 4y ago
  Yep, which is why materialized views don't tend to work great for a lot of tasks where they initially seem to be the most natural implementation, particularly almost any view that's a feed or aggregate of data in the system over all time. It's so easy to just play with a simple SQL select query until you get it working, then throw it into a materialized view. It'll probably even work for a long time! But as soon as the data in the system grows and that refresh starts getting slower, you're stuck with a (potentially tricky or at least frustrating) migration to another implementation (maybe something like event sourcing).
cwillu 4y ago
Also computed columns in a covering index.

jrumbut 4y ago

The thing about #2, is poorly designed in comparison to what? Chances are high the application layer is written in JavaScript, PHP, Ruby, or Python. We don't even talk about nasty edge cases in those languages because they are uncountable.

jasonhansel 4y ago
Those languages all have first-class functions and OOP-style encapsulation. In SQL stored procedures (at least in Postgres), you can't have a variable that holds multiple records. You can't even define a variable inside of a block.
- ttfkam 4y ago
  DECLARE foo record[];
- nhinck2 4y ago
  Temp tables?
lmm 4y ago
None of those languages is as unpleasant as SQL. Not even PHP.
- billythemaniam 4y ago
  For me, I would much rather SQL especially when using DBT too to manage the queries like any other language. I thought the same way as you and SQL certainly has its warts, but I have grown to appreciate it's elegance. I think over the long-term it is easier to maintain because it is so concise and the most common second language among programmers.
- ttfkam 4y ago
  Once you start thinking in sets instead of collections of independent statements as per PHP et al, SQL starts looking a lot better. If you try to fit SQL into the general purpose programming language box, you're gonna have a bad time. A really bad time. SQL is a DSL for set theory. Nothing more. Nothing less.

goto11 4y ago

> in particular, its historically limited support for structured data within fields This is not particular to SQL though, and is the rationale behind the first normal form. Codd argued that any complex data structure could be represented in the form of relations, so adding non-relational structures would just complicate things for no additional power.

jasonhansel 4y ago
The issue is that, to put things in 1NF, you need to fully normalize everything, which has a big performance penalty since every query now has to JOIN a large number of tables together. Of course, an RDBMS could be designed to do that without a performance penalty, by storing data in a denormalized form and automatically translating queries for the normalized data accordingly. But SQL doesn't have the features you'd need to control and manage that sort of transparent denormalization. So you'd end up having to extend SQL to support it properly so that the performance penalty in question could be mitigated in all cases. edit: Rather than "you need to fully normalize everything," I should have said "you need to split all your data across multiple tables to eliminate the need for structured data within records." The performance penalty happens when you need to do this everywhere for sufficiently complex datasets.
- dragonwriter 4y ago
  > The issue is that, to put things in 1NF, you need to fully normalize everything Not unless you redefine “fully normalize” you don’t.
- goto11 4y ago
  I doubt querying JSON or XML or some other embedded structured format would be faster than querying normalized data. It might be true in some special cases, but certainly not in the general case of ad-hoc queries across nested data structures. But I totally agree SQL could be improved to make normalization feel like less of a burden. It really highlights a problem when it feels like its more convenient to just dump a JSON array into a field rather than extract to a separate table.
4y ago

Kwpolska 4y ago

SQL being a plaintext, human-friendly language is a good thing. SQL is a common skill transferrable between languages and environments, and it is also easily usable by non-developers. If we replaced SQL with some abstract language, what language would you use when talking to the database directly (via psql, sqlplus, or whatever)? Would you need to learn the PythonQuery, JavaQuery, and C#Query languages separately? Would the language used in ETL tools be different still? What language would you write database views, triggers, functions in?

bb88 4y ago
Have you looked at Quel? We have multiple languages to run our code on generic CPUs (C, Python, Java, Ada, Go, Rust, Lisp, etc.) Why must we have only one database language that doesn't do a very good job at Codd's relational calculus? So many people have drank the kool-aid that SQL is the answer. Maybe it's time to change this. The success of ORM's could be thought as the market voting against SQL. https://www.holistics.io/blog/quel-vs-sql/ https://www.holistics.io/blog/quel-vs-sql/
- Kwpolska 4y ago
  The blogspam post you linked has zero examples of QUEL. Looking at Wikipedia [0], it seems much uglier and less readable than SQL. I am not a database theory person. I'm a developer who does not care about Codd's relational calculus. As for ORMs, they are great at solving simpler problems and CRUD data access, and they make the developer’s life easier by giving them nice objects to work with as opposed to raw database rows. However, any advanced analytics/reporting/summary queries tend to look awful with an ORM. [0]: https://en.wikipedia.org/wiki/QUEL_query_languages https://en.wikipedia.org/wiki/QUEL_query_languages
  - bb88 4y ago
    I was hoping you would address the points about composability rather than turning it into a beauty contest between the two languages.
jasonhansel 4y ago