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

161–170 of 255 posts

Re: The startup's Postgres survival guide

#161

Post by 10000truths »

frollogaston wrote: post #94
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.

That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.

Every RDBMS out there has an option to configure a DB-enforced transaction timeout. But that configuration is tied to a connection, not to a transaction. So using that configuration means giving up connection pooling. Which a lot of people don't want to do because it impacts latency and DB resource usage.

Re: The startup's Postgres survival guide

#162

Post by Tostino »

outworlder wrote: post #154
Tostino wrote: post #98

Earlier quoted context omitted.

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

[flagged]

Re: The startup's Postgres survival guide

#163

Post by frollogaston »

waisbrot wrote: post #131
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…

This is an excellent distillation. Yes, in practice you may find that one or two of these don't apply to your own special start-up. But probably they all actually do.

Thanks, that's what I was going for.

Re: The startup's Postgres survival guide

#164

Post by frollogaston »

10000truths wrote: post #161
frollogaston wrote: post #94

Earlier quoted context omitted.

That's an easy way to accidentally leave a xact open way too long. You might have enough connections in to support this normally, but when things go slightly wrong, they go very wrong.

Every RDBMS out there has an option to configure a DB-enforced transaction timeout. But that configuration is tied to a connection, not to a transaction. So using that configuration means giving up connection pooling. Which a lot of people don't want to do because it impacts latency and DB resource usage.

Every xact within a given connection will use the same connection-wide config, but the timeout is counting how long a single transaction takes, right? I don't see why you'd need to give up pooling for this unless you need different settings per xact.

Re: The startup's Postgres survival guide

#165

Post by wastedpotencial »

thisismyswamp wrote: post #73

the first rule of database management is to not host or manage your database unless you are willing to pay someone to do it full time

can you share what are the common pitfalls with self hosting a postgres image, as I'm planning to do? I know self hosting will bite my ass sooner than later, I just want to be somewhat ready and prevent easy mistakes

Re: The startup's Postgres survival guide

#166

Post by 0x696C6961 »

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…

Don't trust anyone who tells you event sourcing is simple to implement.

Re: The startup's Postgres survival guide

#167

Post by 10000truths »

frollogaston wrote: post #164
10000truths wrote: post #161

Earlier quoted context omitted.

Every RDBMS out there has an option to configure a DB-enforced transaction timeout. But that configuration is tied to a connection, not to a transaction. So using that configuration means giving up connection pooling. Which a lot of people don't want to do because it impacts latency and DB resource usage.

Every xact within a given connection will use the same connection-wide config, but the timeout is counting how long a single transaction takes, right? I don't see why you'd need to give up pooling for this unless you need different settings per xact.

Pooling means you're sending multiple queries (from different HTTP requests) over the same DB connection. A well-designed DB wire protocol will allow for pipelining those queries:

[send Query 1] -> [send Query 2] -> [send Query 3] -> [receive Result 1] -> [receive Result 2] -> [receive Result 3]

But in Postgres and MySQL, pipelined queries are not executed in parallel. They're just queued up for a single thread (per connection) to execute sequentially. Thus, if Query 1 is a transaction that takes too long, then it ends up blocking the execution of Query 2 and Query 3.

Re: The startup's Postgres survival guide

#168

Post by 9dev »

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.

It’s really not that hard, especially with an AI agent to help you. Running a Postgres server and a read replica in Hetzner with pgBackRest backing up to their S3 buckets and a cloud volume can be had for under 50€, has HA, PITR, 3-2-1 backups, and will carry you through your series A comfortably, with GDPR compliance built-in.

What does the equivalent RDS setup cost you?

Re: The startup's Postgres survival guide

#169

Post by jashmatthews »

outworlder wrote: post #154
Tostino wrote: post #98

Earlier quoted context omitted.

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

If it works for you it’s great but the RDS EBS limitations are real enough… AWS built Aurora and for RDS a new 3 node topology using local SSDs for writes and EBS only for the data directly.

Re: The startup's Postgres survival guide

#170

Post by gen220 »

If you have a horizontally scaled app (many 100s of API servers and async workers) you’re also probably going to need a connection pooling proxy like pgbouncer! with separate pools for separate connection configs (lower/higher timeouts, reader/writer). There’s a section on this that’s a bit of a stub right now, but IME tuning and configuring these connection poolers is pretty nontrivial and worth an expanded section!

I’ve seen this pointed out in other comments but I’d also strongly recommend expanding with a section on monitoring and alerting. One could write a blog post almost of this length just on monitoring :)

Post reply on HN