[HN Gopher] Our great database migration
___________________________________________________________________
Our great database migration
Author : TheAnkurTyagi
Score : 32 points
Date : 2024-06-28 13:21 UTC (2 days ago)
(HTM) web link (shepherdinsurance.com)
(TXT) w3m dump (shepherdinsurance.com)
| pm2222 wrote:
| Does the sqlite java lib bundle support for many platforms which
| jacks up the app size?
| lmz wrote:
| Yes [1], but it sounds like they committed the sqlite file into
| Github as well?
|
| [1]: https://github.com/xerial/sqlite-jdbc?tab=readme-ov-
| file#how...
| skeeter2020 wrote:
| I don't see any mention of the data size or volume of
| transactions? Also, your API response times were worse after you
| finished and optimized, and that's a success? or you're comparing
| historical SQLite vs new PostgreSQL? I kinda see this more as a
| rewrite than a database migration (which I'm going through now
| from SQL Server to PostgreSQL)
| fbdab103 wrote:
| The success was all of the resumes that came out with a few
| more buzzwords.
| dcmatt wrote:
| "Overall, this migration proved to be a massive success" but
| their metrics shows this migration resulted in, on average,
| slower response times. Wouldn't this suggest the migration was
| not successful. Postgres can be insanely fast, and given the
| volume of data this post suggests, it baffles me that the
| performance is so bad.
| cmnzs wrote:
| What a bizarre article... performance ended up being worse, how
| can that be considered a resounding success? Doesn't seem like
| it's a slam dunk case for using neon
| sgt101 wrote:
| You. Were. Running. An. Insurance. Company. On. SQLite?
|
| What?
|
| What possessed them?
| simonw wrote:
| Sounds like a perfectly cromulent solution to me. It's an
| upgrade from Excel!
| Exoristos wrote:
| True in many ways, yet Excel is considerably more robust.
| simonw wrote:
| Can you back that up? SQLite has an extremely strong
| reputation for robustness.
| samtho wrote:
| The database in question was 80mb, which is very small. It's
| also fast and backups are as simple as copying the file.
|
| Not advocating for this particular use case, but we've all seen
| more egregious abuses of SQLite.
| simonw wrote:
| Lots of comments about the drop in performance. No matter how
| well you tune network PostgreSQL it's going to have trouble
| coming close to the performance you can get from a read-only 80MB
| SQLite file.
|
| They didn't make this change for performance reasons.
| mgh95 wrote:
| From the article:
|
| > 4. Performance
|
| > How can we ensure there are no performance regressions, and
| even performance improvements? When we launched Shepherd we
| promised to respond to every submission with an indication in
| 24 hours or less, which directly relates to the performance of
| our platform.
|
| I actually run a (small, bootstrapped) startup in a very
| related space (we operate as an agency and MSB, and have direct
| tie-ins to a much broader range of insurance and financial
| products) and this article just feels like going the wrong
| direction. We host on prem (colo) because we knew the DB would
| be a latency bottleneck and clone operations need to be fast
| for compliance purposes.
|
| This just feels like the wrong solution for the problem.
| threecheese wrote:
| I had a similar reaction; one of their constraints was
| "Reduce server memory", which tells me their Serverless
| vendor charges more, which is a constraint they don't need to
| have. Using a colo server, memory cost is rarely an important
| factor.
| mgh95 wrote:
| Pretty much. You can run your business basically saying
| things like "we will take PITR not less than every seven
| (7) days" and then just run it on off hours with
| `pg_dumpall` on each database and the memory/storage costs
| are basically nil.
|
| This entire blog post reflects a lack of awareness of how
| much these problems can flat out be ignored with different
| approaches.
| chrisandchris wrote:
| > Ensure database is in same region as application server
|
| People tend to forget that using The Cloud (tm) still means that
| there's copper between a database server and an application
| server and physics still exist.
| willsmith72 wrote:
| I would narrow that down to people preaching about The Edge.
|
| Storage and compute colocation almost always wins out by far.
|
| In saying that, I love CloudFlare workers for certain stuff
| pocketarc wrote:
| > Furthermore, bundling an 80MB+ SQLite file to our codebase
| slowed down the entire Github repository and hindered us from
| considering more robust hosting platforms.
|
| It's... an 80MB database. It couldn't be smaller. There are local
| apps that have DBs bigger than that. There is no scale issue
| here.
|
| And... it's committed to GitHub instead of just living somewhere.
| And they switched to Neon.
|
| To me, this screams "we don't know backend and we refuse to
| learn".
|
| To their credit, I will say this: They clearly were in a
| situation like: "we have no backend, we have nowhere to store a
| DB, but we need to store this data, what do we do?" and someone
| came up with "store it in git and that way it's deployed and
| available to the app". That's... clever. Even if terrible.
| BHSPitMonkey wrote:
| > It's... an 80MB database. It couldn't be smaller. There are
| local apps that have DBs bigger than that. There is no scale
| issue here.
|
| It depends. If that 80MB binary file in git is updated/replaced
| often, you likely have a problem (every 100
| changes/replacements might grow the repo by as much as 8GB).
| __float wrote:
| They said it was stored with Git LFS, so it's just a pointer
| in the repo, not all 80 MB.
| candiddevmike wrote:
| > That's... clever
|
| It's tech debt
| shrubble wrote:
| If it is a read-only database, I don't fully understand where all
| the latency is coming from. Is it complex SQL queries?
| justinclift wrote:
| It really _sounds_ like their chosen solution (Neon?) is doing
| something awfully inefficient when processing the transactions.
| :(
| kwillets wrote:
| I'm guessing from spreadsheets riddled with per-cell SQL
| fetches.
| ed_elliott_asc wrote:
| This post is 100% marketing "oh we had so few customers SQLite
| was great but now we need Postgres" ignore it
| morgante wrote:
| > bundling an 80MB+ SQLite file to our codebase slowed down the
| entire Github repository and hindered us from considering more
| robust hosting platforms
|
| This seems like a decent reason to stop committing the database
| to GitHub, but not a reason to move off SQLite.
|
| If you have a small, read-only workload, SQLite is very hard to
| beat. You can embed it ~everywhere without any network latency.
|
| I'm not sure why they wouldn't just switch to uploading it to S3.
| Heck, if you really want a vendor involved that's basically what
| https://turso.tech/ has productized.
| banish-m4 wrote:
| PSA: If you're running a business and some databases store vital
| customer or financial data, consider EnterpriseDB (EDB). It funds
| Postgres and can be used almost like Oracle DBMS. And definitely
| send encrypted differential backups to Tarsnap for really
| important data.
| kwillets wrote:
| The latency before/after histograms unfortunately use different
| scales, but it appears that eg the under-200ms bucket is only a
| few percentage points smaller after the change, maybe 38 before
| and 33 after.
|
| What I'm curious about is whether Neon can run pg locally on the
| app server. The company's SaaS model doesn't seem to support
| that, but it looks technically doable, particularly with a read-
| only workload.
| willsmith72 wrote:
| > 79.15% of our pricing operations averaged 1 second or less
| response time
|
| These numbers are thrown out there like they're supposed to be
| impressive. They must be doing some really complex stuff to
| justify that. For a web server to have a p79 of 1 second is
| generally terrible.
|
| > 79.01% to average 2 seconds or less
|
| And after the migration it gets FAR worse.
|
| I get that it's a finance product, but from what they wrote it
| doesn't seem like a large dataset. How is this the best
| performance they're getting?
|
| Also a migration where your p79 (p-anything) doubled is a
| gigantic failure in my books.
|
| I guess latency really mustn't be critical to their product
___________________________________________________________________
(page generated 2024-06-30 23:01 UTC)