> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pinecone.io/llms.txt
> Use this file to discover all available pages before exploring further.

# pg_pinecone quickstart

> Install the pg_pinecone extension for Postgres and search a sample table with a native index, which runs the search engine Pinecone uses at scale.

This quickstart installs pg\_pinecone on a PostgreSQL server you manage, or deploys it to a hosting platform. You create a sample table with vectors and search it with a `native` index, which runs the search engine Pinecone uses at scale inside Postgres. You can bring your own vectors, with no API key or network access, or have Pinecone generate embeddings. To compare `native` with the other engines, see [Storage engines](https://docs.pinecone.io/guides/pg-pinecone/storage-engines.md).

If you already have a table with a pgvector index, complete steps 1 and 2 to install pg\_pinecone, then [switch from pgvector to native](https://docs.pinecone.io/guides/pg-pinecone/switch-from-pgvector.md).

## Prerequisites

* A PostgreSQL 17 or 18 server with a superuser role, Docker, or an account on a supported hosting platform. Postgres.app, the Docker image, and the hosting platform templates include Postgres and pgvector.
* Optional: a [Pinecone API key](https://app.pinecone.io/organizations/-/projects/-/keys), if you want Pinecone to generate the embeddings. A key from the free [Starter plan](https://www.pinecone.io/pricing/) is enough.

## 1. Install pg\_pinecone

> [tab] Release archive
>
> 1. If your server doesn't have pgvector, install it. Run `sudo apt install postgresql-17-pgvector` (or `postgresql-18-pgvector`) on Debian or Ubuntu, `brew install pgvector` on macOS, or see the [pgvector installation guide](https://github.com/pgvector/pgvector#installation).
>
> 2. Download the release archive for your platform and Postgres version from the [releases page](https://github.com/pinecone-io/pg_pinecone/releases).
>
>    Archives are published for PostgreSQL 17 and 18 on Linux x86\_64, Linux arm64, and macOS arm64. The Linux x86\_64 build needs a CPU that supports x86-64-v3 (Intel Haswell or newer, which covers current cloud instances), and the Linux builds need glibc 2.39 or later, such as Ubuntu 24.04 or Debian 13.
>
> 3. Extract the archive and copy its files into your Postgres installation.
>
>    The commands use `PG=17`. If your server runs PostgreSQL 18, change it to `PG=18`. To check your version, run `pg_config --version`. `pg_config` also reports where your Postgres installation keeps extension files:
>
>    ```bash Linux theme={null}
>    PG=17
>    tar xzf pg_pinecone-0.13.0-pg$PG-linux-x86_64.tar.gz
>    sudo cp usr/lib/postgresql/$PG/lib/pg_pinecone.so "$(pg_config --pkglibdir)/"
>    sudo cp usr/share/postgresql/$PG/extension/pg_pinecone* "$(pg_config --sharedir)/extension/"
>    ```
>
>    ```bash macOS theme={null}
>    PG=17
>    tar xzf pg_pinecone-0.13.0-pg$PG-darwin-arm64.tar.gz
>    cp opt/homebrew/lib/postgresql@$PG/pg_pinecone.dylib "$(pg_config --pkglibdir)/"
>    cp opt/homebrew/share/postgresql@$PG/extension/pg_pinecone* "$(pg_config --sharedir)/extension/"
>    ```
>
> On Linux arm64, use the `linux-arm64` archive. If more than one Postgres version is installed, use the `pg_config` that belongs to the server you're installing into. On other platforms, including Debian 12, Ubuntu 22.04, RHEL 9, and older x86\_64 CPUs, use the PGXN tab, which builds from source, or [build from source](https://github.com/pinecone-io/pg_pinecone#readme) yourself.

> [tab] Postgres.app
>
> [Postgres.app](https://postgresapp.com/) is a Mac app that runs PostgreSQL and includes pgvector. pg\_pinecone is one of its downloadable extensions, built for PostgreSQL 18 on Apple silicon.
>
> 1. Open Postgres.app and start a PostgreSQL 18 server. If the server is new, click **Initialize**.
>
> 2. On the Postgres.app [extensions page](https://postgresapp.com/extensions/), find pg\_pinecone and download it. Open the installer it downloads and follow the prompts.
>
> 3. Back in the Postgres.app window, click **Stop**, then **Start**, so the server can find the extension you installed.

> [tab] PGXN
>
> `pgxn install` downloads the [pg\_pinecone release on PGXN](https://pgxn.org/dist/pg_pinecone/) and builds it from source for your Postgres server. The build takes a few minutes.
>
> 1. If your server doesn't have pgvector, install it. Run `sudo apt install postgresql-17-pgvector` (or `postgresql-18-pgvector`) on Debian or Ubuntu, `brew install pgvector` on macOS, or see the [pgvector installation guide](https://github.com/pgvector/pgvector#installation).
>
> 2. Install the build dependencies and the [PGXN client](https://pgxn.github.io/pgxnclient/). For PostgreSQL 18, change `17` to `18` in the Postgres package name:
>
>    ```bash Debian or Ubuntu theme={null}
>    sudo apt install build-essential pkg-config libssl-dev postgresql-server-dev-17 pgxnclient
>    ```
>
>    ```bash RHEL theme={null}
>    sudo dnf install gcc make pkgconf-pkg-config openssl-devel postgresql17-devel pgxnclient
>    ```
>
>    On Red Hat Enterprise Linux (RHEL), `postgresql17-devel` and `pgxnclient` come from the [PostgreSQL Yum repository](https://www.postgresql.org/download/linux/redhat/), and `postgresql17-devel` needs the CodeReady Builder (CRB) repository enabled. On macOS, you need only the Xcode Command Line Tools (`xcode-select --install`) and the PGXN client (`brew install pgxnclient`). On other platforms, install the equivalent packages.
>
> 3. Install [Rust](https://www.rust-lang.org/tools/install), then cargo-pgrx 0.19.2, the version pg\_pinecone builds with:
>
>    ```bash theme={null}
>    cargo install cargo-pgrx --version 0.19.2 --locked
>    ```
>
> 4. Build and install pg\_pinecone:
>
>    ```bash theme={null}
>    pgxn install pg_pinecone
>    ```
>
>    The build targets the Postgres that the first `pg_config` on your `PATH` belongs to. If more than one Postgres version is installed, put the `pg_config` of the server you're installing into first on your `PATH`. On RHEL, `pg_config` isn't on your `PATH` by default, so run `export PATH=/usr/pgsql-17/bin:$PATH` first.

> [tab] Docker
>
> The [`pineconeteam/pg_pinecone`](https://hub.docker.com/r/pineconeteam/pg_pinecone) image is the [`pgvector/pgvector`](https://hub.docker.com/r/pgvector/pgvector) image with pg\_pinecone added, so it includes Postgres and pgvector. It runs on x86\_64 and arm64 hosts. On x86\_64, it needs a CPU that supports x86-64-v3, like the Linux x86\_64 release archive.
>
> 1. Pull the image. The image includes its own Postgres, so you don't need to match a server you already have. This command uses PostgreSQL 17, the more widely used of the two supported versions. For PostgreSQL 18, use the `0.13.0-pg18` tag here and in the next step instead:
>
>    ```bash theme={null}
>    docker pull pineconeteam/pg_pinecone:0.13.0-pg17
>    ```
>
> 2. Start a container from the image:
>
>    ```bash theme={null}
>    docker run -d --name pg_pinecone \
>      -e POSTGRES_PASSWORD=secret \
>      pineconeteam/pg_pinecone:0.13.0-pg17 \
>      -c shared_preload_libraries=pg_pinecone
>    ```
>
>    The `shared_preload_libraries` setting isn't needed for this quickstart, but the `remote` engine, full-text search, and semantic search need it, and setting it now saves a restart later.

> [tab] Hosting platform
>
> Deploy a template to a hosting platform, then connect to it. Each template runs PostgreSQL 18 with pg\_pinecone installed and preloaded.
>
> | Platform | Deploy | Connect |
> | - | - | - |
> | Render | [pg\_pinecone template](https://render.com/deploy?repo=https://github.com/pinecone-io/pg_pinecone_render) | In the service's **Shell**, run `psql -U postgres -d pg_pinecone`. |
> | Railway | [pg\_pinecone template](https://railway.com/deploy/pg-pinecone) | On your computer, run `psql "YOUR_DATABASE_PUBLIC_URL"` with the `DATABASE_PUBLIC_URL` value from the service's **Variables** tab. If you don't have `psql`, install the [PostgreSQL client](https://www.postgresql.org/download/). |
>
> The template creates the extensions, so you can skip to [step 3](#3-add-sample-data).

## 2. Connect and create the extensions

### 1. Connect to your database

Connect as a superuser with `psql`:

```bash Linux theme={null}
sudo -u postgres psql
```

```bash macOS theme={null}
psql postgres
```

```bash Docker theme={null}
docker exec -it pg_pinecone psql -U postgres
```

On Linux, the `postgres` system user is the superuser that the package creates. With Homebrew or Postgres.app on macOS, your own user is the superuser. In the Docker image, the superuser is `postgres`. To connect to a different database or a remote server, add `-d your_database`, `-h your_host`, and `-U your_superuser`.

With Postgres.app, if your terminal can't find `psql`, add Postgres.app's command-line tools to your `PATH`, as its [installation steps](https://postgresapp.com/) show.

### 2. Create the extensions

Create pgvector first, because pg\_pinecone builds on its `vector` type and operators:

```sql SQL theme={null}
CREATE EXTENSION vector;
CREATE EXTENSION pg_pinecone;
```

## 3. Add sample data

> [tab] Your own vectors
>
> 1. Create the table. These vectors have three dimensions:
>
>    ```sql SQL theme={null}
>    CREATE TABLE docs (
>        id        bigint PRIMARY KEY,
>        body      text,
>        embedding vector(3)
>    );
>    ```
>
> 2. Insert a few short documents with their vectors. In a real application, you'd generate the vectors with an embedding model of your choice. To skip that step here, each document gets a hand-made vector whose values stand for food, nature, and places:
>
>    ```sql SQL theme={null}
>    INSERT INTO docs (id, body, embedding) VALUES
>        (1, 'The Eiffel Tower was completed in 1889 for the World''s Fair in Paris.', '[0.1, 0.1, 0.9]'),
>        (2, 'Photosynthesis converts sunlight, water, and carbon dioxide into glucose.', '[0.05, 0.9, 0.1]'),
>        (3, 'The Great Barrier Reef is the largest coral reef system in the world.', '[0.1, 0.8, 0.3]'),
>        (4, 'Espresso is brewed by forcing hot water through finely ground coffee.', '[0.95, 0.05, 0.05]'),
>        (5, 'Mount Everest sits on the border between Nepal and China.', '[0.05, 0.4, 0.8]'),
>        (6, 'Honeybees communicate the location of flowers with a waggle dance.', '[0.2, 0.85, 0.05]'),
>        (7, 'A croissant is a flaky pastry made from laminated, buttered dough.', '[0.95, 0.05, 0.1]'),
>        (8, 'The Amazon rainforest produces a large share of the world''s oxygen.', '[0.05, 0.9, 0.3]');
>    ```

> [tab] Pinecone embeddings
>
> 1. Store your Pinecone API key so the extension can generate embeddings. The key is stored in the `pinecone.secret` table, which only superusers can read by default:
>
>    ```sql SQL theme={null}
>    SELECT pinecone.set_api_key('YOUR_API_KEY');
>    ```
>
> 2. Create the table. The [`multilingual-e5-large`](https://docs.pinecone.io/models/multilingual-e5-large) model that `pinecone.embed_batch()` uses by default produces 1024-dimensional vectors:
>
>    ```sql SQL theme={null}
>    CREATE TABLE docs (
>        id        bigint PRIMARY KEY,
>        body      text,
>        embedding vector(1024)
>    );
>    ```
>
> 3. Insert a few short documents and generate their embeddings in the same statement:
>
>    ```sql SQL theme={null}
>    WITH input(body) AS (
>        VALUES
>            ('The Eiffel Tower was completed in 1889 for the World''s Fair in Paris.'),
>            ('Photosynthesis converts sunlight, water, and carbon dioxide into glucose.'),
>            ('The Great Barrier Reef is the largest coral reef system in the world.'),
>            ('Espresso is brewed by forcing hot water through finely ground coffee.'),
>            ('Mount Everest sits on the border between Nepal and China.'),
>            ('Honeybees communicate the location of flowers with a waggle dance.'),
>            ('A croissant is a flaky pastry made from laminated, buttered dough.'),
>            ('The Amazon rainforest produces a large share of the world''s oxygen.')
>    ),
>    numbered AS (
>        SELECT row_number() OVER () AS ordinal, body FROM input
>    )
>    INSERT INTO docs (id, body, embedding)
>    SELECT n.ordinal, n.body, e.embedding::vector
>    FROM numbered n
>    JOIN pinecone.embed_batch((SELECT array_agg(body ORDER BY ordinal) FROM numbered)) e
>      ON e.ordinal = n.ordinal;
>    ```

## 4. Search inside Postgres

### 1. Create a native index

Create a `native` index on the vector column. `native` is the default engine, so `engine => 'native'` is optional:

```sql SQL theme={null}
SELECT pinecone.create_index('docs', 'embedding', engine => 'native');
```

With Pinecone embeddings, `pinecone.create_index()` prints an informational message, labeled `NOTICE`, that suggests `SET STORAGE PLAIN` on the vector column, which speeds up searches on large tables. You can ignore it for this sample.

### 2. Run a search

Search with an ordinary pgvector query, the same query you'd run without pg\_pinecone. `<=>` is pgvector's cosine distance operator:

> [tab] Your own vectors
>
> Query with `[1, 0, 0]`, a vector that points entirely toward food, with no nature or places:
>
> ```sql SQL theme={null}
> SET enable_seqscan = off;
>
> SELECT id, body
> FROM docs
> ORDER BY embedding <=> '[1, 0, 0]'
> LIMIT 2;
> ```
>
> The two closest documents are the two about food:
>
> ```text Output theme={null}
>  id |                                 body
> ----+-----------------------------------------------------------------------
>   4 | Espresso is brewed by forcing hot water through finely ground coffee.
>   7 | A croissant is a flaky pastry made from laminated, buttered dough.
> ```

> [tab] Pinecone embeddings
>
> Query with a question, which `pinecone.embed()` turns into a vector:
>
> ```sql SQL theme={null}
> SET enable_seqscan = off;
>
> SELECT id, body
> FROM docs
> ORDER BY embedding <=> (SELECT pinecone.embed('What should I order for breakfast at a cafe?')::vector)
> LIMIT 2;
> ```
>
> `::vector` converts the embedding to pgvector's `vector` type. The subquery embeds the question once, before the search, so Postgres can use the index. If you call `pinecone.embed()` directly in `ORDER BY`, Postgres reads every row instead.
>
> The two closest documents are about espresso and croissants, even though neither one mentions breakfast or a cafe:
>
> ```text Output theme={null}
>  id |                                 body
> ----+-----------------------------------------------------------------------
>   4 | Espresso is brewed by forcing hot water through finely ground coffee.
>   7 | A croissant is a flaky pastry made from laminated, buttered dough.
> ```

> [!NOTE]
>
> `SET enable_seqscan = off` makes Postgres use the index on this eight-row table. On a table this small, the planner would otherwise read every row directly, because that's cheaper than using any index. Once a table is large enough that reading every row costs more, the planner chooses the index on its own, and you don't need this setting. It lasts until you disconnect.

### 3. Check which index served the query

`EXPLAIN` shows the plan Postgres uses to run a query, including which index it reads. To confirm the `native` index served the search, check the plan:

```sql SQL theme={null}
SET enable_seqscan = off;

EXPLAIN (COSTS OFF)
SELECT id
FROM docs
ORDER BY embedding <=> (SELECT embedding FROM docs WHERE id = 4)
LIMIT 2;
```

The plan includes `Index Scan using docs_embedding_pinecone_idx`, the `native` index you created in step 4.1. If Postgres read every row instead, the plan would show `Seq Scan`.

## 5. Clean up

If you don't need the sample table anymore, delete it. `DROP TABLE` permanently deletes the table, its rows, and its indexes, including the `native` index. To try [switching from native to remote](https://docs.pinecone.io/guides/pg-pinecone/switch-to-remote.md) next, skip this step, because that guide uses the same table.

```sql SQL theme={null}
DROP TABLE docs;
```

If you deployed to a hosting platform, delete the deployment so it stops billing. On Render, select **Disconnect Blueprint** in the Blueprint's settings, then delete the service. On Railway, delete the project.

## Next steps

* [Choose a storage engine](https://docs.pinecone.io/guides/pg-pinecone/storage-engines.md) for your workload.
* [Switch from pgvector to native](https://docs.pinecone.io/guides/pg-pinecone/switch-from-pgvector.md) on an existing table.
* [Switch from native to remote](https://docs.pinecone.io/guides/pg-pinecone/switch-to-remote.md) when a table outgrows one server.
* [Search a table with full-text search](https://docs.pinecone.io/guides/pg-pinecone/full-text-search.md).
* [Search a table with semantic search](https://docs.pinecone.io/guides/pg-pinecone/semantic-search.md).
