DEV Community

Gleb Otochkin
Gleb Otochkin

Posted on • Originally published at Medium on

AlloyDB Omni on Mac with Podman

#podman #postgres #mac #alloydb

Do you like to sink deep in an armchair with a laptop on your lap and do some development or run some tests for PostgreSQL? I do it all the time, sometimes from my living room and sometimes from an airport or sitting in a plane. And it is really handy to have a database engine running locally on your laptop. What database do you use in such a case? I think AlloyDB Omni can be a perfect candidate. Let me share my setup here.

I use Podman as the main platform to run containers on my Mac. I like the fact it is an open source project and runs under Apache License 2.0. You can use it totally free on your Mac and everybody can participate and contribute to the product and make it better. And it works perfectly well on my Mac.

When you install the Podman you need to create a default VM to host your containers. On Mac and Windows you need that VM since the container’s core depends on the Linux kernel. I would suggest assigning about 3GB memory to the VM to have some room for some advanced AlloyDB features. And I usually don’t use more than 50 GB storage for my sample databases.

Installing AlloyDB Omni is quite straightforward if you only want to test the process. You run the following command in your terminal.

podman run --name my-omni \
    -e POSTGRES_PASSWORD=MyVeryStongPassword \
    -d google/alloydbomni:latest
Enter fullscreen mode Exit fullscreen mode

It works out of the box and you can start to test or develop right away. Let’s connect and create a database with name quickstart_db.

otochkin@Glebs-MacBook-Pro ~ % podman exec -it my-omni psql -h localhost -U postgres
psql (15.7)
Type "help" for help.

postgres=# create database quickstart_db;
CREATE DATABASE
postgres=# \l+ quickstart_db
                                                                   List of databases
     Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges | Size | Tablespace | Description
---------------+----------+----------+-----------------+---------+-------+-----------+-----------+-------------------+-------+------------+-------------
 quickstart_db | postgres | UTF8 | icu | C | C | und-x-icu | | | 12 MB | pg_default |
(1 row)
Enter fullscreen mode Exit fullscreen mode

That’s great but … That works for a simple test and probably good enough to be used to verify something or run a quick check when you don’t need to return back to it. It has a couple of issues. It requires access through the container itself which is not the most convenient way and it stores all the data on the ephemeral layer of the container. Every time when you recreate the container it wipes out all the data and starts from the blank list.

To make it right we need to add at least a couple of parameters. To give access through the network we can translate port 5432 to a local port. It can be the same 5432 or any other port. Let’s make it 5433 for my first container. In such a case the parameter will look like -p 5433:5432. And the second parameter specifies a directory where I want to keep my data. It will allow me to recreate containers, upgrade or modify some container parameters without losing my data. I’ve created a directory ~/Podman/my-omni on my Mac excatly for that purposes. Let’s see how we can combine all the parameters.

podman run -d --name my-omni \
-e POSTGRES_PASSWORD=MyVeryStongPassword \
-v ~/Podman/my-omni:/var/lib/postgresql/data \
-p 5433:5432 \
docker.io/google/alloydbomni:latest
Enter fullscreen mode Exit fullscreen mode

Here is our AlloyDB Omni in the Podman GUI

Now everything looks much better I can connect using psql utility and create my database again.

otochkin@Glebs-MacBook-Pro ~ % psql -h localhost -p 5433 -U postgres
Password for user postgres:
psql (17.4 (Postgres.app), server 15.7)
Type "help" for help.

postgres=# create database quickstart_db;
CREATE DATABASE
postgres=# \l+ quickstart_db
                                                                   List of databases
     Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges | Size | Tablespace | Description
---------------+----------+----------+-----------------+---------+-------+-----------+-----------+-------------------+-------+------------+-------------
 quickstart_db | postgres | UTF8 | icu | C | C | und-x-icu | | | 12 MB | pg_default |
(1 row)

postgres=#
Enter fullscreen mode Exit fullscreen mode

What is next? You know AlloyDB is packed with different features such as automatic memory management or columnar engine. To make the columnar engine working right it is recommended to make shared memory available to the container by adding the shm-size flag. Let’s remove the old container and create a new with shm-size equal to 1 GB.

