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

81–90 of 255 posts

Re: The startup's Postgres survival guide

#81

Post by drdexebtjl »

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…

> hiring at least a couple of DBAs and get more flexibility

Every place I’ve ever worked at that had DBAs had the complete opposite of more flexibility. You have to do things the DBA’s way, and if their way doesn’t work for your service, you need to fight for their time and priority.

Meanwhile every place I worked at where every team completely owned their databases + did periodic data recovery drills had much more flexibility and no data loss.

Re: The startup's Postgres survival guide

#82

Post by groundzeros2015 »

nomel wrote: post #80
groundzeros2015 wrote: post #56

Earlier quoted context omitted.

Shared memory is shared memory. Are the pages zeroed out?

This worry relies on a zero day bug/memory exploit in one of the most widely used access methods for Postgres. This worry can be applied to every component of the software stack, including the OS.

Hmm, not really. Whether the kernel is managing memory for processes properly is different than asking whether a reused Postgres connection clears all relevant memory.

But thanks for info about level of issue.

Re: The startup's Postgres survival guide

#83

Post by sgarland »

> I’ve found normal forms to sometimes be at odds with query efficiency and ease of use, which is critical when you’re moving fast—sometimes it’s just easier to dump data into a jsonb column.

If you’re a startup, the performance cost of storing everything in JSONB is going to outstrip any gains you might get from denormalization. JOINs are simply not that hard if you design your schema intelligently. Additionally, allowing freeform text columns for things like statuses will eventually bite you with fun problems like `closed != CLOSED != Closed`.

> Use foreign keys with cascading deletes for low-volume tables, particularly where database consistency and correctness are important. Careful at higher volume.

Absolutely. Just be careful with 1:M, or M:N, for large values of M and N. You don’t want to trigger a surprise deletion of hundreds of thousands of rows.

> Indexes by default use a btree implementation. It’s most helpful to think of indexes as just another table in Postgres, with data stored in a specific format which is optimized for lookups (more on this later).

For a single row lookup (which is what this section was referring to), yes. For range scans, if the indexed column isn’t k-sortable, a sequential scan can start beating the performance of the multiple lookups pretty quickly.

> There are cases where you think an index should be used, but the query planner is still seq scanning anyway, despite table statistics being up to date and the index being valid.

This is usually caused by one of two things: forgetting that indices are (generally) B+trees and having data laid out in a manner that is inefficient for the query, or having data that isn’t uniformly distributed - for example, for some / many companies, the geographical distribution of users is going to be heavily clustered around more populous cities. Histograms are one way to deal with this.

Another topic not discussed in TFA is other index types - BRIN in particular can be incredibly performant while adding almost zero overhead, if the shape of your data makes sense for them (time-series is the obvious one, but anything with useful clustering should be considered).

All in all, this is one of the better tl;dr articles on Postgres I’ve read. Well done, Hatchet.

Re: The startup's Postgres survival guide

#84

Post by geoka9 »

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…

If you already run k8s, why not just use cnpg?

https://github.com/cloudnative-pg/cloudnative-pg

Re: The startup's Postgres survival guide

#85

Post by k_bx »

Tostino wrote: post #7
rsyring wrote: post #5

Earlier quoted context omitted.

FWIW, we use: https://pgbackrest.org/ Offers point-in-time recovery which is an improvement over a custom solution we used to have which gave us nightly backups. We have it backing up to Backblaze B2 (S3 like). Was relatively easy to setup and no problems really.

Can't recommend pgbackrest enough. It's fantastic software, and I love the work they put into doing inter-file deltas for backups (so if 8kb of a 1gb file changes, you only backup the difference). It saved my last company a ton of money on storage while keeping good RTO/RPO.

It's great, but it's not incremental like git, e.g. you do need to make a full backup periodically, unfortunately.

I didn't understand that, and after 5 months of usage caught my backblaze to be using 40TB, and nightly restores taking forever for other reasons. So: not ideal, and be careful to check!

Re: The startup's Postgres survival guide

#86

Post by frollogaston »

ComputerGuru wrote: post #23

Some comments and corrections: * Use uuidv7 not uuid in general (typically v4) * in addition to minimizing locked records, make sure your locks are ordered deterministically across all queries (eg by id asc, always) or you’ll deadlock (but postgres has a really good deadlock detector so you’ll more likely just error out if you’re lucky) * always use explain (generic_plan) to be able to a) copy-and-paste your queries…

Any kind of uuid PK is quite expensive and usually not worth, because you're so frequently joining on PKs. A safe default is to use serial PKs, then have a secondary-indexed uuid4 if you wish to publicly-expose anything. Why uuid7, is the btree performance better with it than with uuid4?

Re: The startup's Postgres survival guide

#87

Post by CodesInChaos »

zer00eyz wrote: post #4

To this articles credit, it does start out with normalization and design! There needs to be more emphasis how important this is! I cant tell you how often I see it done "badly" (we let our ORM build the db for us). The best text I have ever found on this is "Database Design for Mere Mortals", over the years I have bought more that a few copies and I always end up giving them away to those in need (and there are alway…

I'm a big fan of persisting almost everything in the primary database. With one exception: I'd immediately use object storage (S3) for files which are large in number or size. Files which are few and small (e.g. templates) are fine in the db.

Re: The startup's Postgres survival guide

#88

Post by frollogaston »

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 secondary denormalized tables that are mutated, but that's only for performance/convenience and shouldn't be your sot.

5. Use connection pools, but be mindful of how many connections you're using. You probably don't need PgBouncer unless you've messed something up.

6. In code, avoid explicit transactions unless there's a clear reason you need them. Usually only need those for denormalized parts. Just take a conn from the pool, do something, commit, return conn to pool. If you're going to keep an xact open, never do long-running stuff in the middle like RPCs. Too often I see people leave xacts open without much thought. Edit: Also don't use SERIALIZABLE xacts almost ever.

7. Something is probably wrong if you're using explicit locking like SELECT FOR UPDATE.

8. 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. Seems oddly specific, but for some reason someone always tries this.

9. Related to above, don't reinvent a graph DB, typically with "node"/"edge" tables that FK into themselves or in a cycle. 99% of the time what you're trying to do is easily solvable with regular normalized tables.

Re: The startup's Postgres survival guide

#89

Post by edoceo »

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…

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?

Re: The startup's Postgres survival guide

#90

Post by dwedge »

throwaway894345 wrote: post #78
dwedge wrote: post #62

Earlier quoted context omitted.

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…

> 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. Obviously "let RDS manage your database" doesn't require egregious read replicas. The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.

> Obviously "let RDS manage your database" doesn't require egregious read replicas

Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it.

> The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.

Assuming you're talking about letting RDS manage anything, then sure - apart from it being more likely to slip through the net if nobody has to configure them. Database is just an expensive cost nobody necessarily drills into.

However if you mean the decision to let $cloud manage the replicas (and keep the primary managed), that totally depends on the cloud and the options. For example have you ever tried having a primary in GCP Cloud SQL but the replica not in cloud SQL?

Post reply on HN