41 ms·

How we upgraded our 4TB Postgres database

384 points by mrbabbage 4y ago

mrbabbage 4y ago
Hey folks—I wrote the post! This was my biggest Postgres project to date, and it proved quite tricky since I didn't rehearse with a test database of the same size. I learned a bunch about Postgres, not least the incredibly powerful NOT VALID option for safely and quickly adding constraints. Happy to stick around and answer any questions you have.
- aoms 4y ago
  This instance must cost you a ton
  - mrbabbage 4y ago
    More than I care to admit! As mentioned downthread, we're doing some work soon to remove the audit table from the database, which will cut storage usage by over 50%.
    - clessg 4y ago
      > we're doing some work soon to remove the audit table from the database Out of pure curiosity, what are you replacing it with (if anything)? Just a simple rotating log file?
      - mrbabbage 4y ago
        Exact strategy to be determined—we're looking at various data layers at the moment. I wish we could do something simple like a rotating log file, but we want to be able to query it in the app (for instance, to show recent logins).
        clessg 4y ago
        Very interesting! Looking forward to the next blog post on that ;)
        jamesrr39 4y ago
        Have you considered an OLAP database like Clickhouse or QuestDB? An OLAP database would be a much better fit for audit tables given the read and append-only writing requirements, would compress better, and you might be able to fit it directly without changing any app code with a Postgresql foreign data wrapper.
        lillecarl 4y ago
        I was thinking TimescaleDB
      - icheishvili 4y ago
        You can partition your audit/event table by time period and archive old events [1] or you can avoid the records hitting the database in the first place by generating the events elsewhere to begin with [2]. [1] https://github.com/icheishvili/audit-trigger https://github.com/icheishvili/audit-trigger [2] https://github.com/eulerto/wal2json https://github.com/eulerto/wal2json
    - Teletio 4y ago
      What's your strategy? Using compression? Second instance for old data? Creating in-between states? Just curious :)
    - tmikaeld 4y ago
      Seems to be around 1784$/monthly according to the pricing calculator
    - loopdoend 4y ago
      Let me save you a bunch of money: https://adamj.eu/tech/2021/04/13/reindexing-all-tables-after-upgrading-to-postgresql-13/ https://adamj.eu/tech/2021/04/13/reindexing-all-tables-after...
      - karmelapple 4y ago
        This technique saved us from seriously increasing the cost of our Heroku Postgres instance. Thank goodness it exists and works so well. Multiple 80+ GB indexes shrinks down to less than 10GB after just a couple of hours.
- abrkn 4y ago
  Great read! Thanks
- tmikaeld 4y ago
  How come you didn't consider a distributed database like CochroachDB, instead of Postgres?
  - nrmitchi 4y ago
    I'm not OP, but they were upgrading from Postgres 9.6, which at least implies that this initial db was from ~2017. This is barely past the initial release of Cockroach. It would have been kind of crazy for the Retool team to use an experimental db with a lack of history when building up their product (that was not dependent on experimental new features)
    - tmikaeld 4y ago
      Baidu migrated from an old MySQL cluster to CockroachDB for their 40TBs of databases, now runs on 20 clusters. When does it stop being experimental to you? https://www.cockroachlabs.com/customers/ https://www.cockroachlabs.com/customers/
      - flyinknockin 4y ago
        I don't think they're calling Cockroach experimental right now - rather that it was experimental in 2017.
        tmikaeld 4y ago
        Yeah, but they didn’t upgrade in 2017, they upgraded now, it’s in the first sentence. They had ample opportunity to migrate
- wrs 4y ago
  Wondering why you couldn’t use in-place upgrade (pg_upgrade [0]). Not supported in Azure? [0] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
  - majewsky 4y ago
    I'm guessing this would still take more than one hour since it needs to rewrite all (or most) of the 4TB?
    - aidos 4y ago
      Not in my experience. You can use the —-link flag to get it to use hard links so it doesn’t need to move the data at all. Have been through this process myself a few times and it only took seconds on a 50-100GB db. I’m always a little surprised with how week it works.
      - wrs 4y ago
        Exactly, it just has to touch the metadata, unless the data page format has changed, which the developers try hard to avoid (successfully so far).
- palijer 4y ago
  I'm curious why a test run on a proper sized replica database wasn't in the testing plans. That is something I've been ensuring happens for a while now for similar projects.
- tianzhou 4y ago
  Good write-up. I am wondering what's the process of performing regular schema change (e.g. ALTER TABLE) on a 4TB instance looks like?
- davidshepherd7 4y ago
  I'm probably missing something, but it sounds like using Warp has a bunch of downsides vs "just" creating a read only replica using logical replication and then failing over. Did you choose Warp only because of Azure's limitations or were there other reasons?
- somtum 4y ago
  Great post! Did you migrate your old database to Azure Flexible Server, Hyperscale (Citus) or a standalone VM, as Postgres 13 does not seem to be available for Azure Postgres Single Server.
georgewfraser 4y ago
It is amazing how many large-scale applications run on a single or a few large RDBMS. It seems like a bad idea at first: surely a single point of failure must be bad for availability and scalability? But it turns out you can achieve excellent availability using simple replication and failover, and you can get huge database instances from the cloud providers. You can basically serve the entire world with a single supercomputer running Postgres and a small army of stateless app servers talking to it.
- dijit 4y ago
  Even when it’s not a cloud provider, in fact, especially when it’s not a cloud provider: you can achieve insane scale from single instances. Of course these systems have warm standbys, dedicated backup infrastructure and so it’s not really a “single machine”; but I’ve seen 80TiB Postgres instances back in 2011.
  - cogman10 4y ago
    We are currently pushing close to 80tb mssql on prem instances. The biggest issue we have with these giant dbs is they require pretty massive amounts of RAM. That's currently our main bottle neck. But I agree. While our design is pretty bad in a few ways, the amount of data that we are able to serve from these big DBs is impressive. We have something like 6 dedicated servers for a company with something like 300 apps. A hand full of them hit dedicated dbs. Were I to redesign the system, I'd have more tiny dedicated dbs per app to avoid a lot of the noisy neighbor/scaling problems we've had. But at the same time, It's impressive how far this design has gotten us and appears to have a lot more legs on it.
    - Nathanba 4y ago
      Can I ask you how large tables can generally get before querying becomes slower? I just can't intuitively wrap my head around how tables can grow from 10gb to 100gb and why this wouldnt worsen query performance by x10. Surely you do table partitions or cycle data out into archive tables to keep up the query performance of the more recent table data, correct?
