6 ms·

How we upgraded our 4TB Postgres database

384 points by mrbabbage 4y ago

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.
- luhn 4y ago
  Mind expanding on what the "nightmare" is?
- 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...
- 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.
  - jeffbee 4y ago
    My only experience in the financial sector indicates the opposite. The firm held its trading history for tens of thousands of accounts going back 60 years in a SQL Server. Anyone who wanted a question answered had to submit it for overnight analysis and get the answer the next day. But in an optimal non-RDMS representation, said trading history could be condensed to a single 200MB flat file that could be queried interactively, in microseconds. Dumping the RDBMS for most use cases pretty much revolutionized the daily experience for the people at that firm.
    - icedchai 4y ago
      Were they running this on a 386? Why didn't they optimize their database?
      - jeffbee 4y ago
        It was "optimized" for the aesthetic concerns of RDBMS purists and for ease of implementation of certain other systems, in other words it was optimal for the developers and severely sub-optimal for the users, which is a problem endemic to RDBMS fandom.
        4y ago
- nrmitchi 4y ago
  One of the biggest thing that keeping audit records in your DB gives you is transactionality around your audit logs. Sending audit events to an external system (quite often) loses this, and the resources to address this before you have to are way larger than a slightly larger AWS/GCP/Azure/<insert-computer-provider-here> bill.
  - magicalhippo 4y ago
    We're implementing something similar to what OP describes, but we'll keep the "queue" in the DB in order to insert the application audit event in the same transaction as the data change. A background process then uploads to secondary storage. We won't have billions of rows though, so once uploaded to secondary storage we'll just clear the blob field and set a "processed" flag. This way we can find all the relevant keys for a given order, invoice etc quickly based on a partial key search in the database, and transparently fetch from either db directly or secondary storage as needed.
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?
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.
- 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?
- 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).
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.
- strictfp 4y ago
  I agree in principle. But one major headache for us has been upgrading the database software without downtime. Is there any solution that does this without major headaches? I would love some out-of-the-box solution.
  - karmakaze 4y ago
    The way I've done it with MySQL since 5.7 is to use multiple writers of which only one is actively used by clients. Take one out, upgrade it, put it back into replication but not serving requests until caught up. Switch the clients to writing to the upgraded one then upgrade the others.
  - msh 4y ago
    Migrate the data to a new host having the new version.
  - brentjanderson 4y ago
    Depends on the database - I know that CockroachDB supports rolling upgrades with zero downtime, as it is built with a multi-primary architecture. For PostgresQL or MySQL/MariaDB, your options are more limited. Here are two that come to mind, there may be more: # The "Dual Writer" approach 1. Spin up a new database cluster on the new version. 2. Get all your data into it (including dual writes to both the old and new version). 3. Once you're confident that the new version is 100% up to date, switch to using it as your primary database. 4. Shut down the old cluster. # The eventually consistent approach 1. Put a queue in front of each service for writes, where each service of your system has its own database. 2. When you need to upgrade the database, stop consuming from the queue, upgrade in place (bringing the DB down temporarily) and resume consumption once things are back online. 3. No service can directly read from another service's database. Eventually consistent caches/projections service reads during normal service operation and during the upgrade. A system like this is more flexible, but suffers from stale reads or temporary service degradation.
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.
  - 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.
  - 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.
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.
- phphphphp 4y ago
  Does that pattern you describe require any considerations when writing code? I’m thinking of applications I’ve worked on where events are triggered by change, and so a database rolling back independent of my application would be a nightmare. I treat the database as a place to store data, not an authority: the application is the authority. Do you approach it differently? Thanks!
  - brightball 4y ago
    The database is the only place that can be the authority because the application can have race conditions. It’s the only way to guarantee data integrity.
    - grogers 4y ago
      There's no way to specify every single application specific constraint directly in the database. Race conditions are not present when using locking reads (select ... for update, or DB specific shared locking selects) or serializable isolation level, which are the typical way of enforcing application level constraints.
  - kijin 4y ago
    ON DELETE CASCADE can be dangerous when used with applications that expect to be notified of deletions, like in your case. Ideally, everything that needs to change when a row is deleted would be changed automatically and atomically using database-side constraints and triggers. In practice, applications often need to sync state with external services that the database knows nothing about, so I understand your concerns. ON DELETE RESTRICT, on the other hand, will result in errors just like any other query error that you can handle in your application. Nothing happened, so there's nothing to be notified of.
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.
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

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.

