98% Cloud Cost Saved By Writing Our Own Database

Recorded live on twitch, GET IN
Article
hivekit.io/blo...
By: / hivekit_io
My Stream
/ theprimeagen
Best Way To Support Me
Become a backend engineer. Its my favorite site
boot.dev/?prom...
This is also the best way to support me is to support yourself becoming a better backend engineer.
MY MAIN YT CHANNEL: Has well edited engineering videos
/ theprimeagen
Discord
/ discord
Have something for me to read or react to?: / theprimeagenreact
Kinesis Advantage 360: bit.ly/Prime-K...
Hey I am sponsored by Turso, an edge database. I think they are pretty neet. Give them a try for free and if you want you can get a decent amount off (the free tier is the best (better than planetscale or any other))
turso.tech/dee...

[@Fik0n]

The best thing about saving 98% cloud cost is that developer hours are free and that this will be super easy to maintain when the original devs quit.

[@JeremyAndersonBoise]

😂😂😂🎉😂😂😂

[@7th_CAV_Trooper]

Read my mind

[@trueriver1950]

WARNING Irony detected

[@lupf5689]

Why would that be a problem? Was anything shown here that hard to understand? You should know how to do file and network io. What's left is a bit of domain knowledge and a one-time effort to encode and decode a rather simple data structure. Sometimes I really don't get that "let's better not do it ourselves" mentality.

[@andrasschmidthu]

Skill issue.

[@TomNook.]

I saved 99% of my cloud costs by connecting my frontend to an excel spreadsheet. Such a great idea!

[@SimonBuchanNz]

What's the 1%

[@marcogenovesi8570]

@@SimonBuchanNz the frontend

[@StuermischeTage]

You are a true genius. Are you available to optimize our IT department?

[@opposite342]

tom is a genius jdsl deez

[@Melpheos1er]

99.9% for me because it's connected to libreoffice calc. I'm even saving on Microsoft Office costs !

[@bfors8498]

I call this impressive-sounding-blogpost-driven-development

[@Fik0n]

Medium-driven-development

[@Peter-UK-nl6cv]

RDD - resume driven development

[@neo-vj4zq]

Did these numbers before getting out of the garage office stage

[@EagerEggplant]

How about bdedd, read bidet: big-dick-energy-driven-development

[@zimpoooooo]

I call it fun.

[@michaelcohen7676]

Chat misunderstanding RTK. RTK is literally just correcting GPS data using a known point in realtime. It is not better than GPS, it just enhances the way the measurements are interpreted

[@dobacetr]

Let's expand this a little for the curious. GNSS works by measuring the timing of a signal between the receiver and the (constellation of) satellites. Since we know the speed of these signals, we can calculate the distance from the timing. In 3D Space, we need 3 (linearly independent) measurements to pin-point a location. In Space-Time (4D) we need 4 (Time is unknown because all clocks are imprecise, and since we are talking speed of light, every nano-second matters, by about 30cm :) ). This is how we know the position from the GNSS. However, there are factors which need to be considered. The signal traverses from space to the ground, trough atmosphere. There, the signal is corrupted by various effects. Some of these are tracked and accounted for (may look-up Tropospheric correction and Ionospheric correction). After these you may get your position accuracy down to few meters. However, there are still some errors that could be predicted left. But, you would need a closeby station to measure those effects. RTK is when you use a station with known position to measure these residuals. Then, you could use the same correction for any nearby device to improve their accuracy. Depending on conditions you may get centimeters-decimeters accuracy. However, generally speaking, in a city I would not expect more than a meter accuracy. I would probably not trust it to be that precise either. RTK relies on having similar conditions and there may be interference that isn't similar. Or it may be just my paranoia. Let me know if I have missed anything, or made a mistake.

[@zerker2000]

And GPS is not better than dead reckoning, it just corrects data location drift :^)

[@gregoconnor8308]

It is just like data interpolation right?

[@saburq]

It’s cancelling out errors by taking readings from multiple GPSs and doing a least squares reduction to get the final location solution.

[@ivanjermakov]

TLDR: they wrote their own log file. No ACID = not a DB.

[@krux02]

you forgot to put in the nerd emoji 🤓

[@monolith-zl4qt]

@@krux02 is it nerdy to know the absolute basics of CS?

[@jerrygreenest]

Log file stores entire stream of data, and they seem to store both «last state» data (as last as possible), and a log file, too. So technically it’s kinda like a simple database after all. From log file they can probably write entire path of car movement for example, as it is a series of data. For rare cases when you truly need this history. In database, they have their current position, battery/fuel levels, etc. For common cases.

[@andreffrosa]

Then no-sql dbs are not dbs?

[@tropicaljupiter]

@@monolith-zl4qtconsidering how self taught everyone is: yes, sort of

[@dv_xl]

You mentioned at the beginning of the video that making your own language makes sense if its designed to actually solve a problem in a better way. This is that. They did not attempt to write a general purpose db. They wrote a really fast log file that is queryable in real time for their domain. This wins them points in costs (margins matter) but more importantly, gives them a marked advantage against competitors. Note that theyre storing and querying way more efficiently. Quality of product is improving while cost of competition is increasing. Seems like a no brainer on the business side.

[@yung_shaka]

How does one make a log file?

[@TurtleKwitty]

