<story><title>Why PostgreSQL High Availability Matters and How to Achieve It</title><url>https://www.yugabyte.com/postgresql/postgresql-high-availability/</url></story><parent_chain><item><author>endisneigh</author><text>It’s amazing that this isn’t a solved problem, but we have all of this crazy language model stuff.&lt;p&gt;Unfortunately Spanner isn’t open source. Yugabyte and Citus are close but have annoying issues. Cockroach isn’t 100% compatible (and has its own issues) and things like FoundationDB which are truly HA and comparable to Spanner in terms of consistency and fault tolerance are not easily plugged into Postgres as the underlying storage engine since sadly it’s only a key value store.&lt;p&gt;edit: when I say close, I&amp;#x27;m talking strictly about HA, not general functionality.&lt;p&gt;lately I&amp;#x27;ve been thinking of using FoundationDB, which is closest to Spanner in terms of ACID and serializability and mvsqlite.&lt;p&gt;Then, I was thinking, since SQLite doesn&amp;#x27;t have online schema changes (nor does mvsqlite) to have a schema such as:&lt;p&gt;&lt;pre&gt;&lt;code&gt;   [UUID, Data, Version, CreatedAt, UpdatedAt]
&lt;/code&gt;&lt;/pre&gt;
Where Data is a JSON or Proto and Version is an integer. You then could mimic an online schema change by in your application code supporting two adjacent &amp;quot;versions&amp;quot;, and then in an eventually consistent manner run [&lt;i&gt;small&lt;/i&gt;] transactions to update the Data to the new Version as necessary. You would index Version, and UpdatedAt as necessary to find the rows in the table that are not &amp;quot;migrated.&amp;quot;&lt;p&gt;In SQLite you can also create indexes on expressions so technically all of your JSON or Proto could also have indexes.</text></item></parent_chain><comment><author>franckpachot</author><text>Citus is not close to Spanner. No global secondary indexes, no cross-shard ACID (the commit status is eventually consistent). No auto-resharding.
Yugabyte and Cockroach have a spanner-like architecture, but different open-source model and postgres-compatibility. What are those annoying issues?</text></comment>
<story><title>Why PostgreSQL High Availability Matters and How to Achieve It</title><url>https://www.yugabyte.com/postgresql/postgresql-high-availability/</url></story><parent_chain><item><author>endisneigh</author><text>It’s amazing that this isn’t a solved problem, but we have all of this crazy language model stuff.&lt;p&gt;Unfortunately Spanner isn’t open source. Yugabyte and Citus are close but have annoying issues. Cockroach isn’t 100% compatible (and has its own issues) and things like FoundationDB which are truly HA and comparable to Spanner in terms of consistency and fault tolerance are not easily plugged into Postgres as the underlying storage engine since sadly it’s only a key value store.&lt;p&gt;edit: when I say close, I&amp;#x27;m talking strictly about HA, not general functionality.&lt;p&gt;lately I&amp;#x27;ve been thinking of using FoundationDB, which is closest to Spanner in terms of ACID and serializability and mvsqlite.&lt;p&gt;Then, I was thinking, since SQLite doesn&amp;#x27;t have online schema changes (nor does mvsqlite) to have a schema such as:&lt;p&gt;&lt;pre&gt;&lt;code&gt;   [UUID, Data, Version, CreatedAt, UpdatedAt]
&lt;/code&gt;&lt;/pre&gt;
Where Data is a JSON or Proto and Version is an integer. You then could mimic an online schema change by in your application code supporting two adjacent &amp;quot;versions&amp;quot;, and then in an eventually consistent manner run [&lt;i&gt;small&lt;/i&gt;] transactions to update the Data to the new Version as necessary. You would index Version, and UpdatedAt as necessary to find the rows in the table that are not &amp;quot;migrated.&amp;quot;&lt;p&gt;In SQLite you can also create indexes on expressions so technically all of your JSON or Proto could also have indexes.</text></item></parent_chain><comment><author>roncesvalles</author><text>Honestly, just use Spanner. It&amp;#x27;s the perfect database*. If you need to trade freshness for savings, put a cache in front of it.&lt;p&gt;If I were building a new startup in 2023, I would need a mountain of evidence against using Spanner. It&amp;#x27;s ugly that it locks you into GCP but hey an iPhone locks you into Apple&amp;#x27;s ecosystem, that&amp;#x27;s just the price you pay to get good things.&lt;p&gt;* unless you need timeseries, columnar, FTS, geospatial, graph or something special like that</text></comment>