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

Database Review 2021

bytebase.com

Post reply on HN
Print view View original

51–60 of 68 posts

Re: Database Review 2021

#51

Post by menaerus »

I concur with author's words here

> and the performance of your product can't be exponentially different from your opponent's, or you won't even have a fight

... but then the same author goes to include the table where Firebolt claims to beat the dust out of Snowflake by a factor of 50x to 6000x.

I am not affiliated with any of those two companies nor any other DB vendor, and the same type of wishful thinking can be found in blogs from pretty much any other DB vendor, but I'd really wish these type of things to become a thing of a past.

I get that they have to sell something to their investors and customers but anybody who knows a thing or two about the domain knows that these type of speed ups aren't possible without trade-offs. People aren't getting any more smarter any time soon.

Re: Database Review 2021

#52

Post by _448 »

Timja wrote: post #2

My prediction: SQLite will keep gaining popularity. Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files? For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in…

Recently I stumbled upon BedrockDB[0] from Expensify. It is based on SQLite and has very interesting idea on HA and distributed DB.

[0] https://bedrockdb.com

Re: Database Review 2021

#53

Post by P5fRxh5kUvp2th »

roncesvalles wrote: post #22
Timja wrote: post #2

My prediction: SQLite will keep gaining popularity. Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files? For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in…

SQLite has seen runaway popularity on HN lately and I bought into the hype too for a while but when I look under the hood, the 3rd party backup and replication stories just seem janky, tedious and not yet mature. It's the kind of thing where a misconfiguration could wipe out everything and/or waste you hours of time. >Especially among pragmatic software builders who run their own business and do not work for the man.…

That's absolutely not true, these solutions have all kinds of costs in terms of training, maintenance, and overall system complexity.

Re: Database Review 2021

#54

Post by tyingq »

AndrewGreen wrote: post #48
tyingq wrote: post #47

Earlier quoted context omitted.

The hard 400kb per item limit does sort of force a pause to make sure it will still work well over the lifetime of the app. Not that huge "rows" are a good idea, but that's roughly 2 pages of text.

I think it's more like 200 pages of text -- 2kB/page is what I've always worked off.

Heh, yep. Missed some magnitude there. Argh. Though still something to consider, like pulling from SQS could use most of the item space for example.

Re: Database Review 2021

#55

Post by noAnswer »

counttheforks wrote: post #40
tehbeard wrote: post #37

Earlier quoted context omitted.

So the solution is pulling down additional gigabytes of images and runtime to run the database?

Yes. It works great.

