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

171–180 of 255 posts

Re: The startup's Postgres survival guide

#171

Post by gen220 »

gen220 wrote: post #170

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

And another nontrivial one, in compound indices it’s really important that you order your columns in descending selectivity order. I.e. the column with the most unique values should go first.

In degenerate table/index situations, this could lead to index scans that are as slow as table scans, or not using an index at all! Especially common in SaaS schemas where you’re dealing with a tenancy key in many of the indicies: that tenancy key should almost always suffix the composite key not prefix it!!

Re: The startup's Postgres survival guide

#172

Post by Stitch4223 »

xmprt wrote: post #122
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…

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

For active record patterns I disagree: it’s nice to work with data in a way that feels natural. Chugging simple stuff around in a recognizable way is what you’d end up writing anyway in many applications. Writing your ORM-lite is waste.

When it comes to advanced queries learning the ORM equivalent to the SQL it should write… ORM’s can be outright terrible, and I completely agree with you.

Every ORM has their own names and way of doing things, so this knowledge is hard to port to other ORMs. It’s requires you knowing the right SQL first, then knowing how to write that in the ORM dialect.

Reaching for the more advanced ORM trickery means grasping hard to grasp subjects twice with the risk of misunderstanding twice, and as a bonus worse ORM documentation than plain active record features. Oh, and others need to understand what you’ve written as well.

Re: The startup's Postgres survival guide

#173

Post by christophilus »

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…

Can’t do RLS without a transaction, though, right?

Re: The startup's Postgres survival guide

#174

Post by manphone »

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.

There’s a bunch of features in compatibility, performance, and cost reasons not to choose these cloud hosted managed databases.

Re: The startup's Postgres survival guide

#175

Post by hmokiguess »

faangguyindia wrote: post #18
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…

I run pgautofailover with 2 replicas and 1 monitor, you can run 2 replicas on equal configuration, though i size primary bigger and monitor node is tiny. You can run this on $10x2 = $20 per month setup for 2 replicas and 1 monitor node for maybe $2-3. For most other projects i just use sqlite, backup periodically to s3. some report (coincidentally i was checking health of my small cluster for an app) Common applicati…

man this makes me feel I'm doing everything wrong, I really should go learn some proper db hardening

Re: The startup's Postgres survival guide

#177

Post by christophilus »

busymom0 wrote: post #11
ComputerGuru wrote: post #10

Earlier quoted context omitted.

It runs easily on a vps at your scale, even the same vps serving your app. That used to mean having a modicum of sysadmin knowhow but it’s straightforward these days, especially if you just use a premade docker file.

I went with the self host route by putting it on a few years old computer with much better specs than cheap vps. Cloudflare tunnels to make the web server accessible on the internet.

Nice. How fast is your home internet connection? I’ve thought about putting an old laptop to this use. But I don’t want my day to day internet to suffer if my site gets traffic spikes. And also, I’m nervous about non-ecc ram.

Re: The startup's Postgres survival guide

#178

Post by itsthecourier »

* BRIN indexes are great for append only with an incremental value (a 50MB instead of 100GB index in timeseries data in my case)

* If your disks are ssd and scsi in different volumes, adapt the random cost of io in the config to let the planner now

* if you have RAID controller and a Battery-Backed Write Cache (BBWC), you can disable Linux filesystem write barriers. removing excessive fsync from the mouth of the psql demigod in SoCaL ~linux 2015, Bruce Momjian IIRC

* monitor disk usage, in backups pipe to gz, never to disk

* counter-intuitevely modern hardware may have an io bottleneck and plenty of cpu, so try Filesystems like ZFS using Zstandard (zstd), for boost in your Transactions per second

* if possible, schema multitenancy instead of database multitenancy, instagram talked about this decades ago

* indexes index functions results too, precompute those fields and partial indexes help a lot

* BM25 and FTS in pg are so good you probably don't need Elasticsearch and you will save a lot in de-sync between both of them

* you may be hacked in this brave new world post Mythos, thus, learn PITR to an external only write, no override medium like S3

Re: The startup's Postgres survival guide

#179

Post by busymom0 »

christophilus wrote: post #177
busymom0 wrote: post #11

Earlier quoted context omitted.

I went with the self host route by putting it on a few years old computer with much better specs than cheap vps. Cloudflare tunnels to make the web server accessible on the internet.

Nice. How fast is your home internet connection? I’ve thought about putting an old laptop to this use. But I don’t want my day to day internet to suffer if my site gets traffic spikes. And also, I’m nervous about non-ecc ram.

My internet is very fast for sure (can give more concrete numbers when I am home later) but I don't think you need to worry about it unless you are operating some massive website with huge traffic concurrently.

Re: The startup's Postgres survival guide

#180

Post by OrangeDelonge »

9dev wrote: post #168
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.

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?

RDS gets you easy integration with the rest of the AWS services, which as a startup, you’re probably using quite a few of.

Also, if you are nickel and diming over expenses in the 50€ range, you’re probably at the wrong startup.

Post reply on HN