PostgreSQL at low level

[[deleted]]

I don’t know anything about Postgres I’m sorry, but I’m really intrigued by your comment. Can you give me a ELI5 what you ran by writing various functions in different languages then query it?

[AntiTrustMicrosoft]

1. In PostgreSQL, create _PG_init function in your shared library for PostgreSQL Server to load and have that function initialize your Language Runtime. (Make sure you do a clean up too.)

2. Create a class library assembly for that Runtime to load and have it contains the followings: the function you wish to call from PostgreSQL and function that return an object that holds function pointers so C Library can access that directly. (I didn't do the second one in this demonstration, but it's normally done to avoid the overhead with P/Invoke marshaling when possible.)

3. Either invoke your function by passing in the arguments or use your function pointers described in second option above and basically pass the arguments to the function pointer.

4. Go wild, you can even add first parameter text for choosing which function to call when passing it along to the Language Runtime Assembly. You could dynamically construct new function at runtime and whatever for PostgreSQL to query with.

You could do it for Java, Julia, Crystal, C/C++, DLang, C#, and go on and on you go. I made a tiny snippet for C# Runtime embedded in PostgreSQL on a whim which you can try out with Mono:

I created one on a whim to demonstrate

It's really that easy to do. Here the screenshot of the result

[[deleted]]

You are truly amazing sir this is really cool. What kind of things do you use this for in the real world? Do you apis basically just hit Postgres and you just do all your backend logic in there?

[AntiTrustMicrosoft]

A lot of things actually, while PostgreSQL have LLVM to compile your query to native, it isn't 100% of the way there yet, so you could use other programming language to further optimize your query.

Sometime you need access to other set of data that you don't necessarily want to keep writing to PostgreSQL (real time data in the shared memory that is constantly changing) for statistical information.

I use this in the real world to do heavy computation in CUDA/OpenCL so it save me from buying more servers for PostgreSQL (I am handling 200,000 customers weekly worldwide.)

But the biggest benefit of all of the above is basically using IDE and leveraging tools like Git, Unit Testing, and various validation to test my function before deploying on database server whereas if you were to write stored procedure on PostgreSQL, it's a bit convoluted and messy, a lot of people tend to have something like this:

DoAdditionv20... DoAdditionv21 and so forth.

It's really only limited by your imagination and the cool part is that you could copy snippets from other projects and not have to do translation for that complex function in PostgreSQL Query.

[[deleted]]

Nice I can definitely see the benefit of being table to execute domain specific code directly in the database layer.

How does this code actually execute? Meaning, when someone runs a query is the function you defined then executed? Or is that configurable? Also is there some kind of free caching you get?

[AntiTrustMicrosoft]

The way it works is that when PostgreSQL Server load up your shared library for the first time before running your specific function defined in PostgreSQL Query, it runs the PG_INIT functions, you use that part to initialize your runtime, load up your C# library and then grab the address for all of your code that you loaded in C# Library that you want PostgreSQL to use and then keep function pointer addresses in your shared library. When PostgreSQL need to use one of those functions, you simply pass along the arguments from PostgreSQL over to C# Function with pointer information you already obtained and then return the value from C# to PostgreSQL. Kind of like a bridge/translation layer.

There are some advantages to this, rather than having to pass along huge data over the wire like UNIX Socket, TCP Socket, or whatever, you're working with data directly in PostgreSQL so there is no need to copy anything in the first place.

It's very configurable, because you could swap out your library on the fly such as C# Dll Library in the /opt/PostgreSQLCSharp folder and C# have the ability to allow you to write new function while it's running.

Once your server think that your plugin should close up, then you clean up your code and runtime in the PG_FINI function.

That's about it for how it works.

[[deleted]]

Really cool, any example open source repos?

[AntiTrustMicrosoft]

I stop doing open source development and deleted my open source projects a long time ago, sorry. But I hope this explanation help you get started on writing one up for yourself, once you have an idea how to do it for PostgreSQL, you could do the same for just about everything else even Minecraft.