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

141–150 of 255 posts

Re: The startup's Postgres survival guide

#141

Post by vanviegen »

mjr00 wrote: post #49
theallan wrote: post #3

Should one of the first things you do with a database not be to have a backup strategy? I understand that HA would be a "nice to have" when first starting out, but surly if you have a production db, a backup and restore plan should be on a survival guide? Neither appear to be mentioned here. What do you all use for your pg backups? Is Barman ( https://pgbarman.org/ ) still the way many do it? (I haven't deployed a ne…

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.

And then.. you're basically trapped inside the AWS cloud (due to egress costs and db latency). No thanks!

Re: The startup's Postgres survival guide

#142

Post by giovannibonetti »

> Because of all these connection footguns, external connection poolers like pgbouncer are great! If you can’t add this for whatever reason, in-memory connection poolers are a great second option. For example, because Hatchet is open-source, we don’t assume that all user databases use connection poolers, so we use pgxpool (an in-memory connection pool for Go) for this purpose.

Few people know that there is a major bifurcation when it comes to connection pooling implementation.

1. Most application connection poolers follow a first-in-first-out (FIFO) algorithm, which is simple enough to implement and is enough to make sure the application always has a connection available to connect to the database. It optimizes low latency, and works great from the point of view from the application. The problem is that it has few mechanisms to remove redundant connections, since the application is constantly keeping them all "warm".

2. PgBouncer and very few external poolers follow the inverse idea – last-in-first-out (LIFO), and they optimize for reducing the number of connections that reach Postgres, thus improving its throughput. The idea might seem crazy at first – the last connection used is the first one to be picked up again – but this algorithm automatically removes excess connections, which will get cold and get closed.

When starting a new application, option (1) is enough, but as it scales up enough, at some time it is recommended to use (2), since having hundreds of open connections to Postgres is bad for performance if you can use PgBouncer or similar to cut it by 90%. Postgres' process-per-connection design works much better when there are fewer connections reaching it.

Re: The startup's Postgres survival guide

#143

Post by frollogaston »

sandeepkd wrote: post #118
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…

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…

I've never actually known business requirements ahead of time, when I worked for a startup and for a large company. Stuff happens. You build your schema iteratively and yes, accept some temporary bloat when cols get added thoughtlessly.

The UUID backup/recovery thing isn't an issue if you're doing append-only. Joins on UUIDs are far slower, enough that even at small scale it can cause issues when you have many joins. Anyway I won't argue too hard against UUIDs cause they work too, just anything is better than using meaningful fields as the PKs.

Re: The startup's Postgres survival guide

#144

Post by frollogaston »

kentm wrote: post #129
xmprt wrote: post #122

Earlier quoted context omitted.

ORMs are just tech debt. Even if your highest cost is developer salaries, you're just pushing that cost down the line.

I'd also argue whether ORMs actually save that much time in practice. In Java, for example, the main time sink is the JDBC plumbing and its easy to use something like JDBI that handles that plumbing without abstracting away the underlying SQL. The application->database layer is pretty impactful and it pays to pay attention to it, because poor access patterns will cause a lot of trouble in the future, and its made wor…

Yeah, they'll often conflate the ORM with the nice stuff you want like connection pools. And the thing about time estimates is real. Another thing is they'll optimize too hard for having fewer tables.

Re: The startup's Postgres survival guide

#145

Post by loevborg »

My advice:

- Don't use long-running transactions. They are a risk for db health. Only use transaction when you have a strong justification

- Set idle_in_transaction_session_timeout to prevent a long-running transaction from holding on to locks or tuples

- Set lock_timeout for migrations to prevent a single DDL statement to bringing down your system

- Set statement_timeout to prevent an expensive query from bringing down your system

Re: The startup's Postgres survival guide

#146

Post by giovannibonetti »

> The reason I think it’s useful to view queries as binary—they either seq scan or they don’t seq scan—is: the more you micro-optimize a query, the more of a risk you take that the query planner goes rogue. If you stick to querying by primary keys and indexes, the query planner will have a much easier time.

It's also important to notice the query planner optimizes for the average case, but often it would be better for the app developer if it was optimized for the worst case. But optimizing for the former is a much more tractable problem, so no wonder that's what is implemented.

I had to fight against the query planner when it would optimize a query for the average user, with few rows in a given table, and it would pick one index that made sense for that situation and return a result in less than 10ms. However, when a heavy user issued the same query, depending on the exact parameters the worst case could take over 1 second. So I had to write a much more complex query to force it to take another path with a different index, which would be slower in the average case, but in the worst case would take still less than 100ms. Avoiding timeouts was much more important for my company than taking 10ms more in the average case.

Re: The startup's Postgres survival guide

#147

Post by giovannibonetti »

> FOR UPDATE SKIP LOCKED > The best way to think about this Postgres feature is that it reserves the rows that you’re selecting for use in your transaction without interfering with other queries. We use it primarily for implementing our job queue;

SKIP LOCKED is useful for implementing job queues with interactive transactions – you lock the row while working on it in the application and keeping the transaction open. For high-performance applications it is best to avoid interactive transactions at all, and just update the rows to "pending" immediately. There is no need for SKIP LOCKED in this case.

As a rule of thumb, as you scale up the application, you want to have less state in the database memory, and interactive transactions are just that. Idempotence beats atomicity at scale.

Re: The startup's Postgres survival guide

#148

Post by poncho_romero »

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?

Re: The startup's Postgres survival guide

#149

Post by atom_arranger »

0x696C6961 wrote: post #121
frollogaston wrote: post #103

Earlier quoted context omitted.

Yes, it's that. You do probably end up wanting to store some "latest" denorm tables at some point, but it takes surprisingly long to reach that point, and isn't hard when you get there. There are disadvantages to this, but it's a safe default. The alternative is possibly losing important data, finding out later you want historical records of things that are stored in kludgy separate tables, getting into more advanced…

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 of implementing CRUD with event sourcing in Postgres would look like, then decide if it’s too complex.

Re: The startup's Postgres survival guide

#150

Post by ozim »

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.

I have never seen a dev and we never would hire a dev that doesn’t know SQL and yet we still use ORM for each and every app we develop.
Post reply on HN