lijogdfljk 4y ago
How does MySQL fair in this type of setup? Do the DBs differ greatly?
- thatwasunusual 4y ago
  I worked for a company that did a similar "upgrade by replication", but with MySQL. It's quite a few years ago, so I don't remember the versions involved, but it was quite straight-forward once we had done _weeks_ of test runs on a dev environment. One invaluable thing, though: our application was from the beginning designed to do 100% of all the reads from a read-only slave _if the slave was up to sync_ (which it was 95% of the time). We could also identify testers/developers in the application itself, so we had them using the upgraded slave for two weeks before the actual upgrade. This made it possible for us to filter out problems in the application/DB-layer, which were few, which means that we probably did a minor version upgrade. But upgrading by replication is something I can recommend.
- evanelias 4y ago
  MySQL's built-in replication has always been logical replication, and it officially supports replicating from an older-version primary to newer-version replicas. So similar concept to what's described here, but much simpler upgrade process. Generally you just upgrade the replicas; then promote a replica to be the new primary; then upgrade the old primary and turn it into a replica. The actual "upgrade" step is quite fast, since it doesn't actually need to iterate over your tables' row data. At large scale, the painful part of major-version MySQL upgrades tends to be performance testing, but that's performed separately and prior to the actual upgrade process. Third-party tools (pt-upgrade, proxysql mirroring, etc) help a lot with this.
giovannibonetti 4y ago
By the way, Google Cloud recently launched in-place upgrade of Postgres instances. A few days ago we used it to upgrade our multi TB database in my company as well. https://cloud.google.com/blog/products/databases/cloud-sql-launches-support-for-in-place-upgrades https://cloud.google.com/blog/products/databases/cloud-sql-l...
- davidkuennen 4y ago
  Woah, this is great. Been waiting for this, since Cloud SQL has been very reliable in the past few years I've been using it, but upgrading was always a pain.
- aeyes 4y ago
  What is "in-place" about this? According to the docs you'll have ~10min downtime and you'll loose table statistics which is exactly what happens when you run pg_upgrade manually. The biggest problem with all the cloud providers is that you won't know exactly when this 10 minute downtime window will start I guess the only advantage here is that you don't have to do 9->10->11->12->13->14 like in the past and maybe that was one of the blockers Azure has. AWS allows to skip some major versions but 9->14 is not possible.
  - sa46 4y ago
    In-place is a separate concept from zero downtime. Similarly, an inplace upgrade of your OS doesn't mean you can continue using the OS during the upgrade; it means you get to keep your data without restoring from an external backup. The benefit of an inplace upgrade for postgres is you don't have to spin up another server, restore from backup, and run pg_upgrade yourself.
  - llama052 4y ago
    Azure doesn't really offer migrations paths, and their database migration tool has a ton of edge cases (not supported over 1tb etc) so while pg_upgrade is nice, Azure doesn't really have a path to use that. On top of that Azure postgres (limited to pg11) has essentially deprecated in place of their v2 Flexible tier with no official migration path.
Spivak 4y ago
It's crazy how many apps opt for an RDBMS for append-only data like audit events. It's so tantalizing at the beginning but turns into a nightmare time marches forward. audit events -> queue -> elastic -> blob storage is so easy to maintain and we save TBs from living in the DB.
- mrbabbage 4y ago
  We (Retool) are going to be doing this very soon and cut our database size by 50%+. And you're exactly right: it's so easy to get started by sticking audits (or other append-only data schema) in an RDBMS, but it quickly becomes a headache and bottleneck.
  - abrkn 4y ago
    What will you use as a queue and storage?
    - mrbabbage 4y ago
      To be determined!
  - baq 4y ago
    up until some size the headache from maintaining a separate datastore is bigger. everything should be in the RDBMS until proven otherwise for the sake of simplicity. it's actually amazing how much you can squeeze out of 'old school' databases. e.g. https://docs.microsoft.com/en-us/azure/azure-sql/database/ledger-append-only-ledger-tables https://docs.microsoft.com/en-us/azure/azure-sql/database/le...
- luhn 4y ago
  Mind expanding on what the "nightmare" is?
- onphonenow 4y ago
  Actually, I've seen more problems with folks mixing lots of different tools up then I have from folks doing an append only audit event in a RDBMS. When your audit trail is in DB, you can pretty easily surface audit events to your customers. Who changed what when is just another feature. Capturing audit events is also usually pretty smooth. The folks doing the blob storage route, you would not BELIEVE the complexity they have to spin up to expose very simple histories etc. This matters a LOT in some spaces (financial etc), less so in others. In my RDBMS model, who changed this field when from what to what is a basic select. You can even shard by recordID or similar if you want to reduce table scans, good select of indexes etc can be a huge help as well. In most cases users don't mind a bit of latency on these queries.
cube00 4y ago
> To resolve this, we ended up choosing to leave foreign key constraints unenforced on a few large tables. > We reasoned this was likely safe, as Retool’s product logic performs its own consistency checks, and also doesn’t delete from the referenced tables, meaning it was unlikely we’d be left with a dangling reference. I was holding my breath here and I'm glad these were eventually turned back on. Nobody should ever rely on their own product logic to ensure consistency of the database. The database has features (constraints, transactions, etc) for this purpose which are guaranteed to work correctly and atomically in all situations such as database initiated rollbacks that your application will never have control over.
- jasfi 4y ago
  True, but the DB constraints often aren't enough. I like to have a verify program to check that the DB is as expected.
  - weird-eye-issue 4y ago
    You don't trust DB constraints?
    - rattray 4y ago
      They're limited in what they can express; your application often has invariants you want to enforce/maintain that can't be (performantly) expressed with DB constraints, and must be validated another way. As great as it can be to enforce rules within the database, a lot of them usually end up needing to be enforced at the application layer instead. Especially when performance at scale comes into play.
      - brightball 4y ago
        I think it’s a balance. Transactions + Constraints can enforce most things but there will certainly be things that can only be verified in the app. My goal is always to verify what I can in the database to minimize potential data cleanup. In my experience, app only verification always leads to future time investments to clean up the mess.
