<turbo-stream action="append" target="posts_list"><template>    <div class="postbit" id="352400" data-post-id="352400">
  <section>
    <div class="post-wrap">


					<div class="post-header">
		        <div class="user-avatar">
		          <img alt="pelopo" src="/assets/icons/user-9f439610.png" width="120" height="120" />
		        </div>
					
						<div class="user-details">
		          <div class="user-name">
		            <h3>
                  pelopo
                    <span class="op-star" title="Thread Starter">
                      <img alt="OP" class="op-star-icon" src="/assets/thread-icons/thread-icon-thread-starter-df91e872.png" />
                    </span>
                  </h3>
		          </div>
						
						</div>
					
					</div>

	        <div class="thread-main">
	            <div class="post-body" data-turbo="false">
								<p>thanks <a class="mention" href="/u/rhcarvalho" rel="nofollow">@rhcarvalho</a> - sqlite3_rsync looks very interesting, especially with fact that both ORIGIN and REPLICA can be in a live/running state. This is really good.</p>
<blockquote>
<p>I’d also advise using separate Ecto repositories and database files for your user data and this apparently read-only data dump</p>
</blockquote>
<p>Totally agree. It will make the generation of the actual app data disentangled from the user management. Less headaches. Any sample docs or guides on how to deal with two DBs in the same app and both being accessed by Ecto? I’m not sure how does it work. One <code>Repo</code> with two DBs or do I need to create a second <code>Repo2</code> for that? Thanks</p> 
	            </div>

	            <div class="base-line">
	                <div class="thread-counters">
	                    <span class="thread-count count-likes js-likers-trigger" title="Likes" data-post-id="352400" data-batch-url="/posts/batch_likers">
                        0
                      </span>
                      <!-- <span class="thread-count js-solved-indicator" title="Marked as solution"></span> -->
	                </div>
	                <div class="go-to-post">
	                  <a title="Go to post" alt="Go to post" href="https://forum.elixirforum.com/t/sqlite-based-app-self-hosted-on-vps-architecture-approach-help-needed/68619/12">Post #11</a>
	                </div>
	            </div>
              <div id="likers-container-352400" 
                   class="likers-container"
                   data-first-post="false"
                   data-batch-url="/posts/batch_likers">
                   <div class="likers-placeholder" 
                     data-likers-post-id="352400"
                     data-batch-url="/posts/batch_likers">
                  <div class="post-likers"></div>
                </div>
              </div>
	        </div>
			

    </div>

    <div class="triangle-top-right type-standard-post cat-standard-post" title="Post #11"></div>
  </section>
</div>
    <div class="postbit" id="352401" data-post-id="352401">
  <section>
    <div class="post-wrap">


					<div class="post-header">
		        <div class="user-avatar">
		          <img alt="ruslandoga" src="https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/ruslandoga/120/22193_2.png" width="120" height="120" />
		        </div>
					
						<div class="user-details">
		          <div class="user-name">
		            <h3>
                  ruslandoga
                  </h3>
		          </div>
						
						</div>
					
					</div>

	        <div class="thread-main">
	            <div class="post-body" data-turbo="false">
								<p><img src="https://forum.elixirforum.com/images/emoji/apple/wave.png?v=15" title=":wave:" class="emoji" alt=":wave:" loading="lazy" width="20" height="20"> <a class="mention" href="/u/pelopo" rel="nofollow">@pelopo</a></p>
