Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The startup's Postgres survival guide

hatchet.run

Post reply on HN
Print view View original

211–220 of 255 posts

Re: The startup's Postgres survival guide

#211

Post by renegade-otter »

mikeocool wrote: post #109
renegade-otter wrote: post #93

Earlier quoted context omitted.

Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.

If your end point does something like: * read from the database * make a request to an API (or really any kind of long running non-database thing) * write to the database You're going to end up with a transaction that is open way longer than it needs to be, particularly if you're upstream API is misbehaving, which will potentially end up causing a lot more grief.

You can easily deal with these cases by tactically using transactions, but a vast majority of code can be simplified by dealing with an HTTP endpoint as an atomic concept. This all depends on what you are building. A classic CRUD app will benefit from this approach, microservices - not so much,.

Re: The startup's Postgres survival guide

#212

Post by williamdclt »

zdc1 wrote: post #159
dwedge wrote: post #62

Earlier quoted context omitted.

At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month. Plus managed database restricts what you can do with the database - sometimes in really annoying ways. So while I partly agree with you, a lot of companies don't really need HA, read replicas, or…

You can use RDS without HA. Many people probably should do this, but no one wants to tell their boss they want to disable RDS HA and wear 1h of downtime a year so they can cut 50% of their spend.

> no one wants to tell their boss they want to disable RDS HA and wear 1h of downtime a year so they can cut 50% of their spend.

If you're a SaaS, 1h of downtime costs a lot of money. In reputation, in lost business, in support time, in on-call response, in making a public postmortem and sharing it to customers and dealing with responses... For saving a few thousand bucks, it's often indeed a very bad deal.

Re: The startup's Postgres survival guide

#213

Post by williamdclt »

9dev wrote: post #188
OrangeDelonge wrote: post #180

Earlier quoted context omitted.

RDS gets you easy integration with the rest of the AWS services, which as a startup, you’re probably using quite a few of. Also, if you are nickel and diming over expenses in the 50€ range, you’re probably at the wrong startup.

That was not quite the point I was making, which is that the equivalent RDS setup would come in at around ten times of that .

ten times 50euros? Sounds like a very reasonable deal to not have to think about these things or spend the time setting it up.

Re: The startup's Postgres survival guide

#214

Post by renegade-otter »

frollogaston wrote: post #203
alfons_foobar wrote: post #184

Earlier quoted context omitted.

Sorry, I am being dense... how does that solve the problem? I still have to get a connection from the pool, I just do it inside the function body now, right? So this @app.get("/users") def get_users(conn = Depends[get_db_conn]): users = conn.execute("SELECT * FROM users") return users would become that instead: @app.get("/users") def get_users(pool = Depends[get_db_pool]): with pool.get_conn() as conn: users = conn.e…

The idea is you only take a connection from the pool when you need to touch the DB, then you give it back immediately. It's very possible that's only a small fraction of the time spent in some handlers. If you inject the connection, you always hold it through the entire request.

No - you do not always give it back immediately in many cases as you have a transaction, which cannot "change hands". If a write connection makes consecutive updates to the DB, you must see it through before closing.

Re: The startup's Postgres survival guide

#215

Post by 9dev »

williamdclt wrote: post #213
9dev wrote: post #188

Earlier quoted context omitted.

That was not quite the point I was making, which is that the equivalent RDS setup would come in at around ten times of that .

ten times 50euros? Sounds like a very reasonable deal to not have to think about these things or spend the time setting it up.

If expending 6k per year on your database alone sounds reasonable to you, we have very different perspectives on a good use of investor funds.

We’re not talking about maintaining your own k8s cluster here mind you, but a basic Postgres setup. If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data.

Re: The startup's Postgres survival guide

#216

Post by mjr00 »

9dev wrote: post #215
williamdclt wrote: post #213

Earlier quoted context omitted.

ten times 50euros? Sounds like a very reasonable deal to not have to think about these things or spend the time setting it up.

