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

151–160 of 255 posts

Re: The startup's Postgres survival guide

#151

Post by frollogaston »

atom_arranger wrote: post #149
0x696C6961 wrote: post #121

Earlier quoted context omitted.

Using event sourcing instead of basic crud should go on a startup suicide guide ...

I don’t have a lot of experience related to this so I’m just noting some things. Some people in this thread don’t seem to think it’s that hard or overcomplicated. When reading Designing Data-Intensive Applications my main takeaway was that event sourcing can make it easier to solve a lot of issues like performance, scaling, consistency, auditability, etc. It would be interesting to look into what a low overhead way o…

Try making Hackernews lite. Users can post, comment on posts, vote/unvote posts, and delete their own posts and comments. CRUD tables might be post, comment, maybe vote. Event tables might be create_post, delete_post, create_comment, delete_comment, vote, unvote.

Re: The startup's Postgres survival guide

#152

Post by preg_match »

sklarsa wrote: post #139
frollogaston wrote: post #88

This advice is good, but every startup I've worked with has run into lower hanging fruit than this even. Less scaling problems and more just organizational. Usually what fixes that is: 1. Don't use an ORM. 2. Use serial PKs, not meaningful fields (article mentions this). 3. Use jsonb if needed, but sparingly. 4. Make your source of truth append-only, meaning you only insert, never update or delete. You can have secon…

There's nothing wrong with an ORM if you want to move quickly and get something up-and-running, which is the case for pretty much every startup who needs an article like this. As long as you're aware of the typical footguns (N+1 queries) and understand your ORM's lazy-loading scheme, IMO it's worth the tradeoff of developing yet another way to manage and parametrize your queries. I'd rather spend the time building ou…

The main problem is ORMs are more work, because you need to understand the ORM. That’s its own beast, each ORM is different, and they change between releases. But SQL is something you already should understand, so you get to reuse the knowledge.

Also you can make SQL much more tolerable if you use migrations. That’s really where ORMs shine, but you don’t need the rest. Also query builders, although those also have a risk of abuse similar to ORMs. 99% of SQL should be static. That sounds like an absurdly high percent but it’s true if you use all the SQL features.

Re: The startup's Postgres survival guide

#153

Post by frollogaston »

jvidalv wrote: post #117
frollogaston wrote: post #92

Earlier quoted context omitted.

Right, that whole class of problems mostly goes away when you're not mutating your sot. I've actually never needed to use SELECT FOR UPDATE that I can think of. It does have its legitimate uses, but there are footguns that general SWEs not super familiar with DBs won't know about, like how it still doesn't prevent all types of race conditions unless you're in SERIALIZABLE mode.

For games is a must have.

If there's a high frequency of changes from a single user all being stored, probably need more specific approaches like that

Re: The startup's Postgres survival guide

#154

Post by outworlder »

Tostino wrote: post #98
saltcured wrote: post #91

Earlier quoted context omitted.

I think you need to analyze the disaster scenarios and recovery costs you are trying to balance before you can really evaluate these different options. If you are on reasonable storage, I think it is arguable that the main reason you would have to recover is due to a software failure leading to corruption of the PostgreSQL backing state files. Otherwise, you would simply be restarting PostgreSQL on your durable and a…

Using something like EBS for your postgres data directory is, IMO, a bad idea. If you've already made that mistake, yeah I suppose using PG dump isn't that bad. I'd much rather just do it right though. Direct attached storage (or whatever storage is fastest / lowest latency for the environment you have available). Setup pgbackreset or barman, use WAL archiving and setup block incremental backups with a new full backu…

If you are on AWS, the direct attached storage option you have is ephemeral volumes. That's bad. If the hypervisor fails you lose data. Which is fine, you have replicas. If there's an event that takes out multiple machines at once, even for a moment, you are hosed(this can be AWS issues, or could be as simple as automation misbehaving and shutting machines down).

I'm all for treating DB as cattle, but your cattle needs to be able to survive long enough.

EBS is expensive but it works for PG. AWS uses EBS for RDS databases. Calling that a 'mistake' is a tall order.

Re: The startup's Postgres survival guide

#155

Post by ozim »

poncho_romero wrote: post #148
frollogaston wrote: post #88

This advice is good, but every startup I've worked with has run into lower hanging fruit than this even. Less scaling problems and more just organizational. Usually what fixes that is: 1. Don't use an ORM. 2. Use serial PKs, not meaningful fields (article mentions this). 3. Use jsonb if needed, but sparingly. 4. Make your source of truth append-only, meaning you only insert, never update or delete. You can have secon…

In the PHP back end I work on, I find the ORM immensely helpful because we need to instantiate objects to do things like permissions checks. The alternative seems like much more work. What am I missing with regards to ORMs being a bad idea?