<blockquote>
<p>1- What are the multiplexed statements?</p>
</blockquote>
<p>If you can guarantee that the database file won’t change, you can open a single connection and execute multiple statements on it concurrently. This mostly means that you don’t need to implement custom pooling strategies and would get pretty good raw performance, limited mostly by SQLite internals.</p>
<blockquote>
<p>2 - Any example of this file watcher or something standard like <strong>LItestream/LiteFS</strong> should be enough?</p>
</blockquote>
<p>I think it’d be a bit different from Litestream/LiteFS. You can poll or subscribe for updates on your local file system or object storage and switch the connection (e.g. by replacing a reference in ETS, the old connection would eventually get garbage collected, once all statements complete, and there are no more references to it) when the changes occur.</p> 
	            </div>

	            <div class="base-line">
	                <div class="thread-counters">
	                    <span class="thread-count count-likes js-likers-trigger" title="Likes" data-post-id="352401" data-batch-url="/posts/batch_likers">
                        0
                      </span>
                      <!-- <span class="thread-count js-solved-indicator" title="Marked as solution"></span> -->
	                </div>
	                <div class="go-to-post">
	                  <a title="Go to post" alt="Go to post" href="https://forum.elixirforum.com/t/sqlite-based-app-self-hosted-on-vps-architecture-approach-help-needed/68619/13">Post #12</a>
	                </div>
	            </div>
              <div id="likers-container-352401" 
                   class="likers-container"
                   data-first-post="false"
                   data-batch-url="/posts/batch_likers">
                   <div class="likers-placeholder" 
                     data-likers-post-id="352401"
                     data-batch-url="/posts/batch_likers">
                  <div class="post-likers"></div>
                </div>
              </div>
	        </div>
			

    </div>

    <div class="triangle-top-right type-standard-post cat-standard-post" title="Post #12"></div>
  </section>
</div>
    <div class="postbit" id="352405" data-post-id="352405">
  <section>
    <div class="post-wrap">


					<div class="post-header">
		        <div class="user-avatar">
		          <img alt="ruslandoga" src="https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/ruslandoga/120/22193_2.png" width="120" height="120" />
		        </div>
					
						<div class="user-details">
		          <div class="user-name">
		            <h3>
                  ruslandoga
                  </h3>
		          </div>
						
						</div>
					
					</div>

	        <div class="thread-main">
	            <div class="post-body" data-turbo="false">
								<blockquote>
<p>Do you think duckdb could be used as a direct substitute for SQLite?</p>
</blockquote>
<p>For simple lookups SQLite is faster in my experience. It’s faster in creating prepared statements, faster with params in prepared statements (DuckDB slows down once a prepared statement contains params), and probably safer too. DuckDB is faster for analytical queries.</p>
<p>DuxDB and DuxDB.Ecto are currently WIP. I wouldn’t recommend them right now. There is <a href="https://github.com/AlexR2D2/duckdbex" class="inline-onebox" rel="noopener nofollow ugc">GitHub - AlexR2D2/duckdbex: The Library embeds C++ DuckDB database into you Elixir application. · GitHub</a> though.</p> 
	            </div>

	            <div class="base-line">
	                <div class="thread-counters">
	                    <span class="thread-count count-likes js-likers-trigger" title="Likes" data-post-id="352405" data-batch-url="/posts/batch_likers">
                        2
                      </span>
                      <!-- <span class="thread-count js-solved-indicator" title="Marked as solution"></span> -->
	                </div>
	                <div class="go-to-post">
	                  <a title="Go to post" alt="Go to post" href="https://forum.elixirforum.com/t/sqlite-based-app-self-hosted-on-vps-architecture-approach-help-needed/68619/14">Post #13</a>
	                </div>
	            </div>
              <div id="likers-container-352405" 
                   class="likers-container"
                   data-first-post="false"
                   data-batch-url="/posts/batch_likers">
                   <div class="likers-placeholder" 
                     data-likers-post-id="352405"
                     data-batch-url="/posts/batch_likers">
                  <div class="post-likers"></div>
                </div>
              </div>
	        </div>
			

    </div>

    <div class="triangle-top-right type-standard-post cat-standard-post" title="Post #13"></div>
  </section>
</div>
    <div class="postbit" id="352413" data-post-id="352413">
  <section>
    <div class="post-wrap">


					<div class="post-header">
		        <div class="user-avatar">
		          <img alt="rhcarvalho" src="https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/rhcarvalho/120/32243_2.png" width="120" height="120" />
		        </div>
					
						<div class="user-details">
		          <div class="user-name">
		            <h3>
                  rhcarvalho
                  </h3>
		          </div>
						
						</div>
					
					</div>

	        <div class="thread-main">
	            <div class="post-body" data-turbo="false">
								<aside class="quote no-group" data-username="pelopo" data-post="12" data-topic="68619">
