supabase-postgres.git / summary / log / commit / refs
commit fe4129dab835e086dee23d55eb72bae23805fd4e
Author: Oliver Rice <github@oliverrice.com>
Commit: GitHub <noreply@github.com>
Date: Mon Apr 08 19:51:25 2024 +0000
Add index_advisor extension to image (#933)
* add index_advisor extension to image
* bump image for testing on staging
* remove -source from target COPY
* add index_advisor to supautils allow list
* bump image name for staging
* update nix to include index_advisor + a smoke test
* remove RC tag
Dockerfile | 20 +++++++++++++++
ansible/files/postgresql_config/supautils.conf.j2 | 4 +--
ansible/vars.yml | 3 +++
docker/orioledb/Dockerfile | 20 +++++++++++++++
flake.nix | 1 +
nix/ext/index_advisor.nix | 30 +++++++++++++++++++++++
nix/tests/prime.sql | 2 ++
nix/tests/smoke/0004-index_advisor.sql | 19 ++++++++++++++
8 files changed, 97 insertions(+), 2 deletions(-)
diff --git a/Dockerfile b/Dockerfile
index a991ab71..316c7d34 100644
--- a/Dockerfile
+++ b/Dockerfile
@@ -35,6 +35,7 @@ ARG wrappers_release=0.3.0
ARG hypopg_release=1.3.1
ARG pgvector_release=0.4.0
ARG pg_tle_release=1.3.2
+ARG index_advisor_release=0.2.0
ARG supautils_release=2.2.0
ARG wal_g_release=2.0.1
@@ -803,6 +804,24 @@ RUN --mount=type=cache,target=/ccache,from=public.ecr.aws/supabase/postgres:ccac
# Create debian package
RUN checkinstall -D --install=no --fstrans=no --backup=no --pakdir=/tmp --nodoc
+######################
+# 30-index_advisor.yml
+######################
+FROM ccache as index_advisor
+ARG index_advisor_release
+ARG index_advisor_release_checksum
+ADD --checksum=${index_advisor_release_checksum} \
+ "https://github.com/olirice/index_advisor/archive/refs/tags/v${index_advisor_release}.tar.gz" \
+ /tmp/index_advisor.tar.gz
+RUN tar -xvf /tmp/index_advisor.tar.gz -C /tmp && \
+ rm -rf /tmp/index_advisor.tar.gz
+# Build from source
+WORKDIR /tmp/index_advisor-${index_advisor_release}
+RUN --mount=type=cache,target=/ccache,from=public.ecr.aws/supabase/postgres:ccache \
+ make -j$(nproc)
+# Create debian package
+RUN checkinstall -D --install=no --fstrans=no --backup=no --pakdir=/tmp --nodoc
+
####################
# internal/supautils.yml
####################
@@ -857,6 +876,7 @@ COPY --from=hypopg-source /tmp/*.deb /tmp/
COPY --from=pg_repack-source /tmp/*.deb /tmp/
COPY --from=pgvector-source /tmp/*.deb /tmp/
COPY --from=pg_tle-source /tmp/*.deb /tmp/
+COPY --from=index_advisor /tmp/*.deb /tmp/
COPY --from=supautils /tmp/*.deb /tmp/
####################
diff --git a/ansible/files/postgresql_config/supautils.conf.j2 b/ansible/files/postgresql_config/supautils.conf.j2
index f192d94e..bfbd590f 100644
--- a/ansible/files/postgresql_config/supautils.conf.j2
+++ b/ansible/files/postgresql_config/supautils.conf.j2
@@ -1,9 +1,9 @@
supautils.extensions_parameter_overrides = '{"pg_cron":{"schema":"pg_catalog"}}'
supautils.policy_grants = '{"postgres":["auth.audit_log_entries","auth.identities","auth.refresh_tokens","auth.sessions","auth.users","realtime.broadcasts","realtime.channels","realtime.presences","storage.buckets","storage.migrations","storage.objects"]}'
-# full list: address_standardizer, address_standardizer_data_us, adminpack, amcheck, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, file_fdw, fuzzystrmatch, hstore, http, hypopg, insert_username, intagg, intarray, isn, lo, ltree, moddatetime, old_snapshot, orioledb, pageinspect, pg_buffercache, pg_cron, pg_freespacemap, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_prewarm, pg_repack, pg_stat_monitor, pg_stat_statements, pg_surgery, pg_tle, pg_trgm, pg_visibility, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgstattuple, pgtap, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, vector, wrappers, xml2
+# full list: address_standardizer, address_standardizer_data_us, adminpack, amcheck, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, file_fdw, fuzzystrmatch, hstore, http, hypopg, index_advisor, insert_username, intagg, intarray, isn, lo, ltree, moddatetime, old_snapshot, orioledb, pageinspect, pg_buffercache, pg_cron, pg_freespacemap, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_prewarm, pg_repack, pg_stat_monitor, pg_stat_statements, pg_surgery, pg_tle, pg_trgm, pg_visibility, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgstattuple, pgtap, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, vector, wrappers, xml2
# omitted because may be unsafe: adminpack, amcheck, file_fdw, lo, old_snapshot, pageinspect, pg_buffercache, pg_freespacemap, pg_surgery, pg_visibility
# omitted because deprecated: intagg, xml2
-supautils.privileged_extensions = 'address_standardizer, address_standardizer_data_us, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hstore, http, hypopg, insert_username, intarray, isn, ltree, moddatetime, orioledb, pg_cron, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_repack, pg_stat_monitor, pg_stat_statements, pg_tle, pg_trgm, pg_walinspect, pgaudit, pgcrypto, pgjwt, pg_prewarm, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgstattuple, pgsodium, pgtap, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, vector, wrappers'
+supautils.privileged_extensions = 'address_standardizer, address_standardizer_data_us, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hstore, http, hypopg, index_advisor, insert_username, intarray, isn, ltree, moddatetime, orioledb, pg_cron, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_repack, pg_stat_monitor, pg_stat_statements, pg_tle, pg_trgm, pg_walinspect, pgaudit, pgcrypto, pgjwt, pg_prewarm, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgstattuple, pgsodium, pgtap, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, vector, wrappers'
supautils.privileged_extensions_custom_scripts_path = '/etc/postgresql-custom/extension-custom-scripts'
supautils.privileged_extensions_superuser = 'supabase_admin'
supautils.privileged_role = 'postgres'
diff --git a/ansible/vars.yml b/ansible/vars.yml
index 28c6c959..236770ad 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -140,3 +140,6 @@ pgvector_release_checksum: sha256:a11cc249a9f3f3d7b13069a1696f2915ac28991a72d7ba
pg_tle_release: "1.3.2"
pg_tle_release_checksum: sha256:d04f72d88b21b954656609743560684ac42645b64a36c800d4d2f84d1f180de1
+
+index_advisor_release: "0.2.0"
+index_advisor_checksum: sha256:2d3642012a9185cda51f1e82ba43d64a81b24a2655a3ac3afdcbbd95d46a1a27
diff --git a/docker/orioledb/Dockerfile b/docker/orioledb/Dockerfile
index a333e26b..b61a90a0 100644
--- a/docker/orioledb/Dockerfile
+++ b/docker/orioledb/Dockerfile
@@ -35,6 +35,7 @@ ARG wrappers_release=0.2.0
ARG hypopg_release=1.3.1
ARG pgvector_release=0.4.0
ARG pg_tle_release=1.3.2
+ARG index_advisor_release=0.2.0
ARG supautils_release=2.2.0
ARG wal_g_release=2.0.1
@@ -907,6 +908,24 @@ RUN --mount=type=cache,target=/ccache,from=public.ecr.aws/supabase/postgres:ccac
# Create debian package
RUN checkinstall -D --install=no --fstrans=no --backup=no --pakdir=/tmp --nodoc
+######################
+# 30-index_advisor.yml
+######################
+FROM ccache as index_advisor
+ARG index_advisor_release
+ARG index_advisor_release_checksum
+ADD --checksum=${index_advisor_release_checksum} \
+ "https://github.com/olirice/index_advisor/archive/refs/tags/v${index_advisor_release}.tar.gz" \
+ /tmp/index_advisor.tar.gz
+RUN tar -xvf /tmp/index_advisor.tar.gz -C /tmp && \
+ rm -rf /tmp/index_advisor.tar.gz
+# Build from source
+WORKDIR /tmp/index_advisor-${index_advisor_release}
+RUN --mount=type=cache,target=/ccache,from=public.ecr.aws/supabase/postgres:ccache \
+ make -j$(nproc)
+# Create debian package
+RUN checkinstall -D --install=no --fstrans=no --backup=no --pakdir=/tmp --nodoc
+
####################
# internal/supautils.yml
####################
@@ -974,6 +993,7 @@ COPY --from=hypopg-source /tmp/*.deb /tmp/
COPY --from=pg_repack-source /tmp/*.deb /tmp/
COPY --from=pgvector-source /tmp/*.deb /tmp/
COPY --from=pg_tle-source /tmp/*.deb /tmp/
+COPY --from=index_advisor /tmp/*.deb /tmp/
COPY --from=supautils-source /tmp/*.deb /tmp/
####################
diff --git a/flake.nix b/flake.nix
index ec2ad035..b276f3e4 100644
--- a/flake.nix
+++ b/flake.nix
@@ -111,6 +111,7 @@
./nix/ext/rum.nix
./nix/ext/timescaledb.nix
./nix/ext/pgroonga.nix
+ ./nix/ext/index_advisor.nix
./nix/ext/wal2json.nix
./nix/ext/pg_repack.nix
./nix/ext/pg-safeupdate.nix
diff --git a/nix/ext/index_advisor.nix b/nix/ext/index_advisor.nix
new file mode 100644
index 00000000..3ed5a5fb
--- /dev/null
+++ b/nix/ext/index_advisor.nix
@@ -0,0 +1,30 @@
+{ lib, stdenv, fetchFromGitHub, postgresql }:
+
+stdenv.mkDerivation rec {
+ pname = "index_advisor";
+ version = "0.2.0";
+
+ buildInputs = [ postgresql ];
+
+ src = fetchFromGitHub {
+ owner = "olirice";
+ repo = pname;
+ rev = "v${version}";
+ hash = "sha256-G0eQk2bY5CNPMeokN/nb05g03CuiplRf902YXFVQFbs=";
+ };
+
+ installPhase = ''
+ mkdir -p $out/{lib,share/postgresql/extension}
+
+ cp *.sql $out/share/postgresql/extension
+ cp *.control $out/share/postgresql/extension
+ '';
+
+ meta = with lib; {
+ description = "Recommend indexes to improve query performance in PostgreSQL";
+ homepage = "https://github.com/olirice/index_advisor";
+ maintainers = with maintainers; [ samrose ];
+ platforms = postgresql.meta.platforms;
+ license = licenses.postgresql;
+ };
+}
diff --git a/nix/tests/prime.sql b/nix/tests/prime.sql
index 28390311..084ad3b5 100644
--- a/nix/tests/prime.sql
+++ b/nix/tests/prime.sql
@@ -18,3 +18,5 @@ CREATE EXTENSION IF NOT EXISTS wrappers;
CREATE EXTENSION IF NOT EXISTS http;
CREATE EXTENSION IF NOT EXISTS pg_graphql;
CREATE EXTENSION IF NOT EXISTS pg_jsonschema;
+CREATE EXTENSION IF NOT EXISTS hypopg;
+CREATE EXTENSION IF NOT EXISTS index_advisor;
diff --git a/nix/tests/smoke/0004-index_advisor.sql b/nix/tests/smoke/0004-index_advisor.sql
new file mode 100644
index 00000000..53170f6a
--- /dev/null
+++ b/nix/tests/smoke/0004-index_advisor.sql
@@ -0,0 +1,19 @@
+-- Start transaction and plan the tests.
+begin;
+ select plan(1);
+
+ create extension if not exists index_advisor;
+
+ create table account(
+ id int primary key,
+ is_verified bool
+ );
+
+ select is(
+ (select count(1) from index_advisor('select id from public.account where is_verified;'))::int,
+ 1,
+ 'index_advisor returns 1 row'
+ );
+
+ select * from finish();
+rollback;
[parent: 8efbee9205c6]