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

181–190 of 255 posts

Re: The startup's Postgres survival guide

#181

Post by rawgabbit »

ucarion wrote: post #43

Do folks have any thoughts on ways of avoiding deadlocking access patterns? In a codebase where folks are sort of adding ad-hoc endpoints left and right, it's hard to avoid the case of two endpoints that more or less want to do: tx1: update a tx2: update b tx1: update b tx2: update a Is there a "discipline" or practice that works well? Like, can you realistically, in a real-world messy business codebase, impose an "o…

If you have different endpoints contending over the same rows, I would create “backend batching”. I would force endpoints to call a stored procedure. The stored procedure would append only to a “queue” table. Multiple workers would read from the queue and update the real tables in batches then update the queue with success and error codes. The batches are partitioned by the PK to minimize lock contention.

Re: The startup's Postgres survival guide

#182

Post by ozim »

frollogaston wrote: post #157
ozim wrote: post #155

Earlier quoted context omitted.

It is just „SQL people” crying out not knowing how to work with ORM. They always claim that devs who use ORM don’t know SQL. But I never seen or hired a dev that doesn’t know SQL and yet for each project we use ORM. I also never seen anyone claiming that you doesn’t have to know SQL and ORM is enough from the opposite side. If you really run into spot where your ORM breaks you can always drop to SQL. If you build pro…

The issue is if you use the ORM, you still need to understand the DB it's on top of. There isn't much point in using such a leaky abstraction when it's easy enough to just use SQL.

I just wrote it at the end of my previous comment.

You can use well known library that more people will comfortably using

or

given enough time you will build crooked, half baked ORM of your own.

Re: The startup's Postgres survival guide

#183

Post by venkat971 »

Interesting topic, We built and use DeepSQL (https://deepsql.ai/) at Stayflexi(YC) to address some of these issues. It's an DBA agent to prevent schema bloat, over indexing and does continuous monitoring of query workloads and proposes fixes.

Schema blot issues are real when you are vibe coding. Our engineers vibe coded and bloated our schema from 230 tables to 600+ tables. Many of them have repetitions of columns across the tables and often too much indexing. If your Postgres is on Aurora, the bloat easily multiplies your bills.

Re: The startup's Postgres survival guide

#184

Post by alfons_foobar »

0x696C6961 wrote: post #123
alfons_foobar wrote: post #119

Earlier quoted context omitted.

I might be outing myself as a noob here, but... what is the (better) alternative?

You inject the pool itself.

Sorry, I am being dense... how does that solve the problem?

I still have to get a connection from the pool, I just do it inside the function body now, right?

So this

    @app.get("/users")
    def get_users(conn = Depends[get_db_conn]):
        users = conn.execute("SELECT * FROM users")
        return users
would become that instead:

    @app.get("/users")
    def get_users(pool = Depends[get_db_pool]):
        with pool.get_conn() as conn:
            users = conn.execute("SELECT * FROM users")
        return users
But I still need enough connections in the pool to handle all concurrent requests, no?

Re: The startup's Postgres survival guide

#185

Post by bob1029 »

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…

The best way to cure a developer of their ORM dependency is to put them on a project with a complex OLAP / data warehouse component. OLTP workloads are reasonably well aligned with a lot of ORM patterns so it's more difficult to demonstrate the caveats here (but it's definitely still possible). The limitations of ORMs are much more apparent with OLAP workloads. ORMs simply cannot cope with provider specific requirements. All ORMs fall on their asses when it comes time to load any meaningful amount of data into the provider.

The biggest consequence of forcing the RDBMS through a lazily evaluated object graph is that we've become decoupled from the mechanical realities of what the database provider can do. I had a client quickly drop the "you must use EF" commandment for the project I was assisting on once they saw how many rows it was loading per unit time without EF. "I didn't know that was possible". Many such cases.

Re: The startup's Postgres survival guide

#187

Post by e12e »

poncho_romero wrote: post #148
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?

I think orms are typically fine. Something like rails can even play reasonably well with legacy databases (but you might need to create a view or two to rename columns names that collide with "magic column names").

There are reasonable arguments for leaving most to the db; effectively exposing some VIEWs and FUNCTIONs as the application interface for any and all bindings. Like legacy java and greenfield php for example.

I'd say it's different work rather than strictly more work.

Re: The startup's Postgres survival guide

#188

Post by 9dev »

OrangeDelonge wrote: post #180
9dev wrote: post #168

Earlier quoted context omitted.

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.

That was not quite the point I was making, which is that the equivalent RDS setup would come in at around ten times of that.

Re: The startup's Postgres survival guide

#189

Post by arkh »

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 reinvent a type system

I'd like to add: stay away from using arrays and user defined types. They feel like a good idea at first, but will become a problem when using fetched data in your code.

And another miss, if you're managing your servers: pgtune. The default postgres configuration is very conservative regarding resources so you can get good performance gains just by adjusting them to your server specifications.

Post reply on HN