Using AI to generate data structures for PostgreSQL

07.2024 / Category: / Tags: |

Artificial Intelligence is the motto of the day. Everybody is talking about it, everybody seems to know what it means and the insane number of buzzwords floating around on the internet is just mind-boggling. It seems that in some areas of the industry, the number of slides and marketing videos exceeds the number of real-world use cases and sometimes even the amount of code that actually exists.

However, as I tend to prefer the real PostgreSQL world over the fake marketing side of things, I decided to show some of the things  we recently experimented with to give you  some insights into what can be done with AI and to point out where the limitations are.

First step: Generating data models for PostgreSQL

One of the most obvious things one might do with AI is to help people create basic data models. This use case presents itself as a perfect fit for all the new LLMs (Large Language Models) including ChatGPT and similar models.

For this little demo, I used a local deployment of “llama3”, which has been provided by Meta. It is a general-purpose model, but it happens to do a decent job and produces reasonable drafts that can serve as the basis for expanding your SQL code quickly and efficiently.

How can anybody make use of an LLM when dealing with PostgreSQL? Well, the first thing to do is to install an Open Source tool called “ollama”. It will help you to download, deploy, and run the language model of your choice. Ollama runs as a server and offers a really simple interface. I ran these models on my local Apple M1 chip. OS X is able to automatically include your GPU. On Linux, a bit more effort is needed to run on your NVIDIA chip, but you will also be awarded with better response times in general, assuming your are not utilizing an outdated chip.

In general, I found it useful to use Python to interact with AI models. However, for testing, the command line works just fine.

Generating PostgreSQL code using Ollama:

Before we dive into the Python code we can use the command line interface to generate some basic stuff:

As you can see Ollama can be started easily and we can simply ask a question.

What comes back is really interesting. Basically we get a markdown document which we can go through step by step:

The first part already contains a warm welcome and a reasonably good SQL model which is decently indexed.

But, there is more: We will also get a full explanation of what the model has already done for us. Check it out. Let me explain the design choices:

These indexes will help with common use cases, such as:

  • Searching for addresses within a specific street range
  • Retrieving all addresses in a particular city or state
  • Filtering addresses based on their zip code

Note: I used the BTREE index type, which is suitable for most use cases. If you expect very large datasets or need more advanced

indexing features, you may want to consider using other index types (e.g., Hash, GiST) or more complex indexing strategies.

Please adjust this design according to your specific requirements and data constraints!

What we got here is really good information that can already serve as some kind of documentation. At the very least we can use it to adjust and to add more information. Clearly, the amount of effort required to come up with this content is considerably lower than if we had produced all that by hand. Usually, the code works in PostgreSQL out of the box, but we have also seen that the AI creates tables in the wrong order in case foreign keys are present. Therefore, it may make sense to create the tables in the first step and then ask the AI to create and generate keys and indexes in a second phase.

Using Python and LLAMA3 to generate SQL code

The following code contains a small Python program to interact with the AI. It sends a sentence to the large language model, which returns a variety of content, including text and code. Therefore, we should extract the SQL part from the result because we might want to deploy that code and test it directly:

The get_model_result function simply takes user input and sends it to our desired model. The second function, (extract_code_from_markdown), dissects the result and extracts the SQL code. Of course, one might also write some code to extract the rest of the result, but for now, this seems easy enough. Finally, we define a question we want to ask, run the model, and display the result.

What we get might look like this:

Wow, this result is really nice and helps us to get some inspiration to expand the data model even more. Of course, nothing is perfect, but come on! All we fed to the model is one sentence. Do you expect a human to create something better if there is zero customer interaction, zero knowledge about the subject, and no background knowledge?

Our research shows that there is certainly a lot of potential in the field and we are in the final stages of adding those features to tools such as CYPEX

The beauty here is that you can run the model locally, and there is no need to procure software or purchase subscriptions, which have value in a secure environment. 

Finally …

If you want to find some more information about a more traditional method to dealing with trends and similar topics, I suggest reading my blog about string encoding in SQL to deal with time series.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram