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

201–210 of 255 posts

Re: The startup's Postgres survival guide

#201

Post by Tostino »

chasd00 wrote: post #47
traceroute66 wrote: post #21

I did a search in that post for "function", zero results. Unimpressive. Not even the most cursory of discussion of stored functions ? Given that many startup's Postgres instances will no doubt be backing some web-ui or app that takes untrusted input, surely they could have at least had a brief discussion about how stored functions can help against SQL injection attacks ? Not only that but it means you have to think,…

> it prevents devs just writing their own random queries. which in turn makes every single change in schema or logic dependent on a DBA making the change in Postgres balanced against their lunch schedule. Good for DBA job security but terrible for productivity and sanity.

That is entirely a design choice if you make your DBA responsible for that type of thing.

I have all my database functions version controlled and deployed by liquibase on every release (along with any other migrations that need to go out).

They are treated like code like any other piece of code in my codebase, get changed along with the rest of the application as necessary, and are deployed automatically with the rest of my application.

DB functions / stored procedures are the right tool for certain jobs. When they are the right fit, they can save your ass performance wise.

Re: The startup's Postgres survival guide

#202

Post by nickjj »

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 go with a simple pg_dumpall approach running on a cron job. It has worked well for over a decade on all sorts of different systems.

Here's a complete walkthrough on how I backup and restore: https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-...

It covers using Plakar too (optionally) if you want deduplication and encryption.

Re: The startup's Postgres survival guide

#203

Post by frollogaston »

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

Earlier quoted context omitted.

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

The idea is you only take a connection from the pool when you need to touch the DB, then you give it back immediately. It's very possible that's only a small fraction of the time spent in some handlers. If you inject the connection, you always hold it through the entire request.

Re: The startup's Postgres survival guide

#204

Post by [deleted] »

oleg2025 wrote: post #191
mrkaye97 wrote: post #97

Earlier quoted context omitted.

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

[deleted]

Re: The startup's Postgres survival guide

#205

Post by frollogaston »

oleg2025 wrote: post #191
mrkaye97 wrote: post #97

Earlier quoted context omitted.

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

This is why I said PgBouncer is a sign of something being wrong. Devs aren't managing connections right, they try to paper over it with PgBouncer, it's not really easier cause they now need to be conscious of xacts instead, and now there's an extra moving part in the DB that most of the team doesn't really understand. PgBouncer has its other uses, but I really don't like this one.

I also get it, xact should be 1:1 with connection in a lot of these backend applications. Sometimes I have a few little helpers for that, like pool.sql() will take conn, open xact, execute, close xact, return conn. If the DB driver doesn't already have that.

Re: The startup's Postgres survival guide

#206

Post by ozim »

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

In my experience, ORMs save a little bit of writing SQL and then cost an unbounded quantity of time in debugging mysterious problems because knowing why a query is slow now requires understanding the DB, your own code, and also the ORM.

In my experience ORM doesn’t save anything on writing queries. You still have to write somewhat same code that looks like sql query. You still need to know joins, you still need to know DB structure.

Getting rid of SQL queries is not the job of ORM.

ORM saves you time on object mapping boilerplate THAT IS THE JOB of an ORM it is right there in the name „object relational mapper”.

Re: The startup's Postgres survival guide

#207

Post by superze »

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…

How can this comment be true. 4 Ms for analytical queries. Does your db consist of just one int column that tracks ur daily coffee consumption or what? I am calling bs

Re: The startup's Postgres survival guide

#209

Post by Ankiit111 »

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…

[flagged]

Re: The startup's Postgres survival guide

#210

Post by williamdclt »

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…

good advice!

> learn about GIN (and GIST) indexes

yes, but also learn about the trade-offs and in particular the "pending list". The flush of the pending list can be slow, causing timeout, causing the list to not be flushed, causing the next write to trigger flush again and failing in the same way, which means you're having downtime. The default pending list size is weirdly high IMO

Post reply on HN