Postgresql – When (or why even) use PLPython(3)u

If you only have a few variables I would consider keeping separate boolean columns.

• Indexing is easy. In particular, indexes on expressions are easy.
• Conditions for queries and partial indexing are easy to write and read and meaningful.
• A boolean column occupies 1 byte. For only a few variables this occupies the least space.
• Unlike the other options boolean columns allow NULL values for individual bits if you should need that. You can always define columns NOT NULL if you don't.

Optimizing storage

If you have more than a hand full variables but less than 33, an integer column may serve you best. (Or a bigint for up to 64 variables.)

• Occupies 4 bytes on disk.
• Very fast indexing for exact matches (= operator).
• Handling individual values may be slower / less convenient than with bit string or boolean.

With even more variables, or if you want to manipulate the values a lot, or if you don't have huge tables and disk space / RAM is no issue, or if you are not sure what to pick, I would consider bit(n) or bit varying(n).

• Occupies at least 5 bytes (or 8 for very long strings) plus 1 byte for each group of 8 bits (rounded up).
• You can use bit string functions and operators directly.

Examples

For just 3 bits of information, individual boolean columns get by with 3 bytes, an integer needs 4 bytes and a bit string 6 bytes (5 + 1).

For 32 bits of information, an integer still needs 4 bytes, a bit string occupies 9 bytes for the same (5 + 4) and boolean columns occupy 32 bytes.

Further reading

• Is there any difference between integer and bit(n) data types for a bitmask?
• Calculating and saving space in PostgreSQL
• Can I convert a bunch of boolean columns to a single bitmap in PostgreSQL?

1. UDFs in interpreted languages are pretty much always slower than UDFs written in C or built-in functions, all other things being the same.

2. Each language binding has different code to connect PostgreSQL to the language, with different degrees of optimisation, different ways of passing some data types, etc. So variation certainly exists. It shouldn't be huge unless you're passing a data type that gets very different handling by one language than another, e.g. one passes a hstore as a string, and another converts it to a dict.

3. Unclear what "the context" is. Can you use it for "real time data mapping" ... well, depends on what the function does and if it's fast enough on the server it's running on, for the clients it's taking to, and for your requirements. How long is a piece of string? Benchmark.

4. PL/PgSQL is simpler to write, and offers faster access to SQL. It's generally better when you need to wrap a little logic around a lot of SQL. It's very slow for mathematical operations and complex algorithms, so purely computational code in PL/PgSQL should be avoided whenever possible in favour of C, or a faster procedural language.

Speedups when re-implementing PL/PgSQL code in C can vary from neglible to over 1000 times. It all depends on what the code is actually doing.

(This kind of multi-question isn't well suited to Stack Exchange as it's harder to have a definitive answer)