A MAJOR part of this that went unmentioned, they didn't try to get all their data in there either, just the specific domain that they operate in so they're still clearly using aregular DB for anything else and that's why the version field is a lot less important for their use case, it's well known data theyve been dealing with for a while and its a specific subset of the data they use

[@krisavi633]

@@TurtleKwitty Yep, like writing parts of python in rust, just the ones that hit performance the most in python.

[@domogdeilig]

@@TurtleKwitty Less important doesnt mean unimportant. They will have to change this at one point, and this will cause the worst headache in the universe.

[@wwjdtd1]

​@@domogdeilig Depending on the wrapper, they might not. If you change a device ID on update, then you can just point to the old ID for archive retrieval and store what version the device is using. Possibly even encoding it into the ID itself. You can even run a different database backend for v1 and v2 since I doubt you would make a breaking change very often. Then you just query the right DB.

[@andrasschmidthu]

Great solution! If they want to further optimize they should use fixed point instead of floating point and do variable length difference encoding. Most numbers would fit 8 or 16 bits. Using that the memory requirement could easily be half or even less. The size of the entry should be stored in uint16 or uint8 even. If size>65536 is possible then use variable length encoding for the size too. The whole data stream should be stored like that: a stream. 30.000 34 byte entries a second is 1MB/s which is a joke. Write all logs into a stream and parallel collect them for each data source in RAM until a disc block worth of data is collected. Only flush the whole blocks to the disc. This would optimize storage access and you could reach bandwidth limit of the hardware. In case of power failure the logs have to be re-processed like a transaction log is reprocessed by a database. Once we have optimized such a logger that we used no FS raw access to a spinning HDD and we could sustain very good write bandwidth using cheap hardware.

[@PeterSteele111]

I do GIS at work and have several hand held units that connect over bluetooth to iOS and Android on my desk right now that can get sub meter accuracy. I have even played with centimeter accuracy. I have trimble and juniper geode units on hand. I built the mobile apps we use for marking assets in the field and syncing back to our servers, and am currently working on an offline mode for that. So yeah, GPS has come a long way since last you looked. Internal hardware is like 10-20 meters on a phone, but dedicated hardware that can pass over as a mock location on Android or whatever can get much much more accurate results.

[@mcspud]

Its not GPS, its the tesselators that process it.

[@7th_CAV_Trooper]

We used to average over N readings to get pretty good sub meter precision, but I don't think GPS is any better than 5 to 10 meters today. The trade off was battery life. More readings allows better precision, but burns battery. Less precision means the device can run longer without charge or replacement.

[@Hyperlooper]

Isn't it a restriction put in place for government?

[@7th_CAV_Trooper]

@@Hyperlooper The US Gov used to limit, but no longer: www.gps.gov/systems/gps/modernization/sa/

[@honkhonk8009]

I only know about Trimble because of the fucking Tractor edits where they play on that whole "missile guidance system" meme lmfao

[@jsax01001010]

5:35 Preping for scale can be worthwhile if they manage to get a contract with a very large company. A company I work for recently contracted with a company that provides a similar service. The small scale test with 2,000 GPS trackers was straining their infrastructure. The full rollout of 200,000 trackers broke their service for a week or two while the had to rush to scale up their service by about 20x.

[@maximilian19931]

Dump it into leveldb and see if it crashes under load if not use it!

[@GrizikYugno-ku2zs]

I signed in and made a youtube account just now to say THANK YOU! 15:00 I DIDN'T THINK ABOUT VERSIONING MY DATA! Sometimes, the things you don't know when self taught are just jaw dropping. This has been very humbling.

[@GrizikYugno-ku2zs]

Follow up note: I can see why this is particularly dangerous for binary, but it certainly applies to all data driven applications. Workarounds in JSON would be possible, especially with Rust, but versioning makes it so, so simple. I am nothing close to a novice or junior - despite how naive I was here - so it really goes to show that you must always remain a student. Seven years of building all types of systems, and yet that means nothing when it comes to things I haven't done. I've only ever built systems and thrown them away when they didn't make money. Running something long term requires maintenance which is something I NEVER thought about. Wow. This is why Prime is great. Everyone else gives useless tips and tricks to people learning JavaShit. Nobody else is out here helping programmers who are already competent and capable. THANK YOU THANK YOU THANK YOU!!!!

[@skylark.kraken]

Well, if you're making your own binary format you ought to have looked at how other people have done it and you always see a version first thing which should tip you off

[@diadetediotedio6918]

It is an excelent thing, but it also has nothing to do with being "self-taught" or not.

[@-_James_-]

Personally, I would put version information higher up anyway. For performance reasons, you don't want to mix v1, v2, v3, etc data in the same stream. You want a stream of v1 data, a stream of v2 data, etc. That way your server can have distinct forks in its code to process each version as optimally as possible. When a customer needs new functionality you make them a v2 node with the additional data they need.

[@saiv46]

Versioning binary data is for pussies. I've seen how the biggest russian social network live-migrated 4.8 trillion messages while being on high-load, and it's just work of three PHP programmers.

[@christ.4977]

Isn't streaming data like this what kafka was made for?

[@thomas-sinkala]

Read this post like 3 weeks ago and that was my question. Kafka, just use kafka!

[@retagainez]

Perhaps it wasn't considered due to the fact that majority of customers deploy on-prem?

[@georgehelyar]

​@@retagainez you can deploy Kafka on prem easily enough. Also they said they were coming from AWS Aurora so their on prem thing is a bit weird.

