Postgres SQL vs SQLite vs MS SQL vs MY SQL

[NoviceSortOf]

Curious what advantages if any people are finding working with DBs other than the default SQLLite?

We are considering migrating to MSSQL to avoid kinks/ETL involved with having various DB backends for

office and server applications, but uncertain the additional cost will be worth it or not.

[Avraham Serour]

I do not want to speak ill of sqlite, it is very useful for development, testing and other uses, but in short it is not a fully featured DBMS.

Django can work with many different databases, not only sqlite and MSSQL.

You will have license costs for the database and for the OS, I would personally choose postgres, but it would be valid to use MSSQL if your organization has no problem paying the license for the ultimate version and already have an experienced MS DBA with time to worry about the DB for your application.

What kinds of costs are you worried about?

Avraham

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/42f1c4b9-e75d-4aed-b179-63191b407da0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[NoviceSortOf]

The primary cost is licensing unless we can scale the MS-SQL db projects size and more expensive hosting cost .  

Currently we use PostGresSQL, based in part of my suspicions of the limitations of SQLite. 

Our DB requirements though are complicated by the need to work with Asian languages, Chinese, Japanese and so on as well as European languages.  This makes a relative import/export trivial between various tables and is a major bottleneck. The workstations are Windows, the appeal of Azure is also knocking at the door, being able to off set some admin hardware costs. 

[Vijay Khemlani]

https://sqlite.org/whentouse.html

SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasis scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite does not compete with client/server databases. SQLite competes with fopen().

---

In that regard, for web development, sqlite is not very useful because it is not made for that.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/d8ab0337-d8b9-4c72-9f70-66624acba00f%40googlegroups.com.

[Melvyn Sopacua]

Hi,

On Thursday 22 December 2016 03:56:07 NoviceSortOf wrote:

> Our DB requirements though are complicated by the need to work with
> Asian languages, Chinese, Japanese and so on as well as European
> languages. This makes a relative import/export trivial between
> various tables and is a major bottleneck.

I don't get how languages tie in to choice of DB server software, other
then "can it handle encoding X and Y". This you can research and for
your own sanity, you may want to convert everything to UTF-8 before it
enters the database.
The cost calculation is simple:
What can MSSQL do, that you actually *use* that others cannot.

When you put the emphasis on feature use, a lot of propriety software
becomes schockingly expensive and cheap to work around / do differently
in perspective.

--
Melvyn Sopacua

[graeme]

SQLite is very good for what it is and requires zero admin and no installation, but it cannot scale, has no replication, and cannot run on separate server.

Postgres has robust transaction DDL, which means that if you get a crash in the middle of a migration the change gets reversed. MySQL does not have this, MSSQL used not to do it very well, SQlite does not do it but Django migrations try to emulate it. See here for more on migrations; https://docs.djangoproject.com/en/1.10/topics/migrations/#backend-support

Postgres is also very flexible, feature rich, FOSS, well supported by Django (especially with contrib.postgres )  and well tested with Django. It is even available on Azure as a hosted service from at least two providers. It should be the default choice unless you have strong reasons for using something else. 

[Sundararajan Seshadri]

The situation justifies the data base. There are more data bases (like Oracle and Firebird) than what you have specified. But, let me compare the ones you listed. Same observations apply to the other data bases too.

On the first level comparison, you can say SQLITE is excellent for productivity during development. It is also free. You can live with it if only one or two users are likely to use the system. But if there are more users or more entities (or tables), go for 'regular' RDBMS.

If you are ready to pay consistent with the number of users (and get a consistent support too!), go for MS SQL. But remember, MS SQL will also mean more investment in terms of better hardware. (Note: There is a special 'reduced' version of MS SQL which is 'free' can also be used. But remember to read the licensing condition)

If you want more than SQLITE but not ready to pay money, go for MySQL or Postgre SQL. They are,by nature, without support (except among users, forum etc.) but there are companies which offer paid support for these.) Either of them is fine and almost they are replaceable by each other. But, personally I would vote for Postgrew SQL since this is a little more 'corporate' in nature. (Please do not fight with me - I love MYSQL too. The comparison is like that between PHP and PYTHON. Again, I love both languages!)

