4 ms·

For JOINs? Absolutely! Who wants to hand-code queries at the executor level?! It's expensive! You need a query language. You don't necessarily need ACID, a

For JOINs? Absolutely! Who wants to hand-code queries at the executor level?! It's expensive! You need a query language. You don't necessarily need ACID, and you don't necessarily need a bunch of things that SQL RDBMSes give you, but you definitely need a QL, and it has to support a lot of what SQL supports, especially JOINs and GROUP BY w/ aggregations. NoSQLs tend to evolve into having a QL layered on top. Just start with that if you really want to build a NoSQL.

otterley 1y ago
To be clear here, I'm not arguing that OpenSearch/ElasticSearch is an adequate substitute for Postgres. They're different databases, each with different strengths and weaknesses. If you need JOINs and ACID compliance, you should use Postgres. And if you need distributed search, you should use OpenSearch/ElasticSearch. Unless they're building for single-host scale, you're not going to get JOINs for free. Lucene (the engine upon which ES/OS is based) already has JOIN capability. But it's not used in ES/OS because the performance of JOINs is absolutely abysmal in distributed databases.
- philippemnoel 1y ago
  Our customers typically deploy ParadeDB in a primary-replicas topology, with one primary Postgres node and 2 or more read replicas, depending on read volume. Queries are executed on a single node today, yes. We have plans to eventually support distributed queries.
- cryptonector 1y ago
  I'm arguing that sometimes you don't need ACID, or rather, sometimes you accept that ACID is too painful so you accept not having ACID, but no one ever really doesn't want a QL -- they only think that they don't want a QL until they learn better. I.e., NoACID does not imply NoQueryLanguage, and you can always have a QL, so you should always get a QL, and you should always use a QL. > Unless they're building for single-host scale, you're not going to get JOINs for free. If by 'free' you mean not having to code them, then that's wrong. You can always have or implement a QL. If by 'free' you mean 'performant', then yes, you might have to denormalize your data so that JOINs vanish, though at the cost of write amplification. But so what, that's true whether you use a QL or not -- it's true in SQL RDBMSes too.
