5 ms·

What's the purpose of this? Why this over a function in PL/pgSQL?

bluejekyll 8y ago
I expect extensions in Rust will be much faster than those written in pgSQL, which would need to be interpreted, of course this should be validated. In addition to that, this is stdlib code in Rust, and stable, so you should be able utilize anything from the Rust ecosystem in an extension built in Rust. There's always a trade-off of adding new languages to a project, and for simple things, Rust might not be the best choice.
- Dowwie 8y ago
  Curious how tantivvy performs for full text search, in comparison with the native one. This work inspires me. I am going to try to make a fake-rs extension to enable fake data generation
  - nicoburns 8y ago
    The benchmarks seem to show it neck and neck with lucene, so I'd imagine faster (and with a better ranking alogrythm) than the beuild in postgres search.
paulddraper 8y ago
PostgreSQL has several bindings, including Python and JavaScript (V8). You might want to do complex value manipulation at the DB-level, like a regex-like operation, or processing XML.
TrueDuality 8y ago
A lot of Postgres extensions are designed to directly integrate external information systems that PL/pgSQL would never have access too such as announcing certain types of changes to RabbitMQ, or ensuring an ElasticSearch index is fresh with a subset of the data. Additionally extensions can be used to extend PostgreSQLs native functionality (like PostGIS does).
derefr 8y ago
That's a funny comparison. The much more obvious contrast would be to writing a Postgres extension in C. Or are you asking, "why have native DBMS extensions at all?" Looking at the set of native Postgres extensions that currently exist would probably be enlightening: • PostGIS, for one. You can't really define those types—and especially, the algorithms that operate on them—efficiently in PL/pgSQL. In this case, the extension is native for low overhead operation. • uuid-ossp, for another. To generate the data it does, this extension needs access to several pieces of data from the OS (e.g. the MAC address of an Ethernet interface) that you can't access from PL/pgSQL. In this case, the extension is native to query native OS data APIs. • Citus, for a third. Citus basically changes Postgres's runtime "operational architecture" in several fundamental ways. It augments the Postgres Postmaster, changes how replication works, etc. None of this is possible from PL/pgSQL. In this case, the extension is native to use native OS threading and IPC APIs to override how Postgres works. (And that's ignoring the fact that having native extension support usually gets you ABI compatibility with the embeddable runtimes of most other languages, allowing you to then support things like Python or Java—and thus get access to low-overhead shared-memory+FFI-based "IPC" with existing libraries in those languages, rather than needing to have them run as co-servers and communicate over sockets. Which, in turn, gets you the ability to cheaply and easily use client libraries written in those languages to create Postgres Foreign Data Wrapper libraries for Datomic, or Elastic, or the AWS Public Dataset API, or whatever other weird-API-with-relatively-few-library-impls you like.)
- joevandyk 8y ago
  I thought uuid-ossp was just a bunch of utilities around uuid parsing and generation. It shouldn’t need anything special from the OS. https://www.postgresql.org/docs/9.4/uuid-ossp.html https://www.postgresql.org/docs/9.4/uuid-ossp.html
  - the_duke 8y ago
    The very first function on that link is for generating uuid v1s, which require the mac address...
  - derefr 8y ago

I expect extensions in Rust will be much faster than those written in pgSQL, which would need to be interpreted, of course this should be validated. In addition to that, this is stdlib code in Rust, and stable, so you should be able utilize anything from the Rust ecosystem in an extension built in Rust. There's always a trade-off of adding new languages to a project, and for simple things, Rust might not be the best choice.

Dowwie 8y ago
Curious how tantivvy performs for full text search, in comparison with the native one. This work inspires me. I am going to try to make a fake-rs extension to enable fake data generation
- nicoburns 8y ago
  The benchmarks seem to show it neck and neck with lucene, so I'd imagine faster (and with a better ranking alogrythm) than the beuild in postgres search.

paulddraper 8y ago

PostgreSQL has several bindings, including Python and JavaScript (V8). You might want to do complex value manipulation at the DB-level, like a regex-like operation, or processing XML.

TrueDuality 8y ago

A lot of Postgres extensions are designed to directly integrate external information systems that PL/pgSQL would never have access too such as announcing certain types of changes to RabbitMQ, or ensuring an ElasticSearch index is fresh with a subset of the data. Additionally extensions can be used to extend PostgreSQLs native functionality (like PostGIS does).

derefr 8y ago

That's a funny comparison. The much more obvious contrast would be to writing a Postgres extension in C. Or are you asking, "why have native DBMS extensions at all?" Looking at the set of native Postgres extensions that currently exist would probably be enlightening: • PostGIS, for one. You can't really define those types—and especially, the algorithms that operate on them—efficiently in PL/pgSQL. In this case, the extension is native for low overhead operation. • uuid-ossp, for another. To generate the data it does, this extension needs access to several pieces of data from the OS (e.g. the MAC address of an Ethernet interface) that you can't access from PL/pgSQL. In this case, the extension is native to query native OS data APIs. • Citus, for a third. Citus basically changes Postgres's runtime "operational architecture" in several fundamental ways. It augments the Postgres Postmaster, changes how replication works, etc. None of this is possible from PL/pgSQL. In this case, the extension is native to use native OS threading and IPC APIs to override how Postgres works. (And that's ignoring the fact that having native extension support usually gets you ABI compatibility with the embeddable runtimes of most other languages, allowing you to then support things like Python or Java—and thus get access to low-overhead shared-memory+FFI-based "IPC" with existing libraries in those languages, rather than needing to have them run as co-servers and communicate over sockets. Which, in turn, gets you the ability to cheaply and easily use client libraries written in those languages to create Postgres Foreign Data Wrapper libraries for Datomic, or Elastic, or the AWS Public Dataset API, or whatever other weird-API-with-relatively-few-library-impls you like.)

joevandyk 8y ago
I thought uuid-ossp was just a bunch of utilities around uuid parsing and generation. It shouldn’t need anything special from the OS. https://www.postgresql.org/docs/9.4/uuid-ossp.html https://www.postgresql.org/docs/9.4/uuid-ossp.html
- the_duke 8y ago
  The very first function on that link is for generating uuid v1s, which require the mac address...
- derefr 8y ago