AdamProut

Agents should be driving up the quality of software if we focus them on finding and fixing bugs.

Very cool! pg_duckdb itself is missing fully integrated storage - it can query data stored externally (say in S3) in delta/iceberg formats, but it can't write out data in those formats via transactional writes to PG tables (insert\update\deletes). pg_mooncacke is one neat way of solving that problem. It lets you have a columnstore table in Postgres that can do both reads and writes as if it's any other PG table and have the storage format be an open format like delta/iceberg/etc with that data persisted to blob store (like most cloud DWs would do anyways).

Had a similar thought. Azure Postgres has something similar to pg_parquet (pg_azure_storage), but we're looking into replacing it with pg_duckdb assuming the extension continues to mature.

It would be great if the Postgres community could get behind one good opensource extension for the various columnstore data use cases (querying data stored in an open columnstore format - delta, iceberg, etc. being one of them). pg_duckdb seems to have the best chance at being the goto extension for this.

Checksums can detect a torn page, but not always repair them. It's likely a good part of the database page is gone (i.e., an amount of data that matches the disk / file system atomic write unit size is probably missing). Torn page writes are a pretty common scenario too, so databases need to be able to fully recover from them - not just detect them and report a corruption (ie., just pull the power plug from the machine during a heavy write workload and you're likely to get one - it doesn't require a solar ray to flip a bit :) ).

Maybe good to mention torn pages somewhere too? Both MySQL and Postgres jump through some hoops to both detect them and repair them [1][2]. So, even the scenario in the post where fsync is used to harden writes, the database still needs to handle torn pages (or requires using a file system \ storage that guarantees atomic page writes at the page size the database is using as several managed\cloud databases do).

[1] https://wiki.postgresql.org/wiki/Full_page_writes [2] https://dev.mysql.com/doc/refman/8.0/en/innodb-doublewrite-b...

I'm not sure why you're getting downvoted. I think the pressure to appease Gartner usually starts when companies bring in CEOs whose primary background is enterprise sales. They tend to over value magic quadrant positioning (in my view).

Based on the writings of 2 Turing awards winners for work on databases; No MongoDB doesn't have ACID transactions.

Control theory is also used by databases (probably not as often as it should be). It's great for "self tuning" [1], for example tuning the various cache sizes a database has to maximize throughput under changing workload conditions. Its definitely worth spending the time to understand PID controllers if your an engineer working on databases.

[1] https://www.vldb.org/conf/2006/p1081-storm.pdf

RE: "Distributed SQL offers elasticity (no downtime resize"). I'm not sure this is as much of an advantage of distributed databases vs single host databases anymore. Some of the tech to move virtual machines between machines quickly (without dropping TCP connections) is pretty neat. Neon has a blog post about it here[1]. Aurora Serverless V2 does the same thing (but I can't find a detailed technical blog post talking about how it works). Your still limited by "one big host" but its no longer as big of a deal to scale your compute up/down within that limit.

[1] https://neon.tech/blog/scaling-serverless-postgres

Databricks has kept their Photon[1][2] query engine for Spark closed sourced thus far. Unless EMR has made equivalent changes to the Spark runtime they use Databricks should be much faster. Photon brings the standard vectorized execution techniques used in SQL data warehouses for many years to Spark.

[1] https://docs.databricks.com/en/clusters/photon.html [2] https://dl.acm.org/doi/10.1145/3514221.3526054

yeah, that is part of the trade off. Using an ephemeral SSD (for a database) means the database needs to have another means of making the data durable (replication, storing data in S3, etc.).

There are AWS instance types (I3en) with large and very fast SSDs (many times higher IOPS then EBS).

Its a popular design for SQL Data warehouses. I think almost all of them (snowflake, redshift, etc.) store cold data in S3 and hot data on local disk[1][2].

It works well if the data is stored as immutable files (i.e., A log structure merge tree) or is not index at all (classical columnstores). S3 doesn't provide an efficient way to update a file.

[1] https://dl.acm.org/doi/10.1145/2882903.2903741 (snowflake SIGMOD paper) [2] https://dl.acm.org/doi/10.1145/3514221.3526055 (singlestore SIGMOD paper)

Yeah for workloads with any long running write transactions a single writer design is a pretty big limitation. Say some long running data load (or a big bulk deletion) running along with some faster high throughput key value writes - the big data load would block all the faster key-value writes when it runs.

No "mainstream" database I'm aware of has a global single writer design.