derefr 1y ago
It's funny; as someone who is exactly pg_search's market, I actually often want the opposite: ACID, MVCC transactions, automatic table and index management... but no query language. At the data scale + level of complexity our OLAP queries operate at, we very often run into situations where Postgres's very best plan [with a well-considered schema, with great indexes and statistics, and after tons of tuning and coaxing], still does something literally interminable — not for any semantic reason to do with the query plan, but rather due to how Postgres's architecture executes the query plan[1]. The last such job, I thought would be simple enough to run in a few hours... I let it run for six days[2], and then gave up and killed it. Whereas, when we encoded the same "query plan" as a series of bulk-primitive ETL steps by: 1. dumping the raw source data from PG to CSV with a `COPY`, 2. whipping out simple POSIX CLI tools like sort/uniq/grep/awk (plus a few hand-rolled streaming aggregation scripts) to transform/reduce/normalize the source data into the shape we want it in, 3. and then loading the resulting CSVs back into PG with another `COPY`, ...then the runtime of the whole operation was reduced to just a few hours, with the individual steps completing in ~30 minutes each. (And that's despite the overhead of parsing and/or emitting non-string fields from/to CSV with almost every intermediate step!) Honestly, if Postgres would just let us program it the way one programs e.g. Redis through Lua, or ETS tables in Erlang — where the tables and indices are ADTs with low-level public APIs, and you set up your own "query plan" as a set of streaming-channel actors making calls to these APIs — then we would be a lot happier. But even in PL/pgSQL (which we do use, here and there), the only APIs are high-level ones. • Sure, you can get a cursor on a query; but you can't e.g. get an LMDB-like B-tree cursor on a target B-tree index, and ask it to jump [i.e. re-nav down from root] or walk [i.e. nav up from current pos to nearest common ancestor then back down] to "the first row-tuple greater-than-or-equal to [key]". • You can't write your own efficient implementation of TABLESAMPLE semantics to set up your own Bigtable-esque balanced cluster-order-partitioned parallel seq scan. • You can't collect pointers to row-tuples, partially materialize them, filter them by some criterion on the read (but perhaps not parsed!) columns, and then more-fully materialize those same row-tuples "directly" from the references to them you still hold. --- [1] One example of what I mean by "execution": did you know that Postgres doesn't use any form of concurrency for query plans — not even the most basic libuv-like "This Merge Append node's child-node A is in a blocking-wait on IO; that blocking-wait should yield, so that the Merge Append node's child-node B can instead send row-tuple batches for a while" kind of concurrency? --- [2] If you're wondering, the query that ran for six days was literally just this (anonymized): SELECT a, b, SUM(value) AS total_value FROM ( SELECT a, b, value FROM source1 UNION ALL SELECT a, b, value FROM source2 ) AS u GROUP BY a, b; `source1` and `source2` are ~150GB tables. (Or at least, they're 150GB when dumped to CSV.) Two integer keys (a,b), and a bigint value. With a b-tree index on `(a,b) INCLUDE (value)`, with correct statistics. And its EXPLAIN query plan looked like this (with `SET enable_hashagg = OFF;`) — nominally pretty good: GroupAggregate (cost=1.17..709462419.92 rows=40000 width=40) Group Key: a, b -> Merge Append (cost=1.17..659276497.84 rows=6691282944 width=16) Sort Key: a, b -> Index Only Scan using source1_a_b_idx on source1 (cost=0.58..162356175.31 rows=3345641472 width=16) -> Index Only Scan using source2_a_b_idx on source2 (cost=0.58..162356175.31 rows=3345641472 width=16) Each one of the operations here is "obvious." It's what you'd think you'd want! You'd think this would finish quickly. And yet. (And no, the machine it ran on was not resource-bottlenecked. It had 1TB of RAM with no contention from other jobs, and this PG session was allowed to use much of it as work memory. But even if it was spilling to disk at every step... that should have been fine. The CSV equivalent of this inherently "spills to disk", for everything except the nursery levels of sort(1)'s merge-sort. And it does fine.)
strbean 1y ago
Obligatory whine that the term NoSQL got co-opted to mean "no relational". There's tons of space for a better query language for querying relation databases.

otterley 1y ago

To be clear here, I'm not arguing that OpenSearch/ElasticSearch is an adequate substitute for Postgres. They're different databases, each with different strengths and weaknesses. If you need JOINs and ACID compliance, you should use Postgres. And if you need distributed search, you should use OpenSearch/ElasticSearch. Unless they're building for single-host scale, you're not going to get JOINs for free. Lucene (the engine upon which ES/OS is based) already has JOIN capability. But it's not used in ES/OS because the performance of JOINs is absolutely abysmal in distributed databases.

philippemnoel 1y ago
Our customers typically deploy ParadeDB in a primary-replicas topology, with one primary Postgres node and 2 or more read replicas, depending on read volume. Queries are executed on a single node today, yes. We have plans to eventually support distributed queries.
cryptonector 1y ago
I'm arguing that sometimes you don't need ACID, or rather, sometimes you accept that ACID is too painful so you accept not having ACID, but no one ever really doesn't want a QL -- they only think that they don't want a QL until they learn better. I.e., NoACID does not imply NoQueryLanguage, and you can always have a QL, so you should always get a QL, and you should always use a QL. > Unless they're building for single-host scale, you're not going to get JOINs for free. If by 'free' you mean not having to code them, then that's wrong. You can always have or implement a QL. If by 'free' you mean 'performant', then yes, you might have to denormalize your data so that JOINs vanish, though at the cost of write amplification. But so what, that's true whether you use a QL or not -- it's true in SQL RDBMSes too.

derefr 1y ago

It's funny; as someone who is exactly pg_search's market, I actually often want the opposite: ACID, MVCC transactions, automatic table and index management... but no query language. At the data scale + level of complexity our OLAP queries operate at, we very often run into situations where Postgres's very best plan [with a well-considered schema, with great indexes and statistics, and after tons of tuning and coaxing], still does something literally interminable — not for any semantic reason to do with the query plan, but rather due to how Postgres's architecture executes the query plan[1]. The last such job, I thought would be simple enough to run in a few hours... I let it run for six days[2], and then gave up and killed it. Whereas, when we encoded the same "query plan" as a series of bulk-primitive ETL steps by: 1. dumping the raw source data from PG to CSV with a `COPY`, 2. whipping out simple POSIX CLI tools like sort/uniq/grep/awk (plus a few hand-rolled streaming aggregation scripts) to transform/reduce/normalize the source data into the shape we want it in, 3. and then loading the resulting CSVs back into PG with another `COPY`, ...then the runtime of the whole operation was reduced to just a few hours, with the individual steps completing in ~30 minutes each. (And that's despite the overhead of parsing and/or emitting non-string fields from/to CSV with almost every intermediate step!) Honestly, if Postgres would just let us program it the way one programs e.g. Redis through Lua, or ETS tables in Erlang — where the tables and indices are ADTs with low-level public APIs, and you set up your own "query plan" as a set of streaming-channel actors making calls to these APIs — then we would be a lot happier. But even in PL/pgSQL (which we do use, here and there), the only APIs are high-level ones. • Sure, you can get a cursor on a query; but you can't e.g. get an LMDB-like B-tree cursor on a target B-tree index, and ask it to jump [i.e. re-nav down from root] or walk [i.e. nav up from current pos to nearest common ancestor then back down] to "the first row-tuple greater-than-or-equal to [key]". • You can't write your own efficient implementation of TABLESAMPLE semantics to set up your own Bigtable-esque balanced cluster-order-partitioned parallel seq scan. • You can't collect pointers to row-tuples, partially materialize them, filter them by some criterion on the read (but perhaps not parsed!) columns, and then more-fully materialize those same row-tuples "directly" from the references to them you still hold. --- [1] One example of what I mean by "execution": did you know that Postgres doesn't use any form of concurrency for query plans — not even the most basic libuv-like "This Merge Append node's child-node A is in a blocking-wait on IO; that blocking-wait should yield, so that the Merge Append node's child-node B can instead send row-tuple batches for a while" kind of concurrency? --- [2] If you're wondering, the query that ran for six days was literally just this (anonymized): SELECT a, b, SUM(value) AS total_value FROM ( SELECT a, b, value FROM source1 UNION ALL SELECT a, b, value FROM source2 ) AS u GROUP BY a, b; `source1` and `source2` are ~150GB tables. (Or at least, they're 150GB when dumped to CSV.) Two integer keys (a,b), and a bigint value. With a b-tree index on `(a,b) INCLUDE (value)`, with correct statistics. And its EXPLAIN query plan looked like this (with `SET enable_hashagg = OFF;`) — nominally pretty good: GroupAggregate (cost=1.17..709462419.92 rows=40000 width=40) Group Key: a, b -> Merge Append (cost=1.17..659276497.84 rows=6691282944 width=16) Sort Key: a, b -> Index Only Scan using source1_a_b_idx on source1 (cost=0.58..162356175.31 rows=3345641472 width=16) -> Index Only Scan using source2_a_b_idx on source2 (cost=0.58..162356175.31 rows=3345641472 width=16) Each one of the operations here is "obvious." It's what you'd think you'd want! You'd think this would finish quickly. And yet. (And no, the machine it ran on was not resource-bottlenecked. It had 1TB of RAM with no contention from other jobs, and this PG session was allowed to use much of it as work memory. But even if it was spilling to disk at every step... that should have been fine. The CSV equivalent of this inherently "spills to disk", for everything except the nursery levels of sort(1)'s merge-sort. And it does fine.)

strbean 1y ago

Obligatory whine that the term NoSQL got co-opted to mean "no relational". There's tons of space for a better query language for querying relation databases.