[@pieterrossouw8596]

Exactly, there's plenty of data streaming stuff available. If you don't need exactly-once delivery, NATS Jetstream is also worth a look.

[@oggatog3698]

I was just thinking this...

[@atlasz911]

I had hand on a similar database that's a bit more complex than this, lives with small changes for over 25 years and runs in thousands if instances. This was one of the few features that allowed my former employer to outcompete several competitors who based their solutions on general databases. The difference in performance, scalability and HW requirements is astronomical. The investment to R&D has payed off many-many times. If this company expects to grow substantially than this DB can give them the edge against their competitors in pricing and flexibility. Assuming that they will be able to incorporate their future needs into it's design.

[@StCreed]

It's the last assumption that breaks many backs of many camels. Recently worked for a very large financial organization, and they went the "roll your own db" on mainframe, around 1975. They're still stuck with it and it's a huge and very expensive mess.

[@avwie132]

Saved cloud cost, now they have maintenance and ultra-specific-high-payed-developer cost and a self-induced vendor lock-in. Well done. Tens of thousands of vehicles and people isn't special and isn't big at all. Somehow everybody thinks their problem is a unique one. But it isn't. Looking at their proposition it looks like something FlightTracker has been doing for ages..... Writing a blog post about something you _just_ built is always easy because everything appears to work like it should. Now fast forward to 5 years in the future, and see how you handled all the incoming business requirement changes in your bespoke binary format.

[@TerrenceLP]

I agree, too many people get caught up on the technology and don't know why, rather than understanding the concept of the best possible solution and then applying it with what's available. If by some chance, you find nothing 😮 you might have something unique. But 99% of my job wasn't that 😂

[@mikeshardmind]

The thing about the version in the header is spot on, but unlikely to help them here since they want to be able to directly access specific bytes for fast indexing, so all the bytes for that can't ever change meaning. Assuming they haven't already used all of the available flags, the last flag value could be used to indicate another flag-prefixed data section.

[@hck1bloodday]

that would be true if the package has a fixed lenght, but since you can skip sections (hence the has xxx flags in the header) the lenght is variable and they can't just go to specifyc bytes via indexing.

[@mikeshardmind]

The normal purpose of a version as the first field in the header allows everything, including the header, to change. The article (and the video) both discuss indexing on bytes in the header which are always there and not part of the variable capabilities.

[@siquod]

Why would you need a version field in every database record? One for the whole database is enough. Or did you think this was a network protocol? As I understand it, it's a file format.

[@hanro50]

​@@siquod Main issue would be that you'd need to upgrade the whole db at once if there is a change to the format. Which takes engineering time to do right and can lead to down time as data is transitioned.

[@michaellatta]

In their case I would use Kafka to collect the data, and materialize to a database for queries.

[@7th_CAV_Trooper]

Or just leave it in Kafka.

[@Sonsequence]

They're not logging anything they don't already need for querying so if they materialized to a DB it would just be the same throughput problem with extra steps. I don't know whether or not they could have made Kafka work performantly for them on the read end for their GIS queries

[@artursvancans9702]

@@Sonsequence not really. the aggregate view might be buffered, flatmapped and updated every 5 seconds or so. the main thing they want is being able to have the data for whatever reason they might need in the future.

[@michaellatta]

@@Sonsequence if they need every data point yes. But, given they are only keeping 30GB of data they could hold that in RAM using one of the in-memory databases, and let Kafka tiered storage hold the history. No custom serialization required, and a full query engine.

[@Sonsequence]

@@michaellatta yeah, just going with in-memory for the recent might be a good option but I don't think there's a GIS DB for that. Would still have to be custom.

[@complexity5545]

The title is a play on [ not knowing ] the difference between "database" and "database-engine." Databases are just files that store content. A Database-engine is a CPU process that manages connections (and users) that read||write specific blocks of a data file. It was still an interesting article. Good Video.

[@Michaeltje01]

8:58 KeyboardG: "high write and buffered is Kafka" Yeah I'm with this comment. I still don't understand why they couldn't use Kafka instead of some custom DB.

[@themichaelw]

100% this is literally just kafka but shittier. Kafka is crazy fast because it uses DMA and can move data from network card buffers to disk without copy and without CPU involvement. This article honestly reads like some engineers with too much ego to realize that the optimal solution here is to write to kafka and dump that to static storage as an intermediate step, or just right to a data warehouse.

[@rainerwahnsinn2150]

Kafka and then writing into Delta was my first thought.

[@guptadagger896]

would you still have to aggregate out of kafka into something else for reporting

[@Serizon_]

@@themichaelw I understand , kafka seems nice though I don't understand what kafka does :/

[@7th_CAV_Trooper]

@@guptadagger896 why? Kafka is an event db. It supports SQL style queries.

[@mohamednaser4265]

a multi tenant postgis will do that trick, can be horizontally scaled, can have a tenant per customer if you want, still don't get reinventing the wheel now they'll have another headache of maintaining their new db and need new developers to learn it :"

[@Navhkrin]

Yeah, the amount of data they are processing is nowhere near enough to justify custom solution. I mean few hundred writes a second and you need a custom DB for it? That is such a small amount that I really don't see the point. If it were 10K records per second I would understand, you would need to flatten and slap entire chunks to disk to maximize write performance but really any DB should have no trouble handling few hundred writes per second.

[@bkucenski]

