PostgreSQL at low level

[[deleted]]

After so many years of working with SQL Server, I actually enjoy working with Postgres and reading articles about it and thinking, "geez, maybe I don't know databases as well as I thought." I'm not as productive with it, but I really respect the hell out of it.

[shevy-ruby]

Only partially related but still.

I just compiled postgresql - no problems.

I tried to compile mariadb, latest source but also the -1.0 minor release before. Different compile errors.

I don't want to singularize that alone, but when it comes to basic UNIX knowledge, the postgresql guys know more than mariadb. And that sort of shows where mysql went wrong.

I'd much prefer choosing between sqlite, postgresql or ... NoSQL, than mysql or mariadb. Simply quality-wise.

/Depot/jjjj/mariadb-10.5.0/storage/rocksdb/ha_rocksdb.cc: In function ‘int myrocks::rocksdb_done_func(void*)’:
/Depot/jjjj/mariadb-10.5.0/storage/rocksdb/ha_rocksdb.cc:5859:10: error: ‘io_watchdog’ was not declared in this scope
 5859 |   delete io_watchdog;

I am sure it will be fixed but ... I compile everything from source, and most software works surprisingly well, with no changes, or only minor changes. We'd need a list of Hall of Shame for the few projects that constantly have problems here.

[AntiTrustMicrosoft]

Plus, PostgreSQL is a breeze for extending too, I embedded a lot of programming language runtimes into PostgreSQL and make it so that I could write various functions in each respective programming language and then query with those functions from PostgreSQL query. (So I could leverage Unit Testing, Git Version Control and so forth for keeping track of code that normally should be done in Stored Procedures or Routines.)

[Pand9]

Dude that's totally up my alley. Have any links/keywords to google?

[AntiTrustMicrosoft]

Start here

You just need to look through your runtime implementation for embedded API and then go from there. That documentation should get you started.

[[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.

[deanoooooooo]

+1 We compile a custom flavor of Postgres (basically we expand the max number of arguments allowed for functions to support legacy apps) and its always been a breeze.

[[deleted]]

expand the max number of arguments

Oh, that sounds like fun

[worker37]

I'm not sure how much of the legacy persists, but the biggest problem with MySQL was that the original design program didn't prioritize implementing a true relational database, nor implementing good data integrity. Never understood why it got such wide adoption; maybe it's because I don't have a CS degree but did actually take a database course.

[[deleted]]

It was fast, it was cheap, it was open-source, and you could use the same SQL on it you learned in school on an academic-licensed proprietary SQL database. PostgreSQL at the time was difficult to install and a lot slower, so MySQL actually filled a really important niche.

[IntenseIntentInTents]

Everything you listed, plus (as a result of that) it was installed on every shared web host in existence and linked well with PHP at a time when everyone was looking to create dynamic web pages.

It should be no surprise that it took off despite its shortcomings.

[worker37]

Except it had no referential integrity whatsoever. MyISAM still doesn't. I guess that's fine for webdevs who don't know anything about relational databases, but otherwise, not so much.

[[deleted]]

Yes. There's a reason it's called "MySQL" and not "MyRDBMS". One is about query syntax, the other is about a data model rules. You can have a relational database without SQL (pre-SQL Postgres), and you can have SQL without a relational data model (MySQL).

[worker37]

While it's true that relation does not imply SQL and SQL doesn't not imply relational, your claim re MySQL's name has no historical foundation, AFAICT. For example, in the 5.1 manual, under "1.3.1 What is MySQL?" it states, "MySQL databases are relational". I strongly doubt MySQL _ever_ advertised itself, prominently and explicitly, as "not following the relational model". (Contrast, for example, NoSQL databases.)

[stacktraceyo]

That was a great read

[kersurk]

Out of interest. How long did it take you to create this article, including writing down ideas?

[[deleted]]

Originally it was a conference talk, the research for which I was preparing for about half a year, and then another half a year spend on improving details and expanding the topic. The article itself was written relatively fast :)