electroly 4y ago
In SQL Server you just... do the upgrade. You install the upgrade on your nodes starting with the passive nodes, and it will automatically failover from the old version to the new version once half the nodes have been upgraded. No downtime, but your redundancy drops when some nodes have been upgraded but the cluster hasn't fully been upgraded yet. You certainly don't have to dump and restore your database. Without giving private numbers, our database is much bigger than OP's 4TB; dump and restore would be wildly unacceptable. The idea that you don't get a seamless upgrade of the database itself with PostgreSQL is absurd to me. The part about "maximizing the amount of time this upgrade buys is" is only necessary because of how difficult PostgreSQL makes upgrades. We upgrade to every new version of SQL Server. It's not that big of a deal. With every PostgreSQL blog article I read, I become more and more of an SQL Server fanboy. At this point it's full-blown. So many "serious business" PostgreSQL ops posts are just nothingburgers in the SQL Server world.
- Teletio 4y ago
  Amy real facts you can show besides your sentiment on blog posts?
  - electroly 4y ago
    What facts are you looking for? I just described the steps from this document: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance https://docs.microsoft.com/en-us/sql/sql-server/failover-clu... -- specifically, the "Perform a rolling upgrade or update" section. There's nothing else to my post other than contrasting the SQL Server upgrade process to the one described in the article, and musing about my growing appreciation for SQL Server; I apologize if it seemed like it was going to be deeper than that. EDIT: I realized you're looking for the other PostgreSQL blog posts. Here's an example of two recent HN posts about PostgreSQL issues that I pulled out of my comment history. Both of these blog posts exist because PostgreSQL doesn't have query hints. SQL Server has them; I've dealt with issues like these blog posts describe but they have trivial fixes in the SQL Server world. Nothing to write a blog post about. I don't have a link handy regarding PostgreSQL's txn id wraparound problem, but SQL Server doesn't have that problem, either. - https://news.ycombinator.com/item?id=30296490 https://news.ycombinator.com/item?id=30296490 - https://news.ycombinator.com/item?id=29981737 https://news.ycombinator.com/item?id=29981737
Teletio 4y ago
The first upgrade Strategie is not the normal or easy one on anything production btw. Very small companies might be able to do this on 'no load day' but from a pure business perspective, running your db twice is easier and way less risky. You could have done this even without downtime by letting your connection proxy handling the switch.
sscarduzio 4y ago
Hey @mrbabbage! Retool customer here! Great service :) Non distributed RDBMS is a great (yet underrated) choice. Thank you for the good writeup. I was thinking you could have a much less delicate migration experience next time (some years from now). So you can go for a quicker parallel "dump and restore" migration. For example: - Client side sharding in the application layer: you could shard your customers' data across N smaller DB instances (consistent hashing on customer ID) - Moving the append-only data somewhere else than postgres prior to the upgrade. You don't need RDBMS capabilities for that stuff anyway. Look at Elasticsearch, Clickhouse, or any DB oriented to time series data. WDYT?
- mrbabbage 4y ago
  The second bullet point is underway! Getting audit events out of the main database will be a major headache saver. The first bullet point is on our radar for the near term. We have a very natural shard key in our schema (the customer ID), with AFAIK no relationships across that shard key. And once we start horizontally sharding, we can do cool things like putting your data in a shard geographically close to you, which will greatly increase app performance for our non US customers. Exciting stuff coming down the pike!
  - sscarduzio 4y ago
    Oh cool! Yes locality is very important! Great idea :)
sega_sai 4y ago
Using pg_upgrade I recently updated this 100Tb sized DB (from PG12 to PG13) in ~10 min of downtime. => select pg_size_pretty(pg_database_size ('**')); pg_size_pretty ---------------- 99 TB (1 row) (The re-analyze of tables took a day or so though)
- riku_iki 4y ago
  Curious what is your storage story? Where DB is actually stored? Some NAS?
  - sega_sai 4y ago
    It's DAS. Mostly it's in one box of RAIDed 32x5.5Tb drives with a couple of tablespaces/WAL elsewhere. The DB is mostly read-only and not many concurrent users, so that's probably not the most typical case.
- comboy 4y ago
  Yeah, my petty 6Tb also went fine with pg_upgrade and practically no downtime. Upgrade slave, promote to master, upgrade master and then promote it back. It's a marvelous piece of technology. It's really just a handful of core people who did most of the work, crafting it so thoughtfully over the years and it has such a huge impact on the world. Doing huge part of it before postgresql was as popular as it is today, spending countless hours on making some great design choices and implementing them carefully. It seems unlikely any of them will read that but I'm so deeply grateful to these people. It allowed so many things to flourish on top thanks to it being open source and free.
- quleap 4y ago
gamegod 4y ago
If you're running a 4 TB Postgres database, but you still have to worry about this level of maintenance, what's the value proposition of using a hosted service? There's usually insane markup on any hosted Postgres instance. If you want to pay multi-thousands dollars a month for a database server, it's WAY cheaper just to slap a server with a ton of drives in colocation.
- orangepurple 4y ago
  Might be an accounting scam^H^H^H^H trick to book the costs as an operating expense vs a capital expenditure. In general capital expenditures have to be planned carefully, held on the books for years, and show a return on investment. Perhaps an accountant can provide more detail.
  - mywittyname 4y ago
    I feel like the explanation is a much more simple: it's easier to use Azure/AWS/GCP for everything. Yeah, this migration might have been somewhat painful, but it looks like it's a situation the company experiences every few years.
  - ipaddr 4y ago
    This is a big reason why yearly pricing vs buying hardware is popular (one of many reasons). An expensive can be used that year where a capital expenses vest over years so only a portion can be applied that year.
wenbin 4y ago
For listennotes.com, we did a postgres 9.6 => 11 upgrade (in 2019), and 11 => 13 upgrade (in 2021). ~0 downtime for read ops, and ~45 seconds downtime for write ops. Our database is less than 1TB. One master (for writes + some reads) + multiple slaves (read-only). Here's what we did - 1, Launched a new read-only db with pg9.6, let's call it DB_A. 2, Stopped all offline tasks, and only maintained a minimal fleet of online servers (e.g., web, api...). 3, Changed all db hosts (no matter master or slave) in /etc/hosts on the minimal fleet of online servers (e.g., web, api...) to use old read-only db with pg9.6, let's call it DB_B. From this point on, all write ops should fail. 4, Ran pg_upgrade (with --link) on DB_A to upgrade to pg11, and promoted it to be a master db. 5, Changed /etc/hosts on the minimal fleet of online servers (e.g., web, api...) to use DB_A for all db hosts. By this point, DB_A is a master db. And write ops should be good now. 6, Changed /etc/hosts for all other servers and brought back all services. Step 4 is the most critical. If it fails or runs too long (e.g., more than 10 minutes), then we had to rollback by changing /etc/hosts on those online servers. We carefully rehearsed these steps for an entire week, and timed each step. By the time we did it on production, we knew how many seconds/minutes each step would take. And we tried to automate as many things as possible in bash scripts.
- aolle 4y ago
  wow
  - aolle 4y ago
