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 new pg instance for a while, but thinking about it for a new project).
So while I partly agree with you, a lot of companies don't really need HA, read replicas, or even PITR (though I would argue the last one is so trivial and cheap to enable that why not), but they click the expensive check box, and I would argue that companies who do need these features should consider hiring at least a couple of DBAs and get more flexibility instead of the current status quo of everyone being scared of the database and everyone just hoping cloud support will come to their rescue if ever needed
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.
My personal experience has been the opposite of yours: lots of data inconsistency issues, data loss only resolved by the database team spinning up backups, etc. And somehow, even after yet another incident, there’s never been appetite to properly fix things.
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.
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?
I'm defending your employer or their mindset, I'm just disagreeing with your claim that this follows from the parent's cost analysis claims. It _seems_ like your organization's problems are precisely because they _weren't_ doing the kind of cost analysis that the parent advocated. In other words, nothing in the parent's comment advocated for blindly following some Terraform guide. It feels unfair to the parent to suggest that their mindset caused your organization problems when it seems like your organization's problems were caused by _not having_ the parent's mindset.
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.
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!
Obviously past a certain point carting around full backups becomes time/dollar prohibitive, but this can take you very far.
Having done both, I'd recommend just starting with pgbackrest.
I just don't understand why people seem so drawn to the bad solution just because it ships with the database.
If you are on reasonable storage, I think it is arguable that the main reason you would have to recover is due to a software failure leading to corruption of the PostgreSQL backing state files. Otherwise, you would simply be restarting PostgreSQL on your durable and available storage. So, if the content has been corrupted by bugs, can you trust the recent WAL log in this scenario? Or can a plain dump be a more reliable "known-good" database recovery point?
And what is the business cost to rolling back to a less frequent dump such as nightly? Not every DB is some kind of multi-party OLTP ledger. Sometimes, a day of lost "new data" may be just a day of labor to repeat some data entry or other repeatable work...
A really robust contingency plan probably has both of these kinds of backup, and scenarios where one recovery versus the other is attempted. But if you are starting at a very small scale, hosted on some reasonable cloud storage like EBS, a cron-based dump that goes into a normal hierarchical file backup system may be totally sufficient for the extreme disaster scenario where you cannot simply restart your DB server on top of the surviving and available storage volume?
I'd much rather just do it right though.
Direct attached storage (or whatever storage is fastest / lowest latency for the environment you have available).
Setup pgbackreset or barman, use WAL archiving and setup block incremental backups with a new full backup every so often.
Now you have point in time recovery with very low RPO/RTO, and your database infrastructure can be treated a bit more like cattle instead of a pet, as you should be testing restores often, and this will become part of your yearly major postgres upgrade strategy.
You also get the ability to have a replica for almost free, as replicas can be created from the backup repo very cheaply, and get caught up to the primary without requiring the primary keep around the WAL for a long time. It just pulls it from the repo until caught up.
Any case where you think EBS was the right choice is better handled by having one (or more) replicas and a failover strategy.
Just do the right thing from the start and you save a lot of headaches. People have run production systems already, and have hit the pain points. Why keep hitting the same ones?
We used EBS snapshots on AWS for multi-TB MongoDB to get incremental backups that are fast to create and fast to restore (with some performance degradation after restore).
It doesn't support point-in-time recovery, but since it's fast you can create frequent snapshots (e.g. hourly). I'd consider adding this as a secondary backup strategy, even if you use a higher-level postgres-specific backup tool.
This guide is only satisfactory if the database is managed, otherwise there are a whole bunch of things going on.
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.
I'd rather spend the time building out my product than prematurely optimizing and overthinking my DB schemas at the earliest phases of a project.
And what is people's opinion of the reverse: source-of-truth in traditionally-modelled mutable relational tables, with a change log written out with triggers?
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 right now. The same with point no. 9
1. Most of these advices are unfortunately impractical and incomplete for startups. A good Data Model is highly dependent on understanding the business requirements, data flows. Means unless you are repeating yourself in the same domain its really hard to come up with a good schema in first iteration.
2. Startups are in the mode of discovering the schema for most part
3. Deleting columns is harder than adding additional columns, no one takes that risk so everyone ends up with schema bloat
4. Once you go a little bigger you will realize that the integer based UUID are not that great of a choice. Those are separate tables which maintain those index counters and not part of your DDLs. They have their own set of issues with data merging, backups and recovery
For the OP, I looked at the repo (https://github.com/hatchet-dev/hatchet/blob/main/sql/schema/...) ,
1. seems like the schema has database functions - Thats a potential scaling issue, plus you are asking vertical only scalable component to do something which could have taken care by horizontally scalable component
2. TEXT datatype for pretty much every attribute - this is a footgun, you cant use them for indexes properly, in the absence of length checks they can be abused from client side
The application->database layer is pretty impactful and it pays to pay attention to it, because poor access patterns will cause a lot of trouble in the future, and its made worse by not having a very accurate understanding of whats going on in that layer.
I think a lot of developers don't have a good sense on where their time sinks actually are. Boilerplate is not pleasant to write but also not the timeline-destroyer people tend to think it is. And its often not enough of a time-sink to warrant introducing "magic" that will have very large negative future impacts.
It does have its legitimate uses, but there are footguns that general SWEs not super familiar with DBs won't know about, like how it still doesn't prevent all types of race conditions unless you're in SERIALIZABLE mode.
Yes, in practice you may find that one or two of these don't apply to your own special start-up. But probably they all actually do.
There are disadvantages to this, but it's a safe default. The alternative is possibly losing important data, finding out later you want historical records of things that are stored in kludgy separate tables, getting into more advanced locking situations, and having more complex DB migrations. Which I've had to pull teams out of many times.
* read from the database
* make a request to an API (or really any kind of long running non-database thing)
* write to the database
You're going to end up with a transaction that is open way longer than it needs to be, particularly if you're upstream API is misbehaving, which will potentially end up causing a lot more grief.
If any of the data is PII and you will be subject to GDPR (you probably want to be at some point) then you will need a way to hard delete it.
A large portion of append-only datasets I’ve worked with have run into some edge case that required an update.
Also if you’re doing an append only log plus mutable view then please use triggers or materlialized views. I ran into a few cases where the approach was to just update both tables and that lead to divergences between the two.
While it's not my first choice, or habit, in many use cases, using an ORM is still OK looking back in the start especially when the schema is being fleshed out prior to understanding what needs optimizing. It's trivial to start optimizing a query after taking it away from ORM. The new angle I think is the ability for LLMs to use ORMs given the documentation, etc.
The only other thing I'd say is the benefits of running something that works with Postgres, such as Supabase, Hasura, etc. It really can be the best of multiple worlds, especially in the beginning in terms of having flexibility in one place.
* 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 with placeholders for parameters as-is, b) see how your query will actually be optimized when Postgres doesn’t have visibility into the specific parameter values
* use set seqscan = off when testing your query plans esp when tables are empty or nearly so so you can see if indexes will be used when seq scans become less cheap
* everyone defaults to btree indexes which are heavy and increase index bloat. Consider using a hash index instead if you just need to look up by column/id but not sort or get values greater/lesser than a param. You can’t create unique hash indexes but you can create exclude using hash constraints for the same effect (except no multicolumn unique index support)
* learn about GIN (and GIST) indexes. They can speed up common queries without needing new syntax, something people coming from MySQL might not expect to be possible; i.e. you can use them to speed up Plain Jane like ‘%foo%’ queries without switching to FTS.
> 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)
This is really good advice, I should put this somewhere in the guide. To add to this, not only can you deadlock by not having a consistent `ORDER BY` when you're locking sets of rows, but you should also be careful of locking rows on tables in different orders. For example, even if you lock each row in a table with an ORDER BY and FOR UPDATE, if one tx locks `table_a` and then `table_b`, and the other locks `table_b` and then `table_a`, you'll deadlock. This is obvious in theory but exponentially harder to debug in practice, because you need to be globally aware of every table that a write touches - something that's bitten us in particular with certain extensions.
> learn about GIN (and GIST) indexes
We're just testing GIN for fast key-value lookups for JSONB columns, and the performance improvements have been really massive. Interestingly there was a large performance skew between AND vs OR on these key-value queries.
More usefully, a uuidv7 can take the place of both the id and the created_at field (if precision is sufficient), and can (depending on your security comfort) also take the place of the uuidv4 public id field.
With uuidv7 the data is naturally sorted so you don’t run into the issues with btree worst case scenarios that you would with a uuidv4 id, and you can even take it a step further and use BRIN instead of btree indexes for a massive boost (also applicable to serial ids, though).
How well does this work for you? I thought if you have _any_ index, Postgres will use it if you disabled sequential scans. Diabling sequential scans won't tell if you if you have the right index
7/4 'converters' have been featured on HN a few times:
The infrastructure decisions in early-stage startups are brutal.
Most founders I know underestimate Postgres connection pooling
until it bites them at 10x scale. PgBouncer saved us twice.> 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.
For example, AWS will send you an email if you're approaching XID wraparound. In a startup that email is very likely to be missed, especially if it's sent on Boxing day. You want whatever AWS is watching to send you that email to be something connected to a pager.
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.
If you are doing some kind of full cross product where the join creates a much larger set of rows, it could optimize the DB load and network traffic to fetch the source sets and then generate the permuted set locally.
But, many inner join patterns are selective. They produce a much smaller output than the source records. The traffic to pull all the records and then intersect and filter locally is much worse than having the DB do it.
And that's before you even consider indexed joins, where the query plann is able to make good use of indexes to avoid doing brute-force table scans, sorting, and filtering.
Also, to re-emphasize: we do this rarely, but it's been helpful the times we've done it
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/Supabase, but last time I tried them they ended up becoming a tightly coupled annoying dependency after scale that defeated the cost savings as they grew in costs and we ended up migrating to Aurora / RDS lol
EDIT: I'm aware of the self-hosted path but I find configuring the above things faster/cheaper in terms of my admin hours than the self hosted postgres db. Maybe I just suck at being a DBA or need better education on it, that said, I have AI now so I should give it a chance again as it's been a minute since I created a fresh thing
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.
tx1: update a
tx2: update b
tx1: update b
tx2: update a
I, at least, don't know of a perfect fix here. Re: the original comment - Postgres will also error on deadlocks after it detects them without setting your isolation level to Serializable, but I agree with you that often retrying doesn't help, and could even cause cascading / snowballing failures if you have a backlog of retries piling up because of deadlocks.
I don't know if there's a good solution, really. We've fixed deadlocks incrementally over time as we've found them, which has worked pretty well, but of course that means also needing to deal with the "finding" part, which has generally come in the form of lots of `deadlock detected` log lines and errors (and retries accompanying those).
One thing that might be worth auditing is why there are two different bits of application code that are updating the same rows in two different tables in different orders. I know it's a contrived example, but it seems like it could be a code smell to me. Maybe this is the kind of thing that arises when two different subteams are working on the same database and are largely siloed.
Alexander will likely have more thoughts here as well, just my two cents!
As far as storing a datetime with an associated timezone, I agree that usually this can be problematic. However, for things like weekly repeats, you may want to store broken out components so it handles cleanly across time switch boundaries - e.g. when going in and out of DST. So you'd have `timezone`, `time` (no TZ, no date), repeat schedule (likely using interval, internally stored in months/days/microseconds), and use these to set up your next exact timestamptz value.
One bit not mentioned, and particularly useful in more modern RDBMS with JSON binary expressions in the database are to leverage JSON columns and avoid joins altogether for a lot of use cases. There are a lot of times where you have variance of sub-information, or other data where table normalization and joins work against you. Even with indexes, joins are costly, especially under load at scale with millions of simultaneous users. You can avoid a lot of this by simply having that sub-table information inside a JSON field with the row in question.
For example, logs and notes related to a specific field. Variable transaction data (paypal vs amazon vs google payments), where the logs/details from the API aren't something that really needs to be in a separate table but related to the transaction.
Another would be something like a classifieds site where many fields are repeated, but sub-fields can vary dramatically by the type of item or category.
Knowing how/when to leverage denormalization and JSON can be one of the most impactful things you can do in terms of performance in practice, short of falling back to a search database (Elastic, Quickwit, etc), which can also be practical depending on your needs, but adds complexity.
Similarly, knowing how your datagase uses certain types of data/serialization... for example UUIDv7 if you don't mind storing creation time (utc) of a record, or COMB if using say MS-SQL in particular... the serialization of said field in practice helps in terms of understanding how indexes update and impact performance.
I do wish the guide was expanded a bit with lots of specific examples and details... a lot of it is hand-wavy blurbs.
I appreciate the feedback; I'm usually someone who tends to go into way too much detail, so this was difficult to write - I tried to focus on the "mental model" of understanding Postgres rather than very nuanced specifics. I tried to link out to my favorite articles on a number of subjects, and the Postgres manual is quite good.
Some external links from the article:
- https://www.digitalocean.com/community/tutorials/database-no...
- https://www.cybertec-postgresql.com/en/benefits-of-a-descend...
- https://martinfowler.com/bliki/ParallelChange.html
- https://www.cybertec-postgresql.com/en/tuning-autovacuum-pos...
Some internal links on where I've gone into our own use-cases in more detail:
- https://hatchet.run/blog/multi-tenant-queues (PG-backed queues)
- https://hatchet.run/blog/postgres-partitioning (PG partitioning)
(edit: formatting)
I mean, sure start with a unified schema file until you have a production release... deploy, populate with placeholder data, etc... but once released, having a file for each set of changes isn't a bad thing.
Also, the management tools you can have single files for each view/sproc, etc... it's just schema migrations you need to take care of.
This only works if you don't care about being able to auto roll back DB changes without making a new commit, cause Postgres doesn't have a declarative DDL.
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.
In most (all?) cases the pooler manages one pool per database user, so even if there was something leaking, it would not be anything that the database user couldn't access anyway.
But if you are paranoid, you can configure the pooler to run "RESET ALL", "RESET ROLE", "RESET SESSION AUTHORIZATION" and "ROLLBACK" before handing out a connection.
But thanks for info about level of issue.
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.
Also zero mention of `text`, which is highly encouraged in Postgres instead of the silly old `varchar(255)`
Stored procedures are useful in cases such as annoying data type conversions (for example, before the newer ltree versions, its path couldn't accept hyphens and so if you were using UUIDs you needed a way to convert the UUID to a ltree compatible representation) or when you want to write a function that is used by a constraint, but it's not something I would generally reach for and certainly not for SQL injection reasons.
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's something that OP didn't discuss is shared databases vs services owning their own. When you are startup, it's really tempting to have services reach into database and skip API call.
We're heavy users of stored functions because we're (perhaps overly) reliant on Postgres triggers, which can improve performance by reducing network round-trips but are fairly risky because they're difficult to monitor and observe.
I've spent a lot of time writing my own random queries. I don't know that I've ever written a stored function.
And I've spent a lot of my working life cleaning up after people who write random queries who then start blaming the database for being "slow" and insisting they need some sort of over-engineered Redis caching layer or whatever.
100% of the time the database is perfectly fine, but the query is slop.
Not saying you are one of them, but you would very much be in the tiny minority if you are not. ;)
In most situations I'd try to avoid using stored procedures. Unless you're all in on them, the effect will be that it hides some logic from the developers since it is not in the main part of the codebase.
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 what the function does.
How many developers do you know who've gone back and read the source code of the function ? Assuming its open-source anyway and not a OS API.
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 documented well enough and has quirks that force you to understand in more detail how it works. This is not external library code, it's still part of your application.
Even worse !
Don't get me started on people who treat databases like a black-box dumping ground and insist they must have "portable schemas".
And if they "do have", they're not spending enough time with their service-market match
If they have time to write SQL queries, they have time to write stored functions.
Its really not that difficult and it certainly does not take a substantial amount of time.
They have the time to write SQL queries in their code
They don't have time to (or better, shouldn't) materialize them as a stored function in the DB
"Oh but your CI/CD should automatically..." Let me stop right there
The time they spend with this can be better used to ship and to improve their SW to customers, not with yak shaving
Which is why they end up spending time on mea-culpa "we take your data security seriously, but clearly not seriously enough" emails when they inevitably get pwned by a completely predictable and avoidable SQL injection attack.
The sort of startups you describe are jokes that barley take security seriously, let alone know what a pen-test or code audit is, let alone actually do them on a regular basis.
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 always people around in pretty dire need).
The one thing I would say is missing from this article is to not be afraid of using postgres for "stupid" things. Cache, queue's, and so on, especially on the road to launch.
One should also not be afraid of having more than one Postgres instance, especially if you're using it as a work queue.
Lastly there is a stupid amount of power in Postgres roles (its "user" system). The manual here is somewhat OK, but really undersells richness that it makes available to you.