Indeed! Materialized views won't work here as PG doesn't support "always updated" / "auto-refreshing" materialized views natively (although you can get something similar with extensions like TimescaleDB). Left joins are the thing we're often trying to avoid though, especially when the join conditions are involved, as we've seen the planner just make poor decisions in the past at unpredictable times. That's exactly the sort of situation where you might reach for this sort of trick
+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-...) by suggesting using dependency injection to manage database connections, only to start seeing connection pool exhausted errors as soon as the number of concurrent requests exceeds the number of allowed connections.
Thanks! I should have clarified - we haven't been using this pattern for selective joins. Strongly agreed that pulling down extra data into memory and then doing the filtering doesn't make much sense. We've found it useful in the case where it's hard to write a query where the planner _does_ make good decisions because of the complexity of the join conditions (e.g. joins using cases, a boolean "or", or something similar).
Also, to re-emphasize: we do this rarely, but it's been helpful the times we've done it
I, at least, don't know of a perfect fix here. Re: the original comment - Postgres will also error on deadlocks after it detects them without setting your isolation level to Serializable, but I agree with you that often retrying doesn't help, and could even cause cascading / snowballing failures if you have a backlog of retries piling up because of deadlocks.
I don't know if there's a good solution, really. We've fixed deadlocks incrementally over time as we've found them, which has worked pretty well, but of course that means also needing to deal with the "finding" part, which has generally come in the form of lots of `deadlock detected` log lines and errors (and retries accompanying those).
One thing that might be worth auditing is why there are two different bits of application code that are updating the same rows in two different tables in different orders. I know it's a contrived example, but it seems like it could be a code smell to me. Maybe this is the kind of thing that arises when two different subteams are working on the same database and are largely siloed.
Alexander will likely have more thoughts here as well, just my two cents!
One small addendum here is we've had a lot of success performing joins in memory in a few very specific situations where the alternative is a single, often overcomplicated query. I've heard / seen advice many times in the past about performing fewer round trips to the database being something to optimize for (often good advice!). Sometimes this is taken too far, resulting in overly-complex queries requiring complicated JOIN or UNION logic, CASE logic, and so on.
We have a couple of places in our codebase where we perform two or more simpler queries independently instead, and then loop through their results and use maps to match the relevant rows. Conventional wisdom often suggests this path will hurt performance because of the extra database round trip in addition to the loops needed to perform the join, but it is actually beneficial in these cases because of more predictable query planning behavior. We use this trick sparingly, but it can be helpful in a pinch.
Note that some ORMs will also do this for you in the background, which we don't necessarily endorse, and we try to use this sparingly when writing a single query on its own is not realistic.
I was just going to post this, and searched first and landed here. Not much to say beyond that reading this piece made me so happy, and brought back tons of nostalgia for days I'd long forgotten.
I've been using Elixir, which has been wonderful, mostly because of how amazing the built in `Enum` library is for working on lists and maps (since the majority of AoC problems are list / map processing problems, at least for the first while)
IMO this is very dependent on the risk of cutting once, so to speak. I'd imagine that at PostHog, the idea is there's little risk of cutting many times - iterating - and more damage is done by the measuring taking far too long.
Mostly to learn some Rust and because I thought most of the features of Splitwise worth paying for would be fun to build. Been loving working in Axum and getting to implement some fun database things
Hey everyone!
I built a little app I thought people in this community might like / find useful, so I thought I'd share here. It's pretty MVP so go easy on me ;)
The app: https://jobcrawler.matthewrkaye.com/
A blog post about it: https://matthewrkaye.com/posts/2023-11-10-jobcrawler/jobcraw...
The idea is to make it easier to find jobs at companies you're interested in (especially in tech) and track your search without the usual bullshit of LinkedIn, Indeed, etc. I also won't sell your data, it's free (for now at least until my hosting costs go up), etc.
Give it a shot and lmk what you think!
I built an app for job searching with some features I've found useful, and I'm excited to share it with HN! The main features include: job recommendations, automated emails when relevant, new postings go up at companies you're interested in, and analytics.