- karmelapple 4y ago
  We did something similar recently jumping from 10 to 13. We took measurements, did some dry runs, and came up with strategies to ensure our read-only followers would work fine and we’d have a minimum downtime for writes. We missed one or two pieces of reconnecting things afterwards, and some of that seems to be limitations of Heroku Postgres that we couldn’t change. Hopefully those keep improving.
booleanbetrayal 4y ago
Low / zero downtime is totally achievable with pg_logical and really boils down to whether or not you want to try to adopt bi-directional writes (and conflict management / integrity issues), or if you're willing to just have a brief session termination event and swapover. To me, the latter has generally been preferable as conflict management systems tend to be more complicated in reality (based on business logic / state) than what pg_logcical provides. Interested if people here have had success with bi-directional writes though.
simonw 4y ago
I found the script they used for copying data really interesting: https://gist.github.com/peterwj/0614bf6b6fe339a3cbd42eb93dc5b37a https://gist.github.com/peterwj/0614bf6b6fe339a3cbd42eb93dc5... It's written in Python, spins up a queue.Queue object, populates it with ranges of rows that need to be copied (based on min < ID < max ranges), starts up a bunch of Python threads and then each of those threads uses os.system() to run this: psql "{source_url}" -c "COPY (SELECT * FROM ...) TO STDOUT" \ | psql "{dest_url}" -c "COPY {table_name} FROM STDIN" This feels really smart to me. The Python GIL won't be a factor here.
- teej 4y ago
  For ETL out of Postgres, it is very hard to beat psql. Something as simple as this will happily saturate all your available network, CPU, and disk write. Wrapping it in Python helps you batch it out cleanly. psql -c "..." | pigz -c > file.tsv.gz
- mrbabbage 4y ago
  Thanks Simon! I can indeed confirm that this script managed to saturate the database's hardware capacity (I recall CPU being the bottleneck, and I had to dial down the parallelism to leave some CPU for actual application queries).
- SahAssar 4y ago
  Sounds to me like this is the exact thing that the normal parallel command was made for, not sure python is needed here if the end result is shelling out to os.system anyway.
jeffrallen 4y ago
Nifty. But I can't help thinking this was harder than it needed to be in the cloud. Because frankly, 4 TB is not big: my home Synology backup server is 4 TB. Making a pair of standalone Linux servers to rehearse this locally, and with full control of which Postgres modules and other software to use, would have made things easier, it seems. Anyway, thanks for food for thought.
- aeorgnoieang 4y ago
  I wouldn't think using "standalone" versus whatever would make much of a difference. If you're using a hosted DB service, you're (probably) stuck in needing/wanting to rehearse using the hosted service (which is what the blog post describes). If they were running the DB on 'regular server' cloud instances, it seems just as good to me to rehearse with other cloud server instances versus "standalone" servers.
forinti 4y ago
I recently upgraded a 9.x database to 14 using pg_upgrade and it was just simple and fast. No issues whatsoever. I only wish my Oracle updates were so simple and bug-free.
valzam 4y ago
I am generally a fan of using as few moving parts as possible but if > 60-70% (2TB + a "few hundred GB") of your prod database are an append only audit log surely if would make sense to split that part into a separate DB server? Especially when you are using a hosted service. It sounds like both uptime and consistency requirements are very different between these two parts of the production data.
- ranyefet 4y ago
  My thoughts exactly. Does Postgres still make sense for append only tables or maybe elastic or other kind of database would be more suitable?
  - abraxas 4y ago
    Postgres makes a lot of sense with append only tables. You can easily partition them by time (usually) and thus have an easy way to break up the index trees as well as using a cheap indexing scheme like BRIN and being able to just drop old chunks as they become irrelevant.
dboreham 4y ago
Perhaps time to re-architect to avoid having a huge monolith database, particularly since the SaaS product is inherently multi-tenant?
burai 4y ago
I used to work on a company that had MongoDB as the main database. Leaving a lot of criticism aside, the replicaset model for Mongo made the upgrades much easier than the ones in other type of databses.
- stingraycharles 4y ago
  While that’s true, managed services on eg AWS provide hot replica’s as well, which you can use to upgrade the database and do a failover to the new version. We actually migrated from vanilla Postgres to Aurora that way with minimal risk / downtime, it was a really smooth process.
TruthWillHurt 4y ago
Wow this takes me back 20 years ago when we did this kind of partial-dump+sync migrations of mysql. Then the cloud and DBaaS was invented.
rkwasny 4y ago
"However, on our 4 TB production database, the initial dump and restore never completed: DMS encountered an error but failed to report the error to us." THERE IS NO CLOUD: It’s just someone else’s computer
bogomipz 4y ago
>"Last fall, we migrated this database from Postgres version 9.6 to version 13 with minimal downtime." I thought it was interesting that they upgraded 4 major version numbers in one go. I kept expecting to read something about version compatibility and configuration but was surprised there was none. Are major upgrades like this just less of an issue with Postgres in general?
- aeorgnoieang 4y ago
  I think so? PostgreSQL is very well written software AFACT. I've run into version incompatibilities before, but it was my fault – they were expertly documented in the release notes and I just hadn't read them (or sufficiently tested the upgrade before the live performance of it).
more_corn 4y ago
Set site read only. Snapshot master. Create new db from snapshot. Point site to new db?
- aeorgnoieang 4y ago
  Sure – and fail to meet their constraints
VincentEvans 4y ago
Why is SQL Server able to backup and restore databases 100s of gigabytes in size in single-digit minutes, while Postgres is at least 10x slower?
- aeorgnoieang 4y ago
  Is that even true? I don't think I've seen or read a direct apples-to-apples comparison of the two. There could be all kinds of reasons why that is tho (if it is in fact the case).
hankman86 4y ago
Having successfully built (and sold!) a technology startup myself, I would always, always opt for a managed database service. Yes, it’s more expensive on paper and you want to run the numbers and choose the right offering. But nothing beats the peace of mind of storing your customers’ data on a system that others (Google Cloud in our case) look after. Not to mention that you’re better off focussing on your core value proposition and spending your scarce resources there than to have a database administrator on your payroll.
- pojzon 4y ago
  This only works to some scale. Startup from my previous contract reached the point where “using bigger instance” would be losing them money. On the other hand, self built databases maintained by professionals were ALOT cheaper. We are talking here about million of dollars bills only for databases per month. Self hosted solution did cost around 300k per month. This included precompiled kernel patches, tweaks to postgres engine etc. Overall the investment of a year of work of dbadmins will probably return itself by ten times if not more.
