7 ms·
Our great database migration
- skeeter2020 2y agoI 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 2y agoThe success was all of the resumes that came out with a few more buzzwords.
- dcmatt 2y ago"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.
- hobs 2y agoFor 80MB of data sqlite should also be insanely fast, 1s doesn't seem like a DB problem.
- agent001 2y ago80MB is actually large enough to impact git performance according to Github. Imagine every single developer on the team having repo performance impacts bc of the db and how that compounds over time https://docs.github.com/en/repositories/working-with-files/managing-large-files/about-large-files-on-github https://docs.github.com/en/repositories/working-with-files/m...
- zie 2y agoAnd yet you didn't read what you are linking to. Also, the OP was already doing LFS, which fixes the issue Github is talking about. So that's not the issue, I don't know what the OP's problems were with an 80MB file, but that is not the issue.
- TheAnkurTyagi 2y agofair point about the slower response times But Neon's not just any Postgres. They've got that whole serverless angle, which can be a game-changer for scaling and costs. Maybe the team's still figuring out how to optimize for that setup? Plus, with Neon, they probably ditched that whole SQLite-in-git headache.
- dcmatt 2y agoGame changer for scaling and costs? It’s an 80 MB database that needs to get duplicated by n developers and a test and production instance. They could get away with a $200 beelink under a desk. I see no reason to prematurely scale. I would focus on making things fast and creating a “duplicate database” button so devs can work without interrupting prod
- cmnzs 2y agoWhat 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
- agent001 2y agowhat was bizarre in this article. I found it's a simple customer success story about Neon but I see the improvement came from caching and connection pooling but Neon looks promising because of its database branching feature, which made testing and development faster looks like Neon(https://github.com/neondatabase/neon https://github.com/neondatabase/neon) seems to have helped them with testing and development. Being able to iterate and validate changes is a great benefit.
- meiraleal 2y agoHey agent001, don't be in such a hurry to prove that this article is about marketing neon database. Your green nickname doesn't help much.
- TheAnkurTyagi 2y agoeasy to slam others but I don't see what's bizarre about the article. It highlights a successful customer story where Neon (https://neon.tech https://neon.tech) played a significant role in improving their db management with its db branching feature. Yes, caching and pooling helped, but Neon made their testing and development much faster and efficient and it's not easy to write such detailed articles. why not any constructive feedback which can help them?
- sgt101 2y agoYou. Were. Running. An. Insurance. Company. On. SQLite? What? What possessed them?
- simonw 2y agoSounds like a perfectly cromulent solution to me. It's an upgrade from Excel!
- Exoristos 2y agoTrue in many ways, yet Excel is considerably more robust.
- simonw 2y agoCan you back that up? SQLite has an extremely strong reputation for robustness.
- lucb1e 2y agoCheck the new workflow diagram. If I read it correctly, they still edit the database as a spreadsheet and convert that into sql. The diagram is near this text: > we reduced the process down to a 1- click solution. We built a dashboard in our underwriting platform that actuaries can visit and request factor changes. With a single click, an asynchronous job kicks off, automating the steps of Google Sheet extraction > parsing > codegen > SQL script generation > PR creation. The only engineering involvement now required is a PR review.
- roblh 2y agoThis is completely unhinged, right? Am I crazy? All that work just to make absolutely 100% sure their staff never have to try to learn anything new or modify their processes in any way shape or form.
- sgt101 2y agoutterly utterly unhinged. It's basically a design that someone both very naive and suffering late stage Rabies would come up with.
- simonw 2y agoLots 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 2y agoFrom 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 2y agoI 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 2y agoPretty 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.
- TheAnkurTyagi 2y agoIMO, think of it like switching from a bicycle to a car. The bicycle might be faster in certain conditions, but the car offers more features and flexibility for different terrains. Similarly, Neon’s database branching feature is like adding all-terrain capability.
- chrisandchris 2y ago> 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 2y agoI 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 2y ago> 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 2y ago> 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 2y agoThey said it was stored with Git LFS, so it's just a pointer in the repo, not all 80 MB.
- candiddevmike 2y ago> That's... clever It's tech debt
- mbreese 2y agoIt’s tech debt, but it’s cheap debt. 80Mb isn’t much to have to worry about. There are many ways to use a DB of that size that will be “good enough”. So long as the queries were returning fast enough and the dev overhead wasn’t too much (LFS was a good choice), I’m not sure I’d have worried much. There is tech debt that will eat away at you, and then there is this… something that you’ll want to replace someday, but it can wait until you are ready to tackle it properly. And you may not even know what data access patterns you need right away. For a new company with a pretty new way of working with insurance, this seems like a good trade off to me. Plus, it seems clear that they weren’t confident in their backend engineering yet, so this gave them time to figure things out.
- shrubble 2y agoIf it is a read-only database, I don't fully understand where all the latency is coming from. Is it complex SQL queries?
- justinclift 2y agoIt really sounds like their chosen solution (Neon?) is doing something awfully inefficient when processing the transactions. :(
- kwillets 2y agoI'm guessing from spreadsheets riddled with per-cell SQL fetches.
- ed_elliott_asc 2y agoThis post is 100% marketing “oh we had so few customers SQLite was great but now we need Postgres” ignore it
- agent001 2y agodisagree- It's 100% a happy customer success story and Neon seems to be fit for them.
- morgante 2y ago> 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/ https://turso.tech/ has productized.
- banish-m4 2y agoPSA: 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.
- mattashii 2y ago> EDB ... or any of the other companies that provide support and actively contribute to the PostgreSQL project, such as Cybertec, Data Egret, Crunchy Data, AWS, ... I wouldn't choose any PostgreSQL supplier for their similarity to Oracle DBMS (why would you want to buy into that ecosystem?); as long as it behaves just as vanilla PostgreSQL it is good enough for me.
- banish-m4 2y agoThe marketing of EDB is as a drop-in replacement for Oracle.
- kwillets 2y agoThe 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.
- nikita 2y ago(Neon CEO) We could consider that. Right now we are exploring to actually run the app in the same or next to the micro-VM that runs Postgres. https://neon.tech/docs/introduction/architecture-overview https://neon.tech/docs/introduction/architecture-overview
- tarasglek 2y agoI think turso in memory replicas might work here. They have some code to capture pg cdc to do that. Likewise using something like pglite to run a pure pg in memory replica would be very cool
- willsmith72 2y ago> 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
- pantsforbirds 2y agoI wonder if DuckDB with parquet storage on S3 (or equivalent) would have been a nice drop-in replacement. Plus DuckDB probably would have done quite well in the ETL pipeline.
- apithowaway 2y agoWhere is the cto or senior technical leader in this? The team seems to be trying hard and keeping the lights on, but honestly there are several red flags here. I’m especially skeptical about the painful and complex manual process that is now 1-click. I want to hope they succeed, but this sounds awfully naive.
- cpursley 2y agoIf starting off with Elixir and Postgres from the get-go, all this could have been avoided - including the async pains. Said another way: don’t write you backend in JS and just use Postgres.
- jhgg 2y agoHow exactly would writing this in elixir have avoided async pains? Can you elaborate.
- hobobaggins 2y agoIf most queries take ~ 1s on a relatively small 80MB dataset, then it sounds to me like they really needed to run EXPLAIN on their most complex queries and then tune their indexes to match. They could have probably stayed with SQLite, in fact, because most likely it's a serious indexing problem, and then found a better way to distribute the 80MB file rather than committing it to Github. (Although there are worse ideas, esp with LFS)
- deleted 2y ago[deleted]
- zie 2y agoIt's more complicated and slower but it's still a "success". LOL.
- deleted 2y ago[deleted]
- hipadev23 2y agoShepherd raised $13.5M earlier this year. Imagine being an investor in this company and seeing this post. They seriously wrote a lengthy post publicizing their struggles with an 80MB database and running some queries. The entire technical team at this company needs to be jettisoned. These are the sort of technical struggles a high school student learning programming encounters. Not a well-funded series A startup. This is absolutely bonkers.
- zitterbewegung 2y agoNot to be negative but it seems like many tech posts like this are thinly veiled hiring / recruitment blog posts .
- pm2222 2y agoDoes the sqlite java lib bundle support for many platforms which jacks up the app size?
- lmz 2y agoYes [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-does-sqlitejdbc-work https://github.com/xerial/sqlite-jdbc?tab=readme-ov-file#how...