Database Review 2021 • Hacker News Board

Re: Database Review 2021

#11

Post by mritchie712 » Fri, Nov 11, 2022, 11:53 AM UTC

stevefan1999 wrote: post #7

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…

The very problem of SQLite: Single user only. Although SQLite does have WAL but it still doesn't allow you to do concurrent write unless you want to see file corruption. This means SQLite is very much locked to things that works with one specific purpose and almost nothing else. Sure, you can be read-only, but you have to run alongside the app in the specific node, too. Another problem (although without solving the s…

why would you do all this work when postgres sorted this all out over a decade ago?

Re: Database Review 2021

#12

Post by gwd » Fri, Nov 11, 2022, 12:00 PM UTC

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…

> When I copy the DB over to another system, I don't need that table. So it would be nice to have like primary.db and secondary.db. With 'details' in secondary.db. Any downside to this approach? Are JOINS slower across two files than across two tables in the same file?

I'm in the middle of refactoring my personal project such that "shared" data is in one database, and "personal" data is in a separate database; the idea being that every user will have a separate SQLite "connection", with their own "personal" data ATTACHed. I had reasonably extensive functional testing before the refactor, and after the refactor I didn't have any issues from a functional perspective.

Potential advantages:

- Each user can download their own "personal" database whenever they want

- This is essentially a form of "sharding", which should go a long way towards mitigating the "single writer" bottleneck; as the "shared data" will change much less frequently than the "personal" data. It should also make it fairly straightforward to distribute the workload across multiple servers / regions, should my project ever get that big.

Haven't done any performance testing yet.

Main issues I've encountered so far:

- Foreign key constraints across the databases is missing; that's just a reduction in safety, however.

- Golang's "automatic connection management" doesn't play well with SQLite's "ATTACH" command: it expect to automatically open new connections, but the secondary connections won't have the ATTACHed databases. This is solvable, but something to watch out for.

As implied, I'm still in the middle of changing things over, so it's early days; but so far things seem positive.

Re: Database Review 2021

#13

Post by century19 » Fri, Nov 11, 2022, 12:14 PM UTC

dottedmag wrote: post #3

This is a review of companies making databases, not the databases themselves.

Correct. And Databricks/Spark is not really a database, it's a processing engine which can connect to many databases.

Re: Database Review 2021

#14

Post by exceptione » Fri, Nov 11, 2022, 12:15 PM UTC

pizza234 wrote: post #8

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…

> 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 code, so be strict up front.

I know, SQLite has added the option to enforce type checking. The authors still don´t believe in the value of it and the available types are quite limited and thus loose. I think this is something that pgsql got quite right, where you can have your domain types on the database level.

On the other hand, if you keep this as a replacement for your config file ( I thought this was the original purpose?), then yeah, you get an awesome deal. I wouldn't dare to build my business on it, just like I don´t believe in MongoDb and any untyped language for serious purposes.

Re: Database Review 2021

#15

Post by collyw » Fri, Nov 11, 2022, 12:22 PM UTC

mritchie712 wrote: post #11

stevefan1999 wrote: post #7

Earlier quoted context omitted.

The very problem of SQLite: Single user only. Although SQLite does have WAL but it still doesn't allow you to do concurrent write unless you want to see file corruption. This means SQLite is very much locked to things that works with one specific purpose and almost nothing else. Sure, you can be read-only, but you have to run alongside the app in the specific node, too. Another problem (although without solving the s…

why would you do all this work when postgres sorted this all out over a decade ago?

Setting up Postgres is a PITA compared to SQLite. It comes bundled with python these days. Obviously it's going to be a trade off as to which one causes you more pain.

Re: Database Review 2021

#16

Post by phyrex » Fri, Nov 11, 2022, 12:26 PM UTC

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…

SQLite has strict mode now

Re: Database Review 2021

#17

Post by exceptione » Fri, Nov 11, 2022, 12:28 PM UTC

phyrex wrote: post #16

exceptione wrote: post #14

Earlier quoted context omitted.

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…

SQLite has strict mode now

I think I mentioned that, or I don´t understand what you mean.

Re: Database Review 2021

#18

Post by mritchie712 » Fri, Nov 11, 2022, 12:34 PM UTC

collyw wrote: post #15

mritchie712 wrote: post #11

Earlier quoted context omitted.

why would you do all this work when postgres sorted this all out over a decade ago?

Setting up Postgres is a PITA compared to SQLite. It comes bundled with python these days. Obviously it's going to be a trade off as to which one causes you more pain.

agreed, running pg locally is a pain. I use a cloud postgres instance (even for local dev). They're dirt cheap and it's not worth the hassle of working with a local pg.

Re: Database Review 2021

#19

Post by nemo44x » Fri, Nov 11, 2022, 12:37 PM UTC

> There will be a new database coming out, and the main selling point will be developer workflow.

I think Xata is headed towards this. I’ve played with it a bit and it has some potential imo. The branching feature could be quite useful.

Re: Database Review 2021

#20

Post by heurisko » Fri, Nov 11, 2022, 12:37 PM UTC

collyw wrote: post #15

mritchie712 wrote: post #11

Earlier quoted context omitted.

why would you do all this work when postgres sorted this all out over a decade ago?

Setting up Postgres is a PITA compared to SQLite. It comes bundled with python these days. Obviously it's going to be a trade off as to which one causes you more pain.

No docker access? postgres images are available in seconds.