Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Comparing Database Types

prisma.io

Post reply on HN
Print view View original

141–150 of 176 posts

Re: Comparing Database Types

#141

Post by takeda »

zawerf wrote: post #116
asah wrote: post #28

Earlier quoted context omitted.

I've done this (commercially!) with PostgreSQL - just start with a single table, with one JSON field, and as you want performance, integrity, etc, add expression indexes, break out frequently used expressions into columns etc. On large tables, obviously there's a cost for this reorganization but you can partition the data first, and only reorg the most recent data (e.g. range partitioning by time). https://www.google…

I am trying this out and I am still on the edge of whether I like it or not. Create a table with a json column: CREATE TABLE Doc ( id UUID PRIMARY KEY, val JSONB NOT NULL ); Then later it turns out all documents have user_ids so you add a check constraint and an index: ALTER TABLE Doc ADD CONSTRAINT check_doc_val CHECK ( jsonb_typeof(val)='object' AND val ? 'user_id' AND jsonb_typeof(val->'user_id')='string' ); CREAT…

Just a note about using uuid as a primary key. Typically you will use a b-tree index, which likes to keep things sorted. So something like a serial number works best, because it is already sorted and will be appended at the end. Otherwise inserting a new column will cause traversal the b-tree all over the place which will hurt performance it you do a lot of inserts.

If you really want to use uuid and care about performance you might prefix it with something that's increasing like a date, or perhaps (did not try it) use hash index (need to be PG 10+).

Re: Comparing Database Types

#142

Post by takeda »

nailer wrote: post #38

> Relational vs. Document Tabular vs Document. Having relations is orthogonal to the shape of your data. There are document databases with relations - RethinkDB was pretty popular. Mongo sadly doesn't have them but will probably eventually get them too.

The adjective relational in a relational database comes from mathematical relations, tuples i.e. data in tables.

It's common misconception that it is from foreign keys.

Re: Comparing Database Types

#143

Post by hodgesrm »

barrkel wrote: post #122
minitoar wrote: post #96

Earlier quoted context omitted.

That makes sense, they really are just relational databases optimized for certain tasks, with corresponding limitations e.g. they don't support arbitrary joins.

There's nothing intrinsic about not supporting joins, in a columnar store; it's just that you lose a huge amount of the linear scanning performance if you have to do joins for each value. Most columnar stores I've used (primarily Impala, SparkSQL and Clickhouse) all support joins, but they materialize one side of the join as an in-memory hash table, which limits the allowable size of the join, and is a cost multiplie…

The ClickHouse team is working on merge joins which will supplement the currently supported in-memory hash join mechanism. It's not a panacea as you point out, especially on distributed tables. That said it will help with a number of important use cases such as those that require joining a fact table against very large dimension tables.

Re: Comparing Database Types

#144

Post by nikolasburk »

616c wrote: post #104
AlphaWeaver wrote: post #2

This article comes from the team at Prisma, who are doing some really cool work building "database schema management and design with code" tools. They're working on a new version of their library right now (Prisma 2) and are regularly giving updates to the community and providing test versions. Most everything they make is open source and really well designed. Would recommend checking it out!

I am curious about Prisma2 because I tried to build a server side API with v1 as a novice to graph systems and it became an unwieldy nightmare. Partially my fault for wanting to do it without the SaaS they provide but trying to build with something complicated and Apollo on the frontend with a skilled FE dev got me so confused I put it off.

Prisma 1 indeed has a couple of quirks that we're currently ironing out with Prisma 2 (or the "Prisma Framework" as we now call it). Would love to hear from your whether the new version actually solves your pain points!

Feel free to reach out to me: burk@prisma.io or @nikolasburk on the Prisma Slack https://slack.prisma.io

Re: Comparing Database Types

#145

Post by zawerf »

takeda wrote: post #141
zawerf wrote: post #116

Earlier quoted context omitted.

I am trying this out and I am still on the edge of whether I like it or not. Create a table with a json column: CREATE TABLE Doc ( id UUID PRIMARY KEY, val JSONB NOT NULL ); Then later it turns out all documents have user_ids so you add a check constraint and an index: ALTER TABLE Doc ADD CONSTRAINT check_doc_val CHECK ( jsonb_typeof(val)='object' AND val ? 'user_id' AND jsonb_typeof(val->'user_id')='string' ); CREAT…