This may depend on if your use case is only vector search in isolation (ANN lookups). In this scenario pgvector is potentially not the best option (https://ann-benchmarks.com/)

That said, using pgvector (or using other SQL databases with vector search support.. many have this capability) will let you do both ANN and your usual SQL filtering and joining (or full text, or.. etc) to produce more hand tuned results to a query. This is something the specialized vector databases don't have much support for.

Yep, SingleStoreDB (formerly MemSQL) is not open source (probably never will be), but it does have many paying customers who have had workloads in production for over a decade at this point.

Also, isn't taking a bet on a very recently launched database as a service based around an open source database also pretty risky? Say for example the very recently launched Clickhouse Inc. service (which your a co-founder of?).

Most SQL Analytical databases don't discourage joins (think BigQuery, Redshift, Snowflake, etc.) and all of them are columnstores. I think discouraging joins is something very specific to Clickhouse, Druid, Pinot and others that have very limited support for joins.

Yep, for writes network bandwidth usage is independent of separation of storage of compute in some sense. Any database that provides high availability is writing over the network somewhere before it acks a transaction committed. It matters who is on the other end of that network write though. Take the typical Cloud DW (i.e., Snowflake) design of forcing writes to the blob storage (shared remote disks) before commit. That is a much higher latency write then what a high performance transaction log replication protocol will do to replicate a write to another host.

There are hybrid designs for separation of storage and compute that are aimed at mixed workloads[1]. They avoid writes to remote storage on transaction commit (i.e., act like a shared nothing databases for commits, but still push data asynchronously to a shared remote disk that can be used for scale up/point in time restores/branching).

[1] https://www.singlestore.com/blog/separating-storage-and-comp...

(disclosure: SingleStoreDB CTO)

Columnstores can do row level access by trading off a bit in terms of compression. If you organize the columnstore files as an LSM tree and use incremental compression schemes (so you don't have to compress too many more rows then the one your after) it can get close to the performance of a B-Tree for point reads - it depends on the specific table schema.

[1] https://dl.acm.org/doi/abs/10.1145/3514221.3526055

I read the article. And yes, this was probably a mistake to engage with you on it.

Yandex N.V. (A dutch holding company of Yandex Russia) is listed as an investor of Clickhouse Inc. This is all I'm stating.

Because half truths like your statements bother me? Yandex is a large investor in clickhouse Inc. (at last as far as public record shows). Have they forced Yandex to divest their holdings?

Yandex (Google of Russia) is a large share holder of Clickhouse Inc[1]. Yes, this maybe a European Yandex subsidiary.. I really don't care to dig, but I think its dishonest to say the company doesn't have Russian backers. Has Clickhouse Inc forced Yandex to divest their large ownership stake?

[1] https://www.crunchbase.com/organization/clickhouse/company_f...

Almost no one does "official" TPC results these days (maybe other then Oracle and some of the Chinese vendors).

Most other cloud DWs have public TPC-H or TPC-DS results that are easily googlable. Clickhouse is missing for a reason...

  - https://research.gigaom.com/report/data-warehouse-cloud-benchmark/
  - https://www.databricks.com/blog/2021/11/02/databricks-sets-official-data-warehousing-performance-record.html
  - https://celerdata.com/blog/starrocks-queries-outperform-clickhouse-apache-druid-and-trino
  - https://aws.amazon.com/blogs/big-data/amazon-redshift-continues-its-price-performance-leadership/

Clickhouse is great a single table analytical queries (Group-by + aggregates + filters over a single table). It can match any top tier columnstore database at this, maybe even better at smaller scales, and if that is all you need it will do the job well

Beyond this, it has a naive query optimizer and limited ability to run distributed joins. This is why you won't find well known analytical benchmark results like TPC-H and TPC-DS for clickhouse vs other SQL data warehouses.

SinglestoreDB has been doing real time analytics much longer then clickhouse and has a much more mature feature set at this point[1][2]. Our go to market is more big enterprise driven so we are definitely much less well known among developers. Revenue share-wise I suspect we are the leader in real time analytics (see disclaimer below - I'm biased but our revenue is reaching thresholds for IPO readiness).

Also, if you follow Jacks later posts you'll see he replaces DynamoDB and Redis with SinglestoreDB as well. Now were getting into places Clickhouse doesn't tread at all...

Disclaimer: I'm one of the cofounders of MemSQL/SingleStoreDB (still working away on making it better all these years later...).

[1] https://www.singlestore.com/blog/the-technical-capabilities-...

[2] https://dl.acm.org/doi/abs/10.1145/3514221.3526055

I'm not sure why this is being downvoted? Dividing up memory statically at startup/compile time has some nice benefits described in the article, but it also makes the database less flexible to changing workload requirements. For example, if a workload wants to do a big hash join that needs 90% of the memory at time X and then a big data load that needs most of the memory in a completely different sub system at time Y - its nice to be able to dynamically shift the memory around to match the workload. Doing static up front allocations seems to block this from happening, and that is maybe a fine trade-off for TigerBeetleDB based on the types of workloads they're optimizing for, but its definitely a valid dis-advantage of their approach.

I was keeping a tally of how many companies were offering "ClickHouse as a service" at one point last year. I think I got up to 7 or 8.

It will be interesting to watch this unfold from a code licensing perspective. Will Clickhouse Inc. move to a more restrictive license to block all these other ClickHouse services?

This is a reasonable benchmark from the Clickhouse folks for single table anlaytical query performance over smallish data sets (10s of GB of data). Most of the DW vendors are on there.

https://benchmark.clickhouse.com/

Timescale apparently lags pretty far behind modern columnstore engines.

We do still use skiplists for in-memory rowstore indexes. They're great for very high throughput writes (less good for scans). In the years since this blog post most of our engineering effort went towards building a columnstore that supports efficient point (few row) read and write queries without giving up the traditional very fast scan performance of a regular columnstore. This is now our default table storage.

Much details in our SIGMOD 2022 paper from June: https://dl.acm.org/doi/abs/10.1145/3514221.3526055

(disclosure: SingleStore/MemSQL CTO)

Yep, lots of real world workloads shard very well. That wasn't my point. My point was that "most" distributed SQL Databases that do sharding will do very well on TPC-C. You can google search for equivalently impressive results for many distributed SQL databases. Each of them can keep adding more warehouses to the TPC-C workload along with more hosts and shards to the distributed SQL database to push the TPC-C TpmC results into the millions.

That's true, I should have been a bit more specific.

Each individual query runs within a single shard as far I recall (its been a few years...). There maybe transactions (like new order) that do multiple queries such that the transaction does read and writes to more then one shard. This doesn't really change how easy the workload is to scale out too much though. Harder to scale workloads require a single query to run across many (often all) shards or require data reshuffling for joins (there is not one good shard key that keeps all joins shard local). In these cases adding more nodes or shards will make each query a bit slower.