mildbyte

Funnily enough, I did a Sudoku one too (albeit with Poetry) a few years ago: https://github.com/mildbyte/poetry-sudoku-solver

Have you seen duckdb_fdw (https://github.com/alitrack/duckdb_fdw)? IIRC it's built based on sqlite_fdw, but points the outbound queries to DuckDB instead of SQLite, and it does handle running aggregations inside of DuckDB. Could be useful.

Another difference is that this solution uses parquet_fdw, which handles fast scans through Parquet files and filter pushdown via row group pruning, but doesn't vectorize the groupby / join operations above the table scan in the query tree (so you're still using the row-by-row PG query executor in the end).

pg_analytics uses DataFusion (dedicated analytical query engine) to run the entire query, which can achieve orders of magnitude speedups over vanilla PG with indexes on analytical benchmarks like TPC-H. We use the same approach at EDB for our Postgres Lakehouse (I'm part of the team that works on it).

Damn, literally a day after I wrote up my experiments[0] with LLaVA 1.5 and computing image embeddings. Interesting to see the performance with the fine-tuned Mistral-7B variant being pretty close to the one with Vicuna-13B - using Mistral 7B is what BakLLaVA did back with LLaVA 1.5.

[0] https://mildbyte.xyz/blog/llama-cpp-python-llava-gpu-embeddi...

You can indeed run LiteFS by yourself, without Consul, as a sidecar / wrapper around your application. We do it in our project and have a Docker Compose example at [0]. In this case, you specify a specific known leader node. We haven't tried getting it running independently with Consul to do leader election / failover.

[0] https://github.com/splitgraph/seafowl/blob/main/examples/lit...

Thanks for the catch! Fixed.

I mentioned it recently[0], but this looks like a very good topic to plug our new database, Seafowl, that we released last year [1]. It also uses Apache DataFusion (like IOx) and separates storage and compute (like Neon, Snowflake etc) but is designed for client-side Web apps to run analytical SQL queries over HTTP (using semantics that make the query results cacheable by browser caches and CDNs). This makes it really useful for things like interactive visualizations or dashboards.

We're currently doing a lot of work at Splitgraph to reposition the product around this "analytics at the edge" use case, with usage-based billing, eventually moving our query execution from PostgreSQL to Seafowl.

[0] https://news.ycombinator.com/item?id=34175545

[1] https://seafowl.io

why not just accept SQL and cut out all the unnecessary mapping?

You might be interested in what we're building: Seafowl, a database designed for running analytical SQL queries straight from the user's browser, with HTTP CDN-friendly caching [0]. It's a second iteration of the Splitgraph DDN [1] which we built on top of PostgreSQL (Seafowl is much faster for this use case, since it's based on Apache DataFusion + Parquet).

The tradeoff for allowing the client to run any SQL vs a limited API is that PostgREST-style queries have a fairly predictable and low overhead, but aren't as powerful as fully-fledged SQL with aggregations, joins, window functions and CTEs, which have their uses in interactive dashboards to reduce the amount of data that has to be processed on the client.

There's also ROAPI [2] which is a read-only SQL API that you can deploy in front of a database / other data source (though in case of using databases as a data source, it's only for tables that fit in memory).

[0] https://seafowl.io/

[1] https://www.splitgraph.com/connect

[2] https://github.com/roapi/roapi

It's possible! Currently this is running GROUP BY queries using Socrata's query API on the original government data portal. We're adding the ability to import data from these sources into a columnar format in the future, either into Splitgraph itself or syncing the data out into Seafowl (https://seafowl.io/) which uses Parquet and is much faster.

Technically, the ability is already there (you can add a dataset to Splitgraph and select Socrata as a source if you know the dataset ID), but it's not as turnkey as landing on a dataset page and clicking a button. More to come!

