5 ms·

I have to say I have a disliking for what appears to be the current trend of marketing things as "postgres (wire) compatible". CockroachDB is another example t

I have to say I have a disliking for what appears to be the current trend of marketing things as "postgres (wire) compatible". CockroachDB is another example that does the same. The implication is that "if you can do it in postgres, you can do it in X". But the reality is, of course, different. If you are using databases properly, not just as a blackbox dumping ground, then if you use postgres on the wire, its because you are talking to postgres and using postgres specific features (like you should). Unless you have feature parity, don't tell me I can connect to another database using my postgres libraries. Its pointless. Its meaningless. Why would I want to do that if I can't use the features ? Rant over. ;-)

kasey_junk 5y ago
Largely for driver coverage. Postgres drivers are available in tons of languages so by being wire compatible you can skip all that work.
- traceroute66 5y ago
  > Postgres drivers are available in tons of languages So are SQLite drivers (and MySQL and all the other top-10/top-20 databases). Why add un-necessary abstraction ? Why add an un-necessary layer with bugs and edge-cases that you will subsequently waste hours of your life debugging ? Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. > What SQLite3 driver out there allows you to talk over a network connection to a remote database? (Replied here because I can't reply to your point below) Maybe that's because, as per the SQLite website[1], SQLite was never intended to be used as a client/server database !!! I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. There are many excellent client/server databases out there (including the postgres referred to here). If you need that functionality you should be using the right tool for the job, there's no need to shoehorn SQLite into it. [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
  - ansible 5y ago
    > Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. What SQLite3 driver out there allows you to talk over a network connection to a remote database? Not any one that I've ever used.
  - ripley12 5y ago
    > I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. It depends a lot on what you're using the remote connectivity for. I think I have a pretty good understanding of SQLite's strengths+capabilities, enough to understand when it can replace a traditional RDBMS for a back-end service. I wouldn't build a solution where multiple clients are connecting to the DB remotely - but sometimes I want the convenience of connecting to the DB remotely for observability/troubleshooting. This is something that's trivial with traditional RDBMSs, and a bit painful with SQLite. Exposing the SQLite DB over the network for occasional interactive use seems quite useful to me.
    - nerdponx 5y ago
      It's also useful if you initially built an application SQLite and are transitioning to Postgres.
- ansible 5y ago
  Yes. I don't see the harm in the PostgreSQL wire protocol becoming a defacto standard for sending SQL statements over a network connection and getting results back. SQL itself is only a vague standard these days, as almost every single database has its own dialect. The only ones that are the same are MariaDB and MySQL... and that doesn't count. [1] And I don't see this situation changing in the medium term, or ever, really. [1] ... because the first is the fork of the second.
  - unilynx 5y ago
    This. I’ll take the pg protocol over OCI and especially ODBC any day.
  - clhodapp 5y ago
    There's a small amount of harm because Postgres doesn't currently support request multiplexing and so its wire protocol doesn't have any facility for it. This pushes other databases to inherit one of Postgres's few true weak points.
    - ansible 5y ago
      I take it that opening multiple database connections to the same server is not usually viable? I realize there are usually limits to open sockets / file descriptors and such, and that multiple connections will consume more memory on each end. Are there problems beyond that?
      - clhodapp 5y ago
        It's usually (but not always) viable and it's ultimately what one ends up doing. You're right about the drawbacks. One additional drawback is the setup time for opening new connections (network round-trip, re-authenticating, etc.).
    - amitlan 5y ago
      If I’m not wrong, I think this situation has changed with the latest Postgres release: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html https://www.postgresql.org/docs/current/libpq-pipeline-mode....
amalcon 5y ago
This particular one is described as a way to use existing Postgres GUI tooling (DBeaver, Postico 2) with SQLite. Building a compatibility shim for existing tooling is sometimes a more efficient way to solve this class of problem than adapting the tooling to support multiple backends. I'm not saying it's what I would've done (I have little use for GUI database tooling), but I can see why someone else might.
- benbjohnson 5y ago
  Postlite author here. Yes, this is exactly the reason. There's no standard for connecting to a remote SQLite database so this is simply a shim to help support existing tooling. I don't use GUI tooling either but it's the most common complaint I hear when developers try switching to SQLite.
- funcDropShadow 5y ago
  Dbeaver supports sqlite directly. It's homepage states: > Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc.
flyinghamster 5y ago
Seems to me that something like this would be good for on-the-fly interchange between SQLite and Postgres, without the need for dumping and re-importing data.
bradrydzewski 5y ago
The purpose of this software is to allow remote management of sqlite databases using existing postgres GUI tools. > Postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier. The readme does not seem to advertise this software as being a drop-in replacement for Postgres by any means, although I can understand your frustration with database vendors that market their software as such. I suspect they used Postgres as the wire format because otherwise they would need to invent one from scratch, and then work with existing tools to integrate. Leveraging the Postgres wire form means they can integrate with existing GUI tools on day one, and seems to be a very pragmatic choice.
simonw 5y ago
I think calling something "PostgreSQL wire compatible" is completely honest: you're not saying it's PostgreSQL compatible, the word "wire" is there for a very good reason.
manigandham 5y ago
That's the meaning of "wire" or "protocol" compatible. It's saying that the language is the same, not the features. It's similar to how Redis uses RESP which has been implemented by other systems. You can even move the abstraction down a level with non-relational databases like Cassandra and Elastic offering SQL interfaces. They don't offer relational features, but let you express queries using a relational language.

kasey_junk 5y ago

Largely for driver coverage. Postgres drivers are available in tons of languages so by being wire compatible you can skip all that work.

traceroute66 5y ago
> Postgres drivers are available in tons of languages So are SQLite drivers (and MySQL and all the other top-10/top-20 databases). Why add un-necessary abstraction ? Why add an un-necessary layer with bugs and edge-cases that you will subsequently waste hours of your life debugging ? Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. > What SQLite3 driver out there allows you to talk over a network connection to a remote database? (Replied here because I can't reply to your point below) Maybe that's because, as per the SQLite website[1], SQLite was never intended to be used as a client/server database !!! I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. There are many excellent client/server databases out there (including the postgres referred to here). If you need that functionality you should be using the right tool for the job, there's no need to shoehorn SQLite into it. [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- ansible 5y ago
  > Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. What SQLite3 driver out there allows you to talk over a network connection to a remote database? Not any one that I've ever used.
- ripley12 5y ago
  > I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. It depends a lot on what you're using the remote connectivity for. I think I have a pretty good understanding of SQLite's strengths+capabilities, enough to understand when it can replace a traditional RDBMS for a back-end service. I wouldn't build a solution where multiple clients are connecting to the DB remotely - but sometimes I want the convenience of connecting to the DB remotely for observability/troubleshooting. This is something that's trivial with traditional RDBMSs, and a bit painful with SQLite. Exposing the SQLite DB over the network for occasional interactive use seems quite useful to me.
  - nerdponx 5y ago
    It's also useful if you initially built an application SQLite and are transitioning to Postgres.
ansible 5y ago
Yes. I don't see the harm in the PostgreSQL wire protocol becoming a defacto standard for sending SQL statements over a network connection and getting results back. SQL itself is only a vague standard these days, as almost every single database has its own dialect. The only ones that are the same are MariaDB and MySQL... and that doesn't count. [1] And I don't see this situation changing in the medium term, or ever, really. [1] ... because the first is the fork of the second.
- unilynx 5y ago
  This. I’ll take the pg protocol over OCI and especially ODBC any day.
- clhodapp 5y ago
  There's a small amount of harm because Postgres doesn't currently support request multiplexing and so its wire protocol doesn't have any facility for it. This pushes other databases to inherit one of Postgres's few true weak points.
  - ansible 5y ago
    I take it that opening multiple database connections to the same server is not usually viable? I realize there are usually limits to open sockets / file descriptors and such, and that multiple connections will consume more memory on each end. Are there problems beyond that?
    - clhodapp 5y ago
      It's usually (but not always) viable and it's ultimately what one ends up doing. You're right about the drawbacks. One additional drawback is the setup time for opening new connections (network round-trip, re-authenticating, etc.).
  - amitlan 5y ago
    If I’m not wrong, I think this situation has changed with the latest Postgres release: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html https://www.postgresql.org/docs/current/libpq-pipeline-mode....

amalcon 5y ago

This particular one is described as a way to use existing Postgres GUI tooling (DBeaver, Postico 2) with SQLite. Building a compatibility shim for existing tooling is sometimes a more efficient way to solve this class of problem than adapting the tooling to support multiple backends. I'm not saying it's what I would've done (I have little use for GUI database tooling), but I can see why someone else might.

benbjohnson 5y ago
Postlite author here. Yes, this is exactly the reason. There's no standard for connecting to a remote SQLite database so this is simply a shim to help support existing tooling. I don't use GUI tooling either but it's the most common complaint I hear when developers try switching to SQLite.
funcDropShadow 5y ago
Dbeaver supports sqlite directly. It's homepage states: > Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc.

flyinghamster 5y ago

Seems to me that something like this would be good for on-the-fly interchange between SQLite and Postgres, without the need for dumping and re-importing data.

bradrydzewski 5y ago

The purpose of this software is to allow remote management of sqlite databases using existing postgres GUI tools. > Postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier. The readme does not seem to advertise this software as being a drop-in replacement for Postgres by any means, although I can understand your frustration with database vendors that market their software as such. I suspect they used Postgres as the wire format because otherwise they would need to invent one from scratch, and then work with existing tools to integrate. Leveraging the Postgres wire form means they can integrate with existing GUI tools on day one, and seems to be a very pragmatic choice.

simonw 5y ago

I think calling something "PostgreSQL wire compatible" is completely honest: you're not saying it's PostgreSQL compatible, the word "wire" is there for a very good reason.

manigandham 5y ago

That's the meaning of "wire" or "protocol" compatible. It's saying that the language is the same, not the features. It's similar to how Redis uses RESP which has been implemented by other systems. You can even move the abstraction down a level with non-relational databases like Cassandra and Elastic offering SQL interfaces. They don't offer relational features, but let you express queries using a relational language.