- deleted 4y ago
  [deleted]
abbbi 4y ago
Another question: how do you backup a 4 TB Postgres Database? WAL file push? Filesystem snapshots?

384 points by mrbabbage 4y ago

Hey folks—I wrote the post! This was my biggest Postgres project to date, and it proved quite tricky since I didn't rehearse with a test database of the same size. I learned a bunch about Postgres, not least the incredibly powerful NOT VALID option for safely and quickly adding constraints. Happy to stick around and answer any questions you have.

aoms 4y ago
This instance must cost you a ton
- mrbabbage 4y ago
  More than I care to admit! As mentioned downthread, we're doing some work soon to remove the audit table from the database, which will cut storage usage by over 50%.
  - clessg 4y ago
    > we're doing some work soon to remove the audit table from the database Out of pure curiosity, what are you replacing it with (if anything)? Just a simple rotating log file?
    - mrbabbage 4y ago
      Exact strategy to be determined—we're looking at various data layers at the moment. I wish we could do something simple like a rotating log file, but we want to be able to query it in the app (for instance, to show recent logins).
      - clessg 4y ago
        Very interesting! Looking forward to the next blog post on that ;)
      - jamesrr39 4y ago
        Have you considered an OLAP database like Clickhouse or QuestDB? An OLAP database would be a much better fit for audit tables given the read and append-only writing requirements, would compress better, and you might be able to fit it directly without changing any app code with a Postgresql foreign data wrapper.
        lillecarl 4y ago
        I was thinking TimescaleDB
    - icheishvili 4y ago
      You can partition your audit/event table by time period and archive old events [1] or you can avoid the records hitting the database in the first place by generating the events elsewhere to begin with [2]. [1] https://github.com/icheishvili/audit-trigger https://github.com/icheishvili/audit-trigger [2] https://github.com/eulerto/wal2json https://github.com/eulerto/wal2json
  - Teletio 4y ago
    What's your strategy? Using compression? Second instance for old data? Creating in-between states? Just curious :)
  - tmikaeld 4y ago
    Seems to be around 1784$/monthly according to the pricing calculator
  - loopdoend 4y ago
    Let me save you a bunch of money: https://adamj.eu/tech/2021/04/13/reindexing-all-tables-after-upgrading-to-postgresql-13/ https://adamj.eu/tech/2021/04/13/reindexing-all-tables-after...
    - karmelapple 4y ago
      This technique saved us from seriously increasing the cost of our Heroku Postgres instance. Thank goodness it exists and works so well. Multiple 80+ GB indexes shrinks down to less than 10GB after just a couple of hours.
abrkn 4y ago
Great read! Thanks
tmikaeld 4y ago
How come you didn't consider a distributed database like CochroachDB, instead of Postgres?
- nrmitchi 4y ago
  I'm not OP, but they were upgrading from Postgres 9.6, which at least implies that this initial db was from ~2017. This is barely past the initial release of Cockroach. It would have been kind of crazy for the Retool team to use an experimental db with a lack of history when building up their product (that was not dependent on experimental new features)
  - tmikaeld 4y ago
    Baidu migrated from an old MySQL cluster to CockroachDB for their 40TBs of databases, now runs on 20 clusters. When does it stop being experimental to you? https://www.cockroachlabs.com/customers/ https://www.cockroachlabs.com/customers/
    - flyinknockin 4y ago
      I don't think they're calling Cockroach experimental right now - rather that it was experimental in 2017.
      - tmikaeld 4y ago
        Yeah, but they didn’t upgrade in 2017, they upgraded now, it’s in the first sentence. They had ample opportunity to migrate
wrs 4y ago
Wondering why you couldn’t use in-place upgrade (pg_upgrade [0]). Not supported in Azure? [0] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
- majewsky 4y ago
  I'm guessing this would still take more than one hour since it needs to rewrite all (or most) of the 4TB?
  - aidos 4y ago
    Not in my experience. You can use the —-link flag to get it to use hard links so it doesn’t need to move the data at all. Have been through this process myself a few times and it only took seconds on a 50-100GB db. I’m always a little surprised with how week it works.
    - wrs 4y ago
      Exactly, it just has to touch the metadata, unless the data page format has changed, which the developers try hard to avoid (successfully so far).
palijer 4y ago
I'm curious why a test run on a proper sized replica database wasn't in the testing plans. That is something I've been ensuring happens for a while now for similar projects.
tianzhou 4y ago
Good write-up. I am wondering what's the process of performing regular schema change (e.g. ALTER TABLE) on a 4TB instance looks like?
davidshepherd7 4y ago
I'm probably missing something, but it sounds like using Warp has a bunch of downsides vs "just" creating a read only replica using logical replication and then failing over. Did you choose Warp only because of Azure's limitations or were there other reasons?
somtum 4y ago
Great post! Did you migrate your old database to Azure Flexible Server, Hyperscale (Citus) or a standalone VM, as Postgres 13 does not seem to be available for Azure Postgres Single Server.

georgewfraser 4y ago

It is amazing how many large-scale applications run on a single or a few large RDBMS. It seems like a bad idea at first: surely a single point of failure must be bad for availability and scalability? But it turns out you can achieve excellent availability using simple replication and failover, and you can get huge database instances from the cloud providers. You can basically serve the entire world with a single supercomputer running Postgres and a small army of stateless app servers talking to it.

dijit 4y ago
Even when it’s not a cloud provider, in fact, especially when it’s not a cloud provider: you can achieve insane scale from single instances. Of course these systems have warm standbys, dedicated backup infrastructure and so it’s not really a “single machine”; but I’ve seen 80TiB Postgres instances back in 2011.
- cogman10 4y ago
  We are currently pushing close to 80tb mssql on prem instances. The biggest issue we have with these giant dbs is they require pretty massive amounts of RAM. That's currently our main bottle neck. But I agree. While our design is pretty bad in a few ways, the amount of data that we are able to serve from these big DBs is impressive. We have something like 6 dedicated servers for a company with something like 300 apps. A hand full of them hit dedicated dbs. Were I to redesign the system, I'd have more tiny dedicated dbs per app to avoid a lot of the noisy neighbor/scaling problems we've had. But at the same time, It's impressive how far this design has gotten us and appears to have a lot more legs on it.
  - Nathanba 4y ago
    Can I ask you how large tables can generally get before querying becomes slower? I just can't intuitively wrap my head around how tables can grow from 10gb to 100gb and why this wouldnt worsen query performance by x10. Surely you do table partitions or cycle data out into archive tables to keep up the query performance of the more recent table data, correct?

lijogdfljk 4y ago