There's a work around for the version in header thing. You can run V2 on a different port. But that's less safe than getting your minimum header right out of the gate. Error checking is also a good idea so that if something gets munged up in transit (or you send the wrong version to the wrong destination), a simple math function will mostly guarantee the bits won't math and the message can be rejected. You can also then check what version the bits do math for and give a nice error message.

[@EraYaN]

You really don’t need the version per record, per chunk is more than good enough. You are going to do time based migrations anyway so it’s all good (as in start a new chunk at time stamp x with version n+1).

[@isaacpaul239]

Right, it's only an entry. We don't need to version each row in a database :) I think the confusion was the entry starting with a data length which makes it seem like it is an independent piece of data when its really only needed because you can attach arbitrary data to the entry.

[@0x0404]

This could be a rare example of not having a version field in the header, or even a header at all. They've got the database itself. If they have to change anything, new whatever, stick it in a new database. 1 extra byte per entry when you've got data coming in as fast as it sounds like they are might be too expensive on something that effectively doesn't change.

[@StCreed]

That is what the Dutch Tax Authorities used to do: just store the tax filings for any given year in its own database. This worked really well until politicians started to retroactively apply laws to existing situations, and add more changes every year than they could manage, and then they could no longer afford to have a new database every year, and now they're stuck with an incredible maintenance problem because they didn't think it through very well. But if you can manage it, this solution works quite well. Divide and conquer is often a really good solution.

[@hwertz10]

Yup. Data storage is easy (if you aren't worried about resiliency, reliability, crash recovery, etc. etc.). Databases are hard. I would NOT reinvent the wheel. I could see a scenario where one COULD save 98% (if the cloud provider overcharges, and your DB is small so you can run it in a small instance or two.) So, in essence, they don't have a full database, they don't need the failsafeness and resiliency of a proper database. Good on them for "thinking outside the box"!

[@bdafeesh]

This is such a huge decision; I would only trust the most competent teams/coworkers to pull off writing our own database solution... Such a cost to undertake for such a generic use-case. Sure, they have customers and looking to grow, great, pick any of the many open-source options for efficiently storing time-series data. So much more reliable using an already battle-tested product. Not to mention that material already exists for everyone/new team-members to reference and learn from... Don't roll your own database folks. Even when you win, you'll still lose. And to my business friends: Keep simple, more engineers = more cost. Efficient engineers = happy engineers = faster + better products..

[@research417]

They're going to need to pay for a dedicated team of people to manage and work on this, and I'm pretty sure it'll come out to more than 10k a month...

[@steffenbendel6031]

Well, I would say it is mainly very simple binary file. They not even did some tricky compression. (I once did a binary file for storing exchange data, that used arithmetic compression on the diffs of the values. Would also fit the requirements since compressing ist faster than decompressing)

[@paulmdevenney]

I thought the first rule was "don't invent your own security", but I think a close second might be don't invent your own database. If your entire business workflow isn't focused around making that thing better, then you're in for a bad time.

[@adamszalkowski8226]

Reading the requirements, sound like they would be fine saving the data in S3

[@hanswoast7]

Yes, they say so in the article^^

[@manafount2600]

I'm at a company that ended up writing their own DB for time-series data. The scale is much larger, both in terms of our engineering organization (thousands) and the amount of data processed (~500 billion writes/day, trillions of reads/day). We can accept similar sacrifices in consistency, but our use case and the data we store aren't quite as specific. All of the things you pointed out about engineering hours for developing and maintaining a custom DB are spot on - cost savings, even at our scale, are not a good reason to roll your own DB. Maybe if we were Google-scale that'd be different, though...

[@MagusArtStudios]

In my experience writing databases went extremely well with some caveats over time as branching databases emerged to keep different data sources organized. It was actually so good I turned some data into a chatbot AI with context labeled networks weights and synonym, antonym, noun, and reflection attention mechanisms. Long story short writing databases is so much fun. :)

[@DKLHensen]

If stuff like this counts then I'm a database developer as well, putting that on my resume right now! thanks, another good video

[@ldybdahl]

We did something similar - it took 2 ukrainian programmers a couple of months to create an insanely fast system that runs at negligible cost. The costs of developing and using the database engine were lower than the costs of introducing a database like Postgresql into production. The complexity level is comparable to writinh parquet files.

[@hemmper]

Storing diff's is a good idea. Like in video codecs like mr Prime said. Also, if some accuracy can be sacrificed, like with lossy compression for video, skipping records and interpolate (linear or "curvy") /calculate them instead when you need them, GPS track pruning. Maybe look at alternative float formats, including store the logarithms of the numbers as ints instead of the floats themselves, which is kind of a little bit of what the usual float formats do, but maybe with more precision bits than you really need in the traditional float bit formats. Traditional RDBMS'es can have user defined functions programmed in common languages, including Java and C and such, and compiled into the database. Those functions can pack/unpack the data directly in SQL and run quite fast. Postgres can also index on the result of such functions. I think most of us should go far in order to NOT create our own database systems. Also most larger database systems need secondary analytics databases where only the data most needed for analytics/statistics are transformed and copied into that.

[@mikemcaulay9507]

I worked for a company that did tracking of mining and construction equipment and from what I recall they were able to setup Wi-Fi access points at a location to help with triangulation. Pretty sure this is why your iPhone can give such precise locations if they have access to your APs.

[@velo1337]

