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

221–230 of 255 posts

Re: The startup's Postgres survival guide

#221

Post by frollogaston »

renegade-otter wrote: post #214
frollogaston wrote: post #203

Earlier quoted context omitted.

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.

I meant you give it back immediately when you're done with it. So usually after you commit, unless you want to hold it longer for some special reasons.

Re: The startup's Postgres survival guide

#222

Post by mjr00 »

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

Earlier quoted context omitted.

> 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.

Sure, and you need to do the build vs buy math, but why not take the "If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data." logic further and say

1. Don't use a third-party auth provider, if you can't build out an OAuth provider in an afternoon you shouldn't be entrusted with login credentials

2. Don't use Github, if you can't build out an internal Gitlab instance in an afternoon you shouldn't be entrusted with securing source code

3. Don't use Github Actions or CircleCI, if you can't self-host a Jenkins server in an afternoon you shouldn't be entrusted with secure deployments

4. Don't use a lawyer or outsourced HR, if you can't draft legally binding employment agreement in an afternoon you shouldn't be entrusted with hiring people

Re: The startup's Postgres survival guide

#223

Post by frollogaston »

hmokiguess wrote: post #8

Postgres is my favourite thing, but I find it's prohibitively costly when bootstrapping something that is lean and frugal. I end up with a mixture of serverless storage like DynamoDB, S3, DuckDB on S3, and SQLite. Am I crazy? How can one have a decent Postgres and not pay at least $100/mo (yes, when I say frugal I mean really frugal ... think solo founder that likes to stay on free tiers haha) -- I am aware of Neon/S…

Cheapest Amazon RDS Postgres is like $15/mo if that's cheap enough. If you're doing lots of projects and don't want to pay that for each one, you can CREATE DATABASE for each with separate ACLs.

You mentioned not wanting to spend admin hours fine-tuning a self-hosted instance though. Are you really hitting the DB hard enough for that to matter, but SQLite works fine? Cause I haven't tuned local Postgres in years.

Re: The startup's Postgres survival guide

#224

Post by alfons_foobar »

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.

ahh, gotcha! thanks!

Re: The startup's Postgres survival guide

#225

Post by hmokiguess »

frollogaston wrote: post #223
hmokiguess wrote: post #8

Postgres is my favourite thing, but I find it's prohibitively costly when bootstrapping something that is lean and frugal. I end up with a mixture of serverless storage like DynamoDB, S3, DuckDB on S3, and SQLite. Am I crazy? How can one have a decent Postgres and not pay at least $100/mo (yes, when I say frugal I mean really frugal ... think solo founder that likes to stay on free tiers haha) -- I am aware of Neon/S…

Cheapest Amazon RDS Postgres is like $15/mo if that's cheap enough. If you're doing lots of projects and don't want to pay that for each one, you can CREATE DATABASE for each with separate ACLs. You mentioned not wanting to spend admin hours fine-tuning a self-hosted instance though. Are you really hitting the DB hard enough for that to matter, but SQLite works fine? Cause I haven't tuned local Postgres in years.

Fair pushback, I think a lot of it is just stigma from not working close to the db layer and relying on these abstractions

I think most of the work I've done just fits in the other storage models and they scale really far really fast at $0 cost

I haven't paid for then ever, similarly, I have a few super tiny apps on Supabase and Neon too that cost me $0

On another project that got traction though, Postgres became better DX than DynamoDB and such, but that traction came with the overhead cost

The "fine-tuning" was setting up things like pgbouncer, restart, vaccuum, query optimizer, etc. Like, the db would always create some amount of work every fortnight. Coming from managed services, especially these serverless ones, they just work and you do nothing so it makes you spoiled.

Different mental model maybe?

On the "hitting the DB hard enough for that to matter but SQLite works fine" -- Absolutely! I have a <10gb SQLite inside a Lambda for some complex queries, and that gets refreshed whenever that data changes (which is not that often).

Re: The startup's Postgres survival guide

#226