I think there was a statement in Django documentation to the effect 'we personally love Postgre!'. May be I am wrong?

So back to my original reply: the situation decides what should be used. 

Food for thought: there are situations where RDBMS are not the best - and they go for 'non-SQL Database'!

---------------------------------------------------------------------------------------------------------------

[Avraham Serour]

if one really wants to pay for suport you can still use postgres and pay to enterpriseDB

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/64d650a0-6bc1-4259-8d08-063fbb66c231%40googlegroups.com.

[Antonis Christofides]

Hi,

I'm using SQLite in production in one application I've made for an eshop hosted by BigCommerce. It gets the orders from the BigCommerce API and formats them on a PDF for printing on labels. It has no models, and all the data is stored in BigCommerce. The only significant data stored in SQLite is the users' names and passwords used for login, by ``django.contrib.auth``. It's hardly three users. Recreating them would be easier than maintaining a PostgreSQL installation. So SQLite it is.

What if your database is small and you don't have many users, but you store mission-critical data in the database? That's a hard one. The thing is, no-one really knows if SQLite is appropriate, because no-one is using it for mission-critical data. Thunderbird doesn't use it for storing emails, but for storing indexes, which can be recreated. Likewise for Firefox. The SQLite people claim it's appropriate for mission-critical applications (https://www.sqlite.org/testing.html), but industry experience on that is practically nonexistent. I've never seen corruption in SQLite. I've seen corruption in PostgreSQL, but we are comparing apples to oranges. I have a gut feeling (but no hard data) that I can trust SQLite more than MySQL.

If I ever choose to use SQLite for mission-critical data, I will make sure I not just backup the database file, but also backup a plain text dump of the database. I trust plain text dumps more than database files in case there is silent corruption that can go unnoticed for some time.

As for MySQL, I never understood why it has become so popular when there's PostgreSQL around. My only explanation is it was marketed better. PostgreSQL is more powerful, it is easier, and it has better documentation. Every now and then I hear of silly MySQL problems that are unheard of in PostgreSQL (the latest I heard is the broken Unicode support, https://mathiasbynens.be/notes/mysql-utf8mb4). If you have a reason to use MySQL, it's probably that you already know it, or that people around you know it (e.g. it is company policy). Same thing with MS SQL. Otherwise PostgreSQL is easily the best option.

Regards,

Antonis Christofides
http://djangodeployment.com

--
You received this message because you are subscribed to the Google Groups "Django users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/42f1c4b9-e75d-4aed-b179-63191b407da0%40googlegroups.com.

[Tim Chase]

On 2016-12-22 03:56, NoviceSortOf wrote:
> Our DB requirements though are complicated by the need to work with
> Asian languages, Chinese, Japanese and so on as well as European
> languages.

If you are genuinely interested in MS-SQL Server, I would spin up a
simple test server and bang against it. Especially when it comes to
these international characters. I've had issues with how it treats
Unicode regardless of my settings and encodings (UTF-8 and UTF-16 can
get truncated mid-sequence, spotty support for anything beyond 16-bit
character ranges, tooling support for such characters, etc). If
you're willing to treat your strings as opaque binary blobs and do
all your character translation in Python, it's a little less
headache.

I used to be more vociferously against MSSQL due to its lack of
OFFSET support, but have since learned that OFFSET is almost always
the wrong solution and keysets+indexes almost always offer a more
performant (and reliable) solution. So while OFFSET is nice for
dev/testing, I now try to avoid it in production.

While MSSQL has some great features, my only reservations now boil
down to licensing: both in terms of monetary cost (especially when
scaling) and availability of F/LOSS connectors. It can be done, but
make sure that the benefits you receive are worth those costs.

I wouldn't touch MySQL with a 10-foot pole thanks to Oracle. Its
sister MariaDB (MySQL-minus-Oracle) has a few benefits, but more
weirdnesses than I like to have when entrusting it with my data
https://grimoire.ca/mysql/choose-something-else

As others have mentioned, sqlite is great for local dev work, but
doesn't scale as well. The classic "sqlite is a replacement for
fopen()" is a good reminder.

-tim