we track around 400 vehicles and our postgres db is burning. but we also do a lot of computation on that data. its around 12-14k transactions/second

[@SandraWantsCoke]

What about optimizing the tracking by not tracking too often when the car is on a straight road with no intersections? Or when the speed is 0 track less?

[@muaathasali4509]

You should at least use timescaledb with postgres. It's just an extension and it will significantly improve performance. But also if your use case is very analytics heavy, then u should use clickhouse, tdengine, victoriametrics etc.. which are also better for a distributed setting compared to postgres.

[@velo1337]

@@SandraWantsCoke standing time is valuable data

[@BosonCollider]

If you are not using timescaledb, make sure to use BRIN indexes.

[@LtdJorge]

@@muaathasali4509Those other database engines you suggest are all OLAP which are very, very bad at many TPS. Op is better served by something like Timescale or InfluxDB.

[@nightshade427]

If queries aren't often but collecting the data needs to be fast wonder if something like Kafka/redpanda capturing the data (throughput of 30k+ specified shouldn't be issue for these) and process at their leisure into a view db after it's captures for easy querying would have worked. I don't know their specifics but seems like it might have been simpler? Would even work on premises for some of their clients.

[@fb-gu2er]

We do about 60-70k transactions per second on average. With peak hour much higher. This is not a whole lot

[@muaathasali4509]

Yeah... I don't really get it. A cheap PC can handle 100k+ writes per second with batching

[@TheofilosMouratidis]

per database node?

[@jdahern]

I was thinking the same thing. There load is not that high. It sounds more like poor indexing or a bad set of hardware for the on premise clients.

[@SimonBuchanNz]

Transaction != Transaction. You can't just compare incrementing a view counter to whatever GIS magic is going on here; 30k/s might be easy, it might be impossible.

[@fb-gu2er]

@@SimonBuchanNz according to the article is very simple logic

[@aaronjamt]

About the versioning issue: there may be flag bit(s) reserved for future versioning, even one bit is enough. That way, then you can say "if you see this bit set, parse the rest differently" and maybe add a version field at that point. Also, maybe there's some reserved lat/long value they use as an update flag, like 65536 degrees or similar.

[@steffenbendel6031]

And there might be a header for the file. They only showed a single data entry.

[@m4cias]

@@steffenbendel6031 That's what I thought. Repeating version in each entry would cost extra few % of storage. It would make more sense in case of the broadcasting data between nodes idea.

[@darkwoodmovies]

At first I thought saving $10k per month was worth it, but then I realized that a single entry-level software engineer costs more

[@alexsherzhukov6747]

merica

[@darkwoodmovies]

@@alexsherzhukov6747 Huh?

[@alexsherzhukov6747]

@@darkwoodmovies entry level 10k/mo? there is one single place on earth where that could be happening

[@darkwoodmovies]

@@alexsherzhukov6747 Ooh yeah, true

[@Narblo]

entry level software engineer are 3k/mo

[@hanskessock3941]

The amount of storage they claim to create, 100GB per month does not remotely match the storage rates they claim they need - even if they only stored two doubles for lat/long, they would store 40GB per day. Supposedly they store a ton of extra stuff, and they are (weirdly) rolling their own compression as deltas, but those deltas require significant digit precision - it seems like they’re just making things up

[@Comeyd]

Why on earth would you need two *doubles* to store location data on earth? 16 bytes is excessive, and their frames only have 34 (or was it 36?) bytes to work with. Latitude and longitude can both be stored into a single u16 (360*180 = 64,800) And two u32 are more than enough to have sub meter location accuracy. If you’re okay with 1 meter accuracy you could do it with a single u32 or two u16 though… you only need 17 bits to get sub meter, and 18 gets you centimeter accuracy… so you technically only need 36 out of those 64 bits… There, did it in 10 bytes. Floats are evil, never trust them. They’re a dirty trick by the magic electric sand that we call computers. Additionally, say it takes 36 bytes, every second… 36 * 3600 * 24 = ~3.1 Million… also known as 3.1… megabytes per day, per tracked device. Which, yes, if you multiply that by 13,000 you do indeed end up with 40GB/day! However, why store 36 bytes every second when 10 will do? 10 * 3600 * 24 = 864k 0.87mb * 13,000 = 11.3 GB / day, neat, much smaller! That is worst case scenario stuff though. From the article they did say that not every single device reports 24 hours per day, and that their *peak* is 13k devices! If there average was say… 3,400 devices every second for the entire day (total logs / entire day in seconds) 3,400 * 0.87 = ~3GB/day. 3GB * 30 = 90 I think that says their average reporting is around ~3,000 per second across the entire day, and I also bet that this is not taking into account their “on-premise” offering. Either way, it’s not so far off that I think they’re lying… it just seems like a small company that is being very careful with what information they choose to reveal.

[@Tempo_Gigante]

I am not even a programmer and I did something similar - created my own DNA database that was 8 times faster than using SQL equipment. Oh did I mention that the data was also compressed. P.S. you don't need a version field - just have a different UDP port number for each version. That way you save on data transmission.

[@zxuiji]

15:59 They could just make one of the available flags mean "has extended flags" or "has version"

[@Bozebo]

They might be able to get away with assuming the version from the time too? Similar to flags it'd be better at the start of the header though if used for version; could get away with it if production is only expected to read the latest version and not older versions too.

[@tsx7878]