podman stop my-omni
podman rm my-omni
podman run -d --name my-omni \
-e POSTGRES_PASSWORD=MyVeryStongPassword \
-v ~/Podman/my-omni:/var/lib/postgresql/data \
-p 5433:5432 \
--shm-size=1g \
docker.io/google/alloydbomni:latest
Enter fullscreen mode Exit fullscreen mode

Now we can enable and use columnar engine on our AlloyDB Omni. By the way, if you connect to it you will see that our quickstart_db is still there.

otochkin@Glebs-MacBook-Pro ~ % psql -h localhost -p 5433 -U postgres
Password for user postgres:
psql (17.4 (Postgres.app), server 15.7)
Type "help" for help.

postgres=# \l+ quickstart_db
                                                                   List of databases
     Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges | Size | Tablespace | Description
---------------+----------+----------+-----------------+---------+-------+-----------+-----------+-------------------+-------+------------+-------------
 quickstart_db | postgres | UTF8 | icu | C | C | und-x-icu | | | 12 MB | pg_default |
(1 row)

postgres=#
Enter fullscreen mode Exit fullscreen mode

We can use a codelab to test the columnar engine and see if how it works. I am not going to reproduce all the steps from the lab here — you can do it by yourself, but I can show the query from the lab and information about the columnar store.

Here is what we got in the columnar store when we’ve enabled columnar engine and ran queries several times.

quickstart_db=# SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
 database_name | schema_name | relation_name | column_name
---------------+-------------+--------------------------------------+-------------
 quickstart_db | public | insurance_producers_licensed_in_iowa | city
 quickstart_db | public | insurance_producers_licensed_in_iowa | expirydate
 quickstart_db | public | insurance_producers_licensed_in_iowa | loa_has_ah
(3 rows)
Enter fullscreen mode Exit fullscreen mode

And here is the query itself. During my tests execution time without columnar engine was about 45ms and with columnar engine was about 7.5 ms.

quickstart_db=# SELECT city, count(*) FROM insurance_producers_licensed_in_iowa WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH' GROUP BY city ORDER BY count(*) desc limit 5;
    city | count
-------------+-------
 TAMPA | 1996
 OMAHA | 1686
 KANSAS CITY | 1221
 AUSTIN | 1178
 MIAMI | 1082
(5 rows)

Time: 7.630 ms
Enter fullscreen mode Exit fullscreen mode

And we can see in the execution plan that it indeed uses the columnar engine to speed up the execution. That performance difference visible even on my mac with very fast CPU and storage.

quickstart_db=# explain analyze SELECT city, count(*) FROM insurance_producers_licensed_in_iowa WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH' GROUP BY city ORDER BY count(*) desc limit 5;
                                                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=2335.09..2335.10 rows=5 width=17) (actual time=15.165..15.173 rows=5 loops=1)
   -> Sort (cost=2335.09..2348.24 rows=5261 width=17) (actual time=15.160..15.167 rows=5 loops=1)
         Sort Key: (count(*)) DESC
         Sort Method: top-N heapsort Memory: 25kB
         -> HashAggregate (cost=2195.10..2247.71 rows=5261 width=17) (actual time=13.167..14.439 rows=7639 loops=1)
               Group Key: city
               Batches: 1 Memory Usage: 1169kB
               -> Append (cost=20.00..1712.64 rows=96492 width=9) (actual time=13.160..13.166 rows=96751 loops=1)
                     -> Custom Scan (columnar scan) on insurance_producers_licensed_in_iowa (cost=20.00..1708.62 rows=96491 width=9) (actual time=13.156..13.158 rows=96751 loops=1)
                           Filter: ((loa_has_ah = 'Yes'::text) AND (expirydate > (now() + '6 mons'::interval)))
                           Rows Removed by Columnar Filter: 114169
                           Rows Aggregated by Columnar Scan: 96751
                           Columnar cache search mode: native
                     -> Seq Scan on insurance_producers_licensed_in_iowa (cost=0.00..4.02 rows=1 width=9) (never executed)
                           Filter: ((loa_has_ah = 'Yes'::text) AND (expirydate > (now() + '6 mons'::interval)))
Enter fullscreen mode Exit fullscreen mode

There are some other features like adaptive autovacuum, automatic memory management or index adviser — all are baked in the AlloyDB. And we recently published a lot of useful information in our AlloyDB Omni documentation about best practices and recommendations how to use it. Try it an let us know what you think, share your experience.

Top comments (0)