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

191–200 of 255 posts

Re: The startup's Postgres survival guide

#191

Post by oleg2025 »

mrkaye97 wrote: post #97
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.

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

FastAPI pattern works very well with Pgbouncer, when it is in transaction pool mode.

Your Python application maintains a connection to Pgbouncer during the lifecycle of the request, but the physical Postgres connection is allocated only during the DB transaction. You will need open/close transactions in your code though.

Re: The startup's Postgres survival guide

#192

Post by oleg2025 »

In my experience, good monitoring is a must have from the very beginning. Eyball dashboard from time to time to spot issues, and use during incidents.

Things like connections stats, deadlock monitoring, slow queries. All come standard in AWS/GCP.

Re: The startup's Postgres survival guide

#193

Post by lbriner »

This guide is nicely formatted and very helpful but I think the comments prove that all it has done is taken a subset of the information from the manual and decided that "these parts are important" whereas the comments then unhelpfully point out, "yes but also this", "and this".

There is no line that says X is important for a startup and Y isn't, it is mostly a matter of degree.

Instead, what the guide has done although could be a little clearer is e.g. explain that there are types of indexes that are a better trade-off of space and performance for particular scenarios, here is one example and click here to learn about the other indexes. That is enough for a startup to understand.

A second example might be, "Optimizing your postgres resource limits is important for x, y, and z reasons. You should try and balance giving your server as much RAM as it can but without using up RAM that is needed by other things. A starting point might be X percent of total RAM for shared_buffers but for more details see the main docs here".

I love postgres but it is not a toy and it takes investment of time. I think what most people want is a map with a few examples so they can pick out what concerns them the most.

Re: The startup's Postgres survival guide

#194

Post by inigyou »

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.

This only works if you're already on the cloud, otherwise you're paying through the nose for bandwidth.

Re: The startup's Postgres survival guide

#195

Post by masklinn »

frollogaston wrote: post #86
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?

> Why uuid7, is the btree performance better with it than with uuid4?

Yes, being ordered uuid7 leads to less splitting and index bloat than the fully random uuid4. So while the lookup is pretty similar the index is in much better shape.

And if lookup is correlated with recency (which is probably the case for most record types) recent entries will be grouped together on the same pages in the uuid7 index leading to better cache presence, whereas in a uuid4 index they’ll be randomly spread over the entire index set.

Re: The startup's Postgres survival guide

#197

Post by ktaraszk »

dwedge wrote: post #90
throwaway894345 wrote: post #78

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

They won't let you. It's part of their business to keep you locked in.

Re: The startup's Postgres survival guide

#198

Post by KronisLV »

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…

> Highly debatable. When your highest cost is developers salaries.

I think everyone always is needlessly partisan when ORMs get mentioned.

Personally, I view it like so:

  * using something separate from your ORM for migrations *can* be good (e.g. you don't couple your DB to your app's ORM), like dbmate; needs to be said
  * making plenty of views for MOST of your complex queries is a good idea, if approaches like BFF are popular for APIs, then why not the same in regards to interacting with your DB? also makes testing a breeze, instead of having to pull some unreadable generated SQL bullshit from trace logs
  * you can make read only entity mappings against those views and keep the querying complexity in the DB but let the ORM handle the app side stuff
  * with that in mind, using the ORM for most of the simple queries and regular CRUD stuff is also a breeze
  * sprinkle in some DB procedures/functions if needed, but also don't go all gung-ho in storing 90% of your business logic in the DB and using the app as a glorified view/controller, while that might appeal to a subset of people, in the *present* time that inevitably sucks
  * similarly to CTE's, your views can reference other views, as building blocks; also sometimes having two similar views that just compose others is nicer than dynamically generated SQL (since once you start trying to generate it dynamically, people get too eager about it and before you know it you can't figure out WTF is going on)
Like I don't passionately hate something like jOOQ or myBatis on Java side as well, it's just that often they mean that you can't quite surmise what the query will be, compared to just being able to look at a DB view and incorporate it into your SQL query tool of choice easily. Same goes for Hibernate, if you have to write complex HQL queries, then maybe consider whether you can do things more simply. There are also projects out there for which JDBI3 and the likes of which are perfectly okay, too! Note that I only mention Java cause it has a lot of ORMs and options, with various approaches to solving the same issue.

Reading some other comments, I'd also add: know SQL, use SQL for the problem it's good at, same for ORMs; don't let either overstep the other. In my opinion a really good litmus test for this is whether you are capable of generating all of your ORM mappings when you point a tool at your schema and its tables/views. If not, and you need complex workarounds, something is wrong.

Re: The startup's Postgres survival guide

#199

Post by khurs »

throw0101d wrote: post #51
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…

> Use uuidv7 not uuid in general (typically v4) 7/4 'converters' have been featured on HN a few times: * https://github.com/ali-master/uuidv47 * https://github.com/stateless-me/uuidv47

I don't do this.

I find that most ID's in majority of tables are internal and only used for internal joins, so I use v7

The tables where the uuid will be sent to the client e.g. UserId etc, I also store an external_id of v4.

Much rather have a static column than continually convert.

Re: The startup's Postgres survival guide

#200

Post by traceroute66 »

fabian2k wrote: post #40
traceroute66 wrote: post #39

Earlier quoted context omitted.

> it hides some logic from the developers I do not buy this argument. Its called a documented function. The developers know the function's inputs and outputs and what it does. That's all they should need to know. Its no different to functions in the libraries of whatever programming language you are using. Devs just do their coding based off the function signature and docs. They know what goes in, what comes out and…

It's more of a problem with triggers. But in the end you're switching languages at that point, and devs that have no problem reading your backend language will not necessarily be good at reading stored procedures. Of course depends on how complex you make them. And of course devs read the content of functions they call. Unless it's a well written library used by many different people, odds are the function isn't docu…

> devs that have no problem reading your backend language will not necessarily be good at reading stored procedures

But again ... why do they need to ?

If you are a developer and you want to convert a string to upper case, you just use `strings.ToUpper($foo)` or whatever based on the signature.

Again, its no different with stored procedures.

Dev wants to add a new user ? They look through the stored procedure headers and see a `add_user($foo,$bar)` signature and code a call against that.

Do they need to know that in the background `add_user($foo,$bar)` inserts into table `users`, `groups` etc. ? No.

Infact it makes the dev's life simpler because instead of sending multiple calls themselves (or perhaps forgetting one or two), they just called the stored procedure.

That is my problems with devs who think they are some sort of geniuses that need absolute access to the database because they think nobody can write SQL queries as well as they can ... too many times they come running to me complaining the database is "slow" when in fact it is their sloppy SQL queries that are slow.

Post reply on HN