281–290 of 464 posts
Earlier quoted context omitted.
Define "big." Double writing in some form is the only option for live migration and you either bolt it on later or plan for it now.
When the overhead of the double writing becomes substantial enough affect the architecture.
I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…
I like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need. When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the…
If your reporting queries are hard or complex it's because you did a bad job architecting the reporting tables. Reporting queries should almost always be the simplest form of query if you designed your warehouse properly.
Days with sales is easy with a simple SUM or COUNT and GROUP BY.
Days without sales is easy if you think about this dimensionally.
SELECT dim_days.day_of_month,
COUNT(fact_orders.order_id)
/*
Select from dim_days first because you want every day.
*/
FROM dim_days
/*
Outer join to the fact table to pull in the data you have and add that to your dates.
*/
LEFT JOIN fact_orders
ON dim_days.calendar_day = fact_orders.order_day
/*
Filter results by the desired range.
*/
WHERE dim_days.month = 4
AND dim_days.year = 2020
GROUP BY dim_days.day_of_month;
"SQL is easy. Data is hard."
Earlier quoted context omitted.
> The application language was a pass through later between the client and the database. This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with ar…
In my observations, ORM use has a perspicuous relationship to piles of arcane spaghetti code. Not to mention, 50%+ of ORM managed DB schemas that I've observed don't have proper constraints, indexes, relationships, etc. Because the developers using the ORM think it's a magical tool that makes understanding SQL and relational databases optional.
Here's a fun bug I had a few years ago - Had a postgres database which was using pgbouncer for connection pooling. The most senior developer (24yo or so) we had on the project was using Go to connect to the database to write some simple reports, but each report took hours to run, and often had to sleep for 30+ minutes. So, after a while, pgbouncer would kill their connection, and their report would die. No other appl…
Earlier quoted context omitted.
> The application language was a pass through later between the client and the database. This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with ar…
There's a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures) Here's a powerful mindset trick: think of SQL views as an sort of a REST API , but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical). I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API tha…
I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…
I struggle with lack of experience with SQL by always being told that I should always use an ORM or I would regret it in the future when I would change database technology. I'm in the future now and spend a lot of time debbuging the ORM and the sql statements it produces, when I could split that work in half by not using the orm at all. Would also have a lot more experience with sql so there would probably be less bu…
Earlier quoted context omitted.
There's a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures) Here's a powerful mindset trick: think of SQL views as an sort of a REST API , but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical). I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API tha…
> highly performant API that outperformed REST at scale -- it's called SQL You are conflating many disparate things here. SQL is a language (DSL) for accessing data. REST is a protocol and a data transport method (one could surmise a way to do REST without HTTP, but when reasonable people refer to REST they mean HTTP (over TCP (over IP (etc.)))). Even REST is not an API. You can't do anything with a GET or a POST wit…
Earlier quoted context omitted.
QUEL[1] was like that, but thanks to Oracle SQL won. [1] https://en.wikipedia.org/wiki/QUEL_query_languages
One thing I never understood is that the SQL language, and its alternatives, share the same theoretical IR -- the relational algebra -- it shouldn't be that difficult to implement for postgres/mysql alternative relational languages like QUEL or Datalog. Or even just a simplified SQL with a sane, consistent syntax. I know PG has a bunch of procedural-language alternatives, but afaik, no relational-language alternative…
libpq refers to PostQUEL.
Ingres became Post-Ingres which became POSTGRES which became Postgres95 which became PostgreSQL.
Earlier quoted context omitted.
There's a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures) Here's a powerful mindset trick: think of SQL views as an sort of a REST API , but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical). I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API tha…
Are you also promoting CQRS?