> 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.
"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.
80MB 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
fair 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.
Game 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
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
easy 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) 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?
what 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) seems to have helped them with testing and development. Being able to iterate and validate changes is a great benefit.
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.
> 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.
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.
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.
IMO, 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.
I very much disagree with this. It is possible (and we do this in production) to branch a pg database without requiring these tools using `pg_dump` or `pg_dumpall` which uses the ability to branch a database. These can be integrated with either standard ORM tools or SQL scripts to achieve the same result with far better performance and lower cost. This very much appears to be a knowledge issue.
If 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)
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)
> 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
> 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.
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.
If 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.
Where 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.
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.
... 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.
Shepherd 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.
I 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.
> 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.
> 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).
Neon actually solved a bunch of problems here. Sure, 80MB might not seem huge, but in a git repo? Also, 80MB is actually large enough to impact git performance according to Github(https://docs.github.com/en/repositories/working-with-files/m...). That can be a pain, especially if you're updating it often. Neon let them ditch the whole 'DB-in-git' approach. No more slowing down the repo or worrying about it ballooning with every update. Plus, it probably made deployment and scaling way smoother. Sometimes the 'clever' solution isn't the best long-term, you know? Kudos to them for recognizing that and making the switch.
It’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.
Check 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.
This 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.
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.