8000 Add index_advisor extension to image (#933) · supabase/postgres@fe4129d · GitHub
[go: up one dir, main page]
Skip to content

Commit fe4129d

Browse files
oliriceolirice
authored
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
1 parent 8efbee9 commit fe4129d

File tree

8 files changed

+97
-2
lines changed
Filter options

8 files changed

+97
-2
lines changed

‎Dockerfile

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@ ARG wrappers_release=0.3.0
3535
ARG hypopg_release=1.3.1
3636
ARG pgvector_release=0.4.0
3737
ARG pg_tle_release=1.3.2
38+
ARG index_advisor_release=0.2.0
3839
ARG supautils_release=2.2.0
3940
ARG wal_g_release=2.0.1
4041

@@ -803,6 +804,24 @@ RUN --mount=type=cache,target=/ccache,from=public.ecr.aws/supabase/postgres:ccac
803804
# Create debian package
804805
RUN checkinstall -D --install=no --fstrans=no --backup=no --pakdir=/tmp --nodoc
805806

807+
######################
808+
# 30-index_advisor.yml
809+
######################
810+
FROM ccache as index_advisor
811+
ARG index_advisor_release
812+
ARG index_advisor_release_checksum
813+
ADD --checksum=${index_advisor_release_checksum} \
814+
"https://github.com/olirice/index_advisor/archive/refs/tags/v${index_advisor_release}.tar.gz" \
815+
/tmp/index_advisor.tar.gz
816+
RUN tar -xvf /tmp/index_advisor.tar.gz -C /tmp && \
817+
rm -rf /tmp/index_advisor.tar.gz
818+
# Build from source
819+
WORKDIR /tmp/index_advisor-${index_advisor_release}
820+
RUN --mount=type=cache,target=/ccache,from=public.ecr.aws/supabase/postgres:ccache \
821+
make -j$(nproc)
822+
# Create debian package
823+
RUN checkinstall -D --install=no --fstrans=no --backup=no --pakdir=/tmp --nodoc
824+
806825
####################
807826
# internal/supautils.yml
808827
####################
@@ -857,6 +876,7 @@ COPY --from=hypopg-source /tmp/*.deb /tmp/
857876
COPY --from=pg_repack-source /tmp/*.deb /tmp/
858877
COPY --from=pgvector-source /tmp/*.deb /tmp/
859878
COPY --from=pg_tle-source /tmp/*.deb /tmp/
879+
COPY --from=index_advisor /tmp/*.deb /tmp/
860880
COPY --from=supautils /tmp/*.deb /tmp/
861881

862882
####################

‎ansible/files/postgresql_config/supautils.conf.j2

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
supautils.extensions_parameter_overrides = '{"pg_cron":{"schema":"pg_catalog"}}'
22
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"]}'
3-
# 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
3+
# 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
44
# omitted because may be unsafe: adminpack, amcheck, file_fdw, lo, old_snapshot, pageinspect, pg_buffercache, pg_freespacemap, pg_surgery, pg_visibility
55
# omitted because deprecated: intagg, xml2
6-
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'
6+
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'
77
supautils.privileged_extensions_custom_scripts_path = '/etc/postgresql-custom/extension-custom-scripts'
88
supautils.privileged_extensions_superuser = 'supabase_admin'
99
supautils.privileged_role = 'postgres'

‎ansible/vars.yml

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -140,3 +140,6 @@ pgvector_release_checksum: sha256:a11cc249a9f3f3d7b13069a1696f2915ac28991a72d7ba
140140

141141
pg_tle_release: "1.3.2"
142142
pg_tle_release_checksum: sha256:d04f72d88b21b954656609743560684ac42645b64a36c800d4d2f84d1f180de1
143+
144+
index_advisor_release: "0.2.0"
145+
index_advisor_checksum: sha256:2d3642012a9185cda51f1e82ba43d64a81b24a2655a3ac3afdcbbd95d46a1a27

‎docker/orioledb/Dockerfile

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@ ARG wrappers_release=0.2.0
3535
ARG hypopg_release=1.3.1
3636
ARG pgvector_release=0.4.0
3737
ARG pg_tle_release=1.3.2
38+
ARG index_advisor_release=0.2.0
3839
ARG supautils_release=2.2.0
3940
ARG wal_g_release=2.0.1
4041

@@ -907,6 +908,24 @@ RUN --mount=type=cache,target=/ccache,from=public.ecr.aws/supabase/postgres:ccac
907908
# Create debian package
908909
RUN checkinstall -D --install=no --fstrans=no --backup=no --pakdir=/tmp --nodoc
909910

911+
######################
912+
# 30-index_advisor.yml
913+
######################
914+
FROM ccache as index_advisor
915+
ARG index_advisor_release
916+
ARG index_advisor_release_checksum
917+
ADD --checksum=${index_advisor_release_checksum} \
918+
"https://github.com/olirice/index_advisor/archive/refs/tags/v${index_advisor_release}.tar.gz" \
919+
/tmp/index_advisor.tar.gz
920+
RUN tar -xvf /tmp/index_advisor.tar.gz -C /tmp && \
921+
rm -rf /tmp/index_advisor.tar.gz
922+
# Build from source
923+
WORKDIR /tmp/index_advisor-${index_advisor_release}
924+
RUN --mount=type=cache,target=/ccache,from=public.ecr.aws/supabase/postgres:ccache \
925+
make -j$(nproc)
926+
# Create debian package
927+
RUN checkinstall -D --install=no --fstrans=no --backup=no --pakdir=/tmp --nodoc
928+
910929
####################
911930
# internal/supautils.yml
912931
####################
@@ -974,6 +993,7 @@ COPY --from=hypopg-source /tmp/*.deb /tmp/
974993
COPY --from=pg_repack-source /tmp/*.deb /tmp/
975994
COPY --from=pgvector-source /tmp/*.deb /tmp/
976995
COPY --from=pg_tle-source /tmp/*.deb /tmp/
996+
COPY --from=index_advisor /tmp/*.deb /tmp/
977997
COPY --from=supautils-source /tmp/*.deb /tmp/
978998

979999
####################

‎flake.nix

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,7 @@
111111
./nix/ext/rum.nix
112112
./nix/ext/timescaledb.nix
113113
./nix/ext/pgroonga.nix
114+
./nix/ext/index_advisor.nix
114115
./nix/ext/wal2json.nix
115116
./nix/ext/pg_repack.nix
116117
./nix/ext/pg-safeupdate.nix

‎nix/ext/index_advisor.nix

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
{ lib, stdenv, fetchFromGitHub, postgresql }:
2+
3+
stdenv.mkDerivation rec {
4+
pname = "index_advisor";
5+
version = "0.2.0";
6+
7+
buildInputs = [ postgresql ];
8+
9+
src = fetchFromGitHub {
10+
owner = "olirice";
11+
repo = pname;
12+
rev = "v${version}";
13+
hash = "sha256-G0eQk2bY5CNPMeokN/nb05g03CuiplRf902YXFVQFbs=";
14+
};
15+
16+
installPhase = ''
17+
mkdir -p $out/{lib,share/postgresql/extension}
18+
19+
cp *.sql $out/share/postgresql/extension
20+
cp *.control $out/share/postgresql/extension
21+
'';
22+
23+
meta = with lib; {
24+
description = "Recommend indexes to improve query performance in PostgreSQL";
25+
homepage = "https://github.com/olirice/index_advisor";
26+
maintainers = with maintainers; [ samrose ];
27+
platforms = postgresql.meta.platforms;
28+
license = licenses.postgresql;
29+
};
30+
}

‎nix/tests/prime.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,3 +18,5 @@ CREATE EXTENSION IF NOT EXISTS wrappers;
1818
CREATE EXTENSION IF NOT EXISTS http;
1919
CREATE EXTENSION IF NOT EXISTS pg_graphql;
2020
CREATE EXTENSION IF NOT EXISTS pg_jsonschema;
21+
CREATE EXTENSION IF NOT EXISTS hypopg;
22+
CREATE EXTENSION IF NOT EXISTS index_advisor;

‎nix/tests/smoke/0004-index_advisor.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
-- Start transaction and plan the tests.
2+
begin;
3+
select plan(1);
4+
5+
create extension if not exists index_advisor;
6+
7+
create table account(
8+
id int primary key,
9+
is_verified bool
10+
);
11+
12+
select is(
13+
(select count(1) from index_advisor('select id from public.account where is_verified;'))::int,
14+
1,
15+
'index_advisor returns 1 row'
16+
);
17+
18+
select * from finish();
19+
rollback;

0 commit comments

Comments
 (0)
0