If expending 6k per year on your database alone sounds reasonable to you, we have very different perspectives on a good use of investor funds. We’re not talking about maintaining your own k8s cluster here mind you, but a basic Postgres setup. If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data.

> We’re not talking about maintaining your own k8s cluster here mind you, but a basic Postgres setup. If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data.

Intelligence is being able to set up your own postgres database with backups, PITR, retention policies, HA/replicas, and everything else needed to prevent catastrophic data loss. Wisdom is realizing that's a solved problem completely unrelated to your business, so you don't have to.

Re: The startup's Postgres survival guide

#217

Post by 9dev »

mjr00 wrote: post #216
9dev wrote: post #215

Earlier quoted context omitted.

If expending 6k per year on your database alone sounds reasonable to you, we have very different perspectives on a good use of investor funds. We’re not talking about maintaining your own k8s cluster here mind you, but a basic Postgres setup. If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data.

> We’re not talking about maintaining your own k8s cluster here mind you, but a basic Postgres setup. If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data. Intelligence is being able to set up your own postgres database with backups, PITR, retention policies, HA/replicas, and everything else needed to prevent catastrophic data loss. Wisdom is realizing that's a…

Well yeah, you can apply that to most problems you encounter as a company - there are service providers for pretty much anything you can imagine. That doesn't mean it's smart to spend a huge chunk of your revenue on OpEx, however.

Re: The startup's Postgres survival guide

#218

Post by dwedge »

williamdclt wrote: post #212
zdc1 wrote: post #159

Earlier quoted context omitted.

You can use RDS without HA. Many people probably should do this, but no one wants to tell their boss they want to disable RDS HA and wear 1h of downtime a year so they can cut 50% of their spend.

> no one wants to tell their boss they want to disable RDS HA and wear 1h of downtime a year so they can cut 50% of their spend. If you're a SaaS, 1h of downtime costs a lot of money. In reputation, in lost business, in support time, in on-call response, in making a public postmortem and sharing it to customers and dealing with responses... For saving a few thousand bucks, it's often indeed a very bad deal.

Most of those are already priced in (support costs, time dealing with a postmortem), and an hour of downtime a year isn't going to meaningfully harm the reputation of most companies, I'd wager a lot of clients wouldn't even notice.

Of course this isn't true for some companies and the cost of downtime far exceeds the cost of HA. Most companies think they are that company, most aren't. And even if they are, "AWS had an outage" does a lot of lifting.

Every time I get a new client they need constant point in time backups, failover, HA everything. Usually when the costs are explained they change their mind and an hour old snapshot restore is actually fine

Re: The startup's Postgres survival guide

#219

Post by ozim »

InsideOutSanta wrote: post #120
frollogaston wrote: post #116

Earlier quoted context omitted.

It was kinda debatable until people started Claude coding everything. Even before, I would've said every SWE should just know SQL, it's not much buy-in to understand the foundation of like your entire backend. Also I'm not a DBA if that's what you meant.

Leaning SQL is arguably less dev work over the long run than learning an ORM and then learning how it works so you can fix performance issues.

Do you have any meaningful experience as a software developer?

That’s a silly take. I somehow learned over the long run: SQL different flavors, couple of ORMs, multiple programming languages and multiple frameworks. Not counting different troubleshooting different operating systems and different applications I had to work with.

Re: The startup's Postgres survival guide

#220

Post by parmindergarcha »

mrkaye97 wrote: post #69
saltcured wrote: post #63

Earlier quoted context omitted.

This kind of advice is very dependent on the scenario. If you are doing some kind of full cross product where the join creates a much larger set of rows, it could optimize the DB load and network traffic to fetch the source sets and then generate the permuted set locally. But, many inner join patterns are selective. They produce a much smaller output than the source records. The traffic to pull all the records and th…

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 simi…

Noted that you only use this sparingly but you could try :-

* Materialised views (especially if the computation/joins are particularly nasty).

* Left outer joins are a good alternative to 'joins using case' and more likely to use the index.

Post reply on HN