It could be the NDJSON parser (DF source: [0]) or could be a variety of other factors. Looking at the ROAPI release archive [1], it doesn't ship with the definitive `columnq` binary from your comment (EDIT: it does, I was looking in the wrong place! https://github.com/roapi/roapi/releases/tag/columnq-cli-v0.3...), so it could also have something to do with compilation-time flags.

FWIW, we use the Parquet format with DataFusion and get very good speeds similar to DuckDB [2], e.g. 1.5s to run a more complex aggregation query `SELECT date_trunc('month', tpep_pickup_datetime) AS month, COUNT(*) AS total_trips, SUM(total_amount) FROM tripdata GROUP BY 1 ORDER BY 1 ASC)` on a 55M row subset of NY Taxi trip data.

[0] https://github.com/apache/arrow-datafusion/blob/master/dataf...

[1] https://github.com/roapi/roapi/releases/tag/roapi-v0.8.0

[2] https://observablehq.com/@seafowl/benchmarks

And if you're looking for a similar experience (very fast analytical SQL queries) but over HTTP, for example, to power a public dashboard or a visualization, you can try ROAPI [0] or Seafowl [1], also built on top of DataFusion (disclaimer: working on Seafowl):

[0] https://github.com/roapi/roapi

[1] https://github.com/splitgraph/seafowl

Great question! With Seafowl, the idea is different from what the modern data stack addresses. It's trying to simplify public-facing Web-based visualizations: apps that need to run analytical queries on large datasets and can be accessed by users all around the world. This is why we made the query API easily cacheable by CDNs and Seafowl itself easy to deploy at the edge, e.g. with Fly.io.

It's a fairly different use case from DuckDB (query execution for Web applications vs fast embedded analytical database for notebooks) and the rest of the modern data stack (which mostly is about analytics internal to a company). Just to clarify, we're not related to IOx directly (only via us both using Apache DataFusion).

If we had to place Seafowl _inside_ of the modern data stack, it'd be mostly a warehouse, but one that is optimized for being queried from the Internet, rather than by a limited set of internal users. Or, a potential use case could be extracting internal data from your warehouse to Seafowl in order to build public applications that use it.

We don't currently ship a Web front-end and so can't serve as a replacement to Superset: it's exposed to the developer as an HTTP API that can be queried directly from the end user's Web browser. But we have some ideas around a frontend component: some kind of a middleware, where the Web app can pre-declare the queries it will need to run at build time and we can compute some pre-aggregations to speed those up at runtime. Currently we recommend querying it with Observable [0] for an end-to-end query + visualization experience (or use a different viz library like d3/Vega).

Re: the second question about Splitgraph for a data lake, the intention behind Splitgraph is to orchestrate all those tools and there the use case is indeed the modern data stack in a box. It's kind of similar to dbt Labs's Sinter [1] which was supposed to be the end-to-end data platform before they focused on dbt and dbt Cloud instead: being able to run Airbyte ingestion, dbt transformations, be a data warehouse (using PostgreSQL and a columnar store extension), let users organize and discover data at the same time. There's a lot of baggage in Splitgraph though, as we moved through a few iterations of the product (first Git/Docker for data, then a platform for the modern data stack). Currently we're thinking about how to best integrate Splitgraph and Seafowl in order to build a managed pay-as-you-go Seafowl, kind of like Fauna [2] for analytics.

Hope this helps!

[0] https://observablehq.com/@seafowl/interactive-visualization-...

[1] https://www.getdbt.com/blog/whats-in-a-name/

[2] https://fauna.com/

Just wanted to also give a shout out to Apache DataFusion[0] that IOx relies on a lot (and contributes to as well!).

It's a framework for writing query engines in Rust that takes care of a lot of heavy lifting around parsing SQL, type casting, constructing and transforming query plans and optimizing them. It's pluggable, making it easy to write custom data sources, optimizer rules, query nodes etc.

It's has very good single-node performance (there's even a way to compile it with SIMD support) and Ballista [1] extends that to build it into a distributed query engine.