Just a note about using uuid as a primary key. Typically you will use a b-tree index, which likes to keep things sorted. So something like a serial number works best, because it is already sorted and will be appended at the end. Otherwise inserting a new column will cause traversal the b-tree all over the place which will hurt performance it you do a lot of inserts. If you really want to use uuid and care about perfo…

(We're getting way off topic) but I think the problem with auto increment is that it can't be sharded easily since multiple shards can increment to the same value. If you then try to go back to random ids you're now stuck with 8 bytes which will conflict once every billion items or so. I guess it's pretty extreme premature optimization but I think UUID is nicer for future-proofing at the cost of some performance. (I would love to see benchmarks to know exactly how much performance I am giving up though)

By the way uuidv1 is already prefixed by a timestamp! But unfortunately it doesn't use a sortable version of the time so it doesn't work for clustering the ids into the same page. I think it was really designed for distributed systems where you would want evenly distributed ids anyway.

Re: Comparing Database Types

#146

Post by pas »

Pheebau wrote: post #69
taffer wrote: post #64

Earlier quoted context omitted.

The table is the relation. A join is just an operation that combines two relations to a superrelation.

A join in an intersection in relational algebra

Isn't a full outer join a Descartes product? And thus any other join is just a special case where rows get selected/projected/filtered out?

Re: Comparing Database Types

#147

Post by ifcologne »

imchairmanm wrote: post #101
mrjn wrote: post #100

Earlier quoted context omitted.

(Dgraph author) Particularly embarrassing because I actually know the founders of Prisma ;-). Amazing folks! They even included YugaByte, with only 2.8K GitHub stars. Dgraph crossed 11K GitHub stars and is in the top 10 Graph DBs on DB Engine now -- what would it take for us to be in the article, Søren? Just joking. Nice article! Keep up the good work, guys!

Hey there, I'm responsible for this omission! I've gotten quite a few comments internally and externally so I'll make sure to give Dgraph a mention when I incorporate some of the feedback I've received from this thread. Thanks for the heads up!

What I miss in the conclusion: Multi-model databases

More and more products support multiple data models today.

This reduces the number of technologies in your tech stack and allows to combine different access patterns without the need to duplicate and sync data between systems.

Re: Comparing Database Types

#148

Post by pas »

zawerf wrote: post #145
takeda wrote: post #141

Earlier quoted context omitted.

Just a note about using uuid as a primary key. Typically you will use a b-tree index, which likes to keep things sorted. So something like a serial number works best, because it is already sorted and will be appended at the end. Otherwise inserting a new column will cause traversal the b-tree all over the place which will hurt performance it you do a lot of inserts. If you really want to use uuid and care about perfo…

(We're getting way off topic) but I think the problem with auto increment is that it can't be sharded easily since multiple shards can increment to the same value. If you then try to go back to random ids you're now stuck with 8 bytes which will conflict once every billion items or so. I guess it's pretty extreme premature optimization but I think UUID is nicer for future-proofing at the cost of some performance. (I…

In MySQL/MariaDB/Percona InnoDB Galera every writeable replica has an auto increment offset.

Re: Comparing Database Types

#149

Post by pas »

ucarion wrote: post #36
asah wrote: post #28

Earlier quoted context omitted.

I've done this (commercially!) with PostgreSQL - just start with a single table, with one JSON field, and as you want performance, integrity, etc, add expression indexes, break out frequently used expressions into columns etc. On large tables, obviously there's a cost for this reorganization but you can partition the data first, and only reorg the most recent data (e.g. range partitioning by time). https://www.google…

Do you have any resources online for how well this approach works in practice? I've been thinking about doing this in replacing a MongoDB database.

Could you please elaborate on why do you want to replace MongoDB?

Re: Comparing Database Types

#150

Post by pas »

tabtab wrote: post #23

I'd like to see "dynamic relational" implemented. It's conceptually very similar to existing RDBMS and can use SQL (with some minor variations for comparing more explicitly). You don't have to throw away your RDBMS experience and start over. And you can incrementally "lock it down" so that you get RDBMS-like protections when projects mature. For example, you may add required-field constraints (non-blank) and type con…

You can create indices in MongoDB on JSON, works pretty well.

And since 4.x they have transactions over shards too!

Post reply on HN