It is just „SQL people” crying out not knowing how to work with ORM. They always claim that devs who use ORM don’t know SQL. But I never seen or hired a dev that doesn’t know SQL and yet for each project we use ORM.

I also never seen anyone claiming that you doesn’t have to know SQL and ORM is enough from the opposite side.

If you really run into spot where your ORM breaks you can always drop to SQL. If you build project „SQL first” you robbed yourself from ORM upsides and you are bound to badly implement your own one in the long run.

Re: The startup's Postgres survival guide

#156

Post by foo42 »

frollogaston wrote: post #88

This advice is good, but every startup I've worked with has run into lower hanging fruit than this even. Less scaling problems and more just organizational. Usually what fixes that is: 1. Don't use an ORM. 2. Use serial PKs, not meaningful fields (article mentions this). 3. Use jsonb if needed, but sparingly. 4. Make your source of truth append-only, meaning you only insert, never update or delete. You can have secon…

On point 4, is the general flow that an "update" would read the current latest, check whatever conditions, then rather than updating, insert a new record (all in a transaction), or is your append only sot more an ordered "log" of update requests with the result of any conditional operations requiring a replay (from at least a check point). Or something else entirely.

I'm generally an immutable first, type developer but ive not had to do much schema design for a while and looking back to previous attempts we never hit scale to stress test my approaches.

Re: The startup's Postgres survival guide

#157

Post by frollogaston »

ozim wrote: post #155
poncho_romero wrote: post #148

Earlier quoted context omitted.

In the PHP back end I work on, I find the ORM immensely helpful because we need to instantiate objects to do things like permissions checks. The alternative seems like much more work. What am I missing with regards to ORMs being a bad idea?

It is just „SQL people” crying out not knowing how to work with ORM. They always claim that devs who use ORM don’t know SQL. But I never seen or hired a dev that doesn’t know SQL and yet for each project we use ORM. I also never seen anyone claiming that you doesn’t have to know SQL and ORM is enough from the opposite side. If you really run into spot where your ORM breaks you can always drop to SQL. If you build pro…

The issue is if you use the ORM, you still need to understand the DB it's on top of. There isn't much point in using such a leaky abstraction when it's easy enough to just use SQL.

Re: The startup's Postgres survival guide

#158

Post by sgarland »

tracker1 wrote: post #6

On migrations, there's a .Net tool called Grate that I tend to use for schema migrations... I don't use all the features, but it works well... using a migration stack in a repository for deployments and a similar tool is IMO more reliable than magic comparison tools or hand migrations in practice. You should defensively write your migrations as much as possible so that re-runs are relatively safe, though the tool hel…

> Even with indexes, joins are costly, especially under load at scale with millions of simultaneous users. You can avoid a lot of this by simply having that sub-table information inside a JSON field with the row in question.

They’re really not that bad. Even on large-ish tables (hundreds of millions of rows), the typical query time I see for a query with 1-2 inner joins is 1-2 msec. That can of course vary with result set size, but in general it’s going to be dwarfed by network RTT.

If you’ve tested your schema with normalized and denormalized versions and found a significant difference that justifies it, by all means, but IME query speed for any non-trivial query is generally dominated by query shape, index design, and schema design (specifically for clustering indices, not taking advantage of it to have physical and logical tuple correlation).

Re: The startup's Postgres survival guide

#159

Post by zdc1 »

dwedge wrote: post #62
mjr00 wrote: post #49

Earlier quoted context omitted.

I might get flak for saying this but if you aren't a postgres expert already: just use RDS or a similar cloud DB. The amount of money you're saving by hosting and managing your own postgres instance is absolute peanuts compared to having battle-tested infrastructure for HA, backup and restores, point-in-time recovery, read replicas, etc.

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.

Re: The startup's Postgres survival guide

#160

Post by frollogaston »

foo42 wrote: post #156
frollogaston wrote: post #88

This advice is good, but every startup I've worked with has run into lower hanging fruit than this even. Less scaling problems and more just organizational. Usually what fixes that is: 1. Don't use an ORM. 2. Use serial PKs, not meaningful fields (article mentions this). 3. Use jsonb if needed, but sparingly. 4. Make your source of truth append-only, meaning you only insert, never update or delete. You can have secon…

On point 4, is the general flow that an "update" would read the current latest, check whatever conditions, then rather than updating, insert a new record (all in a transaction), or is your append only sot more an ordered "log" of update requests with the result of any conditional operations requiring a replay (from at least a check point). Or something else entirely. I'm generally an immutable first, type developer b…

It's the first thing, read-then-insert, not blind insert. Also you don't always need to do the read and the insert in the same transaction.
Post reply on HN