ttfkam

68 ms·

901.
by ttfkam 4y ago
Merge mutates data. A join is simply a read from an associated set of data.
902.
by ttfkam 4y ago
SQL standards are a weird thing. Rather than wait for a spec and then get implementers like in the browser space (for the most part), SQL vendors will solve a particular customer's problem, submit their solution to the standards body,
903.
by ttfkam 4y ago
WITH RECURSIVE is how graph/tree queries work in modern SQL (assuming you aren't using something like ltree [0]. The first part of the recursive CTE identifies the root record and then UNION ALL with the subsequent items r
904.
by ttfkam 4y ago
SQL is a programming language. It's simply one of if not the only successful fourth generation programming language in a world replete with third generation ones. The error is confounding a fourth generation DSL for set theory with a
905.
by ttfkam 4y ago
Yes, just like in browsers where one API is faster in Chrome and slower in Firefox or vice versa. Then Safari drops in and completely upends the notion of "best strategy". The underlying implementation will always affect top level
906.
by ttfkam 4y ago
As an aside, avoid OR as much as possible in your WHERE clauses. Adjust your relations if necessary. ORs can really blow the planner up and slow things down. ANDs are reductive wrt indexes. ORs make multiple passes on the indexes necessary.
907.
by ttfkam 4y ago
Not too terribly cumbersome when you have jsonb_populate_record in Postgres. https://www.postgresql.org/docs/current/functions-json.html
908.
by ttfkam 4y ago
No dynamic SQL necessary. jsonb_populate_record(…) was made precisely for this kind of scenario. Combine with an INSERT…ON CONFLICT DO UPDATE statement and Bob's your uncle. https://www.postgresql.org/docs/current&
909.
by ttfkam 4y ago
https://news.ycombinator.com/item?id=34207659
910.
by ttfkam 4y ago
This is precisely the situation the article solves but without repeating AND NOT is_deleted after every DB query in every app accessing the database. No full DB backup/restore needed. INSERT INTO mytable SELECT recove
911.
by ttfkam 4y ago
Wouldn't this be better handled by marking the user entry as inactive and then reading that value from the join rather than setting every conversation thread as is_deleted/is_deactivated? I feel like you are solving a different pr
912.
by ttfkam 4y ago
Great solution. Unfortunately only works on installations that support extensions, which excludes most managed database services like AWS RDS. I like how you tried to track the standard as close as possible. I've seen (and written) ad
913.
by ttfkam 4y ago
Sadly, we've got a ways to go before anyone can go all in on AV1. Without being publishable on the web, it's hobbled/niche. Mostly it's Apple needing to step up despite their substantial investment in HVEC. https:/
914.
by ttfkam 4y ago
Nothing logical ever does. The human brain is a pattern recognition engine. You just haven't had enough time with these particular patterns. It's like spoken language; it just takes time, exposure, and practice. Just find the righ
915.
by ttfkam 4y ago
But isn't that true of most programs once they reach a certain level of complexity? Especially after they've been optimized? Eventually one relies on tests to ensure your changes don't introduce bugs and unwanted side effects
916.
by ttfkam 4y ago
Just to chime on among the naysayers, I just read your regex at "the speed of flow". Made perfect sense to me. (I'd have used non-matching groups, but that's a very minor nit.) The equivalent code to parse this is much l
917.
by ttfkam 4y ago
Agreed
918.
by ttfkam 4y ago
Also, views are the essence of composability. As for testing in the Postgres sphere, there's pgTAP and pg_prove. https://pgtap.org/ Extension development includes a built-in unit testing harness as well. For use with a
919.
by ttfkam 4y ago
I really wish more people understood this. Thank you. ACID is not just a good time on a Saturday night.
920.
by ttfkam 4y ago
I could agree with HTML rendering and compression, but sending an email during user registration or as part of an event notification is 100% business logic.
921.
by ttfkam 4y ago
CTEs are quite composable. I might be spoiled by Postgres, but types are quite robust there. Replace "NULL" with "unknown" in your head, and the ternary makes more sense. When you're building your schema, does an un
922.
by ttfkam 4y ago
Exactly. Effective use of an ORM requires knowledge of the ORM's API in addition to SQL, not in lieu of it. Using an ORM without a firm understanding of SQL is a recipe for disaster (or at least glacial performance).
923.
by ttfkam 4y ago
Hold up. I need some clarification here. SQL databases are opaque with regard to resource usage but Kafka is completely transparent in this regard? That is your personal experience and assertion?
924.
by ttfkam 4y ago
If you have a single DB instance in production, something has already gone wrong. Read replicas.
925.
by ttfkam 4y ago
> Recently someone started appending to a json field type over and over in our production database. And then on some queries, postgres crashed due to lack of memory. The fix was to remove the field and code that constantly appended to it
926.
by ttfkam 4y ago
If the biz logic is about structured data storage and transformation, it belongs in the database. If the biz logic relates to data validation (data types and data affinity), it belongs in the database (and probably should be checked elsewhe
927.
by ttfkam 4y ago
Excellent book! I was only about 25-30 pages into the first edition when it all finally clicked for me. I've never feared a regex since.
928.
by ttfkam 4y ago
It other words, you're not caching/reusing you regex patterns? I think I see the cause of one of your recurring performance problems.
929.
by ttfkam 4y ago
Those who fail to learn the lessons of SQL are doomed to re-implement it… poorly. SQL-92 is no longer the baseline. Now we have CTEs, laterals, graph queries, JSON storage, system versioning, and more within a reasonably concise DSL for set
930.
by ttfkam 4y ago
This isn't just a younger dev thing. This attitude was common even twenty years ago. And for the record, I love both SQL and tools like Prisma. (I actually prefer Postgraphile, but that's a minor distinction without a substantive

More ›