Post by throwaway894345 »

ktaraszk wrote: post #197
dwedge wrote: post #90

Earlier quoted context omitted.

> Obviously "let RDS manage your database" doesn't require egregious read replicas Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it. > The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them. Assuming you're talking about letting RDS…

They won't let you. It's part of their business to keep you locked in.

More likely it’s just not worth going out of their way to support niche deployments like that. I’ve been using various clouds for years, I’ve done half a dozen cloud migrations, currently working at a multicloud org—the vendors aren’t doing much to lock us in. They very much enable us to move platforms by offering things like bulk data transfer tools, standard application runtimes like Kubernetes, workload identity federation (use external identities as principals in the cloud provider’s IAM system, etc).

Like I have no doubt that they’re all greedy bastards, but they aren’t doing much to lock people in. The people who complain about lock in are usually talking about “cloud providers making their services so much easier to use than bespoke platforms on bare Linux hosts such that no one will want to go back to the latter”.

Re: The startup's Postgres survival guide

#227

Post by mrkaye97 »

parmindergarcha wrote: post #220
mrkaye97 wrote: post #69

Earlier quoted context omitted.

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.

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

Re: The startup's Postgres survival guide

#228

Post by ComputerGuru »

williamdclt wrote: post #210
ComputerGuru wrote: post #23

Some comments and corrections: * Use uuidv7 not uuid in general (typically v4) * in addition to minimizing locked records, make sure your locks are ordered deterministically across all queries (eg by id asc, always) or you’ll deadlock (but postgres has a really good deadlock detector so you’ll more likely just error out if you’re lucky) * always use explain (generic_plan) to be able to a) copy-and-paste your queries…

good advice! > learn about GIN (and GIST) indexes yes, but also learn about the trade-offs and in particular the "pending list". The flush of the pending list can be slow, causing timeout, causing the list to not be flushed, causing the next write to trigger flush again and failing in the same way, which means you're having downtime. The default pending list size is weirdly high IMO

Very good point, but also unfortunately requires getting into the internals. If you can tolerate it just use fastupdate off.

Re: The startup's Postgres survival guide

#229

Post by nomel »

groundzeros2015 wrote: post #82
nomel wrote: post #80

Earlier quoted context omitted.

This worry relies on a zero day bug/memory exploit in one of the most widely used access methods for Postgres. This worry can be applied to every component of the software stack, including the OS.

Hmm, not really. Whether the kernel is managing memory for processes properly is different than asking whether a reused Postgres connection clears all relevant memory. But thanks for info about level of issue.

> This worry can be applied to every component of the software stack, including the OS.

I meant this type of bug, of accessing values in memory not explicitly meant for access, can exist at every level of the stack. It would be a very very very serious memory flaw/bug/exploit if a Postgres cursor could access data unrelated to that cursor, old or new, since it would mean serving bad data. Also, most people use connection pooling, after all, and you're not the first to consider this. A reasonable test for these concerns, if you don't want to believe the documentation/source, is looking for previous CVE related to it.

And, zeroing memory is more of a bandaid against a specific type of memory access bug, since accessing memory that isn't yours means they found a way to access beyond the bytes meant for the value, which often means you're going to be accessing allocated memory adjacent to what was zeroed.

So I guess your question is maybe, how robust is the code against unknown memory access bugs of a very specific type.

Re: The startup's Postgres survival guide

#230

Post by skullone »

drdexebtjl wrote: post #81
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…

> hiring at least a couple of DBAs and get more flexibility Every place I’ve ever worked at that had DBAs had the complete opposite of more flexibility. You have to do things the DBA’s way, and if their way doesn’t work for your service, you need to fight for their time and priority. Meanwhile every place I worked at where every team completely owned their databases + did periodic data recovery drills had much more f…

Now you have lots of DBs all probably operating inefficiently with only periodic recovery testing. This is the stuff DBAs do every day, and you're "hasn't happened to us... yet". You'll be just another lesson someday
Post reply on HN