It’s simpler than that really. Prime is confused here: this is not a wire protocol. It’s an on disk format. You put the version in the file header. When you deploy a new version it starts to write to a new file. But can still read the old files.

[@zxuiji]

@@tsx7878 I know what flags are, I suggestes using them for adding the versioning because that's the easiest way to check what type of object was handed to them without modifying the original object. The lack of the appropriate flag says it's original object, anything else is a newer object. The header can then be modified to expose a new function that excepts a void pointer instead of a predefined type. The old functions source can be renamed to this new function and slightly modified to be a wrapper to the new function, thus retaining the "one root function" rule for stable behaviour without breaking the existing ABI

[@sullivan3503]

@@tsx7878 Thank you. I had this exact thought.

[@josecanciani]

About the version, my take: I think due to the big size they have, new versions can just be implemented in entirely new nodes. The new nodes will run with the new binaries, only for new data. The reads will do parallel connections to different nodes anyway. There's no need to mix different versions in the same nodes, just clusterize based on version. It doesn't seem they would need it, but if they do, they can migrate the old data eventually, although probably won't make sense unless they need to change format too often.

[@WiseWeeabo]

It makes sense when you find out it's geospatial stuff, here there is a lot of room to optimize the system for your specific use-case.

[@drndn]

You can keep the version field minimal like 1 byte to start. By the time you have 255 format versions you can afford to say that if the first byte is 255 then look at the next byte etc to know which subsequent version number you have. Variable length encoding. And by 255 storage will likely be cheaper.

[@635574]

I think they bet on the fact that location data format will never ever change and the version of the software willl be irrelevant for it.

[@nidavis]

With location information it might make more sense to store the raw data as spherical coordinates and then doing a cartesian conversion when presenting it back.

[@billhancock8881]

They didn't even need a database. At least, not a transactional database. Store one file per vehicle, per day. Stored in any data storage you want, S3, B2, whatever. Use JSON, or cbor-x, or whatever schema-less format you want, and zip it. If you need more frequent queries, either write more often (hourly), OR, contact the servers to retrieve the unwritten data for a specific vehicle. The only slightly complicated part is the spatial based queries. Run a script to take all the per vehicle files, and output daily per area files. Find the files for the area you are querying, and read those files. The nice part about this approach is it is client agnostic. I would start with it in NodeJS, but if it's taking too long to process the files (ex, summarizing the location data for a vehicle over a month (2.6 million entries), you can easily process the files in any language you want. If downloading them from S3 is too slow, you can cache them on disk and in memory, because they are immutable.

[@Amit-sp4qm]

Also i think, hiring extra would not be much issue as same application developers are adding this functionality to their app .. In a more simple term they replaced dedicated database and all the handling code to some relatively simple memory writes .. Also probably saved on a few database developers themselves in the team ..

[@krellin]

they effectively did what fintech companies do, you cant use a DB as a service if you truly want exceptional performance, your application must be a DB on its own, like a specialised db.. they all have many common features. Also engineering cost while high is one off cost compared to your ever increasing cloud costs, once engineering did the job its significantly cheaper to run and maintain.

[@tomi.panula-ontto]

I am not so concerned about the versioning. They could simply have one version per file, or data directory, or maybe it is dependent on the software version. If they need to upgrade, they can easily write a program to read old data and spit out the new format. It is quite common in ”real databases” too.

[@mrcuddles90]

Don't try to re-invent the wheel. There are a lot of nicely polished wheels out there.

[@prinzrainerbuyo3234]

protocol extension still possible even though there's no versioning in the field, extended functionalities can be set on the flags, just need reserve them initially.

[@danieltumaini7037]

21:02 best take, for any custom project. gracias concurreagen

[@gzxmx94]

4:02 You can have UWB location trackers in mines (hey I worked on them..) which can be as accurate as 10cm. It does require a complete coverage mesh network of UWB transmitters though, and the location of each transmitter must be known beforehand. UWB can only do "relative" positioning, combined with a known location this can be transformed into absolute GPS coordinates. In mines you can't get a GPS signal so good luck.. (even though there are repeaters, it has its challenges and limitations).

[@neo-vj4zq]

Honestly we use an off the shelf solution, enterprise but external company and this level of throughput is trivial.

[@nulano]

Perhaps they just have a version field per-file rather than per-record.

[@yellingintothewind]

Given the goal of minimizing on-disk space taken, per file or per archive version numbers is a good idea. Depending on expected future requirements you can also just guarantee consistent versioning. If you have to change the format, you then replay the data to convert it to the new format. This especially matters in resource constrained systems where you might not be able to keep the old parser around so having a field that just tells you you can't process the old data isn't of much use.

[@gregorymorse8423]

One remaining flag bit is also enough to do a v1 vs non v1 hack.

[@_winston_smith_]

Given the nature of the data they might be able to get away with using time to define the version, an epoch based version.

[@gregorymorse8423]

@_winston_smith_ not on a real time system. The transition period would be chaos. That is an insanely risky and error prone method.

[@yellingintothewind]

@@gregorymorse8423 There is a 64 bit timestamp already included. The issue is updating versions across multiple systems without requiring downtime. There are two solutions to this. First is to make each node track the protocol version it is using, and the last timestamp of the old protocol. This means you must preserve that metadata as long as you preserve the records from that node. Simple to roll out, but relatively fragile. The second option, assuming you don't need emergency protocol updates, is to pick a time when the protocol change will happen. Roll that out to all nodes, then in a week, or whenever the time ticks over, they go to writing the new version. This is essentially the versioning system used in most crypto currencies. Rules will change on block X. It does mean you need to encode that logic anywhere that needs to process the data, so there is some complexity overhead not present in a simpler versioning scheme. (I can write a TCPv4 parser that doesn't need to know anything about TCP 1, 2, or 3).

