From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?UTF-8?Q?G=C3=A1bor_Boskovits?= Subject: Re: postgresql vs sqlite for Cuirass (was Re: Hackathon: Mumi and Cuirass) Date: Fri, 17 May 2019 10:26:24 +0200 Message-ID: References: <875zqrgqlf.fsf@elephly.net> <20190506225554.43e93d0d@alma-ubu> <87imuabhsw.fsf@roquette.mug.biscuolo.net> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="00000000000016ca690589112417" Return-path: Received: from eggs.gnu.org ([209.51.188.92]:57123) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1hRYCN-0000uJ-FG for guix-devel@gnu.org; Fri, 17 May 2019 04:26:40 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1hRYCM-0006dY-0C for guix-devel@gnu.org; Fri, 17 May 2019 04:26:39 -0400 Received: from mail-ed1-x544.google.com ([2a00:1450:4864:20::544]:43815) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1hRYCL-0006a5-Oe for guix-devel@gnu.org; Fri, 17 May 2019 04:26:37 -0400 Received: by mail-ed1-x544.google.com with SMTP id w33so9314594edb.10 for ; Fri, 17 May 2019 01:26:37 -0700 (PDT) In-Reply-To: <87imuabhsw.fsf@roquette.mug.biscuolo.net> List-Id: "Development of GNU Guix and the GNU System distribution." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guix-devel-bounces+gcggd-guix-devel=m.gmane.org@gnu.org Sender: "Guix-devel" To: Giovanni Biscuolo Cc: Guix-devel --00000000000016ca690589112417 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello, Giovanni Biscuolo ezt =C3=ADrta (id=C5=91pont: 2019. m=C3=A1j= . 16., Cs, 16:23): > Hello Bj=C3=B6rn, > > Bj=C3=B6rn H=C3=B6fling writes: > > [...] > > >> 2) Cuirass > > > > On Guix days, we discussed the idea of using Postgresql as a backend. > > Please do you remember the rationale behind this idea? > > This was just an idea to test if we can make the database scalability problem we currently have solved. > What is the issue with using sqlite? > Currently the database response times on sqlite are not acceptable. Any suggestions to overcome this would be appreciated. > Why a client/server approach vs an embedded database? > > I don't have much experience with web applications backed by sqlite, but > AFAIU sqlite is sometime "misunderstood" > > Some interesting arguments from sqlite developer: > https://sqlite.org/whentouse.html#website > --8<---------------cut here---------------start------------->8--- > > Dynamic content uses about 200 SQL statements per webpage. This setup > runs on a single VM that shares a physical server with 23 others and yet > still keeps the load average below 0.1 most of the time. > > --8<---------------cut here---------------end--------------->8--- > > https://sqlite.org/np1queryprob.html > --8<---------------cut here---------------start------------->8--- > > 200 SQL statements per webpage is excessive for client/server database > engines like MySQL, PostgreSQL, or SQL Server. > > But with SQLite, 200 or more SQL statement per webpage is not a problem. > > SQLite can also do large and complex queries efficiently, just like > client/server databases. But SQLite can do many smaller queries > efficiently too. Application developers can use whichever technique > works best for the task at hand. > > [...] > > N+1 Queries Are Not A Problem With SQLite > > --8<---------------cut here---------------end--------------->8--- > > [...] > > Thanks! Gio' > > -- > Giovanni Biscuolo > > Xelera IT Infrastructures > Best regards, g_bor --00000000000016ca690589112417 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello,

Giovanni Bisc= uolo <g@xelera.eu> ezt =C3=ADrta (= id=C5=91pont: 2019. m=C3=A1j. 16., Cs, 16:23):
Hello Bj=C3=B6rn,

Bj=C3=B6rn H=C3=B6fling <bjoern.hoefling@bjoernhoefling.de> writes:
[...]

>> 2) Cuirass
>
> On Guix days, we discussed the idea of using Postgresql as a backend.<= br>
Please do you remember the rationale behind this idea?


This was just an idea to test if we ca= n make the database scalability problem we
currently have solved.=
=C2=A0

Why a client/server approach vs an embedded database?

I don't have much experience with web applications backed by sqlite, bu= t
AFAIU sqlite is sometime "misunderstood"

Some interesting arguments from sqlite developer:
https://sqlite.org/whentouse.html#website
--8<---------------cut here---------------start------------->8---

Dynamic content uses about 200 SQL statements per webpage. This setup
runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.

--8<---------------cut here---------------end--------------->8---

https://sqlite.org/np1queryprob.html
--8<---------------cut here---------------start------------->8---

200 SQL statements per webpage is excessive for client/server database
engines like MySQL, PostgreSQL, or SQL Server.

But with SQLite, 200 or more SQL statement per webpage is not a problem.
SQLite can also do large and complex queries efficiently, just like
client/server databases. But SQLite can do many smaller queries
efficiently too. Application developers can use whichever technique
works best for the task at hand.

[...]

N+1 Queries Are Not A Problem With SQLite

--8<---------------cut here---------------end--------------->8---

[...]

Thanks! Gio'

--
Giovanni Biscuolo

Xelera IT Infrastructures
Best regards,
g_bo= r
--00000000000016ca690589112417--