I manly work as a sysadmin for small companies. (Most people don't call us when they install new tools, they call when they exploded.) All my hatred goes out to the (Windows) programs (and its creators) that think they need MS SQL Express or the likes to save their two bytes of dust. All my love goes to the programs that just run/save from/to a UNC path.

Re: Database Review 2021

#56

Post by hodgesrm »

higeorge13 wrote: post #30

If pg was olap there wouldn’t exist any competition out there. IMO hybrid oltp and olap is what most companies need for their user facing apps and whoever nails it will become #1 in zero time. Ps. I would like to test firebolt, sounds promising.

> If pg was olap there wouldn’t exist any competition out there.

What does this actually mean? If PG were an OLAP database it probably wouldn't have full ACID support, because pure OLAP workloads typically don't do a lot of updates. And it would have a lot of hooks around storage like compression codecs that don't give a lot of value in an OLTP database. Finally it would probably be eventually consistent because it's expensive to ensure data consistency across a large, distributed dataset.

So it wouldn't really be PG at that point.

Re: Database Review 2021

#57

Post by hodgesrm »

intrasight wrote: post #23
mritchie712 wrote: post #10

> It already seems to be an afterthought after Gigapipe and Firebolt debuts. This is referring to Clickhouse Inc. and is way off the mark. If you're looking for hosted Clickhouse, why would a company whose CTO is the creator of Clickhouse be an "afterthought"? Their cloud product offers separation of storage and compute, but you still have an escape hatch to self-hosting if you need it (which you wouldn't have with F…

I hadn't heard of Clickhouse, so had a look. It seems to be an OLAP backing store and not what one would call an OLAP database - because it doesn't support MDX. You have to use Mondrian as the OLAP server middleware. Or am I misunderstanding? And why would one use Clickhouse vs Sql Server as the backing store for OLAP? Is it that much cheaper?

It's both faster and cheaper. ClickHouse gets a lot of migrations off SQL Server. Not every dataset works, of course. As noted elsewhere ClickHouse does not handle complex joins well.

Re: Database Review 2021

#58

Post by sa46 »

abraxas wrote: post #34
higeorge13 wrote: post #30

If pg was olap there wouldn’t exist any competition out there. IMO hybrid oltp and olap is what most companies need for their user facing apps and whoever nails it will become #1 in zero time. Ps. I would like to test firebolt, sounds promising.

I'm not that familiar with the requirements of a big OLAP setup but can you explain why they can't be met by creating a logical replica using something like pg_logical? I understand that the shape of the schema has to be somewhat different for analytics (though I think that point is overstated and many use cases could probably get accomplished with the same table layout but just different index placements)

Because transactional workloads (many small reads and writes that require consistency) look different from analytical workloads (giant reads with a small number of columns with looser consistency guarantees).

OLAP databases typically use a column store which is amazing for reading a subset of columns because of much better compression and use of vectorized execution as opposed to Postgres’ tuple-at-a-time execution. The tradeoff is it’s expensive to update a column store since you have to rewrite a chunk of the column at least.

Postgres is moving towards better OLAP. AlloyDB is a recent commercial DB that swaps out the Postgres storage engine to better support OLAP.

That said, you can coerce Postgres into doing a reasonable job at OLAP for a surprising amount of data.

Re: Database Review 2021

#59

Post by DemocracyFTW2 »

exceptione wrote: post #14
pizza234 wrote: post #8

Earlier quoted context omitted.

> Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. From the FAQ; the are lots of caveats (especially, the last). > Situations Where A Client/Server RDBMS May Work Better > Client/Server Applications > If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instea…

For me an important caveat is the typing. With all respect for the original author of SQLite -- he has done an outstanding job-- I think he underestimates the value of a good typing system. I have seen some databases that had all kinds of messy data. Back in the day MySQL was also quite loose with regards to checking data. Undoing the damage is in most cases not possible. For a business data is more important than co…

As others have pointed out, there's the strict mode now which is still quite restricted (pun intended), but what you most often don't hear is that you can also use check constraints, as in

    sqlite> create table t ( id integer primary key, n integer check ( typeof( n ) = 'integer' ) );
    sqlite> insert into t ( n ) values ( 1 );
    sqlite> insert into t ( n ) values ( '1' );
    sqlite> insert into t ( n ) values ( true );
    sqlite> insert into t ( n ) values ( 'x' );
    Runtime error: CHECK constraint failed: typeof( n ) = 'integer' (19)
    sqlite> select * from t;
    ┌────┬───┐
    │ id │ n │
    ├────┼───┤
    │ 1  │ 1 │
    │ 2  │ 1 │
    │ 3  │ 1 │
    └────┴───┘
    sqlite> select ( select n from t where id = 1 ) = ( select n from t where id = 2 );
    1 // i.e. true
Check constraints do have the advantage over more classical types that additional constraints can be declared such as valid ranges for numerical types etc.

Re: Database Review 2021

#60

Post by TristanBall »

noAnswer wrote: post #55
counttheforks wrote: post #40

Earlier quoted context omitted.

Yes. It works great.

I manly work as a sysadmin for small companies. (Most people don't call us when they install new tools, they call when they exploded.) All my hatred goes out to the (Windows) programs (and its creators) that think they need MS SQL Express or the likes to save their two bytes of dust. All my love goes to the programs that just run/save from/to a UNC path.

Has SMB locking, and OP Locking become that much more reliable?

Pretty much one of the classic desktop support calls for me was "my access database on the shared drive is corrupt"

I'm out touch now, but one of the failure modes seemed to be that a client would take out an oplock, so it could do local caching etc. Then someone else opens the file, the server sends the oplock break to original client but that message gets lost/ignored, and we end up with 2 or more clients now making unsynchronised changes to a file.

Any smb client access to shared data more complicated than documents and spreadsheets just makes me twitch these days.

It was a long time ago now, and it probably was more prevalent in larger environments.. just more chances for things to go wrong I'd guess.

If something is designed for shared filesystems that's different, but my experience was that at the low midrange things aren't. They seem to work, until they don't.

What's wrong with sql express? Assuming you fit within its size constraints?

Post reply on HN