* 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.
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?
In practice you’ll never notice the difference between joining on a bigint vs uuid for most OLTP purposes and you’ll be able to generate the uuids in your backend instead of in the db which a) can be very helpful if you’re pre-generating linked records and inserting them pipelined rather than sequentially, saving a lot of round trip traffic, b) reduces concurrency bottlenecks on the db server.
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).
> 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.
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
> 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
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
The seqscan option being a binary on/off is a bit of a misnomer; what it actually does is set the cost associated with a seqscan to be astronomically high so that other options will be preferred over it. An index on age when you're looking up by name won't be used whether or not seqscan is enabled.
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 new pg instance for a while, but thinking about it for a new project).
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.
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. Plus managed database restricts what you can do with the database - sometimes in really annoying ways.
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
> hiring at least a couple of DBAs and get more flexibility
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.
That’s usually because they’ve seen a lot of failure modes over the years, and what seems fine to you can have surprising outcomes later.
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.
You can use RDS without HA. Many people probably should do this, but no one wants to tell their boss they want to disable RDS HA and wear 1h of downtime a year so they can cut 50% of their spend.
> no one wants to tell their boss they want to disable RDS HA and wear 1h of downtime a year so they can cut 50% of their spend.
If you're a SaaS, 1h of downtime costs a lot of money. In reputation, in lost business, in support time, in on-call response, in making a public postmortem and sharing it to customers and dealing with responses... For saving a few thousand bucks, it's often indeed a very bad deal.
Most of those are already priced in (support costs, time dealing with a postmortem), and an hour of downtime a year isn't going to meaningfully harm the reputation of most companies, I'd wager a lot of clients wouldn't even notice.
Of course this isn't true for some companies and the cost of downtime far exceeds the cost of HA. Most companies think they are that company, most aren't. And even if they are, "AWS had an outage" does a lot of lifting.
Every time I get a new client they need constant point in time backups, failover, HA everything. Usually when the costs are explained they change their mind and an hour old snapshot restore is actually fine
> 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.
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?
> 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.
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.
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.
If expending 6k per year on your database alone sounds reasonable to you, we have very different perspectives on a good use of investor funds.
We’re not talking about maintaining your own k8s cluster here mind you, but a basic Postgres setup. If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data.
> We’re not talking about maintaining your own k8s cluster here mind you, but a basic Postgres setup. If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data.
Intelligence is being able to set up your own postgres database with backups, PITR, retention policies, HA/replicas, and everything else needed to prevent catastrophic data loss. Wisdom is realizing that's a solved problem completely unrelated to your business, so you don't have to.
Well yeah, you can apply that to most problems you encounter as a company - there are service providers for pretty much anything you can imagine. That doesn't mean it's smart to spend a huge chunk of your revenue on OpEx, however.
Sure, and you need to do the build vs buy math, but why not take the "If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data." logic further and say
1. Don't use a third-party auth provider, if you can't build out an OAuth provider in an afternoon you shouldn't be entrusted with login credentials
2. Don't use Github, if you can't build out an internal Gitlab instance in an afternoon you shouldn't be entrusted with securing source code
3. Don't use Github Actions or CircleCI, if you can't self-host a Jenkins server in an afternoon you shouldn't be entrusted with secure deployments
4. Don't use a lawyer or outsourced HR, if you can't draft legally binding employment agreement in an afternoon you shouldn't be entrusted with hiring people
There was some recent uncertainty about pgBackRest getting discontinued due to lack of funding. But the maintainer secured funding, and pgBackRest development will continue.
Can't recommend pgbackrest enough. It's fantastic software, and I love the work they put into doing inter-file deltas for backups (so if 8kb of a 1gb file changes, you only backup the difference). It saved my last company a ton of money on storage while keeping good RTO/RPO.
It's great, but it's not incremental like git, e.g. you do need to make a full backup periodically, unfortunately.
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!
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.
But the other option is just doing it right from the start and using a tool like pgbackrest. It's no harder to setup, and it puts you into best practices by default rather than having to work at it later.
I just don't understand why people seem so drawn to the bad solution just because it ships with the database.
I think you need to analyze the disaster scenarios and recovery costs you are trying to balance before you can really evaluate these different options.
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?
Using something like EBS for your postgres data directory is, IMO, a bad idea. If you've already made that mistake, yeah I suppose using PG dump isn't that bad.
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?
If you are on AWS, the direct attached storage option you have is ephemeral volumes. That's bad. If the hypervisor fails you lose data. Which is fine, you have replicas. If there's an event that takes out multiple machines at once, even for a moment, you are hosed(this can be AWS issues, or could be as simple as automation misbehaving and shutting machines down).
I'm all for treating DB as cattle, but your cattle needs to be able to survive long enough.
EBS is expensive but it works for PG. AWS uses EBS for RDS databases. Calling that a 'mistake' is a tall order.
If it works for you it’s great but the RDS EBS limitations are real enough… AWS built Aurora and for RDS a new 3 node topology using local SSDs for writes and EBS only for the data directly.
An atomic volume snapshot should work for any database that's durable on power failure. Ideally preceded by a checkpoint, to minimize recovery time. Atomicity of the snapshot mechanism is essential to prevent data corruption using this approach.
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 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 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.
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
I would highly recommend using ORM's, with the caveat to know exactly when not to use them. Startups do not fall in that bucket.
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
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
I've never actually known business requirements ahead of time, when I worked for a startup and for a large company. Stuff happens. You build your schema iteratively and yes, accept some temporary bloat when cols get added thoughtlessly.
The UUID backup/recovery thing isn't an issue if you're doing append-only. Joins on UUIDs are far slower, enough that even at small scale it can cause issues when you have many joins. Anyway I won't argue too hard against UUIDs cause they work too, just anything is better than using meaningful fields as the PKs.
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”.
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.
It was kinda debatable until people started Claude coding everything. Even before, I would've said every SWE should just know SQL, it's not much buy-in to understand the foundation of like your entire backend. Also I'm not a DBA if that's what you meant.
Do you have any meaningful experience as a software developer?
That’s a silly take. I somehow learned over the long run: SQL different flavors, couple of ORMs, multiple programming languages and multiple frameworks. Not counting different troubleshooting different operating systems and different applications I had to work with.
Yep, there have been extended periods of time where my entire job title might as well have been "ORM remover" because they backed themselves into a corner
For active record patterns I disagree: it’s nice to work with data in a way that feels natural. Chugging simple stuff around in a recognizable way is what you’d end up writing anyway in many applications. Writing your ORM-lite is waste.
When it comes to advanced queries learning the ORM equivalent to the SQL it should write… ORM’s can be outright terrible, and I completely agree with you.
Every ORM has their own names and way of doing things, so this knowledge is hard to port to other ORMs. It’s requires you knowing the right SQL first, then knowing how to write that in the ORM dialect.
Reaching for the more advanced ORM trickery means grasping hard to grasp subjects twice with the risk of misunderstanding twice, and as a bonus worse ORM documentation than plain active record features. Oh, and others need to understand what you’ve written as well.
I'd also argue whether ORMs actually save that much time in practice. In Java, for example, the main time sink is the JDBC plumbing and its easy to use something like JDBI that handles that plumbing without abstracting away the underlying SQL.
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.
Yeah, they'll often conflate the ORM with the nice stuff you want like connection pools. And the thing about time estimates is real. Another thing is they'll optimize too hard for having fewer tables.
> 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.
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.
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.
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 project „SQL first” you robbed yourself from ORM upsides and you are bound to badly implement your own one in the long run.
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.
There's nothing wrong with an ORM if you want to move quickly and get something up-and-running, which is the case for pretty much every startup who needs an article like this. As long as you're aware of the typical footguns (N+1 queries) and understand your ORM's lazy-loading scheme, IMO it's worth the tradeoff of developing yet another way to manage and parametrize your queries.
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.
The main problem is ORMs are more work, because you need to understand the ORM. That’s its own beast, each ORM is different, and they change between releases. But SQL is something you already should understand, so you get to reuse the knowledge.
Also you can make SQL much more tolerable if you use migrations. That’s really where ORMs shine, but you don’t need the rest. Also query builders, although those also have a risk of abuse similar to ORMs. 99% of SQL should be static. That sounds like an absurdly high percent but it’s true if you use all the SQL features.
I can certainly see the appeal of number four, but on more than a couple of systems I've worked on, it would have blown up the amount of data stored in a fair proportion of tables for very questionable benefit. It's a good technique to call out, but is it really right to dictate it for everything?
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?
I've done it the other way before, sometimes a team choice rather than my own, and it's ended badly any time the changelog table is actually needed. Startups or just chaotic projects will change requirements and then need data that's only in changelog tables. The changelog tables in the meantime were only barely maintained as an afterthought, lacking FKs of course, and only useful for manual debug if even that.
#8 is https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80... and it’s one upside is that it’s very flexible and it’s downside is literally everything else. If you can be 100% certain that all of the destination value types are the same then it can have compressibility benefits.
Right, that whole class of problems mostly goes away when you're not mutating your sot. I've actually never needed to use SELECT FOR UPDATE that I can think of.
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.
On point 4, is the general flow that an "update" would read the current latest, check whatever conditions, then rather than updating, insert a new record (all in a transaction), or is your append only sot more an ordered "log" of update requests with the result of any conditional operations requiring a replay (from at least a check point). Or something else entirely.
I'm generally an immutable first, type developer but ive not had to do much schema design for a while and looking back to previous attempts we never hit scale to stress test my approaches.
Most of the time you will save yourself a lot of grief by having a transaction decorator for each endpoint, as each HTTP call should be atomic. And then have another read-only decorator for RO transaction.
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.
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.
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.
No - you do not always give it back immediately in many cases as you have a transaction, which cannot "change hands". If a write connection makes consecutive updates to the DB, you must see it through before closing.
I meant you give it back immediately when you're done with it. So usually after you commit, unless you want to hold it longer for some special reasons.
Every RDBMS out there has an option to configure a DB-enforced transaction timeout. But that configuration is tied to a connection, not to a transaction. So using that configuration means giving up connection pooling. Which a lot of people don't want to do because it impacts latency and DB resource usage.
Every xact within a given connection will use the same connection-wide config, but the timeout is counting how long a single transaction takes, right? I don't see why you'd need to give up pooling for this unless you need different settings per xact.
Pooling means you're sending multiple queries (from different HTTP requests) over the same DB connection. A well-designed DB wire protocol will allow for pipelining those queries:
[send Query 1] -> [send Query 2] -> [send Query 3] -> [receive Result 1] -> [receive Result 2] -> [receive Result 3]
But in Postgres and MySQL, pipelined queries are not executed in parallel. They're just queued up for a single thread (per connection) to execute sequentially. Thus, if Query 1 is a transaction that takes too long, then it ends up blocking the execution of Query 2 and Query 3.
* 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.
You can easily deal with these cases by tactically using transactions, but a vast majority of code can be simplified by dealing with an HTTP endpoint as an atomic concept. This all depends on what you are building. A classic CRUD app will benefit from this approach, microservices - not so much,.
Yes, it's that. You do probably end up wanting to store some "latest" denorm tables at some point, but it takes surprisingly long to reach that point, and isn't hard when you get there.
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.
I don’t have a lot of experience related to this so I’m just noting some things.
Some people in this thread don’t seem to think it’s that hard or overcomplicated.
When reading Designing Data-Intensive Applications my main takeaway was that event sourcing can make it easier to solve a lot of issues like performance, scaling, consistency, auditability, etc.
It would be interesting to look into what a low overhead way of implementing CRUD with event sourcing in Postgres would look like, then decide if it’s too complex.
Try making Hackernews lite. Users can post, comment on posts, vote/unvote posts, and delete their own posts and comments. CRUD tables might be post, comment, maybe vote. Event tables might be create_post, delete_post, create_comment, delete_comment, vote, unvote.
For #4 I always tell people to assume 99% append only, but do consider the need for updates/deletes in edge cases.
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.
Yeah, deletes need to be on the table (no pun intended) for PII redaction, and also emergencies where you manually do it. Both those situations are much safer when the DB is normally append-only.
Agreed. Usually an updated-on timestamp is sufficient to cover your bases without over-complicating the table. And your PII redaction is probably going to be shredding or nulling the fields instead of hard record-level deletes.
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 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.
Having been early at a startup that relied on Postgres I think this post doesn't put enough focus on monitoring and alerting. Postgres has a few key failure modes that you want to avoid ever happening, and you can use alerting to get early warning that you're danger of it happening.
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.
This kind of advice is very dependent on the scenario.
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.
Thanks! I should have clarified - we haven't been using this pattern for selective joins. Strongly agreed that pulling down extra data into memory and then doing the filtering doesn't make much sense. We've found it useful in the case where it's hard to write a query where the planner _does_ make good decisions because of the complexity of the join conditions (e.g. joins using cases, a boolean "or", or something similar).
Also, to re-emphasize: we do this rarely, but it's been helpful the times we've done it
I feel like i've heard of people using views for this as well. Like setting up two views and then joining across them because of the complexity of doing it all in one query. I could be wrong though.
> Because of all these connection footguns, external connection poolers like pgbouncer are great! If you can’t add this for whatever reason, in-memory connection poolers are a great second option. For example, because Hatchet is open-source, we don’t assume that all user databases use connection poolers, so we use pgxpool (an in-memory connection pool for Go) for this purpose.
Few people know that there is a major bifurcation when it comes to connection pooling implementation.
1. Most application connection poolers follow a first-in-first-out (FIFO) algorithm, which is simple enough to implement and is enough to make sure the application always has a connection available to connect to the database. It optimizes low latency, and works great from the point of view from the application. The problem is that it has few mechanisms to remove redundant connections, since the application is constantly keeping them all "warm".
2. PgBouncer and very few external poolers follow the inverse idea – last-in-first-out (LIFO), and they optimize for reducing the number of connections that reach Postgres, thus improving its throughput. The idea might seem crazy at first – the last connection used is the first one to be picked up again – but this algorithm automatically removes excess connections, which will get cold and get closed.
When starting a new application, option (1) is enough, but as it scales up enough, at some time it is recommended to use (2), since having hundreds of open connections to Postgres is bad for performance if you can use PgBouncer or similar to cut it by 90%. Postgres' process-per-connection design works much better when there are fewer connections reaching it.
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.
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/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
Cheapest Amazon RDS Postgres is like $15/mo if that's cheap enough. If you're doing lots of projects and don't want to pay that for each one, you can CREATE DATABASE for each with separate ACLs.
You mentioned not wanting to spend admin hours fine-tuning a self-hosted instance though. Are you really hitting the DB hard enough for that to matter, but SQLite works fine? Cause I haven't tuned local Postgres in years.
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.
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
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.
Nice. How fast is your home internet connection? I’ve thought about putting an old laptop to this use. But I don’t want my day to day internet to suffer if my site gets traffic spikes. And also, I’m nervous about non-ecc ram.
My internet is very fast for sure (can give more concrete numbers when I am home later) but I don't think you need to worry about it unless you are operating some massive website with huge traffic concurrently.
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.
Yeah mostly just keepalives and timeouts, increasing kernel maximums for connections, using Unix sockets directly instead of tcp, using pgbouncer, etc. as always, depends on use case and monitoring and measuring to determine your needs is good.
man, i wouldn't worry about tuning something like TCP until you can reliably prove TCP is the bottle neck in performance. That day will likely never come for most companies.
Until you need to scale up it's perfectly acceptable to just run postgres on the same instance as the logic that's executing. It's not a great strategy in the long run due to all your eggs being in one basket and the need to configure things like backups manually but it'll save buckets of money compared to going with something prerolled by AWS while the functionality it'd give you wouldn't be noticeable.
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.
> The reason I think it’s useful to view queries as binary—they either seq scan or they don’t seq scan—is: the more you micro-optimize a query, the more of a risk you take that the query planner goes rogue. If you stick to querying by primary keys and indexes, the query planner will have a much easier time.
It's also important to notice the query planner optimizes for the average case, but often it would be better for the app developer if it was optimized for the worst case. But optimizing for the former is a much more tractable problem, so no wonder that's what is implemented.
I had to fight against the query planner when it would optimize a query for the average user, with few rows in a given table, and it would pick one index that made sense for that situation and return a result in less than 10ms. However, when a heavy user issued the same query, depending on the exact parameters the worst case could take over 1 second. So I had to write a much more complex query to force it to take another path with a different index, which would be slower in the average case, but in the worst case would take still less than 100ms. Avoiding timeouts was much more important for my company than taking 10ms more in the average case.
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 "ordering" on your tables to avoid dining philosophers?
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.
Recalling from my previous studies here: I think you can use Serializable Isolation Level, the strictest level - this will cause one of the two to fail (that is; fail only when the two txns affected rows that would logically conflict). And then you build the expectation of such possible transaction failures into the code and treat retries as a first-class expectation. Does this get to what you're trying to solve at all?
It does get at what I'm talking about. But I've seen retrying in this situation lead to worsening the situation, because your basic problem is two hot paths conflicting with each other and now you're conflicting even more.
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!
When I've dealt with this I've generally made sure the transactions are updating rows in a consistent order. You can do that by sorting the rows before you update them
If you have a horizontally scaled app (many 100s of API servers and async workers) you’re also probably going to need a connection pooling proxy like pgbouncer! with separate pools for separate connection configs (lower/higher timeouts, reader/writer). There’s a section on this that’s a bit of a stub right now, but IME tuning and configuring these connection poolers is pretty nontrivial and worth an expanded section!
I’ve seen this pointed out in other comments but I’d also strongly recommend expanding with a section on monitoring and alerting. One could write a blog post almost of this length just on monitoring :)
And another nontrivial one, in compound indices it’s really important that you order your columns in descending selectivity order. I.e. the column with the most unique values should go first.
In degenerate table/index situations, this could lead to index scans that are as slow as table scans, or not using an index at all! Especially common in SaaS schemas where you’re dealing with a tenancy key in many of the indicies: that tenancy key should almost always suffix the composite key not prefix it!!
> FOR UPDATE SKIP LOCKED
> The best way to think about this Postgres feature is that it reserves the rows that you’re selecting for use in your transaction without interfering with other queries. We use it primarily for implementing our job queue;
SKIP LOCKED is useful for implementing job queues with interactive transactions – you lock the row while working on it in the application and keeping the transaction open. For high-performance applications it is best to avoid interactive transactions at all, and just update the rows to "pending" immediately. There is no need for SKIP LOCKED in this case.
As a rule of thumb, as you scale up the application, you want to have less state in the database memory, and interactive transactions are just that. Idempotence beats atomicity at scale.
I disagree with the timestamptz advice. I tend to use timestamp (without the timezone), this forces me to use UTC everywhere, so I'm not even tempted to use anything else. I work in fintech, and so far whenever i saw someone storing datetimes with an associated time zone, it always ended with a disaster.
`timestamptz` is probably poorly named. It doesn't actually store a timezone at all - all values are stored as UTC. The underlying storage is 8 bytes and otherwise identical for both timestamp types. However, using `timestamptz` allows you to more easily group by day, hour of day, etc. in a non-UTC timezone when that makes sense. Especially when dealing with summer time/daylight savings time, this can be quite useful.
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.
On migrations, there's a .Net tool called Grate that I tend to use for schema migrations... I don't use all the features, but it works well... using a migration stack in a repository for deployments and a similar tool is IMO more reliable than magic comparison tools or hand migrations in practice. You should defensively write your migrations as much as possible so that re-runs are relatively safe, though the tool helps to handle this.
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 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.
> 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.
They’re really not that bad. Even on large-ish tables (hundreds of millions of rows), the typical query time I see for a query with 1-2 inner joins is 1-2 msec. That can of course vary with result set size, but in general it’s going to be dwarfed by network RTT.
If you’ve tested your schema with normalized and denormalized versions and found a significant difference that justifies it, by all means, but IME query speed for any non-trivial query is generally dominated by query shape, index design, and schema design (specifically for clustering indices, not taking advantage of it to have physical and logical tuple correlation).
I usually start by seeing how far I can get with just a single idempotent schema.sql file, usually good enough. Those migration tools have sort of a git within a git managing merge conflicts, which gets very messy with a team of SWEs, esp if rubberstamping Claude-generated PRs. I don't want to introduce that without a very clear reason why the single file with regular git merge tooling isn't good enough.
How do you handle schema changes after your project is in production?
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.
Make a PR that edits the .sql file, deploy to staging, deploy to prod. Git tracks changes to the file, and your CI should be aware of what commit it's on. (If you even have CI)
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.
Great blog! Thanks for writing this one up. Such a useful one for anyone who is build with Postgres. Succinctly reminds of all the battle scars working with many customers over the past decade. ;)
If your domain is analytics-heavy, don't try to optimize your Postgres for analytics. Follow the standard pattern of mirroring your data to a data warehouse and go to town there instead. There will be upfront cost of having to pay for a warehouse and the ETL, but it will be worth it.
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.
This was a helpful guide. For someone using postgres for a few years, but rarely to its limits, a lot of it was review, but it had some great new tidbits to take in.
Lately I been questioning whether it’s actually a good idea to pool connections. Don’t your in the risk of leaking privileges or information from other requests?
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.
This worry relies on a zero day bug/memory exploit in one of the most widely used access methods for Postgres. This worry can be applied to every component of the software stack, including the OS.
Hmm, not really. Whether the kernel is managing memory for processes properly is different than asking whether a reused Postgres connection clears all relevant memory.
> I’ve found normal forms to sometimes be at odds with query efficiency and ease of use, which is critical when you’re moving fast—sometimes it’s just easier to dump data into a jsonb column.
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.
To this articles credit, it does start out with normalization and design!
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.
I'm a big fan of persisting almost everything in the primary database. With one exception: I'd immediately use object storage (S3) for files which are large in number or size. Files which are few and small (e.g. templates) are fine in the db.
can you share what are the common pitfalls with self hosting a postgres image, as I'm planning to do? I know self hosting will bite my ass sooner than later, I just want to be somewhat ready and prevent easy mistakes
* BRIN indexes are great for append only with an incremental value (a 50MB instead of 100GB index in timeseries data in my case)
* If your disks are ssd and scsi in different volumes, adapt the random cost of io in the config to let the planner now
* if you have RAID controller and a Battery-Backed Write Cache (BBWC), you can disable Linux filesystem write barriers. removing excessive fsync from the mouth of the psql demigod in SoCaL ~linux 2015, Bruce Momjian IIRC
* monitor disk usage, in backups pipe to gz, never to disk
* counter-intuitevely modern hardware may have an io bottleneck and plenty of cpu, so try Filesystems like ZFS using Zstandard (zstd), for boost in your Transactions per second
* if possible, schema multitenancy instead of database multitenancy, instagram talked about this decades ago
* indexes index functions results too, precompute those fields and partial indexes help a lot
* BM25 and FTS in pg are so good you probably don't need Elasticsearch and you will save a lot in de-sync between both of them
* you may be hacked in this brave new world post Mythos, thus, learn PITR to an external only write, no override medium like S3
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.
Also zero mention of `text`, which is highly encouraged in Postgres instead of the silly old `varchar(255)`
OP here, I appreciate the feedback! I tried to focus on things which could take down your database, so things like particularly slow reads and writes, autovacuum settings, reducing lock contention, particularly focused on cases that I've seen. There are lots of things that I left out which would belong in a general user guide.
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.
Stored procedures and SQL injection are orthogonal concerns. You can have a parameterized query using PREPARE without needing to resort to stored procedures, and many database drivers or wrappers help you with this by making you provide a string with something like $1 and then the values which are sanitized.
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.
> 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.
Stored Functions/Procedures tend to make database into monolith with API that everyone calls, with endless screaming when it gets too big and any schema change takes days to accomplish.
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.
> 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. ;)
You do not need stored procedures or functions to prevent SQL injection. Any Postgres client library from the last decade or two supports parametrized queries, and that's enough. Odds are, most people will use an ORM anyway, which also avoids SQL injection.
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.
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 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.
> 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.
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.
* 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).
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.
> 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
7/4 'converters' have been featured on HN a few times:
* https://github.com/ali-master/uuidv47
* https://github.com/stateless-me/uuidv47
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.
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
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.
If you're a SaaS, 1h of downtime costs a lot of money. In reputation, in lost business, in support time, in on-call response, in making a public postmortem and sharing it to customers and dealing with responses... For saving a few thousand bucks, it's often indeed a very bad deal.
Of course this isn't true for some companies and the cost of downtime far exceeds the cost of HA. Most companies think they are that company, most aren't. And even if they are, "AWS had an outage" does a lot of lifting.
Every time I get a new client they need constant point in time backups, failover, HA everything. Usually when the costs are explained they change their mind and an hour old snapshot restore is actually fine
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.
What does the equivalent RDS setup cost you?
Also, if you are nickel and diming over expenses in the 50€ range, you’re probably at the wrong startup.
We’re not talking about maintaining your own k8s cluster here mind you, but a basic Postgres setup. If you can’t handle that comfortably in an afternoon, you probably shouldn’t be entrusted with customer data.
Intelligence is being able to set up your own postgres database with backups, PITR, retention policies, HA/replicas, and everything else needed to prevent catastrophic data loss. Wisdom is realizing that's a solved problem completely unrelated to your business, so you don't have to.
1. Don't use a third-party auth provider, if you can't build out an OAuth provider in an afternoon you shouldn't be entrusted with login credentials
2. Don't use Github, if you can't build out an internal Gitlab instance in an afternoon you shouldn't be entrusted with securing source code
3. Don't use Github Actions or CircleCI, if you can't self-host a Jenkins server in an afternoon you shouldn't be entrusted with secure deployments
4. Don't use a lawyer or outsourced HR, if you can't draft legally binding employment agreement in an afternoon you shouldn't be entrusted with hiring people
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.
https://pgbackrest.org/news.html
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?
I'm all for treating DB as cattle, but your cattle needs to be able to survive long enough.
EBS is expensive but it works for PG. AWS uses EBS for RDS databases. Calling that a 'mistake' is a tall order.
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.
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.
https://github.com/cloudnative-pg/cloudnative-pg
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.
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 UUID backup/recovery thing isn't an issue if you're doing append-only. Joins on UUIDs are far slower, enough that even at small scale it can cause issues when you have many joins. Anyway I won't argue too hard against UUIDs cause they work too, just anything is better than using meaningful fields as the PKs.
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”.
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.
That’s a silly take. I somehow learned over the long run: SQL different flavors, couple of ORMs, multiple programming languages and multiple frameworks. Not counting different troubleshooting different operating systems and different applications I had to work with.
When it comes to advanced queries learning the ORM equivalent to the SQL it should write… ORM’s can be outright terrible, and I completely agree with you.
Every ORM has their own names and way of doing things, so this knowledge is hard to port to other ORMs. It’s requires you knowing the right SQL first, then knowing how to write that in the ORM dialect.
Reaching for the more advanced ORM trickery means grasping hard to grasp subjects twice with the risk of misunderstanding twice, and as a bonus worse ORM documentation than plain active record features. Oh, and others need to understand what you’ve written as well.
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.
I think everyone always is needlessly partisan when ORMs get mentioned.
Personally, I view it like so:
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.
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.
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.
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 project „SQL first” you robbed yourself from ORM upsides and you are bound to badly implement your own one in the long run.
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.
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.
Also you can make SQL much more tolerable if you use migrations. That’s really where ORMs shine, but you don’t need the rest. Also query builders, although those also have a risk of abuse similar to ORMs. 99% of SQL should be static. That sounds like an absurdly high percent but it’s true if you use all the SQL features.
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?
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.
I'm generally an immutable first, type developer but ive not had to do much schema design for a while and looking back to previous attempts we never hit scale to stress test my approaches.
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.
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.
I still have to get a connection from the pool, I just do it inside the function body now, right?
So this
would become that instead: But I still need enough connections in the pool to handle all concurrent requests, no?[send Query 1] -> [send Query 2] -> [send Query 3] -> [receive Result 1] -> [receive Result 2] -> [receive Result 3]
But in Postgres and MySQL, pipelined queries are not executed in parallel. They're just queued up for a single thread (per connection) to execute sequentially. Thus, if Query 1 is a transaction that takes too long, then it ends up blocking the execution of Query 2 and Query 3.
* 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.
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.
Some people in this thread don’t seem to think it’s that hard or overcomplicated.
When reading Designing Data-Intensive Applications my main takeaway was that event sourcing can make it easier to solve a lot of issues like performance, scaling, consistency, auditability, etc.
It would be interesting to look into what a low overhead way of implementing CRUD with event sourcing in Postgres would look like, then decide if it’s too complex.
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.
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 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
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
* Materialised views (especially if the computation/joins are particularly nasty).
* Left outer joins are a good alternative to 'joins using case' and more likely to use the index.
Few people know that there is a major bifurcation when it comes to connection pooling implementation.
1. Most application connection poolers follow a first-in-first-out (FIFO) algorithm, which is simple enough to implement and is enough to make sure the application always has a connection available to connect to the database. It optimizes low latency, and works great from the point of view from the application. The problem is that it has few mechanisms to remove redundant connections, since the application is constantly keeping them all "warm".
2. PgBouncer and very few external poolers follow the inverse idea – last-in-first-out (LIFO), and they optimize for reducing the number of connections that reach Postgres, thus improving its throughput. The idea might seem crazy at first – the last connection used is the first one to be picked up again – but this algorithm automatically removes excess connections, which will get cold and get closed.
When starting a new application, option (1) is enough, but as it scales up enough, at some time it is recommended to use (2), since having hundreds of open connections to Postgres is bad for performance if you can use PgBouncer or similar to cut it by 90%. Postgres' process-per-connection design works much better when there are fewer connections reaching it.
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.
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 mentioned not wanting to spend admin hours fine-tuning a self-hosted instance though. Are you really hitting the DB hard enough for that to matter, but SQLite works fine? Cause I haven't tuned local Postgres in years.
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.
Things like connections stats, deadlock monitoring, slow queries. All come standard in AWS/GCP.
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.
It's also important to notice the query planner optimizes for the average case, but often it would be better for the app developer if it was optimized for the worst case. But optimizing for the former is a much more tractable problem, so no wonder that's what is implemented.
I had to fight against the query planner when it would optimize a query for the average user, with few rows in a given table, and it would pick one index that made sense for that situation and return a result in less than 10ms. However, when a heavy user issued the same query, depending on the exact parameters the worst case could take over 1 second. So I had to write a much more complex query to force it to take another path with a different index, which would be slower in the average case, but in the worst case would take still less than 100ms. Avoiding timeouts was much more important for my company than taking 10ms more in the average case.
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!
- Don't use long-running transactions. They are a risk for db health. Only use transaction when you have a strong justification
- Set idle_in_transaction_session_timeout to prevent a long-running transaction from holding on to locks or tuples
- Set lock_timeout for migrations to prevent a single DDL statement to bringing down your system
- Set statement_timeout to prevent an expensive query from bringing down your system
I’ve seen this pointed out in other comments but I’d also strongly recommend expanding with a section on monitoring and alerting. One could write a blog post almost of this length just on monitoring :)
In degenerate table/index situations, this could lead to index scans that are as slow as table scans, or not using an index at all! Especially common in SaaS schemas where you’re dealing with a tenancy key in many of the indicies: that tenancy key should almost always suffix the composite key not prefix it!!
SKIP LOCKED is useful for implementing job queues with interactive transactions – you lock the row while working on it in the application and keeping the transaction open. For high-performance applications it is best to avoid interactive transactions at all, and just update the rows to "pending" immediately. There is no need for SKIP LOCKED in this case.
As a rule of thumb, as you scale up the application, you want to have less state in the database memory, and interactive transactions are just that. Idempotence beats atomicity at scale.
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)
They’re really not that bad. Even on large-ish tables (hundreds of millions of rows), the typical query time I see for a query with 1-2 inner joins is 1-2 msec. That can of course vary with result set size, but in general it’s going to be dwarfed by network RTT.
If you’ve tested your schema with normalized and denormalized versions and found a significant difference that justifies it, by all means, but IME query speed for any non-trivial query is generally dominated by query shape, index design, and schema design (specifically for clustering indices, not taking advantage of it to have physical and logical tuple correlation).
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.
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.
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.
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.
I would compare it to the number of lawyers over population
:(
* If your disks are ssd and scsi in different volumes, adapt the random cost of io in the config to let the planner now
* if you have RAID controller and a Battery-Backed Write Cache (BBWC), you can disable Linux filesystem write barriers. removing excessive fsync from the mouth of the psql demigod in SoCaL ~linux 2015, Bruce Momjian IIRC
* monitor disk usage, in backups pipe to gz, never to disk
* counter-intuitevely modern hardware may have an io bottleneck and plenty of cpu, so try Filesystems like ZFS using Zstandard (zstd), for boost in your Transactions per second
* if possible, schema multitenancy instead of database multitenancy, instagram talked about this decades ago
* indexes index functions results too, precompute those fields and partial indexes help a lot
* BM25 and FTS in pg are so good you probably don't need Elasticsearch and you will save a lot in de-sync between both of them
* you may be hacked in this brave new world post Mythos, thus, learn PITR to an external only write, no override medium like S3
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)`
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.
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.
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.
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.
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.
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.
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.