Enable PostgreSQL extensions: pgvector, pg_trgm and more
Enable PostgreSQL extensions on a Voroa managed database without a restart: uuid-ossp, citext, pg_trgm, pgcrypto, hstore and pgvector for vector search.
To enable a PostgreSQL extension on Voroa, open your Postgres database, go to Settings, find
Extensions, and select Enable next to the one you want. Voroa runs it for you, with no
database restart, and marks it Enabled once it has confirmed the extension is really installed,
usually within a few minutes. Supported extensions are uuid-ossp, citext, pg_trgm,
pgcrypto, hstore and vector (pgvector).
The short version:
- Open your Postgres database from the dashboard.
- Go to Settings and scroll to Extensions.
- Select Enable next to the extension.
- Wait for the status to change from Pending to Enabled.
- Use the extension’s functions and types in your SQL.
Supported extensions
| Extension | What it adds |
|---|---|
UUID Generation (uuid-ossp) |
uuid_generate_v4() and related functions for generating UUIDs inside SQL. |
Case-insensitive Text (citext) |
The CITEXT type for case-insensitive text comparisons without adding LOWER() to every query. |
Trigram Search (pg_trgm) |
Trigram-based similarity search and fast ILIKE queries via GIN indexes. Useful for full-text search on short strings. |
Cryptographic Functions (pgcrypto) |
digest(), crypt(), gen_random_bytes(), and other cryptographic helpers for hashing and encryption inside the database. |
Key-Value Store (hstore) |
The hstore type for storing sets of key-value pairs in a single column. |
Vector Search (vector) |
Store embeddings and run similarity search with the vector type and its indexes. Needs a paid plan and a database built to support it - see below. |
PostGIS (postgis) |
Geometry and geography types, spatial indexes and functions for location data. Available on every plan, on a database built to support it - see below. |
PostGIS Topology (postgis_topology) |
Topology types and functions for shared edges and faces. Turns on PostGIS too. |
PostGIS Raster (postgis_raster) |
Raster (grid) data types and functions on top of PostGIS. Turns on PostGIS too. |
Accent-insensitive Search (unaccent) |
unaccent() removes accents, so a search for “cafe” also finds “café”. |
Fuzzy String Match (fuzzystrmatch) |
levenshtein(), soundex() and metaphone() for typo-tolerant matching. |
Hierarchical Labels (ltree) |
The ltree type for tree paths such as categories or org charts, with fast ancestor queries. |
B-tree GIN Operators (btree_gin) |
Lets GIN indexes cover plain columns, for combined indexes with arrays or JSON. |
B-tree GiST Operators (btree_gist) |
Lets GiST indexes cover plain columns. Needed for exclusion constraints, such as no overlapping bookings. |
Table Functions (tablefunc) |
crosstab() for pivot tables, plus normal_rand() and connectby(). |
Integer Arrays (intarray) |
Fast functions, operators and indexes for arrays of integers. |
Cube (cube) |
A multi-dimensional cube type, for points and boxes in n dimensions. |
Earth Distance (earthdistance) |
Great-circle distance between latitude and longitude points. Turns on Cube too. |
None of these needs a database restart to enable. All except Vector Search and PostGIS are part of the standard Postgres distribution; those two depend on the image your database runs, which is why they are offered only where they will actually work.
If Vector Search is not available on your paid database
Vector Search needs a paid plan and a database that was built to support it. Every new database is built to support it. An older database created on the free plan may not be, and upgrading to a paid plan does not change that on its own: the upgrade changes what you are billed for, and deliberately does not rebuild your running database, because a rebuild means downtime that should be your decision and not ours.
On an older database with that history you will see one of two things, depending on whether you have asked for Vector Search before:
- You have not asked for it. Vector Search is not listed at all. That is on purpose - we would rather not show you a switch that could only fail.
- You asked for it earlier and it failed. It stays in the list as Failed, because a request you made is yours to see. Retry will not help here: nothing about the database has changed, so the retry fails the same way.
In both cases the fix is the same, and it is not something you can do from the dashboard. Contact support and we will go through the options with you, including moving your data to a database that supports it.
If PostGIS is not listed on your database
PostGIS is available on every plan, including Free. Every new database includes it. An older database may not, if it was created before PostGIS was added. If PostGIS is not listed, contact support. We move your database to a build that includes PostGIS. Your data, connection details and settings stay the same. The move needs one short restart, about a minute, and we agree the time with you first. After that, PostGIS appears under Extensions and you enable it like any other.
Enable an extension
- Open your Postgres database from the dashboard.
- Go to the Settings tab.
- Find the Extensions section.
- Select Enable next to the extension you want.
The extension status changes to Pending, and to Enabled once it is on. From that point you can use it immediately in your queries.
Enabled means we checked. We do not mark an extension enabled because a command reported success - we read your database back afterwards and confirm the extension is actually there. So the status you see is the state of your database, not the state of a request.
If a request fails
A request can end as Failed, and the page tells you why in plain words rather than leaving you with a red label. The two common reasons:
- The extension is not available on your database. Some extensions, such as Vector Search, need a database plan and configuration that supports them. Retrying will not change this on its own - contact support and we will tell you what your options are.
- Your database was not reachable when we tried. This one fixes itself: we keep retrying automatically, and no action is needed from you. A stopped database is the usual cause, and the request applies by itself once you start it again.
You can select Retry next to a failed extension at any time. This is worth doing if something has changed since - for example, if support has moved your database to a plan that supports the extension you asked for, your earlier request stays Failed until you retry it.
Use an extension
Once enabled, use the extension’s functions and types in your SQL queries directly. For example, after enabling UUID Generation:
SELECT uuid_generate_v4();
After enabling Trigram Search, create a GIN index for fast text search:
CREATE INDEX ON articles USING gin(title gin_trgm_ops);
Extensions not available today
The following extensions need either a database restart or additional server configuration, and you cannot enable them yourself:
- TimescaleDB (time-series)
- pg_cron (scheduled jobs inside the database)
If your application needs one of these, contact support.
Disable an extension
Extension disable is not available for self-serve at this time. Dropping an extension can break application code that depends on the types or functions it provides. Contact support if you need to remove an extension.
Frequently asked questions
Do I need uuid-ossp to generate UUIDs?
Not on a current PostgreSQL version. gen_random_uuid() is built in from PostgreSQL 13, so
SELECT gen_random_uuid(); works without any extension. Enable uuid-ossp only if your code or an
existing migration calls uuid_generate_v4() or its other functions.
Is pgvector available on Voroa?
Yes, as Vector Search (vector), on paid plans and on databases built to support it. Every
new database is. An older database created on the free plan may not gain it by upgrading; contact
support if Vector Search is not listed on your paid database. See above.
Does enabling an extension restart my database?
No. None of the supported extensions needs a restart, so your app keeps running while one is enabled.
Can I run CREATE EXTENSION myself?
Yes. Your database user can run CREATE EXTENSION for any extension your database already includes,
such as the six above. The Extensions section does the same thing and confirms the result for you.
Extensions that are not installed on the server, such as PostGIS, TimescaleDB or pg_cron, cannot be
added either way.
Can I use PostGIS?
Yes, on every plan. If it is not listed on your database, see above.
Can I use TimescaleDB or pg_cron?
Not by yourself today. They need a restart or extra server configuration. Contact support if your application needs one of them.