Good Morning!

Morning

Morning

månmån

Morn

Morning 🙂

Morning

morning

I’d say Morn’ but it’s a bit ridiculous at this time >_<…

It's never a bad time to say Good Morning! 🙂

It's always Good Morning when we come online 🙂

We just finished Star Trek: Picard season 1 and now we're watching the Ready Room episodes that cover each of the Picard episodes. Fascinating stuff.

I watched the final on friday. I enjoyed the series, although it did take a bit of time to find its stride.

but I thought the ending was nice

Ah @seancorfield, you might be the best person to ask this to :)… I’m wondering, and this might be a really silly idea, but if I was trying to run a simulation where the I wanted to keep the sim state pretty much entirely in a postgres database, what’s the least crazy way you’d approach that? Besides jumping straight into datomic… I’d prefer to not have to hand write the entire thing in sql statements… Is java-jdbc too low level? So I should be looking at HoneySQL or SQLingvo? I’ve also been thinking about maybe taking a look at how datascript does things and just using that+java-jdbc. You’re welcome to tell me ahead of time that this is going to end in tears and fire =)…

I was having a chat with a friend of mine and this came up as something he wanted to try, I’m trying to work out if it’s genius or madness… :face_with_raised_eyebrow:

I'm actually a big fan of SQL and using relational databases... If your data is "just" hash maps and collections of hash maps, that maps nicely to rows and tables, IMO, and next.jdbc is a good way to get that data in and out of the database.

But it depends on the shape of your model and how complex the relationships are between parts of the data model.

Well mostly hashmaps, there’s things like nested maps:

#wagons_and_wizards.sim.House{:peeps [#wagons_and_wizards.sim.Peep{:name "Peep 6",
                                                                   :cash 0,
                                                                   :holdings [],
                                                                   :gender :female,
                                                                   :id 152,
                                                                   :dob {:days -6, :months -2, :years -13}}
                                      ...],
                              :pantry {:food-base {"Wheat Bread" {:name "Wheat Bread",
                                                                  :category :food-base,
                                                                  :portions 121}},
                                       :food-extra {"Eggs" {:name "Eggs", :category :food-extra, :portions 135}},
                                       :drink-base {"Cheap Ale" {:name "Cheap Ale", :category :drink-base, :portions 33},
                                                    "Good Wine" {:name "Good Wine",
                                                                 :category :drink-base,
                                                                 :portions 128}}},
                              :day-of-food {:food-base 9, :food-extra 9, :drink-base 9},
                              :normal-shop {:food-base 126, :food-extra 126, :drink-base 126},
                              :discretionary-funds 5350,
                              :id 153,
                              :dob {:days 0, :months 0, :years 0}}

Basically he pointed out that I could be sticking all of that in postgres, and then I wouldn’t be realising most of the sim in memory… I could then just query/update the bits that I wanted to and not deal with a bunch of extra complexity… However I’m concerned that I’d be drowning in complexity from dealing with sql instead of update/assoc etc…

Trying to work out how to rewrite updates in sql is… 😖

You could either create a normalized (relational) model of that nested structure and just have one place where you save it down to the database/load it back up from the database -- we do that in a few places at work. Or you could treat some portion of it just as JSON and use native support in PostgreSQL.

A lot depends on what you need the lifecycle of the load/save points to be -- are you just looking to keep a record of sim states in the DB, i.e., just using it as a persistence layer -- or whether you want to perform complex queries and updates directly against the DB.

The latter I think?

We have some data structures at work where the DB representation is just a couple of specific columns and then a JSON blob of the whole structure. That's possible because we only query on a few fields, and otherwise do everything in-memory, just loading/saving the data for persistence.

I can do the former, that’s not too problematic, but then there’s a question, how do I decide that sim data is not required? (What I mean by this is, I feel like I’d need to work out that some data isn’t necessary, so therefore it can be dumped from memory and just kept in the postgres db…)

Ok so doing the actual work in-db is a bad idea I take it?

It can be. Depends on the work. I'm very pragmatic about that.

The more you want to query it in the DB, the more you have to normalize it, and the more work it is to load/save (& update) instances of your model.

Hmm, it’s tricky as I’m not sure how complex the sim will get, at present on that snippet of data, what I’d be doing is changing things like, each Peep’s cash and holdings, what’s currently in the pantry by deleting the amount based on :day-of-food or adding based on :normal-shop and adjusting :discretionary-funds

My gut would be to assign a unique ID to each Peep and initially treat the DB purely as an opaque data store and see how it evolves.

It’s just values like :pantry are pretty deep. I’m probably going to have to split that House structure into, House, Peep (foreign key to house), Pantry (foreign key to house)?

So you'd manage the Peep data in-memory while you're developing it.

Yeah, once you have a better sense of the specific individual entities that exist independently -- and need to be persisted independently -- then you can evolve the schema and migrate the data to the new relations.

Ok sure.

The alternative model I was thinking of was to basically lean on datascript to flatten everything into EAVT style structures and then just store that, except in cases where I know what the structure is?

There's also the option of using some sort of EAVT pattern for persistence -- essentially ... yeah

We have an attribute store at work for some stuff because it's open-ended and it just has (in our case) id, user id, attribute name, attribute vaule (and a bit of metadata).

Ok good to know that’s viable 😃

Like I say, I'm pragmatic about it and happy to mix different types of representations in the same system.

The only thing I’m concerned about is performance, I’m not going to suddenly find that this is going to be a bear >_<…

How many millions of Peeps are you going to be simulating?

Well at the moment there’s peeps, shops, stocks and traders, based on 3 systems interacting with each other.

We use Percona (MySQL) and we have a number of tables with tens of millions of rows and it's still fast enough.

Peeps buy from shops, shops become stocks based on success and traders trade them…

Ok, that’s really good 😃

What kind of memory do you need for that though?

If I started off with a fairly lightweight machine to start with is that going to be a problem?

Sure, we sometimes have to tune a complex query because it's not fast enough, but overall we try to keep queries simple and do stuff in Clojure if doing it in the DB would be complex/slow.

I can't remember what our DB servers are these days. Last year we moved from three Big Iron servers to two virtualized servers on more modern hardware.

Hmm, I might start with datascript / java-jdbc then to start and then try creating custom tables when I have a better idea of structure :)…

Use next.jdbc, not clojure.java.jdbc.

And if you need to process large amounts of data, use a reduce or transducing expression over plan.

Actually in your experience is working with postgres json fields slower than native fields?

I don't use PG so I can't comment.

Thanks!

Sure no problem =)…

I wouldn't expect querying on JSON fields to be as fast as regular fields tho'

That's why we partially normalize some data when we have otherwise opaque JSON blobs.

Thanks for your help!

That’s been super helpful =)…