[@marekjkos]

Technology selection is important, they should have started with a columnar time series database, and probably experienced 20-100x compression.

[@bergels9408]

It looks like hes describing an avionics data bus standard? ARINC 429 came to mind and seems to fit in the application. It could be that the application is so generic that any shoe could fit, but I wonder if that's whats being used behind the scenes?

[@wlockuz4467]

They did a vendor lock-in with themselves, its impressive.

[@blarghblargh]

Version 0 is the version without a version. Only would work if you get lucky and the fields in that spot don't conflict with the potential version values.

[@hz8711]

In similar project, i implemented elastic stack like this: A lot of live logs from thousands of machines > rabbitmq cluster (for buffer if logstashes are not able to handle the load) > logstash cluster (aggregating and modifying logs ) > elasticsearch cluster with well designed indexing and hot-warm-cold index rotation. Sounds like each ride can be a single record, and you can query by ID.

[@rhone733]

People who say 'don't reinvent the wheel' don't understand how innovation works.

[@musicalducky6623]

Instant like for the version field.

[@BenjaminScherrey]

Definitely a system looking for an Erlang solution. Telecoms have been doing this at a scale that makes these numbers look like rounding errors. CQRS architecture and actors representing the live objects being tracked. Database gets really simple and is removed from the critical path entirely.

[@Kenjuudo]

They have an "entry length" field that effectively works as a version number.

[@tutacat]

they could sqush more reports still. combining local sensors (RTK), with wifi, wwan (and maybe BT) is pretty accurate.

[@Delfigamer1]

I don't think the individual update frames are ever present by themselves. In the storage, they must be bundled together into large blocks - and then you can write the version in the file header, since you won't ever mix multiple update-frame versions in a single block. The same goes for the real-time updates - they must be happening in the context of some persistent connection, and so there you can negotiate the version during the connection's handshake. Thus, you don't need to version each individual frame, that would actually be a waste of already precious space. It's like if, in HTML, you'd be writing a DOCTYPE for _every individual tag_ instead of just having a single one for an entire document.

[@bigbug1991]

Thank you! Thought exactly the same while watching the video.

[@sullivan3503]

Yeah, him saying this caught me off guard. Pretty sure the only reason we have versions in things like internet packets is because there is physical hardware in the loop that has static circuits based on the version of the packets.

[@steffenbendel6031]

I agree.

[@pawol9315]

"That's probably Machhhron's creation" Love it!

[@JeremyAndersonBoise]

Should have used Redis/ValKey, honestly. Kafka is also a great choice, but I like what they did even though it’s an AOF type log not a DB

[@marcsh_dev]

When I did something similar, I versioned the file stream, not the entries In fact, the first 1k of each file was reserved for metadata, which included a version

[@georgeconradie4854]

SQL Server works great with GIS data, offers compression and partitioning. I worked at a company very similar to this in a different part of the world. Perhaps people should properly evaluate options like "going to the cloud" or "being agile". Everything is not for everyone.

[@DeanRTaylor]

This seems like a business problem not a tech problem. If your outgoings are more then incomings then getting a financial advisor is probably more advisable than swapping your db for a log file.

[@creativecraving]

Lol! You don't need a financial advisor to tell you when you're spending too much! Cloud infrastructure is often excessively expensive.

[@DeanRTaylor]

@@creativecraving Of course but the point I'm trying to make, is not that cloud prices are fair or cheap but that if you need to save 98% of your cost to break even there is more likely a problem with your business model than the managed database instance you're using. Cost optimisation is fine but your data storage should be making money as part of your cost analysis not putting you on the brink of bankruptcy.

[@michaellatta]

Building a special purpose database can make sense, but it requires very special skills and requirements to make sense.

[@creativecraving]

It takes specialized skills to make a general-purpose database; but if you're targeting a specific use case --- where the workload is greatly reduced --- , it makes sense to try something out and see how it goes. If it's bad, you can quickly iterate.

[@macchiato_1881]

So not only did they "save money" on cloud costs (by just converting it into engineering costs), they also secured their jobs for the next 10 years because no one will be able to maintain this if the original team gets fired all of a sudden. GENIUS

[@MichaelScharf]

Use one of the flags as „next byte is version“, and that’s it. No need to have overhead of a version now

[@tylerbakeman]

0:50, “When you write your own language, usually it’s after decades of experience”. Part of the reason there are so many languages, is our ability to build frameworks in other languages. If I have a String formatting, that can be parsed from a file, and I have a custom file extension- that’s essentially the same thing. Magic value String formatting is a common practice (and issue), probably moreso in the gaming industry: there are different formats for object data - it is not uncommon to see a game import an asset, build off of those assets, and create either a JSON or a custom file format. So in a sense, developers create their own languages all of the time (not necessarily Large scale multi-purpose languages like Python), and they probably shouldn’t be most of the time, because there are common formats for just-a-bout’ everything.

[@Forty8-Forty5-Fifty8]