<div class="title">
<div class="quote-controls"></div>
<img alt="" width="24" height="24" src="https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/p/35a633/48.png" class="avatar"> pelopo:</div>
<blockquote>
<p>sqlite3_rsync looks very interesting</p>
</blockquote>
</aside>
<p>Yes. I have only done small tests with it prior to the official release, but it feels solid just like SQLite itself.</p>
<p>This tool didn’t exist when LiteStream and LiteFS were created (I hugely admire Ben Johnson for those projects and more). And it drove changes to SQLite itself, like being able to query and modify DB page information using SQL: <a href="https://www.sqlite.org/dbpage.html" class="inline-onebox" rel="noopener nofollow ugc">The SQLITE_DBPAGE Virtual Table</a></p>
<p>If those features existed earlier, they would have certainly influenced the designs and implementations of LiteStream, LiteFS and other tools in this space.</p>
<aside class="quote no-group" data-username="pelopo" data-post="12" data-topic="68619">
<div class="title">
<div class="quote-controls"></div>
<img alt="" width="24" height="24" src="https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/p/35a633/48.png" class="avatar"> pelopo:</div>
<blockquote>
<p>Any sample docs or guides on how to deal with two DBs in the same app and both being accessed by Ecto?</p>
</blockquote>
</aside>
<p>In summary, you duplicate config and your App.Repo module, and update <code>application.ex</code> to start both repositories on startup.</p>
<p>I recall some threads here in the forum about e.g. using a database per tenant on SQLite.</p>
<p>The introductory guides probably cover what needs to be done - the difference is you end up with 2 (or more) repositories in your app, and parts of your app explicitly reference one or the other.</p>
<ul>
<li><a href="https://hexdocs.pm/ecto/3.12.5/getting-started.html#adding-ecto-to-an-application" class="inline-onebox" rel="noopener nofollow ugc">Getting Started — Ecto v3.12.5</a></li>
</ul>
<aside class="quote no-group" data-username="pelopo" data-post="12" data-topic="68619">
<div class="title">
<div class="quote-controls"></div>
<img alt="" width="24" height="24" src="https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/p/35a633/48.png" class="avatar"> pelopo:</div>
<blockquote>
<p>One <code>Repo</code> with two DBs or do I need to create a second <code>Repo2</code> for that?</p>
</blockquote>
</aside>
<p>Both are possible. I have practical experience with one Repo per DB. Have seen <code>put_dynamic_repo/1</code> discussed here in the forum for some setups.</p>
<ul>
<li><a href="https://hexdocs.pm/ecto/3.12.5/Ecto.Repo.html#c:put_dynamic_repo/1" class="inline-onebox" rel="noopener nofollow ugc">Ecto.Repo — Ecto v3.12.5</a></li>
<li><a href="https://hexdocs.pm/ecto/3.12.5/replicas-and-dynamic-repositories.html#dynamic-repositories" class="inline-onebox" rel="noopener nofollow ugc">Replicas and dynamic repositories — Ecto v3.12.5</a></li>
</ul>
<p>I particularly like having separate repositories, as code is explicit and straightforward.</p> 
	            </div>

	            <div class="base-line">
	                <div class="thread-counters">
	                    <span class="thread-count count-likes js-likers-trigger" title="Likes" data-post-id="352413" data-batch-url="/posts/batch_likers">
                        2
                      </span>
                      <!-- <span class="thread-count js-solved-indicator" title="Marked as solution"></span> -->
	                </div>
	                <div class="go-to-post">
	                  <a title="Go to post" alt="Go to post" href="https://forum.elixirforum.com/t/sqlite-based-app-self-hosted-on-vps-architecture-approach-help-needed/68619/15">Post #14</a>
	                </div>
	            </div>
              <div id="likers-container-352413" 
                   class="likers-container"
                   data-first-post="false"
                   data-batch-url="/posts/batch_likers">
                   <div class="likers-placeholder" 
                     data-likers-post-id="352413"
                     data-batch-url="/posts/batch_likers">
                  <div class="post-likers"></div>
                </div>
              </div>
	        </div>
			

    </div>

    <div class="triangle-top-right type-standard-post cat-standard-post" title="Post #14"></div>
  </section>