How does MySQL fair in this type of setup? Do the DBs differ greatly?

thatwasunusual 4y ago
I worked for a company that did a similar "upgrade by replication", but with MySQL. It's quite a few years ago, so I don't remember the versions involved, but it was quite straight-forward once we had done _weeks_ of test runs on a dev environment. One invaluable thing, though: our application was from the beginning designed to do 100% of all the reads from a read-only slave _if the slave was up to sync_ (which it was 95% of the time). We could also identify testers/developers in the application itself, so we had them using the upgraded slave for two weeks before the actual upgrade. This made it possible for us to filter out problems in the application/DB-layer, which were few, which means that we probably did a minor version upgrade. But upgrading by replication is something I can recommend.
evanelias 4y ago
MySQL's built-in replication has always been logical replication, and it officially supports replicating from an older-version primary to newer-version replicas. So similar concept to what's described here, but much simpler upgrade process. Generally you just upgrade the replicas; then promote a replica to be the new primary; then upgrade the old primary and turn it into a replica. The actual "upgrade" step is quite fast, since it doesn't actually need to iterate over your tables' row data. At large scale, the painful part of major-version MySQL upgrades tends to be performance testing, but that's performed separately and prior to the actual upgrade process. Third-party tools (pt-upgrade, proxysql mirroring, etc) help a lot with this.

giovannibonetti 4y ago

By the way, Google Cloud recently launched in-place upgrade of Postgres instances. A few days ago we used it to upgrade our multi TB database in my company as well. https://cloud.google.com/blog/products/databases/cloud-sql-launches-support-for-in-place-upgrades https://cloud.google.com/blog/products/databases/cloud-sql-l...

davidkuennen 4y ago
Woah, this is great. Been waiting for this, since Cloud SQL has been very reliable in the past few years I've been using it, but upgrading was always a pain.
aeyes 4y ago
What is "in-place" about this? According to the docs you'll have ~10min downtime and you'll loose table statistics which is exactly what happens when you run pg_upgrade manually. The biggest problem with all the cloud providers is that you won't know exactly when this 10 minute downtime window will start I guess the only advantage here is that you don't have to do 9->10->11->12->13->14 like in the past and maybe that was one of the blockers Azure has. AWS allows to skip some major versions but 9->14 is not possible.
- sa46 4y ago
  In-place is a separate concept from zero downtime. Similarly, an inplace upgrade of your OS doesn't mean you can continue using the OS during the upgrade; it means you get to keep your data without restoring from an external backup. The benefit of an inplace upgrade for postgres is you don't have to spin up another server, restore from backup, and run pg_upgrade yourself.
- llama052 4y ago
  Azure doesn't really offer migrations paths, and their database migration tool has a ton of edge cases (not supported over 1tb etc) so while pg_upgrade is nice, Azure doesn't really have a path to use that. On top of that Azure postgres (limited to pg11) has essentially deprecated in place of their v2 Flexible tier with no official migration path.

Spivak 4y ago

It's crazy how many apps opt for an RDBMS for append-only data like audit events. It's so tantalizing at the beginning but turns into a nightmare time marches forward. audit events -> queue -> elastic -> blob storage is so easy to maintain and we save TBs from living in the DB.

mrbabbage 4y ago
We (Retool) are going to be doing this very soon and cut our database size by 50%+. And you're exactly right: it's so easy to get started by sticking audits (or other append-only data schema) in an RDBMS, but it quickly becomes a headache and bottleneck.
- abrkn 4y ago
  What will you use as a queue and storage?
  - mrbabbage 4y ago
    To be determined!
- baq 4y ago
  up until some size the headache from maintaining a separate datastore is bigger. everything should be in the RDBMS until proven otherwise for the sake of simplicity. it's actually amazing how much you can squeeze out of 'old school' databases. e.g. https://docs.microsoft.com/en-us/azure/azure-sql/database/ledger-append-only-ledger-tables https://docs.microsoft.com/en-us/azure/azure-sql/database/le...
luhn 4y ago
Mind expanding on what the "nightmare" is?
onphonenow 4y ago
Actually, I've seen more problems with folks mixing lots of different tools up then I have from folks doing an append only audit event in a RDBMS. When your audit trail is in DB, you can pretty easily surface audit events to your customers. Who changed what when is just another feature. Capturing audit events is also usually pretty smooth. The folks doing the blob storage route, you would not BELIEVE the complexity they have to spin up to expose very simple histories etc. This matters a LOT in some spaces (financial etc), less so in others. In my RDBMS model, who changed this field when from what to what is a basic select. You can even shard by recordID or similar if you want to reduce table scans, good select of indexes etc can be a huge help as well. In most cases users don't mind a bit of latency on these queries.

cube00 4y ago

> To resolve this, we ended up choosing to leave foreign key constraints unenforced on a few large tables. > We reasoned this was likely safe, as Retool’s product logic performs its own consistency checks, and also doesn’t delete from the referenced tables, meaning it was unlikely we’d be left with a dangling reference. I was holding my breath here and I'm glad these were eventually turned back on. Nobody should ever rely on their own product logic to ensure consistency of the database. The database has features (constraints, transactions, etc) for this purpose which are guaranteed to work correctly and atomically in all situations such as database initiated rollbacks that your application will never have control over.

jasfi 4y ago
True, but the DB constraints often aren't enough. I like to have a verify program to check that the DB is as expected.
- weird-eye-issue 4y ago
  You don't trust DB constraints?
  - rattray 4y ago
    They're limited in what they can express; your application often has invariants you want to enforce/maintain that can't be (performantly) expressed with DB constraints, and must be validated another way. As great as it can be to enforce rules within the database, a lot of them usually end up needing to be enforced at the application layer instead. Especially when performance at scale comes into play.
    - brightball 4y ago
      I think it’s a balance. Transactions + Constraints can enforce most things but there will certainly be things that can only be verified in the app. My goal is always to verify what I can in the database to minimize potential data cleanup. In my experience, app only verification always leads to future time investments to clean up the mess.

electroly 4y ago

In SQL Server you just... do the upgrade. You install the upgrade on your nodes starting with the passive nodes, and it will automatically failover from the old version to the new version once half the nodes have been upgraded. No downtime, but your redundancy drops when some nodes have been upgraded but the cluster hasn't fully been upgraded yet. You certainly don't have to dump and restore your database. Without giving private numbers, our database is much bigger than OP's 4TB; dump and restore would be wildly unacceptable. The idea that you don't get a seamless upgrade of the database itself with PostgreSQL is absurd to me. The part about "maximizing the amount of time this upgrade buys is" is only necessary because of how difficult PostgreSQL makes upgrades. We upgrade to every new version of SQL Server. It's not that big of a deal. With every PostgreSQL blog article I read, I become more and more of an SQL Server fanboy. At this point it's full-blown. So many "serious business" PostgreSQL ops posts are just nothingburgers in the SQL Server world.