luhn 4y ago
Mind expanding on what the "nightmare" is?
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...
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.
- jeffbee 4y ago
  My only experience in the financial sector indicates the opposite. The firm held its trading history for tens of thousands of accounts going back 60 years in a SQL Server. Anyone who wanted a question answered had to submit it for overnight analysis and get the answer the next day. But in an optimal non-RDMS representation, said trading history could be condensed to a single 200MB flat file that could be queried interactively, in microseconds. Dumping the RDBMS for most use cases pretty much revolutionized the daily experience for the people at that firm.
  - icedchai 4y ago
    Were they running this on a 386? Why didn't they optimize their database?
    - jeffbee 4y ago
      It was "optimized" for the aesthetic concerns of RDBMS purists and for ease of implementation of certain other systems, in other words it was optimal for the developers and severely sub-optimal for the users, which is a problem endemic to RDBMS fandom.
      - 4y ago
nrmitchi 4y ago
One of the biggest thing that keeping audit records in your DB gives you is transactionality around your audit logs. Sending audit events to an external system (quite often) loses this, and the resources to address this before you have to are way larger than a slightly larger AWS/GCP/Azure/<insert-computer-provider-here> bill.
- magicalhippo 4y ago
  We're implementing something similar to what OP describes, but we'll keep the "queue" in the DB in order to insert the application audit event in the same transaction as the data change. A background process then uploads to secondary storage. We won't have billions of rows though, so once uploaded to secondary storage we'll just clear the blob field and set a "processed" flag. This way we can find all the relevant keys for a given order, invoice etc quickly based on a partial key search in the database, and transparently fetch from either db directly or secondary storage as needed.

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?

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.

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?
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).

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.

strictfp 4y ago
I agree in principle. But one major headache for us has been upgrading the database software without downtime. Is there any solution that does this without major headaches? I would love some out-of-the-box solution.
- karmakaze 4y ago
  The way I've done it with MySQL since 5.7 is to use multiple writers of which only one is actively used by clients. Take one out, upgrade it, put it back into replication but not serving requests until caught up. Switch the clients to writing to the upgraded one then upgrade the others.
- msh 4y ago
  Migrate the data to a new host having the new version.
- brentjanderson 4y ago
  Depends on the database - I know that CockroachDB supports rolling upgrades with zero downtime, as it is built with a multi-primary architecture. For PostgresQL or MySQL/MariaDB, your options are more limited. Here are two that come to mind, there may be more: # The "Dual Writer" approach 1. Spin up a new database cluster on the new version. 2. Get all your data into it (including dual writes to both the old and new version). 3. Once you're confident that the new version is 100% up to date, switch to using it as your primary database. 4. Shut down the old cluster. # The eventually consistent approach 1. Put a queue in front of each service for writes, where each service of your system has its own database. 2. When you need to upgrade the database, stop consuming from the queue, upgrade in place (bringing the DB down temporarily) and resume consumption once things are back online. 3. No service can directly read from another service's database. Eventually consistent caches/projections service reads during normal service operation and during the upgrade. A system like this is more flexible, but suffers from stale reads or temporary service degradation.

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.
- 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.
- 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.

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.

phphphphp 4y ago
Does that pattern you describe require any considerations when writing code? I’m thinking of applications I’ve worked on where events are triggered by change, and so a database rolling back independent of my application would be a nightmare. I treat the database as a place to store data, not an authority: the application is the authority. Do you approach it differently? Thanks!
- brightball 4y ago
  The database is the only place that can be the authority because the application can have race conditions. It’s the only way to guarantee data integrity.
  - grogers 4y ago
    There's no way to specify every single application specific constraint directly in the database. Race conditions are not present when using locking reads (select ... for update, or DB specific shared locking selects) or serializable isolation level, which are the typical way of enforcing application level constraints.
- kijin 4y ago
  ON DELETE CASCADE can be dangerous when used with applications that expect to be notified of deletions, like in your case. Ideally, everything that needs to change when a row is deleted would be changed automatically and atomically using database-side constraints and triggers. In practice, applications often need to sync state with external services that the database knows nothing about, so I understand your concerns. ON DELETE RESTRICT, on the other hand, will result in errors just like any other query error that you can handle in your application. Nothing happened, so there's nothing to be notified of.

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.

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?