Plenty of other projects use it besides IOx, including VegaFusion, ROAPI, Cube.js's preaggregation store. We're heavily using it to build Seafowl [2], an analytical database that's optimized for running SQL queries directly from the user's browser (caching, CDNs, low latency, some WASM support, all that fun stuff).

[0] https://github.com/apache/arrow-datafusion

[1] https://github.com/apache/arrow-ballista

[2] https://github.com/splitgraph/seafowl

Just to follow up on this, I got it working, thanks for the help! (for anyone else interested in all the details of getting LiteFS to run with Docker Compose + a Rust SQLite client: https://github.com/splitgraph/seafowl/tree/main/examples/lit...)

We managed to get it working for analytical SQL queries [1] for our database. It's kind of questionable whether this is an intended use of Cloudflare, but it feels like it, since we use it for query results, which are essentially a static asset in our use case (dashboards and visualizations).

[1] https://www.splitgraph.com/blog/seafowl-sql-cdn

Hey HN,

A new project from us at Splitgraph: Seafowl, a database that's optimized for Web applications running analytical SQL queries straight from the user's browser. Used to power interactive visualizations and dashboards. Features:

- Fast: written in Rust and uses Apache DataFusion. About 5-10x faster than PostgreSQL (some benchmarks available at [1])

- Light: single 50MB binary that starts in 10ms

- Extensible: write user-defined functions in anything that compiles to WASM

- Cache-friendly: REST API designed to work well with CDNs like Cloudflare or caches like Varnish (as well as the user's browser cache).

- Demo of Seafowl providing data to an Observable notebook here [2] (press F12 and refresh the page to see caching in action)

Happy to answer any questions!

[1] https://observablehq.com/@seafowl/benchmarks

[2] https://observablehq.com/@seafowl/interactive-visualization-...

Ah, sweet (and thanks for building Litestream/LiteFS)! This should work great for us, will definitely try to get a PoC going with this.

The live replication (as it used to work in Litestream before the LiteFS move, without Consul) would have been perfect for our use case with Seafowl (I played around with Litestream before that but had to settle on PostgreSQL for the sample multi-node deployment [0]):

- rare writes that get directed to a single instance (e.g. using Fly.io's replay header), frequent reads (potentially at edge locations)

- no need to deploy a PostgreSQL cluster and set up logical replication

- SQLite database stored in object storage, reader replicas can boot up using the object storage copy and then get kept in sync by pulling data from the writer

- delay in replication is fine

LiteFS is probably going to be a great solution here since we're mainly using Fly.io and it has built-in support for it [1], but are there any alternatives that don't require Consul, still look like an SQLite database to the client and can work off of a HTTP connection to the primary, so that we don't have to require our users to deploy to Fly?

[0] https://seafowl.io/docs/guides/scaling-multiple-nodes

[1] https://fly.io/docs/litefs/getting-started/

Kind of off-topic, but this person's e-mail address is "dot at dotat dot at", which is very unique (see https://dotat.at/email.html).

Hey, I co-built Seafowl, thanks for the plug!

To clarify, Seafowl itself can't be hosted statically (it's a Rust server-side application), but it works well for statically hosted pages. It's basically designed to run analytical SQL queries over HTTP, with caching by a CDN/Varnish for SQL query results. The Web page user downloads just the query result rather than required fragments of the database (which, if you're running aggregation queries, might have to scan through a large part of it).

The "running WASM on the server" use case is pretty fun and applies to whenever you want to execute untrusted user code and limit its capabilities / resources, e.g. for plugins. For example, we use it in Seafowl [1] for user-defined functions, so does ScyllaDB [2]. Cloudflare Workers [3] lets you deploy arbitrary WASM code at the edge and pay at high granularities just for the amount of time your function spends executing (e.g. 10ms is the limit for the free tier).

In addition, you can limit the amount of instructions [4] a certain subroutine is allowed to use up before halting.

Also, WASM subroutines can only call out to functions (including system calls) that you allow them to (they're sandboxed otherwise). By default, they can't open files / print to stdout, unless the module can import the relevant functions (which WASI [5] provides).

[1] https://www.splitgraph.com/docs/seafowl/guides/custom-udf-wa...

[2] https://www.scylladb.com/2022/04/14/wasmtime/

[3] https://blog.cloudflare.com/announcing-wasi-on-workers/

[4] https://docs.wasmtime.dev/api/wasmtime/trait.ResourceLimiter...

[5] https://wasi.dev/

We might have what you're looking for with sgr [0] (I'm the co-founder) which works by running SQL commands on top of a PostgreSQL instance and uses PostgreSQL audit triggers for versioning, though note it currently only works on top of a custom PostgreSQL Docker image, since it requires some extensions.

[0] https://www.splitgraph.com/docs/sgr-advanced/getting-started...

Another similar option is cstore_fdw [0] -- it's now part of Citus but can still be used standalone as a foreign data wrapper. We use it at my startup to do OLAP on Postgres. It has some advantages on parquet_fdw:

* Supports writes (actually generating Parquet files was also difficult in my testing: I used odbc2parquet [1] but thanks for the ogr2ogr tip!) so you can write directly to the foreign table by running INSERT INTO ... SELECT FROM ...

* Supports all PG datatypes (including types from extensions, like PostGIS)

* Performance was basically comparable in my limited testing (faster for a single-row SELECT with cstore_fdw in our case since we do partition pruning, same for a full-table scan-and-aggregation).

Re: performance overhead, with FDWs we have to re-munge the data into PostgreSQL's internal row-oriented TupleSlot format again. Postgres also doesn't run aggregations that can take advantage of the columnar format (e.g. CPU vectorization). Citus had some experimental code to get that working [2], but that was before FDWs supported aggregation pushdown. Nowadays it might be possible to basically have an FDW that hooks into the GROUP BY execution and runs a faster version of the aggregation that's optimized for columnar storage. We have a blog post series [3] about how we added agg pushdown support to Multicorn -- similar idea.

There's also DuckDB which obliterates both of these options when it comes to performance. In my (again limited, not very scientific) benchmarking of on a customer's 3M row table [4] (278MB in cstore_fdw, 140MB in Parquet), I see a 10-20x (1/2s -> 0.1/0.2s) speedup on some basic aggregation queries when querying a Parquet file with DuckDB as opposed to using cstore_fdw/parquet_fdw.

I think the dream is being able to use DuckDB from within a FDW as an OLAP query engine for PostgreSQL. duckdb_fdw [5] exists, but it basically took sqlite_fdw and connected it to DuckDB's SQLite interface, which means that a lot of operations get lost in translation and aren't pushed down to DuckDB, so it's not much better than plain parquet_fdw. I had a complex query in the PG dialect generated with dbt that used joins, CTEs and window functions. I don't remember the exact timings, but it was even slower on duckdb_fdw than with cstore_fdw, whereas I could take the same query and run it on DuckDB verbatim, only replacing the foreign table name with the Parquet filename.

This comment is already getting too long, but FDWs can indeed participate in partitions! There's this blog post that I keep meaning to implement where the setup is, a "coordinator" PG instance has a partitioned table, where each partition is a postgres_fdw foreign table that proxies to a "data" PG instance. The "coordinator" node doesn't store any data and only gathers execution results from the "data" nodes. In the article, the "data" nodes store plain old PG tables, but I don't think there's anything preventing them from being parquet_fdw/cstore_fdw tables instead.

[0] https://github.com/citusdata/cstore_fdw

[1] https://github.com/pacman82/odbc2parquet

[2] https://github.com/citusdata/postgres_vectorization_test

[3] https://www.splitgraph.com/blog/postgresql-fdw-aggregation-p...

[4] https://www.splitgraph.com/trase/supply-chains

[5] https://github.com/alitrack/duckdb_fdw

[6] https://swarm64.com/post/scaling-elastic-postgres-cluster/

The other founder of Splitgraph here! I've been experimenting recently with this, since we want users to be able to write to Splitgraph images efficiently without having to turn them into PG tables and then re-snapshot them.

My setup is similar to your cold-hot idea (diagram [1]), where the cold layer uses our "layered querying" FDW. A scan through it is basically a scan through a UNION of cstore_fdw files (columnar format) and we can use the object metadata to determine which objects to download/scan through. If the constraints don't match the object boundaries at all, the scan returns empty. This emulates PG partitions but more dynamically (you can change them without a full table lock).

This means that we can have a view on top of the "hot" and the "cold" blocks that will query both of them, but exclude the "cold" OLAP layer most of the time. We can also redirect writes to the view to hit the "hot" OLTP layer instead using `INSTEAD OF` triggers. In my PoC, I record inserts/updates/deletes in that table and then collapse them into one action per PK using a window function, but if the table is append-only, this should be much easier.

I'm not completely sure re: the locks at changeover/flush time. The idea is that we can create a new object (up to some bookmark) in one transaction, attach it to the Splitgraph table in the second transaction, then truncate the "hot" table up to the same bookmark in the final transaction. Duplicate rows can be eliminated by the view, so the final table will look consistent between transactions.

Re: "using a foreign table as a partition of a local parent table", I think it's actually possible, even though we don't use it here: there's a cool setup in [3] where they use it to shard a table onto multiple worker nodes (the coordinator node has the partitioned table, each partition points to a postgres_fdw shim on the same machine that then points to the actual physical partition on the worker).

We've had some ideas around using this for distributed querying: in our case, each node responsible for a given partition of a dataset would be able to download just the objects in that partition on the fly (though constraint pruning), so we wouldn't need to knowingly seed each worker with data. Interesting to think about, though at some point it feels like reinventing Presto/Snowflake/Spark.

Hope this helps!

[1] https://imgur.com/a/1p394PI

[2] https://www.splitgraph.com/docs/large-datasets/layered-query...

[3] https://swarm64.com/post/scaling-elastic-postgres-cluster/

Splitgraph (https://www.splitgraph.com) | Remote | Full-time

Splitgraph is reshaping how organizations interact with data. We provide a unified interface to discover and query data. In practice, this means we're building a data catalog (a web app) and query layer (implemented with the Postgres wire protocol).

We're a seed-stage, venture-funded startup hiring our initial team. The two co-founders are looking to grow the team by adding multiple engineers across the stack. This is an opportunity to make a big impact on an agile team while working closely with the founders.

Splitgraph is a remote-first organization. The founders are based in the UK, and the company is incorporated in both USA and UK. Candidates are welcome to apply from any geography. We want to work with the most talented, thoughtful and productive engineers in the world.

Open positions:

* Senior Software Engineer - Frontend. Responsible for the web stack, mainly involving Typescript, React, Next.js, Postgraphile, etc.

* Senior Software Engineer - Backend. Responsible for a variety of core services, using Python, PostgreSQL, C, Lua, and a ton of other technologies.

Learn more & apply: https://www.notion.so/splitgraph/Splitgraph-is-Hiring-25b421...

Splitgraph (https://www.splitgraph.com) | Remote | Full-time

Splitgraph is reshaping how organizations interact with data. We provide a unified interface to discover and query data. In practice, this means we're building a data catalog (a web app) and query layer (implemented with the Postgres wire protocol).

We're a seed-stage, venture-funded startup hiring our initial team. The two co-founders are looking to grow the team by adding multiple engineers across the stack. This is an opportunity to make a big impact on an agile team while working closely with the founders.

Splitgraph is a remote-first organization. The founders are based in the UK, and the company is incorporated in both USA and UK. Candidates are welcome to apply from any geography. We want to work with the most talented, thoughtful and productive engineers in the world.

Open positions:

* Senior Software Engineer - Frontend. Responsible for the web stack, mainly involving Typescript, React, Next.js, Postgraphile, etc.

* Senior Software Engineer - Backend. Responsible for a variety of core services, using Python, PostgreSQL, C, Lua, and a ton of other technologies.

Learn more & apply: https://www.notion.so/splitgraph/Splitgraph-is-Hiring-25b421...

We're building something that partially overlaps with this at Splitgraph [0] (co-founder here). Instead of bitemporal tables, we're using columnar storage (cstore_fdw) + delta compression as a storage backend and support Git-like operations (commit, checkout, etc...). We also let you build datasets with a Dockerfile-like language as well as share them with other Splitgraph peers or the public Splitgraph catalog.

[0] https://www.splitgraph.com/docs/getting-started/introduction

We use it in anger at Splitgraph [0] to let people upload/download datasets and Postgres table snapshots (instead of storing them directly in S3).

Pros:

* Less platform-dependent. By self-managing it, we can also run deploys to GCP / Azure / Scaleway / other providers without writing a separate adapter for e.g. Azure Blob Storage.

* Python API [1] much more pleasant to use than boto3 (and can speak to normal S3). It doesn't do everything that boto3 does, but it supports everything we need (e.g. pre-signed URLs).

* minio server itself supports a large chunk of S3's functionality (e.g. SELECT API / AssumeRole / bucket versioning)

* Don't pay per request and for egress: this was a big deal since people might want to download large amounts of data from us (or make a bunch of small requests to download/upload a subset of data).

Cons:

* Have to manage own infrastructure. We run it on managed VMs so it's semi-managed, but we still have to provision block storage, set up backup policies etc.

* In a similar vein, scaling and availability all have to be DIY [2]. We haven't run into situations yet where Minio would be the bottleneck, but it might be something to keep in mind.

* Obviously not as seamless: you don't get things like Glacier or integration with other IAM.

[0] https://www.splitgraph.com/

[1] https://github.com/minio/minio-py

[2] https://docs.min.io/docs/distributed-minio-quickstart-guide....

We have something that might work for your use case with Splitgraph [0] (co-founder here)!

Basically, it's a custom version of PostgreSQL that supports git + Docker-like operations (versioning, sharing, branching, delta compression, Dockerfiles for datasets etc). It's not in-memory but it runs in Docker and has some tooling built around it to make it simple to spin up and manage a throwaway DB for integration test purposes.

Here's an example of it in action in use cases like machine learning [1] where we can switch between different versions (test/train) of the dataset without having to change the client application.

[0] https://github.com/splitgraph/splitgraph

[1] https://github.com/splitgraph/splitgraph/blob/master/example...

Couple reasons:

We don't actually have a massive PostgreSQL instance with all the datasets: we store them in object storage using cstore_fdw. In addition, we can have multiple versions of the same dataset. Basically, when a REST query comes in, we build a schema made out of "shim" tables powered by our FDW [1] that dynamically loads table regions from object storage and point the PostgREST instance to that schema at runtime.

When we were writing this, PostgREST didn't support working against multiple schemas (I think it does now but it still only does introspection once at startup), so we made a change to PostgREST code to treat the first part of the HTTP route as the schema and make it lazily crawl the new schema on demand.

Also, at startup, PostgREST introspects the whole database to find, besides tables and their schemas, also FK relations between tables. This is so that you can grab an entity and other entities related to it by FK with a single query [2]. In our case, we might have thousands of these "shim" tables in a database, pointing to actual datasets, so this introspection takes a lot of time (IIRC it does a giant join involving pg_class, pg_attribute and pg_constraint?). We don't support FK constraints between different Splitgraph datasets anyway, so we removed that code in our fork for now.

[1] https://www.splitgraph.com/docs/large-datasets/layered-query...

[2] https://postgrest.org/en/v7.0.0/api.html#resource-embedding