Teletio 4y ago
Amy real facts you can show besides your sentiment on blog posts?
- electroly 4y ago
  What facts are you looking for? I just described the steps from this document: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance https://docs.microsoft.com/en-us/sql/sql-server/failover-clu... -- specifically, the "Perform a rolling upgrade or update" section. There's nothing else to my post other than contrasting the SQL Server upgrade process to the one described in the article, and musing about my growing appreciation for SQL Server; I apologize if it seemed like it was going to be deeper than that. EDIT: I realized you're looking for the other PostgreSQL blog posts. Here's an example of two recent HN posts about PostgreSQL issues that I pulled out of my comment history. Both of these blog posts exist because PostgreSQL doesn't have query hints. SQL Server has them; I've dealt with issues like these blog posts describe but they have trivial fixes in the SQL Server world. Nothing to write a blog post about. I don't have a link handy regarding PostgreSQL's txn id wraparound problem, but SQL Server doesn't have that problem, either. - https://news.ycombinator.com/item?id=30296490 https://news.ycombinator.com/item?id=30296490 - https://news.ycombinator.com/item?id=29981737 https://news.ycombinator.com/item?id=29981737

Teletio 4y ago

The first upgrade Strategie is not the normal or easy one on anything production btw. Very small companies might be able to do this on 'no load day' but from a pure business perspective, running your db twice is easier and way less risky. You could have done this even without downtime by letting your connection proxy handling the switch.

sscarduzio 4y ago

Hey @mrbabbage! Retool customer here! Great service :) Non distributed RDBMS is a great (yet underrated) choice. Thank you for the good writeup. I was thinking you could have a much less delicate migration experience next time (some years from now). So you can go for a quicker parallel "dump and restore" migration. For example: - Client side sharding in the application layer: you could shard your customers' data across N smaller DB instances (consistent hashing on customer ID) - Moving the append-only data somewhere else than postgres prior to the upgrade. You don't need RDBMS capabilities for that stuff anyway. Look at Elasticsearch, Clickhouse, or any DB oriented to time series data. WDYT?

mrbabbage 4y ago
The second bullet point is underway! Getting audit events out of the main database will be a major headache saver. The first bullet point is on our radar for the near term. We have a very natural shard key in our schema (the customer ID), with AFAIK no relationships across that shard key. And once we start horizontally sharding, we can do cool things like putting your data in a shard geographically close to you, which will greatly increase app performance for our non US customers. Exciting stuff coming down the pike!
- sscarduzio 4y ago
  Oh cool! Yes locality is very important! Great idea :)

sega_sai 4y ago

Using pg_upgrade I recently updated this 100Tb sized DB (from PG12 to PG13) in ~10 min of downtime. => select pg_size_pretty(pg_database_size ('**')); pg_size_pretty ---------------- 99 TB (1 row) (The re-analyze of tables took a day or so though)

riku_iki 4y ago
Curious what is your storage story? Where DB is actually stored? Some NAS?
- sega_sai 4y ago
  It's DAS. Mostly it's in one box of RAIDed 32x5.5Tb drives with a couple of tablespaces/WAL elsewhere. The DB is mostly read-only and not many concurrent users, so that's probably not the most typical case.
comboy 4y ago
Yeah, my petty 6Tb also went fine with pg_upgrade and practically no downtime. Upgrade slave, promote to master, upgrade master and then promote it back. It's a marvelous piece of technology. It's really just a handful of core people who did most of the work, crafting it so thoughtfully over the years and it has such a huge impact on the world. Doing huge part of it before postgresql was as popular as it is today, spending countless hours on making some great design choices and implementing them carefully. It seems unlikely any of them will read that but I'm so deeply grateful to these people. It allowed so many things to flourish on top thanks to it being open source and free.
quleap 4y ago

gamegod 4y ago

If you're running a 4 TB Postgres database, but you still have to worry about this level of maintenance, what's the value proposition of using a hosted service? There's usually insane markup on any hosted Postgres instance. If you want to pay multi-thousands dollars a month for a database server, it's WAY cheaper just to slap a server with a ton of drives in colocation.

orangepurple 4y ago
Might be an accounting scam^H^H^H^H trick to book the costs as an operating expense vs a capital expenditure. In general capital expenditures have to be planned carefully, held on the books for years, and show a return on investment. Perhaps an accountant can provide more detail.
- mywittyname 4y ago
  I feel like the explanation is a much more simple: it's easier to use Azure/AWS/GCP for everything. Yeah, this migration might have been somewhat painful, but it looks like it's a situation the company experiences every few years.
- ipaddr 4y ago
  This is a big reason why yearly pricing vs buying hardware is popular (one of many reasons). An expensive can be used that year where a capital expenses vest over years so only a portion can be applied that year.

wenbin 4y ago

For listennotes.com, we did a postgres 9.6 => 11 upgrade (in 2019), and 11 => 13 upgrade (in 2021). ~0 downtime for read ops, and ~45 seconds downtime for write ops. Our database is less than 1TB. One master (for writes + some reads) + multiple slaves (read-only). Here's what we did - 1, Launched a new read-only db with pg9.6, let's call it DB_A. 2, Stopped all offline tasks, and only maintained a minimal fleet of online servers (e.g., web, api...). 3, Changed all db hosts (no matter master or slave) in /etc/hosts on the minimal fleet of online servers (e.g., web, api...) to use old read-only db with pg9.6, let's call it DB_B. From this point on, all write ops should fail. 4, Ran pg_upgrade (with --link) on DB_A to upgrade to pg11, and promoted it to be a master db. 5, Changed /etc/hosts on the minimal fleet of online servers (e.g., web, api...) to use DB_A for all db hosts. By this point, DB_A is a master db. And write ops should be good now. 6, Changed /etc/hosts for all other servers and brought back all services. Step 4 is the most critical. If it fails or runs too long (e.g., more than 10 minutes), then we had to rollback by changing /etc/hosts on those online servers. We carefully rehearsed these steps for an entire week, and timed each step. By the time we did it on production, we knew how many seconds/minutes each step would take. And we tried to automate as many things as possible in bash scripts.

