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

111–120 of 255 posts

Re: The startup's Postgres survival guide

#111

Post by kentm »

frollogaston wrote: post #105
kentm wrote: post #104

Earlier quoted context omitted.

For #4 I always tell people to assume 99% append only, but do consider the need for updates/deletes in edge cases. If any of the data is PII and you will be subject to GDPR (you probably want to be at some point) then you will need a way to hard delete it. A large portion of append-only datasets I’ve worked with have run into some edge case that required an update. Also if you’re doing an append only log plus mutable…

Yeah, deletes need to be on the table (no pun intended) for PII redaction, and also emergencies where you manually do it. Both those situations are much safer when the DB is normally append-only.

Agreed. Usually an updated-on timestamp is sufficient to cover your bases without over-complicating the table. And your PII redaction is probably going to be shredding or nulling the fields instead of hard record-level deletes.

Re: The startup's Postgres survival guide

#112

Post by j45 »

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…

Nice summary -

While it's not my first choice, or habit, in many use cases, using an ORM is still OK looking back in the start especially when the schema is being fleshed out prior to understanding what needs optimizing. It's trivial to start optimizing a query after taking it away from ORM. The new angle I think is the ability for LLMs to use ORMs given the documentation, etc.

The only other thing I'd say is the benefits of running something that works with Postgres, such as Supabase, Hasura, etc. It really can be the best of multiple worlds, especially in the beginning in terms of having flexibility in one place.

Re: The startup's Postgres survival guide

#113

Post by stackskipton »

traceroute66 wrote: post #21

I did a search in that post for "function", zero results. Unimpressive. Not even the most cursory of discussion of stored functions ? Given that many startup's Postgres instances will no doubt be backing some web-ui or app that takes untrusted input, surely they could have at least had a brief discussion about how stored functions can help against SQL injection attacks ? Not only that but it means you have to think,…

Stored Functions/Procedures tend to make database into monolith with API that everyone calls, with endless screaming when it gets too big and any schema change takes days to accomplish.

That's something that OP didn't discuss is shared databases vs services owning their own. When you are startup, it's really tempting to have services reach into database and skip API call.

Re: The startup's Postgres survival guide

#114

Post by frollogaston »

tracker1 wrote: post #110
frollogaston wrote: post #96

Earlier quoted context omitted.

I usually start by seeing how far I can get with just a single idempotent schema.sql file, usually good enough. Those migration tools have sort of a git within a git managing merge conflicts, which gets very messy with a team of SWEs, esp if rubberstamping Claude-generated PRs. I don't want to introduce that without a very clear reason why the single file with regular git merge tooling isn't good enough.

How do you handle schema changes after your project is in production? I mean, sure start with a unified schema file until you have a production release... deploy, populate with placeholder data, etc... but once released, having a file for each set of changes isn't a bad thing. Also, the management tools you can have single files for each view/sproc, etc... it's just schema migrations you need to take care of.

Make a PR that edits the .sql file, deploy to staging, deploy to prod. Git tracks changes to the file, and your CI should be aware of what commit it's on. (If you even have CI)

This only works if you don't care about being able to auto roll back DB changes without making a new commit, cause Postgres doesn't have a declarative DDL.

Re: The startup's Postgres survival guide

#115

Post by ozim »

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…

Don't use an ORM.

Highly debatable. When your highest cost is developers salaries.

Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col.

Easy to say, harder to not do when you have business requirements on table, customer pressure and budget already gone on discussing with DBA who maybe is right but you are burning money right here and right now. The same with point no. 9

Re: The startup's Postgres survival guide

#116

Post by frollogaston »

ozim wrote: post #115
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…

Don't use an ORM . Highly debatable. When your highest cost is developers salaries. Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col. Easy to say, harder to not do when you have business requirements on table, customer pressure and budget already gone on discussing with DBA who maybe is right but you are burning money right here and…

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.

Re: The startup's Postgres survival guide

#117

Post by jvidalv »

frollogaston wrote: post #92
edoceo wrote: post #89

Earlier quoted context omitted.

What's wrong with select for update? I've found it useful in a few places. It is that fixed when there is an append only source of truth?

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.

Re: The startup's Postgres survival guide

#118

Post by sandeepkd »

ozim wrote: post #115
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…

Don't use an ORM . Highly debatable. When your highest cost is developers salaries. Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col. Easy to say, harder to not do when you have business requirements on table, customer pressure and budget already gone on discussing with DBA who maybe is right but you are burning money right here and…

I would highly recommend using ORM's, with the caveat to know exactly when not to use them. Startups do not fall in that bucket.

1. Most of these advices are unfortunately impractical and incomplete for startups. A good Data Model is highly dependent on understanding the business requirements, data flows. Means unless you are repeating yourself in the same domain its really hard to come up with a good schema in first iteration.

2. Startups are in the mode of discovering the schema for most part

3. Deleting columns is harder than adding additional columns, no one takes that risk so everyone ends up with schema bloat

4. Once you go a little bigger you will realize that the integer based UUID are not that great of a choice. Those are separate tables which maintain those index counters and not part of your DDLs. They have their own set of issues with data merging, backups and recovery

For the OP, I looked at the repo (https://github.com/hatchet-dev/hatchet/blob/main/sql/schema/...) ,

1. seems like the schema has database functions - Thats a potential scaling issue, plus you are asking vertical only scalable component to do something which could have taken care by horizontally scalable component

2. TEXT datatype for pretty much every attribute - this is a footgun, you cant use them for indexes properly, in the absence of length checks they can be abused from client side

Re: The startup's Postgres survival guide

#119

Post by alfons_foobar »

frollogaston wrote: post #99
mrkaye97 wrote: post #97

Earlier quoted context omitted.

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

Oh wow. Dep injection for DB connections is nasty.

I might be outing myself as a noob here, but... what is the (better) alternative?

Re: The startup's Postgres survival guide

#120

Post by InsideOutSanta »

frollogaston wrote: post #116
ozim wrote: post #115

Earlier quoted context omitted.

Don't use an ORM . Highly debatable. When your highest cost is developers salaries. Don't reinvent a type system by having a single table where each row can mean many different things depending on a "type int" enum col. Easy to say, harder to not do when you have business requirements on table, customer pressure and budget already gone on discussing with DBA who maybe is right but you are burning money right here and…

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.
Post reply on HN