I saved 100% of my cloud costs by writing my own database. It doesn't work so I don't need a cloud service anymore

[@yintercept4612]

"The Blockchain" is a rewritten database.

[@jordixboy]

Dont forget about load balancers, they are expensive as hell aswell...

[@hkravch]

Modern developers discovering binary files 😮

[@Tobarja]

As Mr. Warbucks said: "Did I just do a commercial?!"

[@nathanpotter1334]

I showed my co-workers the Tigerbeetle demo - Easily the coolest demo ever

[@vsolyomi]

GPS can be up to cm with some auxiliary groud-based stuff and/or post-processing adjustments

[@rogierlodewijks8646]

Just introduce a bit-flag indicating a version field. Also 0x80 bit set mean: additional flag field next.. and bam... scalez 2 infinity!

[@gammalgris2497]

Sounds rather that they don't need a relational database but a transactional database (don't remember the actual name) where they just store each incoming data record. At any given time you can retrace the movement pattern of each tracked entity by going through all stored records. There surely are numerous implementations for that I would guess.

[@s3rit661]

15:04 In Europe an engineer is paid half of what he is paid in the US for the same skills, sometimes even lower

[@GreyDeathVaccine]

Hello central Europe xD

[@NostraDavid2]

Costs are also much more than in Europe. A trip to the hospital can cost you 100k. Of course you can reduce it if you know how, but you're still stuck with some 15k. The USA can be such an American Country.

[@gjermundification]

3:47 5m x 5m, however with accelometer and other movement trackers; such as RTK it's possible to calculate way better data. Such as triangulation of 5G...

[@emaayan]

good luck finding hiring people to maintain that, everyone who's not desperate and knows what they are going into, would run as soon as they'll hear we've written our own custom db, meaning they won't get experience in well known frameworks and get vendor locked , and it would get worse once the original architects would move away.

[@-_James_-]

If it's well written, well documented code it's no different to maintaining anything else. You wouldn't employ someone specifically to work with this code, you would employ someone to work on the server of which this is a part. I'd much rather maintain code that contains 100% of the solution instead of code that repeatedly jumps in and out of open source/third party libraries that will all have differing standards of (usually poor to middling) quality.

[@emaayan]

@@-_James_- the problem is that it needs to be contiued to be well written and documented by different ppl along the way

[@-_James_-]

@@emaayan That's what coding standards and code reviews are for. If a company doesn't have and use those then it's living on borrowed time no matter what else it does.

[@emaayan]

@@-_James_- true, but ther problem. Is that people (and companies) change, that's why i'm also skeptical about the real agile implementaion holding water as well.

[@LionKimbro]

They might be versioning via the flags; They've got 16 bits in there for it, and I imagine a bunch are reserved.

[@maximilian19931]

Sounds like they need a TSDB system based on the ingest alone! Or bigtable(gcp) or leveldb? And save themself a lot of work by sitting on the shoulders of others!

[@petermeshackjobs8076]

i created mine plus with database API and hosted it locally and am doing fine

[@HunterMayer]

I can't count the number times we've never used the version field. But the number of times we'd have use it saved us months of work.

[@Omnifarious0]

I wrote my own database. But, it was also 1991. And I could guarantee that there was only one reader or writer at any given point in time.

[@Lampe2020]

17:07 Well, then I've done everything right with L2DB (a binary config file format originally intended to be a database, therefore the name), which has its eight magical bytes (\x88L2DB\x00\x00\x00), directly followed by three unsigned shorts for major, minor and patch format version. After that comes all the fun part with the flags and other stuff.

[@YuriyYarosh]

It took me about 15 years of research on Programming Languages Design and Formal Methods to just Design and PoC a Formal Programming Language with an Mbed Database...

[@cravecode1742]

I feel like they solved the wrong problem. Something like Azure’s Event Hub or AWS’s MSK on the ingestion. Partition and store as you’ve chosen. Have meta data in a common DB for assembling needed queries. I’ve faced similar sounding obstacles for financial transnational data

[@horaciomiranda26]

I wonder if converting the data into vectors to store the delta of the vector each X numbers of data points may save space and give you the same quality of granularity.... Also IO2 is fast for persistent storage, if the customer really needs more performance, they can go to ephemeral storage, yes you will lose the data if the server is rebooted or the server fail ( needs to be replicated to another server to protect the data ) but the difference in performance is huge.

[@remrevo3944]

One thing to consider is that *flags* is 2 *bytes* and not *bits*, which means that they have a lot of space to add further flags and could even smuggle a version field into it, if they wanted to.

[@romaincramard5301]

The take on header version is not to be on each object but more on the storage file level

[@PeterVerhas]

1 byte for version is enough. If not, then before you run out allocate a new version byte in the new version (sub version kind of).

[@scottspitlerII]

I literally just saved $15k a month moving off of AWS to another cloud vendor. It’s insane how expensive the cloud is getting

[@reze_dev]

What another cloud vendor?

[@scottspitlerII]

@@reze_dev move to digital ocean, optimize your product to not use so many crappy off the shelf aws services. Save $100k a year

[@JamesMurphy1984]

You can adapt a relational database to use a stored proc (for speed) and just have the coordinates in a bounded box since that’s much more efficient than calculating with a circle. Why did they need a brand new DB solution for it and how much money did it take to build AND maintain it? What about security upgrades and costs associated with that? Crazy stuff.