aolle 4y ago
wow
- aolle 4y ago
karmelapple 4y ago
We did something similar recently jumping from 10 to 13. We took measurements, did some dry runs, and came up with strategies to ensure our read-only followers would work fine and we’d have a minimum downtime for writes. We missed one or two pieces of reconnecting things afterwards, and some of that seems to be limitations of Heroku Postgres that we couldn’t change. Hopefully those keep improving.

booleanbetrayal 4y ago

Low / zero downtime is totally achievable with pg_logical and really boils down to whether or not you want to try to adopt bi-directional writes (and conflict management / integrity issues), or if you're willing to just have a brief session termination event and swapover. To me, the latter has generally been preferable as conflict management systems tend to be more complicated in reality (based on business logic / state) than what pg_logcical provides. Interested if people here have had success with bi-directional writes though.

simonw 4y ago

I found the script they used for copying data really interesting: https://gist.github.com/peterwj/0614bf6b6fe339a3cbd42eb93dc5b37a https://gist.github.com/peterwj/0614bf6b6fe339a3cbd42eb93dc5... It's written in Python, spins up a queue.Queue object, populates it with ranges of rows that need to be copied (based on min < ID < max ranges), starts up a bunch of Python threads and then each of those threads uses os.system() to run this: psql "{source_url}" -c "COPY (SELECT * FROM ...) TO STDOUT" \ | psql "{dest_url}" -c "COPY {table_name} FROM STDIN" This feels really smart to me. The Python GIL won't be a factor here.

teej 4y ago
For ETL out of Postgres, it is very hard to beat psql. Something as simple as this will happily saturate all your available network, CPU, and disk write. Wrapping it in Python helps you batch it out cleanly. psql -c "..." | pigz -c > file.tsv.gz
mrbabbage 4y ago
Thanks Simon! I can indeed confirm that this script managed to saturate the database's hardware capacity (I recall CPU being the bottleneck, and I had to dial down the parallelism to leave some CPU for actual application queries).
SahAssar 4y ago
Sounds to me like this is the exact thing that the normal parallel command was made for, not sure python is needed here if the end result is shelling out to os.system anyway.

jeffrallen 4y ago

Nifty. But I can't help thinking this was harder than it needed to be in the cloud. Because frankly, 4 TB is not big: my home Synology backup server is 4 TB. Making a pair of standalone Linux servers to rehearse this locally, and with full control of which Postgres modules and other software to use, would have made things easier, it seems. Anyway, thanks for food for thought.

aeorgnoieang 4y ago
I wouldn't think using "standalone" versus whatever would make much of a difference. If you're using a hosted DB service, you're (probably) stuck in needing/wanting to rehearse using the hosted service (which is what the blog post describes). If they were running the DB on 'regular server' cloud instances, it seems just as good to me to rehearse with other cloud server instances versus "standalone" servers.

forinti 4y ago

I recently upgraded a 9.x database to 14 using pg_upgrade and it was just simple and fast. No issues whatsoever. I only wish my Oracle updates were so simple and bug-free.

valzam 4y ago

I am generally a fan of using as few moving parts as possible but if > 60-70% (2TB + a "few hundred GB") of your prod database are an append only audit log surely if would make sense to split that part into a separate DB server? Especially when you are using a hosted service. It sounds like both uptime and consistency requirements are very different between these two parts of the production data.

ranyefet 4y ago
My thoughts exactly. Does Postgres still make sense for append only tables or maybe elastic or other kind of database would be more suitable?
- abraxas 4y ago
  Postgres makes a lot of sense with append only tables. You can easily partition them by time (usually) and thus have an easy way to break up the index trees as well as using a cheap indexing scheme like BRIN and being able to just drop old chunks as they become irrelevant.

dboreham 4y ago

Perhaps time to re-architect to avoid having a huge monolith database, particularly since the SaaS product is inherently multi-tenant?

burai 4y ago

I used to work on a company that had MongoDB as the main database. Leaving a lot of criticism aside, the replicaset model for Mongo made the upgrades much easier than the ones in other type of databses.

stingraycharles 4y ago
While that’s true, managed services on eg AWS provide hot replica’s as well, which you can use to upgrade the database and do a failover to the new version. We actually migrated from vanilla Postgres to Aurora that way with minimal risk / downtime, it was a really smooth process.

TruthWillHurt 4y ago

Wow this takes me back 20 years ago when we did this kind of partial-dump+sync migrations of mysql. Then the cloud and DBaaS was invented.

rkwasny 4y ago

"However, on our 4 TB production database, the initial dump and restore never completed: DMS encountered an error but failed to report the error to us." THERE IS NO CLOUD: It’s just someone else’s computer

bogomipz 4y ago

>"Last fall, we migrated this database from Postgres version 9.6 to version 13 with minimal downtime." I thought it was interesting that they upgraded 4 major version numbers in one go. I kept expecting to read something about version compatibility and configuration but was surprised there was none. Are major upgrades like this just less of an issue with Postgres in general?

aeorgnoieang 4y ago
I think so? PostgreSQL is very well written software AFACT. I've run into version incompatibilities before, but it was my fault – they were expertly documented in the release notes and I just hadn't read them (or sufficiently tested the upgrade before the live performance of it).

more_corn 4y ago

Set site read only. Snapshot master. Create new db from snapshot. Point site to new db?

aeorgnoieang 4y ago
Sure – and fail to meet their constraints

VincentEvans 4y ago

Why is SQL Server able to backup and restore databases 100s of gigabytes in size in single-digit minutes, while Postgres is at least 10x slower?

aeorgnoieang 4y ago
Is that even true? I don't think I've seen or read a direct apples-to-apples comparison of the two. There could be all kinds of reasons why that is tho (if it is in fact the case).

hankman86 4y ago

Having successfully built (and sold!) a technology startup myself, I would always, always opt for a managed database service. Yes, it’s more expensive on paper and you want to run the numbers and choose the right offering. But nothing beats the peace of mind of storing your customers’ data on a system that others (Google Cloud in our case) look after. Not to mention that you’re better off focussing on your core value proposition and spending your scarce resources there than to have a database administrator on your payroll.

pojzon 4y ago
This only works to some scale. Startup from my previous contract reached the point where “using bigger instance” would be losing them money. On the other hand, self built databases maintained by professionals were ALOT cheaper. We are talking here about million of dollars bills only for databases per month. Self hosted solution did cost around 300k per month. This included precompiled kernel patches, tweaks to postgres engine etc. Overall the investment of a year of work of dbadmins will probably return itself by ten times if not more.
deleted 4y ago
[deleted]

abbbi 4y ago

Another question: how do you backup a 4 TB Postgres Database? WAL file push? Filesystem snapshots?