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.
The startup's Postgres survival guide
71–80 of 255 posts
Re: The startup's Postgres survival guide
#72for people who have tried hatchet & restate - which one do you prefer ?
Re: The startup's Postgres survival guide
#73the first rule of database management is to not host or manage your database unless you are willing to pay someone to do it full time
Re: The startup's Postgres survival guide
#74I 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.
Re: The startup's Postgres survival guide
#75Earlier quoted context omitted.
The $10 VPS that serves your web app can run Postgres just fine. If it can’t? Fire up another $10 VPS. Learn how to tune your configs and network settings and query/cache efficiently.
Any pointers to network configuration to tune? Some TCP stuff? How much does it matter on that VPS network?
This is a pretty good resource for some basic tuning (mostly buffer sizes and connection count): https://pgtune.leopard.in.ua/
Re: The startup's Postgres survival guide
#76Great 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. ;)
Re: The startup's Postgres survival guide
#77I'd say this applies to any database management system.
Re: The startup's Postgres survival guide
#78Earlier quoted context omitted.
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…
> 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.
Re: The startup's Postgres survival guide
#79Migrating additional columns is interesting to avoid damaging the database.
Re: The startup's Postgres survival guide
#80Earlier quoted context omitted.
The cursor is not shared.
Shared memory is shared memory. Are the pages zeroed out?
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.