<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Denis Magda on Medium]]></title>
        <description><![CDATA[Stories by Denis Magda on Medium]]></description>
        <link>https://medium.com/@magda7817?source=rss-74f56c424c21------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*oYE9KKE5RHSFPj6iwb8zJw.jpeg</url>
            <title>Stories by Denis Magda on Medium</title>
            <link>https://medium.com/@magda7817?source=rss-74f56c424c21------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 18:43:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@magda7817/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[What is the Open Source Alternative to CockroachDB?]]></title>
            <link>https://medium.com/@magda7817/what-is-the-open-source-alternative-to-cockroachdb-c346cbfdbe41?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/c346cbfdbe41</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[cockroachdb]]></category>
            <category><![CDATA[yugabytedb]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Tue, 27 Aug 2024 17:56:25 GMT</pubDate>
            <atom:updated>2024-08-27T18:10:13.739Z</atom:updated>
            <content:encoded><![CDATA[<p>I’ve been hearing this question more frequently since Cockroach Labs <a href="https://www.cockroachlabs.com/blog/enterprise-license-announcement/">further restricted</a> its already limited license. Now, any company making over $10 million in annual revenue has to pay. This marks the end of CockroachDB’s open-source story. Given the database’s popularity, many have started searching for an open-source alternative to this distributed SQL database.</p><p>If you’re reading this, chances are high you’ve been affected by the license change. Now, you can relax and breath out — an alternative does exist. It’s fully open under the Apache 2.0 license and goes by the name <a href="https://github.com/yugabyte">YugabyteDB</a>. Let me give you a quick tour to help you understand the similarities and differences between these two distributed databases.</p><h3><strong>What’s the Similarity?</strong></h3><p>Both CockroachDB and YugabyteDB belong to the class of distributed SQL databases. These databases are designed to scale your read/write workloads across a cluster of interconnected nodes, tolerating all sorts of possible outages from minor server-level glitches to major region-level incidents in the cloud. Unlike NoSQL databases, they do this without compromising data consistency.</p><p>Like CockroachDB, YugabyteDB is typically deployed in a configuration with three or more nodes. Below is an example of a three-node YugabyteDB cluster configured with a replication factor of three, meaning there are three copies of your data across the cluster.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IYQ26pnp5RhzWjT2urAOPQ.png" /></figure><p>The entire dataset is split into shards, and every record you store in the database is mapped to one of these shards. In the example above, there are three shards. With a replication factor of three, each shard has a primary copy (Raft leader) and two backup copies (Raft followers).</p><p>The Raft leaders serve two key purposes. First, they handle all read and write requests for the data mapped to their respective shards. Second, they replicate changes to the followers. YugabyteDB distributes Raft leaders of different shards across different database nodes, enabling even distribution of data and read/write workloads across the cluster.</p><p>If you take a look at the diagram one more time, you’ll not spot any component that could cause a bottleneck. Neither CockroachDB nor YugabyteDB has a master/coordinator/proxy/director node that receives application requests and then distributes them across the cluster. Distributed SQL databases don’t need that component by design because all the nodes communicate with each other directly to coordinate the execution of distributed queries and transactions, replicate changes, and gracefully handle outages.</p><p>This is what makes CockroachDB and YugabyteDB comparable. The next question you might ask is whether there’s anything unique about YugabyteDB apart from its Apache 2.0 license.</p><h3>What’s the Difference?</h3><p>If you start comparing the databases granularly at the feature level, you’ll obviously find some differences here and there, but most of those differences won’t be subtle.</p><p>A truly big difference is Postgres. Both databases claim to be Postgres-compatible, but their levels of compatibility are not the same, and they approached compatibility differently.</p><p>CockroachDB built its SQL engine from scratch and started adding Postgres capabilities gradually over time. YugabyteDB went in the other direction by taking the Postgres source code as-is and replacing the Postgres storage layer with YugabyteDB’s own distributed storage architecture.</p><p>The results of these two approaches are significant. CockroachDB achieved wire-level compatibility with Postgres and supported a subset of core Postgres features. YugabyteDB became not only feature-compatible but also <a href="https://www.yugabyte.com/postgresql/compare-postgresql-compatibility/">runtime-compatible</a> with Postgres. Most of the applications, libraries, tools, and frameworks designed for Postgres see no difference between Postgres and YugabyteDB. YugabyteDB is just like Postgres for them, only fully distributed.</p><p>How is it possible to achieve that level of compatibility? The short answer is that when you connect to YugabyteDB, you literally connect to Postgres and let Postgres execute your application requests over a distributed cluster.</p><p>Let’s break this down into more detail. When you start a Postgres instance, the database starts the <strong>Postgres postmaster </strong>process, which listens for incoming client connections.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jOiY6jgY8oD6Y-YGNYIFkg.png" /></figure><p>When a client or application connects, the postmaster forks itself into a <strong>Postgres backend</strong> process to coordinate that client’s requests. That backend parses and executes queries, works with the Postgres shared memory and files to read and write data, and performs other processing before returning results to the client.</p><p>When you start a YugabyteDB node and open up a client connection with it, you’ll be connecting to the same familiar <strong>Postgres postmaster</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TkTVA4z-dakln5pO7Q4tbQ.png" /></figure><p>Just like in Postgres, the postmaster forks a new <strong>Postgres backend</strong> for each new client connection, and the backend parses and executes the requests. Only this time, the requests are executed across a distributed YugabyteDB storage layer comprised of <strong>tserver</strong> instances/processes.</p><p>Above is a simplified architecture diagram of a single YugabyteDB node. Each YugabyteDB node has its own instances of Postgres postmaster and tserver running.</p><p>Overall, Postgres is what makes YugabyteDB different from CockroachDB. You can think of YugabyteDB as a distributed version of Postgres.</p><p>If YugabyteDB sounds like what you’re looking for, then head over to their <a href="https://github.com/yugabyte">GitHub</a> to learn more about the database and choose a deployment option that works best for you.</p><p>If you have Docker, you can start an instance of YugabyteDB in under a minute:</p><pre>mkdir ~/yugabyte-volume<br><br>docker network create custom-network<br><br>docker run -d --name yugabytedb_node1 --net custom-network \<br>  -p 15433:15433 -p 7001:7000 -p 9000:9000 -p 5433:5433 \<br>  -v ~/yb_docker_data/node1:/home/yugabyte/yb_data --restart unless-stopped \<br>  yugabytedb/yugabyte:latest \<br>  bin/yugabyted start \<br>  --base_dir=/home/yugabyte/yb_data --daemon=false</pre><p>And then connect to it with the ysqlsh tool:</p><pre>docker exec -it yugabytedb-node1 bin/ysqlsh -h yugabytedb-node1</pre><p>The prompt will welcome you to execute your first request:</p><pre>ysqlsh (11.2-YB-2.19.3.0-b0)<br>Type &quot;help&quot; for help.<br><br>yugabyte=#</pre><p>Enjoy the freedom of open source!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c346cbfdbe41" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[When Should You Use Distributed PostgreSQL for Gen AI Apps?]]></title>
            <link>https://medium.com/@magda7817/when-should-you-use-distributed-postgresql-for-gen-ai-apps-64f2470cf1a1?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/64f2470cf1a1</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Wed, 05 Jun 2024 14:24:21 GMT</pubDate>
            <atom:updated>2024-06-05T14:24:21.563Z</atom:updated>
            <content:encoded><![CDATA[<p>Postgres continues to evolve the database landscape beyond traditional relational database use cases. Its rich ecosystem of extensions and derived solutions has made Postgres a formidable force, especially in areas such as time-series and geospatial, and most recently, gen AI workloads.</p><p><a href="https://github.com/pgvector/pgvector">Pgvector</a> has become a foundational extension for gen AI apps that want to use Postgres as a vector database. In brief, pgvector adds a new data type, operators, and index types to work with vectorized data (embeddings) in Postgres. This allows you to use the database for similarity searches over embeddings.</p><p>Pgvector started to take off in 2023, with <a href="https://star-history.com/#pgvector/pgvector&amp;Date">rocketing</a> GitHub stars:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bDNV9UsdJLkwCmaqHRhaeg.png" /></figure><p>Pure vector databases, such as Pinecone, had to acknowledge the existence of pgvector and start publishing <a href="https://www.pinecone.io/blog/pinecone-vs-pgvector/">competitive materials</a>. I consider this a good sign for Postgres.</p><p>Why a good sign? Well, as my fellow Postgres community member <a href="https://x.com/robtreat2/status/1786242344393560241">Rob Treat put it</a>: “First they ignore you. Then they laugh at you. Then they create benchmarketing. Then you win!”</p><p>So, how is this related to the topic of distributed Postgres?</p><p>The more often Postgres is used for gen AI workloads, the more frequently you’ll hear (from vendors behind other solutions) that gen AI apps built on Postgres will have:</p><ul><li>scalability and performance issues</li><li>challenges with data privacy</li><li>a hard time with high availability</li></ul><p>If you do encounter the listed issues, you shouldn’t immediately dump Postgres and migrate to a more scalable, highly available, secure vector database, at least not until you’ve tried running Postgres in a distributed configuration!</p><p>Let’s discuss when and how you can use distributed Postgres for gen AI workloads.</p><h3>What is Distributed Postgres?</h3><p>Postgres was designed for single-server deployments. This means that a single primary instance stores a consistent copy of all application data and handles both read and write requests.</p><p>How do you make a single-server database distributed? You tap into the Postgres ecosystem!</p><p>Within the Postgres ecosystem, people usually assume one of the following of distributed Postgres:</p><ul><li>Multiple standalone PostgreSQL instances with multi-master asynchronous replication and conflict resolution (like. EDB Postgres Distributed)</li><li>Sharded Postgres with a coordinator (like CitusData)</li><li>Shared-nothing distributed Postgres (like YugabyteDB)</li></ul><p>Check out the <a href="https://www.yugabyte.com/postgresql/distributed-postgresql/">following guide</a> for more information on each deployment option. As for this article, let’s examine when and how distributed Postgres can be used for your gen AI workloads.</p><h3>Problem #1: Embeddings Use All Available Memory and Storage Space</h3><p>If you have ever used an embedding model that translates text, images, or other types of data into a vectorized representation, you might have noticed that the generated embeddings are quite large arrays of floating point numbers.</p><p>For instance, you might use an OpenAI embedding model that translates a text value into a 1536-dimensional array of floating point numbers. Given that each item in the array is a 4-byte floating point number, the size of a single embedding is approximately 6KB — a substantial amount of data.</p><p>Now, if you have 10 million records, you would need to allocate approximately 57 Gigabytes of storage and memory just for those embeddings. Additionally, you need to consider the space taken by indexes (such as HNSW, IVFFlat, etc.), which many of you will create to expedite the vector similarity search.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OGpSy53CWOQpeZyxNEpJZA.png" /></figure><p>Overall, the greater the number of embeddings, the more memory and storage space Postgres will require to store and manage them efficiently.</p><p>You <em>can</em> reduce storage and memory usage by switching to an embedding model that generates vectors with fewer dimensions, or by using quantization techniques. But, suppose I need those 1536-dimensional vectors and I don’t want to apply any quantization techniques. In that case, if the number of embeddings continues to increase, I could outgrow the memory and storage capacity of my database instance.</p><p>This is an obvious area where you can tap into distributed Postgres. For example, by running sharded (CitusData) or shared-nothing (YugabyteDB) versions of PostgreSQL, you can allow the database to distribute your embeddings evenly across an entire cluster of nodes.</p><p>Using this approach, you are no longer limited by the memory and storage capacities of a single node. If your application continues to generate more embeddings, you can always scale out the cluster by adding more nodes.</p><h3>Problem #2: Similarity Search is Compute-Intensive Operation</h3><p>This problem is closely related to the previous one, but with a focus on CPU and GPU utilization.</p><p>When we say “just perform the vector similarity search over the embeddings stored in our database,” the task sounds straightforward and obvious to us, humans. However, from the database server’s perspective, it’s a compute-intensive operation requiring significant CPU cycles.</p><p>For instance, here is the formula used to calculate the <a href="https://en.wikipedia.org/wiki/Cosine_similarity">cosine similarity</a> between two vectors. We typically use cosine similarity to find the most relevant data for a given user prompt.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_3IfSIfuyeUfUUIc7SCNvw.png" /></figure><p>Imagine A as a vector or embedding of a newly provided user prompt, and B as a vector or embedding of your unique business data stored in Postgres. If you’re in healthcare, B could be a vectorized representation of a medication and treatment of a specific disease.</p><p>To find the most relevant treatment (vectors B) for the provided user symptoms (vector A), the database must calculate the dot product and magnitude for each combination of A and B. This process is repeated for every dimension (the ‘i’ in the formula) in the compared embeddings. If your database contains a million 1536-dimensional vectors (treatments and medications), Postgres must perform a million calculations over these multi-dimensional vectors for each user prompt.</p><p>Approximate nearest neighbor search (ANN) allows us to reduce CPU and GPU usage by creating specialized indexes for vectorized data. However, with ANN, you sacrifice some accuracy; you might not always receive the most relevant treatments or medications since the database does not compare all the vectors. Additionally, these indexes come with a cost: they take time to build and maintain, and they require dedicated memory and storage.</p><p>If you don’t want to be bound by the CPU and GPU resources of a single database server, you can consider using a distributed version of Postgres. Whenever compute resources become a bottleneck, you can scale your database cluster out and up by adding new nodes. Once a new node joins the cluster, a distributed database like <a href="https://github.com/yugabyte/yugabyte-db">YugabyteDB</a> will automatically rebalance the embeddings and immediately begin utilizing the new node’s resources.</p><h3>Problem #3: Data Privacy</h3><p>Whenever I demonstrate what a combination of LLM and Postgres can achieve, developers are inspired. They immediately try to match and apply these AI capabilities to the apps they work on.</p><p>However, there is always a follow-up question related to data privacy: ‘How can I leverage an LLM and embedding model without compromising data privacy?’ The answer is twofold.</p><p>First, if you don’t trust a particular LLM or embedding model provider, you can choose to use private or open-source models. For instance, use Mistral, LLaMA, or other models from Hugging Face that you can install and run from your own data centers or cloud environments.</p><p>Second, some applications need to comply with data residency requirements to ensure that all data used or generated by the private LLM and embedding model never leaves a specific location (data center, cloud region, or zone).</p><p>In this case, you can run several standalone Postgres instances, each working with data from a specific location, and allow the application layer to orchestrate access across multiple database servers.</p><p>Another option is to use the <a href="https://docs.yugabyte.com/preview/explore/multi-region-deployments/row-level-geo-partitioning/">geo-partitioning capabilities</a> of distributed Postgres deployments, which automate data distribution and access across multiple locations, simplifying application logic.</p><p>Let’s continue with the healthcare use case to see how geo-partitioning allows us to distribute information about medications and treatments across locations required by data regulators. Here I’ve used YugabyteDB as an example of a distributed Postgres deployment.</p><p>Imagine three hospitals, one in San Francisco, and the others in Chicago and New York. We deploy a single distributed YugabyteDB cluster, with several nodes in regions (or private data centers) near each hospital’s location.</p><p>To comply with data privacy and regulatory requirements, we must ensure that the medical data from these hospitals never leaves their respective data centers.</p><p>With geo-partitioning, we can achieve this as follows:</p><ul><li>Create Postgres tablespaces mapping them to cloud regions in the US West, Central and East. There is at least one YugabyteDB node in every region.</li></ul><pre>CREATE TABLESPACE usa_east_ts WITH (<br>    replica_placement = &#39;{&quot;num_replicas&quot;: 1, &quot;placement_blocks&quot;:<br>  [{&quot;cloud&quot;:&quot;gcp&quot;,&quot;region&quot;:&quot;us-east4&quot;,&quot;zone&quot;:&quot;us-east4-a&quot;,&quot;min_num_replicas&quot;:1}]}&#39;<br>);<br><br>CREATE TABLESPACE usa_central_ts WITH (<br>    replica_placement = &#39;{&quot;num_replicas&quot;: 1, &quot;placement_blocks&quot;:<br>  [{&quot;cloud&quot;:&quot;gcp&quot;,&quot;region&quot;:&quot;us-central1&quot;,&quot;zone&quot;:&quot;us-central1-a&quot;,&quot;min_num_replicas&quot;:1}]}&#39;<br>);<br><br>CREATE TABLESPACE usa_west_ts WITH (<br>    replica_placement = &#39;{&quot;num_replicas&quot;: 1, &quot;placement_blocks&quot;:<br>  [{&quot;cloud&quot;:&quot;gcp&quot;,&quot;region&quot;:&quot;us-west1&quot;,&quot;zone&quot;:&quot;us-west1-a&quot;,&quot;min_num_replicas&quot;:1}]}&#39;<br>);</pre><ul><li>Create the <strong>treatment</strong> table that keeps information about treatments and medications. Each treatment has an associated multi-dimensional vector — <strong>description_vector</strong> — that is generated for the treatment’s <strong>description</strong> with an embedding model. Finally, the table is partitioned by the <strong>hospital_location</strong> column.</li></ul><pre>CREATE TABLE treatment (<br>    id int,<br>    name text,<br>    description text,<br>    description_vector vector(1536),<br>    hospital_location text NOT NULL<br>)<br>PARTITION BY LIST (hospital_location);</pre><ul><li>The partitions’ definition is as follows. For instance, the data of the hospital3 which is in San Francisco will be automatically mapped to the <strong>usa_west_ts</strong> whose data belongs to the database nodes in the US West.</li></ul><pre>CREATE TABLE treatments_hospital1 PARTITION OF treatment(id, name, description, description_vector, PRIMARY KEY (id, hospital_location))<br>FOR VALUES IN (&#39;New York&#39;) TABLESPACE usa_east_ts;<br><br>CREATE TABLE treatments_hospital2 PARTITION OF treatment(id, name, description, description_vector, PRIMARY KEY (id, hospital_location))<br>FOR VALUES IN (&#39;Chicago&#39;) TABLESPACE usa_central_ts;<br><br>CREATE TABLE treatments_hospital3 PARTITION OF treatment(id, name, description, description_vector, PRIMARY KEY (id, hospital_location))<br>FOR VALUES IN (&#39;San Francisco&#39;) TABLESPACE usa_west_ts;</pre><p>Once you’ve deployed a geo-partitioned database cluster and defined the required tablespaces with partitions, let the application connect to it and allow the LLM to access the data. For instance, the LLM can query the treatment table directly using:</p><pre>select name, description from treatment where <br>1 - (description_vector ⇔ $user_prompt_vector) &gt; 0.8<br>and hospital_location = $location</pre><p>The distributed Postgres database will automatically route the request to the node that stores data for the specified <strong>hospital_location</strong>. The same applies to INSERTs and UPDATEs; changes to the treatment table will always be stored in the partition-&gt;tablespace-&gt;nodes belonging to that hospital’s location. These changes will never be replicated to other locations.</p><h3>Problem #4: High Availability</h3><p>Although Postgres was designed to function in a single-server configuration, this doesn’t mean it can’t be run in a highly available setup. Depending on your desired recovery point objective (RPO) and recovery time objective (RTO), there are <a href="https://www.yugabyte.com/postgresql/postgresql-high-availability/">several options</a>.</p><p>So, how is distributed Postgres useful? With distributed PostgreSQL, your gen AI apps can remain operational even during zone, data center, or regional outages.</p><p>For instance, with YugabyteDB, you simply deploy a multi-node distributed Postgres cluster and let the nodes handle fault tolerance and high availability. The nodes communicate directly. If one node fails, the others will detect the outage. Since the remaining nodes have redundant, consistent copies of data, they can immediately start processing application requests that were previously sent to the failed node. YugabyteDB provides RPO = 0 (no data loss) and RTO within the <a href="https://dev.to/yugabyte/yugabytedb-recovery-time-objective-rto-with-pgbench-continuous-availability-with-max-15-seconds-latency-on-failure-2po4">range of 3–15 seconds</a> (depending on the database and TCP/IP configuration defaults).</p><p>In this way, you can build gen AI apps and autonomous agents that never fail, even during region-level incidents and other catastrophic events.</p><h3>Summary</h3><p>Thanks to extensions like pgvector, PostgreSQL has evolved beyond traditional relational database use cases and is now a strong contender for generative AI applications. However, working with embeddings might pose some challenges, including significant memory and storage consumption, compute-intensive similarity searches, data privacy concerns, and the need for high availability.</p><p>Distributed PostgreSQL deployments offer scalability, load balancing, and geo-partitioning, ensuring data residency compliance and uninterrupted operations. By leveraging these distributed systems, you can build scalable gen AI applications that scale and never fail.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=64f2470cf1a1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Will Postgres use my index or not?]]></title>
            <link>https://medium.com/@magda7817/will-postgres-use-my-index-or-not-64f48089e597?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/64f48089e597</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[software-engineering]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Thu, 11 Apr 2024 18:33:35 GMT</pubDate>
            <atom:updated>2024-04-11T18:33:35.047Z</atom:updated>
            <content:encoded><![CDATA[<p>Since joining Postgres subreddit, I spend some time daily checking for interesting questions and conversations. One of the last questions I bumped into was about <a href="https://www.reddit.com/r/PostgreSQL/comments/1c1c0ah/sql_query_over_indexed_and_notindexed_columns/?utm_source=share&amp;utm_medium=web2x&amp;context=3">Postgres indexes and sounded as follows</a>:</p><blockquote>Hi folks, how does this scenario works under the hood. Imagine table Person along with Id(key), Name(indexed), DateOfBirth(not indexed).</blockquote><blockquote>Select Name, DateOfBirth<br>from Person<br>where Name=’Jon’ and DateOfBirth = ‘1.1.2000’</blockquote><blockquote>I got confused here, how indexed and non-indexed columns work together, does the db scan the actual table bcs of the non-indexed column or does it take it from the indexed one?</blockquote><p>I had a lunch break and decided to provide a detailed answer. As a result, the answer was good enough for a post. So, let’s take a look at my findings.</p><p>Postgres will try its best to select the most efficient execution plan. It might or might not use the index. The decision is based on index selectivity, the number of records in the table, the columns that you want to retrieve, and many other factors.</p><p>Let’s do this simple experiment. First, I’m creating a sample table and populating it with 100 dummy records:</p><pre>create table account(id int, name text, birthday date);<br><br>INSERT INTO account (id, name, birthday)<br>SELECT <br>  id, <br>  (ARRAY[<br>    &#39;John&#39;, &#39;Emma&#39;, &#39;Olivia&#39;, &#39;Ava&#39;, &#39;Isabella&#39;, <br>    &#39;Sophia&#39;, &#39;Charlotte&#39;, &#39;Mia&#39;, &#39;Amelia&#39;, &#39;Harper&#39;, <br>    &#39;Evelyn&#39;, &#39;Abigail&#39;, &#39;Emily&#39;, &#39;Elizabeth&#39;, &#39;Mila&#39;, <br>    &#39;Ella&#39;, &#39;Avery&#39;, &#39;Sofia&#39;, &#39;Camila&#39;, &#39;Aria&#39;, <br>    &#39;Scarlett&#39;, &#39;Victoria&#39;, &#39;Madison&#39;, &#39;Luna&#39;, &#39;Grace&#39;, <br>    &#39;Chloe&#39;, &#39;Penelope&#39;, &#39;Layla&#39;, &#39;Riley&#39;, &#39;Zoey&#39;<br>  ])[floor(random() * 30 + 1)::int],<br>  (&#39;1970-01-01&#39;::date + (random() * (CURRENT_DATE - &#39;1970-01-01&#39;::date))::int) AS birthday<br>FROM generate_series(1, 100) AS id;</pre><p>Next, let’s index the name column and force Postgres to update its statistics (which Postgres reiles on to select the most appropriate execution plans) using the vacuum analyze command:</p><pre>create index on account(name);<br><br>vacuum analyze;</pre><p>After, let’s see how many Johns we have:</p><pre>select name, birthday from account where name = &#39;John&#39;;<br><br>name |  birthday <br>John | 1972-05-14 <br>John | 1989-02-04 <br>John | 1970-02-08 <br>(3 rows)</pre><p>Has Postgres used the index to get this data? Let’s check using the explain analyze statement:</p><pre>explain analyze select name, birthday from account where name = &#39;John&#39;;<br>                                        QUERY PLAN<br> Seq Scan on account  (cost=0.00..2.25 rows=5 width=10)(actual time=0.060..0.069 rows=5 loops=1)<br>   Filter: (name = &#39;John&#39;::text)<br>   Rows Removed by Filter: 95<br> Planning Time: 0.339 ms<br> Execution Time: 0.146 ms<br>(5 rows)</pre><p>Nope, Postgres decided to ignore the index and scanned the table from start to finish (see the seq scan access method). The database will continue doing the full table scan if you add the birthday = &#39;1989-02-04&#39; to the condition:</p><pre>explain analyze select name, birthday from account where name = &#39;John&#39; and birthday = &#39;1989-02-04&#39;;<br><br>                                 QUERY PLAN<br><br> Seq Scan on account  (cost=0.00..2.50 rows=1 width=10) (actual time=0.059..0.060 rows=0 loops=1)<br>   Filter: ((name = &#39;John&#39;::text) AND (birthday = &#39;1989-02-04&#39;::date))<br>   Rows Removed by Filter: 100<br> Planning Time: 0.492 ms<br> Execution Time: 0.110 ms<br>(5 rows)</pre><p>There are 100 records in the table and Postgres doesn’t bother doing the index scan.</p><p>Next, let’s add 10000 records to the table and see how the execution plan changes.</p><pre>INSERT INTO account (id, name, birthday)<br>SELECT <br>  id, <br>  (ARRAY[<br>    &#39;John&#39;, &#39;Emma&#39;, &#39;Olivia&#39;, &#39;Ava&#39;, &#39;Isabella&#39;, <br>    &#39;Sophia&#39;, &#39;Charlotte&#39;, &#39;Mia&#39;, &#39;Amelia&#39;, &#39;Harper&#39;, <br>    &#39;Evelyn&#39;, &#39;Abigail&#39;, &#39;Emily&#39;, &#39;Elizabeth&#39;, &#39;Mila&#39;, <br>    &#39;Ella&#39;, &#39;Avery&#39;, &#39;Sofia&#39;, &#39;Camila&#39;, &#39;Aria&#39;, <br>    &#39;Scarlett&#39;, &#39;Victoria&#39;, &#39;Madison&#39;, &#39;Luna&#39;, &#39;Grace&#39;, <br>    &#39;Chloe&#39;, &#39;Penelope&#39;, &#39;Layla&#39;, &#39;Riley&#39;, &#39;Zoey&#39;<br>  ])[floor(random() * 30 + 1)::int],<br>  (&#39;1970-01-01&#39;::date + (random() * (CURRENT_DATE - &#39;1970-01-01&#39;::date))::int) AS birthday<br>FROM generate_series(101, 10_000) AS id;<br><br>vacuum analyze;</pre><p>Now, when I search for Johns, Postgres prefers doing the index scan because I have more records, and the index has good selectivity:</p><pre>explain analyze select name, birthday from account where name = &#39;John&#39;;<br><br>                         QUERY PLAN<br>Bitmap Heap Scan on account  (cost=7.14..67.76 rows=369 width=10) (actual time=0.100..0.602 rows=369 loops=1)<br>   Recheck Cond: (name = &#39;John&#39;::text)<br>   Heap Blocks: exact=56<br>       Bitmap Index Scan on account_name_idx  (cost=0.00..7.05 rows=369 width=0) (actual time=0.064..0.064 rows=369 loops=1)<br>       Index Cond: (name = &#39;John&#39;::text)<br> Planning Time: 0.509 ms<br> Execution Time: 0.672 ms<br>(7 rows)</pre><p>Reading the plan from bottom to top you can see that Postgres uses the index to build a bitmap of pages that hold records with John (Bitmap Index Scan) and then uses the bitmap to access specific pages with data in the account table (Bitmap Heap Scan). This last phase is necessary because you want Postgres to return the birthday column which is not part of the index. But if you decide that you don&#39;t need the birthday in the output and you just want to get the namecolumn, then Postgres will perform the most efficient index-only scan:</p><pre>explain analyze select name from account where name = &#39;John&#39;;<br>                            <br>                      QUERY PLAN<br><br> Index Only Scan using account_name_idx on account  (cost=0.29..10.74 rows=369 width=6) (actual time=0.051..0.085 rows=369 loops=1)<br>   Index Cond: (name = &#39;John&#39;::text)<br>   Heap Fetches: 0<br> Planning Time: 0.179 ms<br> Execution Time: 0.136 ms<br>(5 rows)</pre><p>Finally, if I execute the query from the original question, then the execution plan should be as follows (for my data set with 10000 records):</p><pre>explain analyze select name, birthday from account where name = &#39;John&#39; and birthday = &#39;1989-02-04&#39;;<br><br>                         QUERY PLAN<br><br> Bitmap Heap Scan on account  (cost=7.05..68.59 rows=1 width=10) (actual time=0.725..0.727 rows=0 loops=1)<br>   Recheck Cond: (name = &#39;John&#39;::text)<br>   Filter: (birthday = &#39;1989-02-04&#39;::date)<br>   Rows Removed by Filter: 369<br>   Heap Blocks: exact=56<br>       Bitmap Index Scan on account_name_idx  (cost=0.00..7.05 rows=369 width=0) (actual time=0.117..0.117 rows=369 loops=1)<br>           Index Cond: (name = &#39;John&#39;::text)<br>Planning Time: 0.542 ms<br>Execution Time: 0.813 ms<br>(9 rows)</pre><p>Postgres will use the index on the name column to build a bitmap of pages with John and then will perform the bitmap heap scan over the table filtering out records with Johns that don&#39;t satisfy the birthday condition.</p><p>Hope it makes sense. If you’d like to explore more:</p><ul><li>Make sure the explain and explain analyze statements become your regular tools.</li><li>Familiarize yourself with the following great resource: <a href="https://use-the-index-luke.com/">https://use-the-index-luke.com/</a></li><li>Do a deep dive into the Postgres indexing capabilities: <a href="https://postgrespro.com/community/books/internals">https://postgrespro.com/community/books/internals</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=64f48089e597" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Has Figma Reinveted the Wheel With PostgreSQL?]]></title>
            <link>https://medium.com/@magda7817/why-has-figma-reinveted-the-wheel-with-postgresql-3a1cb2e9297c?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/3a1cb2e9297c</guid>
            <category><![CDATA[yugabytedb]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Fri, 29 Mar 2024 14:56:06 GMT</pubDate>
            <atom:updated>2024-03-29T15:01:59.136Z</atom:updated>
            <content:encoded><![CDATA[<h3>Why Has Figma Reinvented the Wheel With PostgreSQL?</h3><p>A few weeks ago, Figma published an <a href="https://www.figma.com/blog/how-figmas-databases-team-lived-to-tell-the-scale/">article</a> introducing their own sharding solution for PostgreSQL. The Figma engineering team was aware of, and evaluated, several existing options that could have made their database layer horizontally scalable. But still, they decided to build their own sharding solution from scratch.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x2ePchGXnYnYniYkr6zJUw.png" /></figure><p>In this article, I’ll share my thoughts (and sometimes speculate) on the following:</p><ul><li>First, why a knowledgeable and experienced Figma engineering team decided to reinvent the wheel instead of selecting one of the existing options from a broad PostgreSQL ecosystem?</li><li>Second, how does the future look for Figma’s custom solution? Will the team double down on their sharding solution or eventually migrate to one of the existing and more mature options?</li><li>Lastly, if Figma eventually migrates to another option from the PostgreSQL ecosystem, then what that option will be?</li></ul><h3>Quick Solution Overview</h3><p>I do understand that not everyone has time to read <a href="https://www.figma.com/blog/how-figmas-databases-team-lived-to-tell-the-scale/">Figma’s article</a> from cover to cover, that’s why I’d like to give a brief overview of their custom solution.</p><p>Sharding is a technique that distributes data and load across several standalone database instances. It allows you to split the original dataset into shards, which are then distributed across multiple database instances.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uwKFESDpg5JJzBsHr-x4ZQ.png" /></figure><p>To get sharding working, you need to come up with a sharding key and a hash function. Then, you write the logic that uses the hash function to map a key with an associated record to a shard, and then the shard to a database node.</p><p>The Figma team solved this first step easily by selecting the most suitable sharding keys for their dataset and the hash function. Also, they implemented a <a href="https://medium.com/@magda7817/colocated-and-interleaved-tables-in-distributed-sql-databases-tradeoffs-f2e7b1b9d68e">data colocation approach</a> that allows storing related data together on the same shards/nodes. For instance, all the comments that were left for a specific Figma file are stored together on the same node with the file record.</p><p>However, this first step in any sharding implementation is the most straightforward. The most challenging parts come next. Once you’ve distributed your data across several database servers, you need to decide on:</p><ul><li>How to maintain data integrity by supporting global unique indexes and foreign keys?</li><li>How to coordinate, commit, and roll back cross-shard/node transactions?</li><li>How to roll out schema changes atomically across all database nodes?</li><li>How to reshard and rebalance the data when necessary?</li><li>How to route the queries from the application layer? Should there be a coordinator, or does this become the responsibility of app developers?</li><li>How to make the overall solution highly available (with standbys, replicas, failover solutions)?</li><li>And many other considerations…</li></ul><p>The Figma team acknowledged most of these challenges and made efforts to address several of them. For instance, they introduced a coordinator component, named DBProxy, which significantly eases the work for application developers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*86MEcDRLg0FJkhYTaX3e_A.png" /></figure><p>The coordinator plays a pivotal role in any sharding solution, provided it’s fully aware of the data distribution across nodes. This component might be referred to by various names, such as coordinator, router, director, or proxy. The client or application connects to the coordinator, which then either routes requests to the specific database nodes or broadcasts them across the entire fleet.</p><h3>Why Custom Sharding Solution?</h3><p>Overall, as an engineer, you will never regret taking part in the development of a sharding solution. It’s a complex engineering problem with many non-trivial tasks to solve. But it’s also a big investment for a company that sponsors the development — someone needs to build and then support/maintain the solution in production.</p><p>The Figma team certainly was aware of the trade-offs between the custom vs. existing solution and evaluated several existing options:</p><blockquote>There are many popular open source and managed solutions for horizontally sharded databases that are compatible with Postgres or MySQL. During our evaluation, we explored CockroachDB, TiDB, Spanner, and Vitess.</blockquote><p>But still, they decided to go ahead and create their own sharding solution from scratch. Why would they do that?</p><p>They justify their custom implementation by saying that:</p><blockquote>However, switching to any of these alternative databases would have required a complex data migration to ensure consistency and reliability across two different database stores… Given our very aggressive growth rate, we had only months of runway remaining. De-risking an entirely new storage layer and completing an end-to-end-migration of our most business-critical use cases would have been extremely risky on the necessary timeline.</blockquote><p>In short, it sounds like some of the evaluated options would have worked for them, but they didn’t have enough time to migrate to one of those existing solutions. This does sound reasonable if you need to transition to distributed databases such as TiDB or Spanner. However, this doesn’t explain why <a href="https://www.citusdata.com/">CitusData</a>, the default go-to sharding extension for PostgreSQL, wouldn’t work for them. They haven’t even added CitusData to their evaluation list.</p><p>However, if you continue reading the article carefully, you’ll notice the team mentioning the following:</p><blockquote>over the past few years, we’ve developed a lot of expertise on how to reliably and performantly run <strong>RDS Postgres</strong> in-house. While migrating, we would have had to rebuild our domain expertise from scratch.</blockquote><p>Figma doesn’t use the open-source distribution of PostgreSQL. Instead, they utilize PostgreSQL as a service by subscribing to <a href="https://aws.amazon.com/rds/">Amazon RDS</a>. There’s an interesting, often overlooked fact about PostgreSQL managed services provided by large cloud providers and smaller vendors. While these services usually offer all the core PostgreSQL capabilities, the list of supported extensions is at the mercy of the service provider.</p><p>Now, we have CitusData, a mature PostgreSQL extension for sharding, and we know that Figma uses RDS, a fully-managed PostgreSQL service by Amazon. However, if you check <a href="https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html">the list of PostgreSQL extensions</a> supported by RDS, CitusData isn’t included:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sbOQ_ahsrf1B2UUSIzbuTw.png" /></figure><p>So, now, let me speculate. The real reason why Figma reinvented the wheel by creating their own custom solution for sharding might be as straightforward as this — <em>Figma wanted to stay on RDS, and since Amazon had decided not to support the CitusData extension in the past, the Figma team had no choice but to develop their own sharding solution from scratch.</em></p><h3>What Does the Future Look Like?</h3><p>What I truly like about the Figma engineering team is their transparency. They have been very open about the choices and trade-offs they had to confront. I deeply respect their decision and believe that the path they chose was the most sensible at the time.</p><p>However, does this imply that the Figma team will continue to use and invest in their own sharding solution? What does the future hold?</p><p>The team gives us the following hint:</p><blockquote>Once we’ve bought ourselves sufficient runway, <strong>we will also reassess our original approach</strong> of in-house RDS horizontal sharding. We started this journey 18 months ago with extremely tight timeline pressure. NewSQL stores have continued to evolve and mature. We <strong>will finally have bandwidth to reevaluate the tradeoffs</strong> of continuing down our current path versus switching to an open source or managed solution.</blockquote><p>Overall, the future might look as follows:</p><ul><li>Option #1: Figma continues investing in its own custom solution for sharding and makes it open source once it becomes mature enough. However, they would then need to find a way to differentiate from the CitusData extension.</li><li>Option #2: Considering that Figma is a customer of Amazon RDS, they might migrate to <a href="https://aws.amazon.com/blogs/aws/join-the-preview-amazon-aurora-limitless-database/">Amazon Aurora Limitless</a> once this service is blessed for production by Amazon. Aurora Limitless is a sharding solution for Postgres for AWS users. However, they would need to assess the TCO (Total Cost of Ownership) carefully, as many who have evaluated this new service suggest that “Limitless” might also imply limitless costs.</li><li>Option #3: Figma migrates to <a href="https://www.yugabyte.com/postgresql/distributed-postgresql/">one of the existing solutions from the PostgreSQL ecosystem</a> that enables horizontal scalability or full distribution. This could be the CitusData extension or even <a href="https://www.yugabyte.com/">YugabyteDB</a>, a distributed shared-nothing database built on Postgres. YugabyteDB might be a likely option, considering that the team previously explored TiDB and CockroachDB.</li></ul><p>Alright, I hope you enjoyed this “investigation.” Let’s wish the Figma team luck and keep an eye on how the story of their sharding solution evolves!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3a1cb2e9297c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating AI Data Analyst With DBeaver]]></title>
            <link>https://medium.com/@magda7817/creating-ai-data-analyst-with-dbeaver-fa7bb8d88e7c?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/fa7bb8d88e7c</guid>
            <category><![CDATA[yugabytedb]]></category>
            <category><![CDATA[dbeaver]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Thu, 14 Mar 2024 18:44:32 GMT</pubDate>
            <atom:updated>2024-03-14T18:44:32.289Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CMBzyrFtBskGZblXLxoZWA.png" /></figure><p>Business people leverage data for decision-making, sourced either publicly or from their company’s databases. However, when internal data is necessary, most lack the expertise in SQL, Cypher, or other database-specific languages. This creates a gap bridged by data analysts who act as business-to-database interpreters, translating human questions into the language the database understands.</p><p>These days, large language models (LLMs) like GPT or Mistral can fulfill some of the tasks performed by data analysts. For instance, LLMs can:</p><ul><li>Understand user queries in various languages (e.g., English, Spanish, Chinese).</li><li>Generate SQL queries based on those questions.</li><li>Use tools and APIs to execute these SQL queries on databases to retrieve relevant business data.</li></ul><p>In this article, I’ll walk you through the steps of creating an AI data analyst using DBeaver. You’ll see that it’s already quite straightforward to “onboard” a junior-level AI specialist onto your team, who can act as that interpreter between the business people and the database, allowing human data analysts to focus on more sophisticated tasks.</p><h3>Exploring AI Chat in DBeaver</h3><p>If you regularly work with data, then <a href="https://dbeaver.com/">DBeaver</a> should be no stranger to you. It’s a popular tool that can connect to hundreds of databases, allowing you to query or manipulate data. The tool is used by developers, administrators, analysts, and everyone working with data.</p><p>DBeaver has a broad ecosystem of editions, features, and extensions, making it a versatile tool for various use cases. <a href="https://dbeaver.com/docs/dbeaver/AI-Smart-Assistance/#understanding-the-ai-integration-in-dbeaver">AI Chat</a> is one of its most recent capabilities. With AI Chat, you can type questions in plain English and let DBeaver generate, and then execute, SQL requests over your database.</p><p>As you can guess, AI Chat is a foundational capability for the AI data analyst. Thus, let’s give it a try.</p><p>I connected DBeaver to my YugabyteDB database cluster, which stores a <a href="https://neon.tech/docs/import/import-sample-data#netflix-data">Netflix dataset</a> with over 8800 movies and shows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6r89XAm6dKFh-8D4Wh5Npg.png" /></figure><p>Next, to enable the AI Chat, I jumped to <strong>Window-&gt;Preferences-&gt;AI (GPT)</strong> and configured the following settings:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x9sXRuo5SIVigh5heGdM_w.png" /></figure><ul><li>Checked the <strong>Enable smart completion</strong> box to activate the AI capabilities of DBeaver</li><li>Turned on the <strong>Execute SQL immediately setting</strong> to allow the future AI data analyst to execute queries autonomously (we’ll discuss later how to set up data access permissions for the analyst)</li><li>Provided my <a href="https://platform.openai.com/">OpenAI key</a> in the <strong>API Token</strong> field</li></ul><p><strong><em>Note: </em></strong><em>I’m using DBeaver Enterprise Edition which comes with built-in support for AI. If you’re on DBeaver Community Edition, then you need to </em><a href="https://dbeaver.com/docs/dbeaver/AI-Smart-Assistance/#installing-the-gpt-extension"><em>install the AI (GPT) extension</em></a><em> first.</em></p><p>After applying the configuration settings, I opened the AI Chat and allowed the sharing of database metadata (table and column names) with OpenAI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B0SNZI5X2Su6oSG5y9qJxg.png" /></figure><p>By sharing the metadata with the LLM, you’re effectively “onboarding” the AI analyst to your team. The analyst will learn about the existing tables, columns, and their dependencies by analyzing the metadata.</p><p>Finally, I tested the Chat interface by asking the following question:</p><p><em>In which years did we release most of the action movies? Get the top three years for me.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GF7TlG1GNw4ioi6i63Kj5Q.png" /></figure><p>The OpenAI GPT model successfully generated a valid SQL request using the PostgreSQL dialect (recognizing that <a href="https://www.yugabyte.com/">YugabyteDB</a> is a distributed version of Postgres), and then DBeaver retrieved the data for me.</p><h3>Setting Up Role for AI Data Analyst</h3><p>The AI Chat interface of DBeaver is going to be the communication channel between business users and the AI data analyst. However, having the Chat alone is not enough. I want AI to work autonomously without my constant supervision. To make this happen, I first need to create a role with a granular set of permissions and, second, have visibility into the queries that were generated by the AI (in case I want to share more knowledge with the AI or fine-tune its behavior).</p><p>I managed to address these two requirements with another solution from the DBeaver ecosystem, Team Edition, which is an advanced data management tool for teamwork. With Team Edition, you can configure a role for the AI data analyst and monitor its behavior.</p><p>First, I <a href="https://dbeaver.com/docs/cloudbeaver/TE-Server-Configuration/">installed the Team Edition server</a> in my local Docker environment. You need the server to manage data access at the user level and keep an eye on how the data is being used across the teams.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cwHIJFdIl1x-YPRAVg3w4Q.png" /></figure><p>Next, I created the <strong>Netflix Streaming Platform</strong> project that will be shared with the AI analyst:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lrPhB1peR4u9w-px9nlpsA.png" /></figure><p>After that, I jumped to the <strong>Access Management</strong> menu and created the <strong>ai_analys</strong> t role:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oq60pb2t3PCbDqOwqi7f8A.png" /></figure><p>Team Edition supports <a href="https://dbeaver.com/2023/03/24/roles-in-team-edition-manager-editor-viewer/">various roles</a> for team members with different skills and responsibilities. I chose the <strong>Manager</strong> role for the AI analyst as long as this role is suitable for specialists who are well-versed in writing SQL queries and need to have direct access to the database.</p><p>Once the role was created, I granted it access to the <strong>Netflix Streaming Platform</strong> project:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OR9x-mjSVXhfAx7wjs6JzQ.png" /></figure><p>As the next step, I configured AI-related settings on the server side:</p><ul><li>Enabled the <strong>AI Services</strong>:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TL6NY4VflMwcY-fwkfcDxw.png" /></figure><ul><li>Selected an LLM provider with a model, and provided my API key:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5B5Eg5th-HP3PcTrYhtF2A.png" /></figure><p>Then, I completed the Team Edition-side configuration by adding a YugabyteDB connection to the <strong>Netflix Streaming Platform</strong> project, which will allow the AI data analyst to access the database directly using the specified connection.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MEhicuH-7PErTCsk3_aVbg.png" /></figure><p>Finally, I used this new database connection to create a read-only <strong>ai_analyst</strong> role at the database level:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eA3rRLmLHa-z_ZHWSGomeg.png" /></figure><p>How is this database-level role different from the one we created earlier with Team Edition? Both roles serve different purposes:</p><ul><li>The <strong>Team Edition-level role</strong> is used to authenticate with the Team Edition server and to gain access to a list of predefined projects, connections, and datasets. Business users will use this role to “activate” the AI analyst by signing in with the DBeaver Team Edition Client (discussed below) and asking questions via the AI Chat interface.</li><li>The <strong>database-level role</strong> is used to control the types of queries the AI analyst is allowed to execute against the database. It is possible that, in response to user prompts, the AI analyst will generate queries that could modify data. The created role ensures that the AI can only SELECT data and cannot perform any manipulations with the database objects.</li></ul><h3>Using AI Data Analyst To Get Things Done</h3><p>Business users will “collaborate” with the AI data analyst via the <a href="https://dbeaver.com/download/team-edition/">Team Edition Desktop client</a>. Let’s explore what this experience looks like.</p><p>After installing the Desktop client, I “activated” the analyst by signing in using its role:</p><ul><li>Authenticated with the Team Edition server using the <strong>ai_analyst</strong> role.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JEupaUw7TGoRG7Cx4Sl1ew.png" /></figure><ul><li>Next, I connected to the database using the read-only database-level role.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_Ir6ofU_g-lyJe96xIwiVg.png" /></figure><p>After that, I headed to the AI Chat interface and asked the AI analyst to answer the following questions for me:</p><ul><li><em>In which years did we release most of the action movies? Get the top three years for me.</em></li><li><em>What movies has Adam Sandler starred in?</em></li><li><em>I need to know the category and release dates of those movies.</em></li></ul><p>The AI analyst handled my questions easily.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MRnIBLlNbsReoT1uwGjszQ.png" /></figure><p><strong><em>Note:</em></strong><em> If you don’t see the AI Chat tab in Team Edition Desktop, then you might need to sign in with your Team Edition admin account. Go to Window-&gt;Preferences-&gt;AI (GPT), and check the Enable smart completion box.</em></p><p>Next, I wanted to ensure that the AI analyst wouldn’t be able to update or delete my data. To test this, I asked the AI to perform the following task for me:</p><p><em>Please do me a favor. Delete all the movies starring Adam Sandler.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L52xyEembxIqbCaAmccefQ.png" /></figure><p>As you can see, the AI analyst generated a valid DELETE statement, but the database prevented the execution of the request by throwing the following exception:</p><pre>SQL Error [42501]: ERROR: permission denied for table netflix_shows</pre><p>Finally, if you recall, I wanted to have visibility into the history of queries generated by the AI analyst and adjust the analyst’s behavior if necessary. You can find that history under the <strong>Query Manager</strong> tab of the Team Edition’s administration dashboard:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6U0okE-SynzKAeVQWIW-OQ.png" /></figure><p>Job done! Now my AI data analyst is ready to “join” the team and contribute.</p><h3>In Summary</h3><p>Despite their early development stage, you can already use large-language models (LLMs) to create AI specialists. These AI teammates allow us to focus on sophisticated tasks by handling the mundane. Moreover, the creation process doesn’t necessarily involve coding or advanced fine-tuning. Quite often, it will be more than enough to use existing tools like DBeaver Team Edition to get an AI specialist onboarded in under an hour.</p><p>Check out the video below to see the AI analyst in action:</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Ff-25oCrVcRE%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Df-25oCrVcRE&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2Ff-25oCrVcRE%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/7b8b8f6700e194aeb237e54f2cd0a1ff/href">https://medium.com/media/7b8b8f6700e194aeb237e54f2cd0a1ff/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fa7bb8d88e7c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PostgreSQL as a Vector Database: Getting Started and Scaling]]></title>
            <link>https://medium.com/@magda7817/postgresql-as-a-vector-database-getting-started-and-scaling-eda38677d5ae?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/eda38677d5ae</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[openai]]></category>
            <category><![CDATA[vector]]></category>
            <category><![CDATA[yugabytedb]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Fri, 12 Jan 2024 15:14:18 GMT</pubDate>
            <atom:updated>2024-01-12T15:14:18.348Z</atom:updated>
            <content:encoded><![CDATA[<p>PostgreSQL has a rich ecosystem of extensions and solutions that let us use the database for general-purpose AI applications. This guide walks you through the steps required to build generative AI applications using PostgreSQL as a vector database.</p><p>We’ll begin with the <a href="https://github.com/pgvector/pgvector">pgvector extension</a>, which enables Postgres with the capabilities specific to vector databases. Then, we’ll review methods to enhance the performance and scalability of AI apps running on PostgreSQL. In the end, we’ll have a fully functional generative AI application recommending Airbnb listings to those traveling to San Francisco.</p><h3>Airbnb Recommendation Service</h3><p>The <a href="https://github.com/YugabyteDB-Samples/openai-pgvector-lodging-service">sample application</a> is a lodging recommendation service. Imagine that you’re planning to visit San Francisco and wish to stay in a nice neighborhood near the Golden Gate Bridge. You go to the service, type in your prompt, and the app will suggest the three most relevant lodging options.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HXFvaUVgSMJXOaYUQJV6yQ.png" /></figure><p>The app supports two distinct modes:</p><ul><li><strong>OpenAI Chat Mode: </strong>In this mode, the application backend leverages the <a href="https://platform.openai.com/docs/guides/gpt/chat-completions-api">OpenAI Chat Completion API</a> and the GPT-4 model to generate lodging recommendations based on the user’s input. While this mode is not the focus of this guide, you are encouraged to experiment with it.</li><li><strong>Postgres Embeddings Mode:</strong> Initially, the backend employs the <a href="https://platform.openai.com/docs/guides/embeddings/what-are-embeddings">OpenAI Embeddings API</a> to convert the user’s prompt into an embedding (a vectorized representation of the text data). Next, the app does a similarity search in Postgres or YugabyteDB (distributed PostgreSQL) to find Airbnb properties that match the user’s prompt. Postgres takes advantage of the pgvector extension for the similarity search in the database. This guide will delve into the implementation of this particular mode within the application.</li></ul><h3>Prerequisites</h3><ul><li>An OpenAI <a href="https://platform.openai.com/">subscription</a> with access to an embedding model.</li><li>The latest <a href="https://github.com/nodejs/release#release-schedule">Node.js version</a></li><li><a href="https://github.com/git-lfs/git-lfs">git-lfs</a> — Git needs this extension to download a large dataset file used by the sample application.</li><li>The latest version of <a href="https://docs.docker.com/desktop/">Docker</a></li></ul><h3>Start PostgreSQL With Pgvector</h3><p>The pgvector extension adds all the essential capabilities of a vector database to Postgres. It lets you store and process vectors with thousands of dimensions, calculate Euclidean and cosine distances between vectorized data, and perform exact and approximate nearest-neighbor searches.</p><ol><li>Start an instance of Postgres with pgvector in Docker:</li></ol><pre>docker run --name postgresql \<br>    -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password \<br>    -p 5432:5432 \<br>    -d ankane/pgvector:latest</pre><p>2. Connect to the database container and open a psql session:</p><pre>docker exec -it postgresql psql -h 127.0.0.1 -p 5432 -U postgres</pre><p>3. Enable the pgvector extension:</p><pre>create extension vector;</pre><p>4. Confirm the vector is present in the extensions list:</p><pre>select * from pg_extension;<br><br>  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition<br>-------+---------+----------+--------------+----------------+------------+-----------+--------------<br> 13561 | plpgsql |       10 |           11 | f              | 1.0        |           |<br> 16388 | vector  |       10 |         2200 | t              | 0.5.1      |           |<br>(2 rows)</pre><h3>Load Airbnb Data Set</h3><p>The application uses an Airbnb dataset that has over 7,500 properties listed in San Francisco for rental. Each listing provides a detailed property description, including the number of rooms, types of amenities, location, and other features. This information is a perfect fit for the similarity search against user prompts.</p><p>Follow the next steps to load the dataset into the started Postgres instance:</p><ol><li>Clone the application repository:<br><em>Make sure you have the </em><a href="https://github.com/git-lfs/git-lfs"><em>git-lfs</em></a><em> installed. The project’s dataset file is stored in the Git Large File Storage.</em></li></ol><pre>git clone https://github.com/YugabyteDB-Samples/openai-pgvector-lodging-service.git</pre><p>2. Copy the Airbnb schema and data files to the Postgres container (replace the {app_dir} with a full path to the application directory):</p><pre>docker cp {app_dir}/sql/airbnb_listings.sql postgresql:/home<br>docker cp {app_dir}/sql/airbnb_listings_with_embeddings.csv postgresql:/home</pre><p>3. Load the dataset to Postgres (note, it can take a minute):</p><pre>docker exec -it postgresql psql -U postgres -c &#39;\i /home/airbnb_listings.sql&#39;<br>docker exec -it postgresql psql -U postgres \<br>    -c &quot;\copy airbnb_listing from /home/airbnb_listings_with_embeddings.csv with DELIMITER &#39;^&#39; CSV&quot;</pre><p>Every Airbnb embedding is a 1536-dimensional array of floating-point numbers. It’s a numerical/mathematical representation of an Airbnb property’s description.</p><pre>docker exec -it postgresql \<br>    psql -h 127.0.0.1 -p 5432 -U postgres \<br>    -c &quot;\x on&quot; \<br>    -c &quot;select name, description, description_embedding from airbnb_listing limit 1&quot;<br>    <br>  <br># Truncated output<br>name                 | Monthly Piravte Room-Shared Bath near Downtown !3<br>description           | In the center of the city in a very vibrant neighborhood. Great access to other parts of the city with all modes of public transportation steps away Like the general theme of San Francisco, our neighborhood is a melting pot of different people with different lifestyles ranging from homeless people to CEO&#39;&#39;s<br>description_embedding | [0.0064848186,-0.0030366974,-0.015895316,-0.015803888,-0.02674906,-0.0083198985,-0.0063770646,0.010318241,-0.011003947,-0.037981577,-0.008783566,-0.0005710134,-0.0028015983,-0.011519859,-0.02011404,-0.02023159,0.03325347,-0.017488765,-0.014902675,-0.006527267,-0.027820067,0.010076611,-0.019069154,-0.03239144,-0.013243919,0.02170749,0.011421901,-0.0044701495,-0.0005861153,-0.0064978795,-0.0006775427,-0.018951604,-0.027689457,-0.00033081227,0.0034317947,0.0098349815,0.0034775084,-0.016835712,-0.0013787586,-0.0041632145,-0.0058219694,-0.020584237,-0.007386032,0.012486378,0.012473317,0.005815439,-0.010990886,-0.015111651,-0.023366245,0.019069154,0.017828353,0.030249426,-0.04315376,-0.01790672,0.0047444315,-0.0053419755,-0.02195565,-0.0057338076,-0.02576948,-0.009769676,-0.016914079,-0.0035232222,... </pre><p>The embeddings were generated with OpenAI’s text-embedding-ada-002 model. If you need to use a different model, then:</p><ul><li>Update the model in the {app_dir}/backend/embeddings_generator.js and {app_dir}/backend/postgres_embeddings_service.jsfile</li><li>Regenerate embeddings by starting the generator with the node embeddings_generator.js command.</li></ul><h3>Find Most Relevant Airbnb Listings</h3><p>By this point, Postgres is ready to recommend the most relevant Airbnb properties to users. The application can obtain those recommendations by comparing a user’s prompt embedding with the embeddings of Airbnb descriptions.</p><p>First, start an instance of the Airbnb recommendations service:</p><ol><li>Update the {app_dir}/application.properties.ini with your OpenAI API key:</li></ol><pre>OPENAI_API_KEY=&lt;your key&gt;</pre><p>2. Start the Node.js backend:</p><pre>cd {app_dir}/backend/node<br>npm i <br>npm start</pre><p>3. Start the React frontend:</p><pre>cd {app_dir}/frontend<br>npm i<br>npm start</pre><p>The application UI should be opened automatically in your default browser. Otherwise, open it at the <a href="http://localhost:3000/">address</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8P4Bd4K6j-RUwCBKOxJe4Q.png" /></figure><p>Now, select the <strong>Postgres Embeddings</strong> mode from the application UI and ask the application to recommend a few Airbnb listings that are most relevant to the following prompt:</p><pre>I&#39;m looking for an apartment near the Golden Gate Bridge with a nice view of the Bay.</pre><p>The service will recommend three lodging options:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QoDjs4VU5sScqJTQP6-YUA.png" /></figure><p>Internally, the application performs the following steps to generate the recommendations (see the {app_dir}/backend/postgres_embeddings_service.js for details):</p><ol><li>The application generates a vectorized representation of the user prompt using the OpenAI Embeddings model ( text-embedding-ada-002):</li></ol><pre>const embeddingResp = await this.#openai.embeddings.create(<br>    {model: &quot;text-embedding-ada-002&quot;,<br>    input: prompt});</pre><p>2. The app uses the generated vector to retrieve the most relevant Airbnb properties stored in Postgres:</p><pre>const res = await this.#client.query(<br>    &quot;SELECT name, description, price, 1 - (description_embedding &lt;=&gt; $1) as similarity &quot; +<br>    &quot;FROM airbnb_listing WHERE 1 - (description_embedding &lt;=&gt; $1) &gt; $2 ORDER BY description_embedding &lt;=&gt; $1 LIMIT $3&quot;,<br>    [&#39;[&#39; + embeddingResp.data[0].embedding + &#39;]&#39;, matchThreshold, matchCnt]);</pre><p>The similarity is calculated as a cosine distance between the embeddings stored in the description_embedding column and the user prompt&#39;s vector.</p><p>3. The suggested Airbnb properties are returned in the JSON format to the React front-end:</p><pre>let places = [];<br><br>for (let i = 0; i &lt; res.rows.length; i++) {<br>    const row = res.rows[i];<br>    places.push({<br>      &quot;name&quot;: row.name, <br>      &quot;description&quot;: row.description, <br>      &quot;price&quot;: row.price, <br>      &quot;similarity&quot;: row.similarity });<br>}<br><br>return places;</pre><h3>Ways to Scale</h3><p>Presently, Postgres stores over 7,500 Airbnb properties. It takes milliseconds for the database to perform an exact nearest neighbor search, comparing embeddings of user prompts and Airbnb descriptions.</p><p>However, the <strong>exact nearest neighbor search</strong> (full table scan) has its limits. As the dataset grows, it will take longer for Postgres to perform the similarity search over multi-dimensional vectors.</p><p>To keep Postgres performant, scalable and highly available amid increasing data volumes and traffic, you can use specialized indexes for vectorized data and/or scale storage and compute resources horizontally using a <a href="https://www.yugabyte.com/postgresql/distributed-postgresql/">distributed version of Postgres</a>.</p><p>The pgvector extension supports several index types, including the top-performing HNSW index (Hierarchical Navigable Small World). This index performs an <strong>approximate nearest neighbor</strong> search (ANN) over vectorized data, allowing the database to maintain low and predictable latency even with large data volumes. However, as the search is <strong>approximate</strong>, the recall of the search might not be 100% relevant/accurate because the index only traverses a subset of the data.</p><p>For example, here’s how you can create an HNSW index in Postgres for the Airbnb embeddings:</p><pre>CREATE INDEX ON airbnb_listing<br>USING hnsw (description_embedding vector_cosine_ops)<br>WITH (m = 4, ef_construction = 10);</pre><p>For a deeper understanding of how the HNSW index is built and performs the ANN search over the Airbnb data, check out this video:</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2F-RnChBOOuOg%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D-RnChBOOuOg&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2F-RnChBOOuOg%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/121b53c9390c54b7168c91473bc4aac1/href">https://medium.com/media/121b53c9390c54b7168c91473bc4aac1/href</a></iframe><p>With distributed PostgreSQL, you can easily scale database storage and compute resources when the capacity of a single database server is no longer sufficient. Although PostgreSQL was originally designed for single-server deployments, its ecosystem now includes several extensions and solutions that enable it to run in a distributed configuration. One such solution is <a href="https://www.yugabyte.com/">YugabyteDB</a>, a distributed SQL database that extends the capabilities of Postgres for distributed environments.</p><p>YugabyteDB supports the pgvector extension since version 2.19.2. It distributes data and embeddings across a cluster of nodes, facilitating similarity searches on a large scale. Thus, if you want the Airbnb service to run on a distributed version of Postgres:</p><p>1. Deploy a multi-node YugabyteDB cluster.</p><p>2. Update the database connectivity settings in the {app_dir}/backend/node/application.properties.ini file:</p><pre># Configuration for a locally running YugabyteDB instance with defaults.<br>DATABASE_HOST=localhost<br>DATABASE_PORT=5433<br>DATABASE_NAME=yugabyte<br>DATABASE_USER=yugabyte<br>DATABASE_PASSWORD=yugabyte</pre><p>3. Load the data from scratch (or migrate it from the running Postgres instance using YugabyteDB Voyager) and restart the application. No other code-level changes are necessary because YugabyteDB is feature and runtime-compatible with Postgres.</p><p>Watch the following video to see how the Airbnb recommendation service works over a distributed version of Postgres:</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FKbAPo9NMzlU%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DKbAPo9NMzlU&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FKbAPo9NMzlU%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/978ed420d2a675f68b0b00fa136414e7/href">https://medium.com/media/978ed420d2a675f68b0b00fa136414e7/href</a></iframe><p>Have fun building scalable AI applications with Postgres, and let me know if you wish to learn more about Postgres as a vector database.</p><p><em>Originally published at </em><a href="https://dzone.com/articles/postgresql-vector-database"><em>https://dzone.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=eda38677d5ae" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[When You Hear ‘Distributed Database’, What Do You Immediately Think Of?]]></title>
            <link>https://medium.com/@magda7817/when-you-hear-distributed-database-what-do-you-immediately-think-of-fc0e111f759c?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/fc0e111f759c</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[nosql]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Tue, 09 Jan 2024 18:19:22 GMT</pubDate>
            <atom:updated>2024-01-09T18:19:22.262Z</atom:updated>
            <content:encoded><![CDATA[<p>Let me guess… When you hear “distributed database,” you immediately think of:</p><ul><li>a) scalability</li><li>b) NoSQL</li><li>c) eventual consistency</li></ul><p>If this happens to you, it’s simply how our brain and memory function.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/666/1*PN0fq0pYstDit3uuYY6qLg.jpeg" /></figure><p>The first successful distributed databases belonged to the NoSQL category, leading our brains to develop certain associations.</p><p>In fact:</p><ul><li>a) Apart from scalability, distributed databases are also chosen for their built-in high availability and data locality. Even if an app handles just 1000 ops/sec, it might have strict availability SLAs (RPO=0, meaning no data loss) and RTO in seconds, or it may need to store and serve data from data centers closest to the users due to regulatory requirements or performance SLAs.</li><li>b) NoSQL databases are not the only type of distributed databases. These days, we have distributed relational databases that speak SQL and execute transactions over a distributed database cluster.</li><li>c) You no longer need to sacrifice consistency for distribution. As mentioned above, distributed SQL databases run distributed ACID transactions at scale.</li></ul><p>Time to develop new associations, my friend!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fc0e111f759c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Getting Started With Postgres: Three Free and Easy Ways]]></title>
            <link>https://medium.com/@magda7817/getting-started-with-postgres-three-free-and-easy-ways-91c6613600e0?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/91c6613600e0</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[yugabyte]]></category>
            <category><![CDATA[yugabytedb]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Thu, 28 Dec 2023 02:16:13 GMT</pubDate>
            <atom:updated>2023-12-28T02:16:37.046Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/540/0*Qa8wOwqhMaGPmv9u.jpg" /></figure><p>Hello, my friend! This year, approximately 90,000 developers took part in the Stack Overflow survey and crowned <a href="https://survey.stackoverflow.co/2023/#section-most-popular-technologies-databases">Postgres as the #1 database</a>. Plus, DB Engines also <a href="https://db-engines.com/en/ranking_trend/system/Microsoft+SQL+Server%3BMongoDB%3BMySQL%3BOracle%3BPostgreSQL">spotlights PostgreSQL</a> as one of the fastest-growing databases worldwide.</p><p>What does this mean for us? We should strive to become <a href="https://dzone.com/articles/advanced-postgresql-features-a-guide">PostgreSQL</a> experts. An essential step in this direction is setting up our own database for hands-on experiments.</p><p>So, whether you prefer reading or <a href="https://www.youtube.com/watch?v=_jIBTwpO4RE">watching</a>, let’s walk through three practical, user-friendly, and absolutely free ways to kickstart your PostgreSQL journey.</p><h3>Option #1: Dive Into Postgres With Docker</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6KaXyta5QADxMihNbjgt9g.png" /></figure><p>The simplest and most pocket-friendly way to start your journey with PostgreSQL is Docker.</p><p>That’s right: with a single Docker command, you have your database container humming merrily on your laptop:</p><pre>docker run --name postgresql \<br>    -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password \<br>    -p 5432:5432 \<br>    -d postgres:latest</pre><p>The advantages are immense! Setting up your database is incredibly fast, and guess what? It all happens right on your hardware.</p><p>Next, use your preferred SQL editor, like <a href="https://www.jetbrains.com/datagrip/">DataGrip</a>, to open a database connection. Ensure you connect to localhost and use the username and password from the Docker command mentioned earlier.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7RGgz7EiFRHjzyP3rWM2cQ.png" /></figure><p>Once connected, execute a few simple SQL statements to ensure the Postgres instance is ready for more advanced experiments:</p><pre>create table back_to_the_future(id int, name text);<br>insert into back_to_the_future(1, &#39;Doc&#39;);<br>select * from back_to_the_future;</pre><h3>Option #2: Jump Into Serverless Postgres With Neon</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*75j0KYd16KpvVBNM-9qdFw.png" /></figure><p>The second cost-free and straightforward way to learn PostgreSQL caters to those eager to use public cloud environments right from the start.</p><p><a href="https://neon.tech/">Neon</a> is a PostgreSQL-compatible database born and bred in the cloud. The icing on the cake? It’s <a href="https://dzone.com/articles/navigate-serverless-databases-a-guide-to-the-right">serverless</a>. Dive in without spending a penny, and it autonomously scales your workload as needed.</p><p>Eager to get started with Neon? For us developers, the command line is home, isn’t it? Kick things off by installing the Neon Command Line Tool:</p><pre>npm i -g neonctl</pre><p>Then authenticate and create an account:</p><pre>neonctl auth</pre><p>Create a new project and database instance:</p><pre>neonctl projects create --name mynewproject --region-id aws-us-east-1<br>neonctl databases create --name newdb</pre><p>Lastly, fetch your database connection string and you’re set:</p><pre>neonctl connection-string --database-name newdb </pre><p>Use that connection string to link up with the database instance via DataGrip:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mxbhqpkGQs4EVWjjJvkkqw.png" /></figure><p>For quick verification, execute a couple of straightforward SQL commands:</p><pre>create table matrix(id int, name text);<br>insert into matrix values(1, &#39;Neo&#39;);<br>select * from matrix;</pre><h3>Option #3: Build on Distributed Postgres With YugabyteDB</h3><p>Therefore, do you think you’re done? Not at all, my friend!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MzfCkeq4gDEcYN7bk_k5dg.png" /></figure><p>We conclude with <a href="https://www.yugabyte.com/">YugabyteDB</a> — the PostgreSQL “ <a href="https://en.wikipedia.org/wiki/Beast_(Marvel_Comics)">beast</a>.” Not only does it scale up and out across zones and regions, but it also withstands the most challenging cloud armageddons. Plus, its special knack: pinning user data to specific geographic locations.</p><p>Want a taste of YugabyteDB straight in the cloud? YugabyteDB Managed (DBaas) offers a free tier, gifting you a dedicated single-node instance, to begin with, and you can simply transition to their dedicated plan when you’re ready.</p><p>And now, for the grand tradition! Time to fire up a YugabyteDB instance straight from the command line. First, install the ybm tool:</p><pre>brew install yugabyte/tap/ybm</pre><p>Next, create an account and sign in using your authentication token:</p><pre>ybm signup<br>ybm auth</pre><p>The final steps involve setting up your first database instance:</p><pre>ybm cluster create \<br>    --cluster-name yugabyte \<br>    --credentials username=admin,password=password-123 \<br>    --cluster-tier Sandbox \<br>    --cloud-provider AWS \<br>    --wait</pre><p>And add your laptop to the database’s IP allow list (yep, YugabyteDB folks take security seriously):</p><pre>ybm network-allow-list create \<br>   --ip-addr \$(curl ifconfig.me) \<br>   --name my-address<br><br>ybm cluster network allow-list assign \\<br>   --network-allow-list my-address \\<br>   --cluster-name yugabyte</pre><p>Alright, once the database is started, take its connection string:</p><pre>ybm cluster describe --cluster-name yugabyte</pre><p>And use that string to establish a connection via DataGrip:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NEpx6CM86lQw2cvRqtjuow.png" /></figure><p>With the connection opened, send a few SQL commands to YugabyteDB to make sure the distributed “beast” is ready to serve your requests:</p><pre>create table avengers(id int, name text);<br>insert into avengers values(1, &#39;Hulk&#39;);<br>select * from avengers;</pre><h3>That’s All… For Now</h3><p>With PostgreSQL’s popularity on the rise, it’s inevitable you’ll cross paths with it in upcoming projects. So why wait? Dive in now. And there’s no better way to learn than by doing. Roll up your sleeves, launch a Postgres instance using one of the three methods outlined in this article, and savor the journey ahead.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=91c6613600e0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[AI Workforce is Already Coming For Junior/Mid-Level Developer Jobs on Wall Street]]></title>
            <link>https://medium.com/@magda7817/ai-workforce-is-already-coming-for-junior-mid-level-developer-jobs-on-wall-street-232b29658836?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/232b29658836</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Sat, 16 Dec 2023 22:38:11 GMT</pubDate>
            <atom:updated>2023-12-16T22:40:49.493Z</atom:updated>
            <content:encoded><![CDATA[<p>A few days ago, I met with a buddy of mine who works as a principal engineer/architect at one of the top Wall Street companies in New York City. He told me a story of how he “hired” the first AI employee on his team.</p><p>It all began with the resignation of one of his front-end developers, who became bored and joined another company. As usual, my friend immediately opened a replacement position. But before chasing and bothering recruiters for candidates, he decided to give a try to an AI model already widely used within the company.</p><p>He started using the AI model as a front-end engineer. He would talk to the AI the way he would talk to the resigned front-end engineer, giving tasks, iterating on the React UI, and making final changes in the source code before pushing it to production. After a week-long pilot, he asked headhunters to use the opened position to find a UI/UX designer for the team instead. He no longer needed a front-end engineer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1LLUO3E9ujx46T2sWRuZGg.png" /></figure><h3>Why AI Workforce</h3><p>Why did my experienced friend decide to settle down with the AI colleague rather than searching for a human replacement? His observations were as follows:</p><ul><li>The AI performs junior/mid-level tasks much better and faster than the former developer. The quality is either better or comparable.</li><li>There is zero headache with the AI — no people management, no need for 1-on-1 conversations to support, motivate, encourage, or lecture.</li><li>The AI doesn’t let you down by resigning during a critical milestone of a project or simply because it became bored with its tasks.</li><li>It’s not a problem at all to write all that text to the AI. My buddy had to write a ton of emails and comments in JIRA anyway for the resigned engineer.</li></ul><h3>What Can We, Humans, Do About That</h3><p>First, take a deep breath and think rationally. The inevitable is already happening. You can’t stop progress. AI is much bigger than the Internet and mobile revolutions combined. We’re literally entering <em>the next era of our civilization</em>.</p><p>Second, the junior/mid-level developer position won’t disappear. These roles will remain, but they will require a completely different skillset from those graduating from universities and entering the job market.</p><p>Lastly, think positively and take advantage. You don’t have a choice but to think positively. How exactly can you take advantage?</p><ul><li><strong>Presently. Learn and practice faster with AI.</strong> Even current models can teach you software engineering, chemistry, physics as well as many traditional educational methods. Don’t be lazy. Be curious.</li><li><strong>Near future (1–3 years). Capitalize on your knowledge and skillset. Train and sell the AI version of yourself.</strong> If you’re a senior+ level technical expert, you’re already passing over your knowledge daily to colleagues and subordinates. Just as you share knowledge with humans, soon you’ll be able to impart your unique skillset to your own AI model (the AI version of yourself). The model will learn from you by observing how you troubleshoot issues, solve engineering tasks, debug logs. Your AI model will grow smarter and more experienced, and eventually, you can send it to <a href="https://loomhr.ai/">the AI job market where companies can “hire” and pay for AI models</a> with unique skills.</li></ul><p>So, buckle up, my friends. The new era is coming…</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=232b29658836" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Colocated and Interleaved Tables in Distributed SQL Databases: Tradeoffs]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/@magda7817/colocated-and-interleaved-tables-in-distributed-sql-databases-tradeoffs-f2e7b1b9d68e?source=rss-74f56c424c21------2"><img src="https://cdn-images-1.medium.com/max/2600/1*aDSqqtXu4HMhhUOhu0Enyg.png" width="7401"></a></p><p class="medium-feed-snippet">Colocated tables can improve performance of JOINs by storing parent and child records together. But there are tradeoffs worth considering.</p><p class="medium-feed-link"><a href="https://medium.com/@magda7817/colocated-and-interleaved-tables-in-distributed-sql-databases-tradeoffs-f2e7b1b9d68e?source=rss-74f56c424c21------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://medium.com/@magda7817/colocated-and-interleaved-tables-in-distributed-sql-databases-tradeoffs-f2e7b1b9d68e?source=rss-74f56c424c21------2</link>
            <guid isPermaLink="false">https://medium.com/p/f2e7b1b9d68e</guid>
            <category><![CDATA[architecture]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[software-engineering]]></category>
            <dc:creator><![CDATA[Denis Magda]]></dc:creator>
            <pubDate>Fri, 08 Dec 2023 17:57:40 GMT</pubDate>
            <atom:updated>2023-12-08T17:59:48.009Z</atom:updated>
        </item>
    </channel>
</rss>