</div>
    <div class="postbit" id="352416" data-post-id="352416">
  <section>
    <div class="post-wrap">


					<div class="post-header">
		        <div class="user-avatar">
		          <img alt="pelopo" src="/assets/icons/user-9f439610.png" width="120" height="120" />
		        </div>
					
						<div class="user-details">
		          <div class="user-name">
		            <h3>
                  pelopo
                    <span class="op-star" title="Thread Starter">
                      <img alt="OP" class="op-star-icon" src="/assets/thread-icons/thread-icon-thread-starter-df91e872.png" />
                    </span>
                  </h3>
		          </div>
						
						</div>
					
					</div>

	        <div class="thread-main">
	            <div class="post-body" data-turbo="false">
								<p>Thanks, that’s very valuable. I was hoping duckdb would be the panacea and the all in one tool for both, analytics/transformation and as a backend. Well, it’s good to hear that SQLite is faster as a backend because it’s very well integrated with Elixir/Phoenix.</p>
<p>I did some experiments with <strong>duckdbex</strong> for reading parquet files. It was good but messy due no support with Ecto.</p>
<p>Anyway, good luck with your own duckdb for Elixir and the Ecto support.</p> 
	            </div>

	            <div class="base-line">
	                <div class="thread-counters">
	                    <span class="thread-count count-likes js-likers-trigger" title="Likes" data-post-id="352416" data-batch-url="/posts/batch_likers">
                        0
                      </span>
                      <!-- <span class="thread-count js-solved-indicator" title="Marked as solution"></span> -->
	                </div>
	                <div class="go-to-post">
	                  <a title="Go to post" alt="Go to post" href="https://forum.elixirforum.com/t/sqlite-based-app-self-hosted-on-vps-architecture-approach-help-needed/68619/16">Post #15</a>
	                </div>
	            </div>
              <div id="likers-container-352416" 
                   class="likers-container"
                   data-first-post="false"
                   data-batch-url="/posts/batch_likers">
                   <div class="likers-placeholder" 
                     data-likers-post-id="352416"
                     data-batch-url="/posts/batch_likers">
                  <div class="post-likers"></div>
                </div>
              </div>
	        </div>
			

    </div>

    <div class="triangle-top-right type-standard-post cat-standard-post" title="Post #15"></div>
  </section>
</div>
    <div class="postbit" id="352417" data-post-id="352417">
  <section>
    <div class="post-wrap">


					<div class="post-header">
		        <div class="user-avatar">
		          <img alt="pelopo" src="/assets/icons/user-9f439610.png" width="120" height="120" />
		        </div>
					
						<div class="user-details">
		          <div class="user-name">
		            <h3>
                  pelopo
                    <span class="op-star" title="Thread Starter">
                      <img alt="OP" class="op-star-icon" src="/assets/thread-icons/thread-icon-thread-starter-df91e872.png" />
                    </span>
                  </h3>
		          </div>
						
						</div>
					
					</div>

	        <div class="thread-main">
	            <div class="post-body" data-turbo="false">
								<p>Thanks. Yes, I feel the two Repos are indeed are easier to deal with. Very clear separation when to use which.</p>
<p>Thanks for the links</p> 
	            </div>

	            <div class="base-line">
	                <div class="thread-counters">
	                    <span class="thread-count count-likes js-likers-trigger" title="Likes" data-post-id="352417" data-batch-url="/posts/batch_likers">
                        0
                      </span>
                      <!-- <span class="thread-count js-solved-indicator" title="Marked as solution"></span> -->
	                </div>
	                <div class="go-to-post">
	                  <a title="Go to post" alt="Go to post" href="https://forum.elixirforum.com/t/sqlite-based-app-self-hosted-on-vps-architecture-approach-help-needed/68619/17">Post #16</a>
	                </div>
	            </div>
              <div id="likers-container-352417" 
                   class="likers-container"
                   data-first-post="false"
                   data-batch-url="/posts/batch_likers">
                   <div class="likers-placeholder" 
                     data-likers-post-id="352417"
                     data-batch-url="/posts/batch_likers">
                  <div class="post-likers"></div>
                </div>
              </div>
	        </div>
			

    </div>

    <div class="triangle-top-right type-standard-post cat-standard-post" title="Post #16"></div>
  </section>
</div>
    <div class="postbit" id="352444" data-post-id="352444">
  <section>
    <div class="post-wrap">


					<div class="post-header">
		        <div class="user-avatar">
		          <img alt="garrison" src="/assets/icons/user-9f439610.png" width="120" height="120" />
		        </div>
					
						<div class="user-details">
		          <div class="user-name">
		            <h3>
                  garrison
                  </h3>
		          </div>
						
						</div>
					
					</div>

	        <div class="thread-main">
	            <div class="post-body" data-turbo="false">
								<aside class="quote no-group" data-username="ruslandoga" data-post="5" data-topic="68619">
<div class="title">
<div class="quote-controls"></div>
<img alt="" width="24" height="24" src="https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/ruslandoga/48/22193_2.png" class="avatar"> ruslandoga:</div>
<blockquote>
<p>Tens of thousands?</p>
</blockquote>
</aside>
<p>I knew the <a href="https://www.sqlite.org/faq.html#q19" rel="noopener nofollow ugc">figures in the faq</a> were outdated, but I didn’t think they were that outdated! Funny, looks like they finally added a disclaimer to that answer just a couple months ago.</p>
<p>I tried this a while ago with Ecto and I seem to recall being a couple of orders of magnitude lower than what you’re getting there, but perhaps Ecto was actually the bottleneck? I assume you’ve done some optimizations of your own, and if so nice work!</p>
<p>Still, 20,000 is extraordinary. I’m surprised your drive can even fsync that fast. Are you using an enterprise ssd, or are drives just that fast now?</p>
<aside class="quote no-group" data-username="pelopo" data-post="16" data-topic="68619">
<div class="title">
<div class="quote-controls"></div>
<img alt="" width="24" height="24" src="https://forum.elixirforum.com/letter_avatar_proxy/v4/letter/p/35a633/48.png" class="avatar"> pelopo:</div>
<blockquote>
<p>I did some experiments with <strong>duckdbex</strong> for reading parquet files. It was good but messy due no support with Ecto.</p>
</blockquote>
</aside>
<p>For DuckDB you can also use the <a href="https://github.com/elixir-explorer/adbc" rel="noopener nofollow ugc">ADBC bindings</a> written for the Explorer project. I believe you can also use it with Explorer if that’s what you’re going for.</p>
<p>But you should stick with SQLite unless you have pretty heavy analytical queries. Once you need DuckDB, you’ll know (because your queries will get very very slow).</p> 
	            </div>

	            <div class="base-line">
	                <div class="thread-counters">
	                    <span class="thread-count count-likes js-likers-trigger" title="Likes" data-post-id="352444" data-batch-url="/posts/batch_likers">
                        1
                      </span>
                      <!-- <span class="thread-count js-solved-indicator" title="Marked as solution"></span> -->
	                </div>
	                <div class="go-to-post">
	                  <a title="Go to post" alt="Go to post" href="https://forum.elixirforum.com/t/sqlite-based-app-self-hosted-on-vps-architecture-approach-help-needed/68619/18">Post #17</a>
	                </div>
	            </div>
              <div id="likers-container-352444" 
                   class="likers-container"
                   data-first-post="false"
                   data-batch-url="/posts/batch_likers">
                   <div class="likers-placeholder" 
                     data-likers-post-id="352444"
                     data-batch-url="/posts/batch_likers">
                  <div class="post-likers"></div>
                </div>
              </div>
	        </div>
			

    </div>

    <div class="triangle-top-right type-last-post cat-last-post" title="Last post!"></div>
  </section>
</div>
</template></turbo-stream><turbo-stream action="replace" target="load-more-container"><template><div id="load-more-container" class="load-more-container">
    <span class="all-loaded">— All posts loaded —</span>
</div></template></turbo-stream>