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

11–20 of 255 posts

Re: The startup's Postgres survival guide

#11

Post by busymom0 »

ComputerGuru wrote: post #10
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…

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.

Re: The startup's Postgres survival guide

#12

Post by allthetime »

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…

The $10 VPS that serves your web app can run Postgres just fine. If it can’t? Fire up another $10 VPS. Learn how to tune your configs and network settings and query/cache efficiently.

Re: The startup's Postgres survival guide

#13

Post by mrkaye97 »

(Matt from Hatchet)

One small addendum here is we've had a lot of success performing joins in memory in a few very specific situations where the alternative is a single, often overcomplicated query. I've heard / seen advice many times in the past about performing fewer round trips to the database being something to optimize for (often good advice!). Sometimes this is taken too far, resulting in overly-complex queries requiring complicated JOIN or UNION logic, CASE logic, and so on.

We have a couple of places in our codebase where we perform two or more simpler queries independently instead, and then loop through their results and use maps to match the relevant rows. Conventional wisdom often suggests this path will hurt performance because of the extra database round trip in addition to the loops needed to perform the join, but it is actually beneficial in these cases because of more predictable query planning behavior. We use this trick sparingly, but it can be helpful in a pinch.

Note that some ORMs will also do this for you in the background, which we don't necessarily endorse, and we try to use this sparingly when writing a single query on its own is not realistic.

Re: The startup's Postgres survival guide

#14

Post by ComputerGuru »

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…

There’s no need to get all complicated and fancy or introduce more dependencies. For most people, a cron job calling pg_dump_all piped to zstd and copying the output to s3/ftp/whatever is plenty good enough.

Obviously past a certain point carting around full backups becomes time/dollar prohibitive, but this can take you very far.

Re: The startup's Postgres survival guide

#15

Post by edoceo »

allthetime wrote: post #12
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…

The $10 VPS that serves your web app can run Postgres just fine. If it can’t? Fire up another $10 VPS. Learn how to tune your configs and network settings and query/cache efficiently.

Any pointers to network configuration to tune? Some TCP stuff? How much does it matter on that VPS network?

Re: The startup's Postgres survival guide

#17

Post by rsyring »

ComputerGuru wrote: post #14
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…

There’s no need to get all complicated and fancy or introduce more dependencies. For most people, a cron job calling pg_dump_all piped to zstd and copying the output to s3/ftp/whatever is plenty good enough. Obviously past a certain point carting around full backups becomes time/dollar prohibitive, but this can take you very far.

FWIW, we started with a system that was essentially this. We eventually moved to pgbackrest and it wasn't any harder to setup. But the ROI on that investment is a lot higher because pgbackrest does a lot more for us than the home rolled solution.

Having done both, I'd recommend just starting with pgbackrest.

Re: The startup's Postgres survival guide

#18

Post by faangguyindia »

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 application queries average under 4 ms:frequent analytics queries: ~0.9–1.4 ms average common inserts: ~0.4–3.4 ms average the slower recurring reporting query: 62 ms average across 53 calls, 308 ms worst case

Query volume is approximately 2.30 million SQL statements/day (~26.6 statements/sec), based on pg_stat_statements over the last 97.3 days. That includes every SQL statement, not just user-facing requests: BEGIN/COMMIT alone account for ~1.05M/day, analytics inserts for ~522K/day, and HA/monitoring checks for ~118K/day.

Re: The startup's Postgres survival guide

#19

Post by pphysch »

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…

pgdump / pgrestore, using native binary format

Re: The startup's Postgres survival guide

#20

Post by mjr00 »

Good article overall, some comments:

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

This might be just me, but I hate cascades, for a very simple reason: at most places, the majority of developers "live" in the Python/Node/Go/whatever application that talks to the database, not the database itself. Cascading deletes (or updates) is basically magic and it can be very hard to understand "why did deleting a row from table A delete something from table B automatically". Especially if someone sets up the cascading wrong! IMO it's better for long-term maintainability to emit explicit delete clauses. Correct use of foreign keys will prevent any issues with database consistency.

> Tricks for large table migrations

The pitfalls and workarounds are all correct, but worth pointing out tooling already exists[0] for managing this for you. Making changes to large tables should be as simple as running a command (and then nervously monitoring for the next 24 hours as the data copies).

Other things to consider,

1. Get used to separating application and database deployments early. It is impossible to transactionally deploy both a schema change and an application change simultaneously, there will always be some delay where the versions of database and application are out of sync, and you will eventually run into a situation where the database change deploys fine but your application change does not. Once your app is in production, get in the habit of only doing backwards compatible schema changes: all new columns are nullable or have a default, no renaming of tables/columns, etc.

2. In the same vein, figure out a schema management strategy early. You really don't want your database deployment process to be "senior dev runs some DDL manually on production from his machine". I'm still partial to liquibase because it's the devil I know, but there's other tooling like Flyway which exists.

[0] https://github.com/shayonj/pg-osc

Post reply on HN