
Ask HN: Are there still good use cases for multivalue databases? - mrsalt
Have you worked with them?
Do you think there is any reason for using them in 2019? (besides legacy software)<p>https:&#x2F;&#x2F;en.wikipedia.org&#x2F;wiki&#x2F;MultiValue
https:&#x2F;&#x2F;en.wikipedia.org&#x2F;wiki&#x2F;Pick_operating_system
======
davismwfl
I worked with them for years, mostly UniVerse and UniData. The short answer is
no, I don't think there is a reason to base any new work using a Pick based
MVDB. Don't get me wrong, I see some value in what they were trying to
accomplish, but today there are NoSQL solutions which are far superior to how
these old Pick style solutions were built. File corruption was a real pain and
normal reality, searching, sorting and managing the data took a full time
DBA/SysAdmin for even smaller implementations. Mixing the language and
database as most of these systems did too was a mess to manage, version
control was a pain usually, value typing was not good and error handling was
always a challenge to do it even just ok at.

To be fair, I do see some elegance in how they work, but just the trade-offs
today aren't the same, so IMO there isn't a good reason to use them when we
have graph databases, the modern NoSQL solutions and SQL for that matter.

Essentially, a current document database does more than these solutions can
and more reliably.

This is also why, I didn't see NoSQL solutions, like Mongo et al, as something
so revolutionary when they came out, to me they are just a modern twist on
things we have had for decades.

~~~
mrsalt
Thanks for commenting. My current job involves working (though in a minimal
way) with a heavily modified install of Rocked U2 which has its own version
control, kind of its own BASIC dialect and I can only agree with what you say.
It also forces you to use a special in-house terminal and text editor for ALL
programs in BASIC. No vi or anything.

They have discussed of doing away with it for years (decades?) and it has
never happened.

The biggest argument against migrating is "performance" (that a RDBMS or even
Mongo will be slower).

~~~
davismwfl
Yea, I had two public companies as clients for my consulting business, one had
the largest UniVerse install I had ever seen running literally their supply
chain, inventory and invoicing applications. They were running ~$5
billion/year off it, but I know talking to IBM at the time that was far from
the largest install. Their original argument was similar about concerns of
performance, reliability etc. A lot of that was coming from their
UniVerse/Pick engineers that didn't have current skills sets talking about how
Oracle and SQL was slow and horrible etc. They were just out of touch with
current solutions and partially burned by an Oracle applications project gone
horrible (don't they all), all of which is why we were brought in by the CTO's
office.

We replaced their solution with Postgres, proper caching and clear separation
of concerns/layers and they went from queries taking 3-4 second average (some
being 30-40 second normally) to being done in 500ms-1s average. Another thing
we fixed, some of their data had to be batch processed overnight everyday for
like pricing adjustments on the UniVerse system, because concurrency was a
major problem for them. After our changes they did real-time pricing
adjustments along with a bunch of other similar things. We built them a proper
OLTP data solution with a data warehouse with cubes for detailed analytics &
reports.

I don't envy anyone having to still work in one of these systems, so many
times simple things you could do in a modern system in short durations will
turn into a nightmare and a huge drawn out project in Pick/U2. I definitely
don't agree with the performance argument, I fought that one for years with
different clients and vendors who felt U2 was so fast, and with a properly
designed RDBMS I always could beat their performance, especially when
concurrency was a key factor. Locking on U2 systems is a complete cluster most
of the time for high concurrency systems.

~~~
mrsalt
Working with people who have spent literal decades in this stuff, it is really
refreshing to see your perspective. Never heard something so accurate for
something this specific on the Internet. Your points about concurrency and
overnights in special.

I am no expert and very young in average for people at my company (Junior
Engineer), so I've never had a chance to take part in this kind of "big"
decisions, but I've always suspected the way Pick/U2 works is the reason we
have all kinds of weird limits across the board (as you pointed out, a big one
is concurrent [write] access to stuff as U2 does not have
transactions/ACID/MVCC). I'm sure paying for U2 also costs a pretty penny.

The company does know that nobody wants to work with U2. If you apply for a
job here, they will barely mention it and will train you from scratch instead
as it is extremely rare for someone to know this.

Thankfully I don't usually have to work directly with it and instead use
SQL/Java, but it never escapes me because this U2 database is the source of
truth for a lot of stuff.

How big were the systems you have migrated? How long did it take to "replace
their solution"? I feel it must be soul-crushing to rewrite this kind of
system entirely, especially testing it.

~~~
davismwfl
So I have migrated two systems completely, and worked in a bunch otherwise.
The overall way we did system migrations was to take sections of the system
and carve them out and then implement it completely. For example, product
catalog was one, and for a short time the UniVerse and our system were running
concurrent and over a couple of months we just turned off the old product
catalog. This just went on and on through the entire system. IMO this is the
only way to migrate really large projects because there wouldn't be stomach
enough from the business to have a big bang project, and the chance of failure
skyrockets when you try to accomplish too much at once.

The largest system we migrated was using somewhere around 25 very large Unix
servers running UniVerse, handling millions of transactions per day and I
think somewhere around 10 years of history. It was very large by UniVerse
standards IMO. We moved that one to Postgres for the primary database and just
partitioned the data properly, as well as separated transactional systems from
data warehouse usage which was very large for that client. UniVerse was trying
to be everything to everyone. The two biggest parts of their UniVerse install
was the data around invoices and products, which also had the most code
written too. And that is where all the Pick guys/gals said only Pick can do
this properly, RDBMS can't handle invoice line items in a performant way etc.
So they wrote really tight query performance requirements for us cause they
wanted it to fail. They really felt it would just fail. My teams experience
was all around large data and distributed systems so we had no issues with
this overall, not that it was easy but we just kept to solid basic
fundamentals and it all worked out well.

The key things we were able to really solve for them was the concurrency and
reliability issues. Like you know these systems struggle with concurrency,
locking and corruption. Corruption is a factor of code quality too, but Pick
made it easy to corrupt data cause the type system sucks too.

The largest project, described above took us a year to get implemented, and
roughly like 6 months between planning and testing (so ~18 months total). It
was not a small undertaking, and our fees alone were pretty significant. To be
fair though, our fees were peanuts compared to the amount of money we were
able to save them, so we never had any pushback on our fees and we were not
inexpensive. We probably could've done it faster to be honest, because 80% of
the system was for known design patterns, e.g. product catalog, invoicing,
customers etc. But a lot of time was spent dealing with the pushback from
their Pick engineers kicking and fighting the whole time and not showing us
pieces of the system trying to sabotage the project. Sadly a few of their Pick
people just imploded and got fired for this behavior, but I will say it was
mostly a small group of them really opposed. A good number of people wanted to
learn new skills and the company was happy to train their people, and we
worked with them to hire a training group that specialized in training
databases etc. In the end, there was still a pretty significant layoff of
people who just couldn't make the transition, which always sucks to see.

~~~
mrsalt
Thanks for sharing your experience. It is interesting to me that it took 18
months. I was expecting a longer time.

In future years we'll probably hear more similar stories from other NoSQL
databases and maybe even SQL databases. Who knows.

