- Install Postgres.app, and Run Postgres database
- Install GHCup
- Install Docker Desktop
- Setup Postgres Database
psql -U postgres -h localhost -d $(whoami)
...=# create database image_catalog;
...=# \c image_catalog
- Run Migrations:
./run_flyway.sh migrate
cabal install --overwrite-policy=always
Should symlink executable to ~/.local/bin/image-catalog
$ HF_TOKEN=... PG_DATABASE=image_catalog PG_USER=postgres PG_HOST=localhost image-catalog
$ $ docker run --platform linux/amd64 -d -p 3000:8080 -v $(pwd)/swagger:/tmp -e SWAGGER_FILE=/tmp/swagger.yaml swaggerapi/swagger-editor
Then visit http://localhost:3000 in your browser to test
Inserting 10000 random images to show usage of GIN index on PGArray, causing Index Scan, (we do not want Seq Scan)
CREATE OR REPLACE FUNCTION insert_random_images(num_rows INT) RETURNS VOID AS $$
DECLARE
i INT;
random_url VARCHAR(248);
random_label TEXT;
random_objects TEXT[];
BEGIN
FOR i IN 1..num_rows LOOP
-- Generate random URL
random_url := 'http://example.com/image/' || i || '.jpg';
-- Generate random label
random_label := 'Image ' || i;
-- Generate random objects (you can customize this list)
random_objects := ARRAY[
'object_' || (FLOOR(RANDOM() * 10) + 1)::TEXT,
'object_' || (FLOOR(RANDOM() * 10) + 1)::TEXT,
'object_' || (FLOOR(RANDOM() * 10) + 1)::TEXT
];
-- Insert the generated data into the images table
INSERT INTO images (url, label, objects)
VALUES (random_url, random_label, random_objects);
END LOOP;
END;
$$
LANGUAGE plpgsql;
image_catalog=# SELECT insert_random_images(10000);
insert_random_images
----------------------
(1 row)
image_catalog=# select count(*) from images;
count
-------
10000
(1 row)
image_catalog=# select * from images order by id desc limit 10;
id | url | label | objects
-------+------------------------------------+-------------+------------------------------
10019 | http://example.com/image/10000.jpg | Image 10000 | {object_5,object_9,object_5}
10018 | http://example.com/image/9999.jpg | Image 9999 | {object_3,object_4,object_7}
10017 | http://example.com/image/9998.jpg | Image 9998 | {object_1,object_2,object_1}
10016 | http://example.com/image/9997.jpg | Image 9997 | {object_9,object_7,object_6}
10015 | http://example.com/image/9996.jpg | Image 9996 | {object_7,object_5,object_2}
10014 | http://example.com/image/9995.jpg | Image 9995 | {object_9,object_3,object_9}
10013 | http://example.com/image/9994.jpg | Image 9994 | {object_6,object_1,object_8}
10012 | http://example.com/image/9993.jpg | Image 9993 | {object_1,object_6,object_5}
10011 | http://example.com/image/9992.jpg | Image 9992 | {object_1,object_1,object_4}
10010 | http://example.com/image/9991.jpg | Image 9991 | {object_3,object_6,object_6}
(10 rows)
image_catalog=# explain select * from images where objects && '{object_7}';
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on images (cost=27.00..243.15 rows=2732 width=112)
Recheck Cond: (objects && '{object_7}'::text[])
-> Bitmap Index Scan on images_objects_idx (cost=0.00..26.31 rows=2732 width=0)
Index Cond: (objects && '{object_7}'::text[])
(4 rows)