*[Data Storage & Retrieval](data-storage-and-retrieval/README.md)

*[Tabular data](data-storage-and-retrieval/tabular-data.md)

*[Vectors](data-storage-and-retrieval/vectors.md)

*[Partitioning](data-storage-and-retrieval/partitioning.md)

*[Deploying PostgresML](deploying-postgresml/README.md)

*[PostgresML Cloud](deploying-postgresml/postgresml-cloud/README.md)

*[Plans](deploying-postgresml/postgresml-cloud/plans/README.md)

Tabular data is data stored in tables.While that's a bit of a recursive definition, tabular data is any kind forformat that defines rows and columns, and is the most common type of datastorage mechanism. Examples of tabular datainclude things like spreadsheets, database tables, CSV files, and Pandas dataframes.

Tabular data is data stored in tables.A table is aformat that defines rows and columns, and is the most common type of dataorganization mechanism. Examples of tabular dataare spreadsheets, database tables, CSV files, and Pandas dataframes.

Storing and accessing tabular data is a subject ofdecades of studies, and is the core purpose ofmany database systems. PostgreSQL has been leading the charge on optimal tabular storage for awhileand remainstodayone of the most popular and effective ways to store, organize and retrievethis kind ofdata.

Storing and accessing tabular datain an efficient manneris a subject ofmultiple decade-long studies, and is the core purpose ofmost database systems. PostgreSQL has been leading the charge on optimal tabular storage for along time,and remains one of the most popular and effective ways to store, organize and retrievetabulardata today.

Postgres makes it really easy to create and use tables. If you're looking to use PostgresML for a supervised learning project, creating a table will be very similar to a Pandas dataframe, except it will be durable and easily accessible for as long as the database exists.

For the rest of this guide, we'll take the[USA House Prices](https://www.kaggle.com/code/fatmakursun/supervised-unsupervised-learning-examples/) dataset from Kaggle, store it in Postgres and query it for basic statistics. The dataset has seven (7) columns and 5,000 rows:

For the rest of this guide, we'll take the[USA House Prices](https://www.kaggle.com/code/fatmakursun/supervised-unsupervised-learning-examples/) dataset from Kaggle, store it in a Postgres table and run some basic queries. The dataset has seven (7) columns and 5,000 rows:

| Column| Data type| Postgres data type|

| ----------------------------| ---------| ------------------|

| Price| Float| REAL|

| Address| String| VARCHAR|

Once we know the column names and data types, the Postgres table definitionalmost writes itself:

Once we know the column names and data types, the Postgres table definitionis pretty straight forward:

The column names are double quoted because they contain special characters like`.` and space, which can be interpreted to be part of the SQL syntax. Generally speaking, it's good practice to double quote all entity names when using them in aPostgreSQLquery, although most of the time it's not needed.

The column names are double quoted because they contain special characters like`.` and space, which can be interpreted to be part of the SQL syntax. Generally speaking, it's good practice to double quote all entity names when using them in a query, although most of the time it's not needed.

If you run this using`psql`, you'll get something like this:

Right now the table is emptyand that's a bit boring. Let's import the USA House Prices datasetinto itusing one of theeasiest andfastest way to do so in Postgres:using`COPY`.

Right now the table is emptywhich is a bit boring. Let's import the USA House Prices dataset using one of the fastest way to do so in Postgres:with`COPY`.

If you're like me and prefer to use the terminal, you can open up`psql` and ingest thedataset like this:

If you're like me and prefer to use the terminal, you can open up`psql` and ingest thedata like this:

As expected, Postgres copied all 5,000 rows into the`usa_house_prices` table.`COPY` accepts CSV, text, and Postgres binary formats, but CSV is definitely the most common.

You may have noticed that we used the`\copy` command in the terminal, not`COPY`. The`COPY` command actually comes in two forms:`\copy` which is a`psql` command thatperforms a local system to remotedatabase server copy, and`COPY`whichis more commonly used in applications. If you're writing your own application to ingest data into Postgres, you'll be using`COPY`.

You may have noticed that we used the`\copy` command in the terminal, not`COPY`. The`COPY` command actually comes in two forms:`\copy` which is a`psql` command thatcopies data from systemfilesto remotedatabases, while`COPY` is more commonly used in applications to send data from other sources, like standard input, files, other databases and streams.

If you're writing your own application to ingest large amounts ofdata into Postgres, you should use`COPY` for maximum throughput.

Querying data stored in tables is what this is all about. After all, just storing data isn't particularly interesting or useful. Postgres has one of the most comprehensive and powerful querying languages of all data storage systems we've worked with so, for our example, we won't have any trouble calculating some statistics to understand our data better.

Let's compute some basic statistics on the "Avg. Area Income" column using SQL:

Querying data stored in tables is what makes PostgresML so powerful. Postgres has one of the most comprehensive querying languages of all databases we've worked with so, for our example, we won't have any trouble calculating some statistics:

FROM usa_house_prices;

which produces exactly what we want:

The SQL language isveryexpressive and allows to select, filter and aggregate any number of columns from any number of tables with a single query.

The SQL language is expressive and allows to select, filter and aggregate any number of columns with a single query.

Because databases store datain perpetuity, adding more data to Postgres cantake several forms. The simplest and most commonly used wayto add datais to just insert it into a tablethat wealready have. Using theUSA House Pricesexample, we can add a new row into the table with just one query:

Because databases store datapermanently, adding more data to Postgres canbe done in many ways. The simplest and most commonly used way is to just insert it into a tableyoualready have. Using thesameexample dataset, we can add a new row with just one query:

INSERT INTO usa_house_prices (

);

Another way to add more data to a table is to run`COPY` again with a different CSV as the source. Many ETL pipelines from places like Snowflake or Redshift split their output into multiple CSVs, which can be individually imported into Postgres using multiple`COPY` statements.

If you have more CSV files you'd like to ingest, you can run`COPY` for each one. Many ETL pipelines from Snowflake or Redshift chunk their output into multiple CSVs, which can be individually imported into Postgres using`COPY`.

{% tabs %}

{% tab title="Python" %}

import psycopg

from globimport glob

with psycopg.connect("postgres:///postgresml")as conn:

cur= conn.cursor()

with cur.copy("COPY usa_house_prices FROM STDIN CSV")as copy:

for csv_filein glob("*.csv"):

withopen(csv_file)as f:

next(f)# Skip header

for linein f:

copy.write(line)

{% endtab %}

{% tab title="Bash" %}

Adding rows is pretty simple, but now that our dataset is changing, we should explore some tools to help us protect it against bad values.

psql postgres:///postgresml \

-c"\copy usa_house_prices FROM '$f' CSV HEADER"

{% endtab %}

{% endtabs %}

Now that our dataset is changing, we should explore some tools to protect it against bad values.

Databases storeveryimportant dataand they were built with many safety features to protectthat datafrom common errors. In machine learning, one of the most common errors is data duplication, i.e. having the same row appear in the a table twice. Postgres can easily protect us against this with unique indexes.

Databases store important dataso they were built with many safety featuresin mindto protect from common errors. In machine learning, one of the most common errors is data duplication, i.e. having the same row appear in the a table twice. Postgres can protect us against this with unique indexes.

Looking at the USA House Price dataset, we can find its natural key pretty easily. Since most columns are aggregates, the only column that seems unique is the "Address".After all, thereshould never be more than one house at a single address, not for sale anyway.

Looking at the USA House Price dataset, we can find its natural key pretty easily. Since most columns are aggregates, the only column that seemslike it should containuniquevaluesis the "Address".Thereshould never be more than one housefor saleat a single address.

To ensure that ourdataset reflects this, let's add a unique index to our table. To do so, we can use this SQL query:

To ensure that ourtable reflects this, let's add a unique index:

CREATEUNIQUE INDEXON usa_house_prices USING btree("Address");

Postgres scans the whole table,ensures there are no duplicates in the "Address" column andcreates an index on that column using the B-Tree algorithm.

When creating a unique index,Postgres scans the whole table,ensuring there are no duplicates in the "Address" column, andwrites the column into an index using the B-Tree algorithm.

If wenowattempt to insert the same row again, we'll get an error:

If we attempt to insert the same row again,nowwe're getting an error:

Postgres supports many more indexing algorithms,namely GiST, BRIN, GIN, and Hash. Many extensions,for example`pgvector`, implement their own index types like HNSW and IVFFlat,to efficiently search and retrieve specialized values. We explore those in our guide about[Vectors](vectors.md).

Postgres supports many more indexing algorithms,e.g. GiST, BRIN, GIN, and Hash. Many extensions,e.g.`pgvector`, implement their own index types like HNSW and IVFFlat,which help efficiently retrieve specialized values. We explore those in our guide about[Vectors](vectors.md).

Once the dataset gets large enough, and we're talking millions of rows, it's no longer practical to query the table directly. The amount of data Postgres has to scanto return a resultbecomesquitelarge and queries become slow. To help with that, tables should have indexes that order and organize commonlyaccessed columns.Scanning a B-Tree index can be done in_O(log n)_ time, which is orders of magnitude faster than the_O(n)_ full table search.

Once the dataset gets large enough, and we're talking millions of rows, it's no longer practical to query the table directly. The amount of data Postgres has to scan becomes large and queries become slow. To help with that, tables should have indexes that order and organize commonlyread columns.Searching a B-Tree index can be done in_O(log n)_ time, which is orders of magnitude faster than the_O(n)_ full table search.

Postgres automatically uses indexes when possiblein order toaccelerate recall. Using our example above, we canquery data usingthe "Address" column and we can do so very quicklyby using theuniqueindex we created.

Postgres automatically uses indexes whenitspossibleand optimal todo so. From our example, we canfilter the dataset bythe"Address" column, and we can do so very quicklybecause of the index we created:

which is exactly what we expected. Since we have a unique index on the table, we should only be getting one row back with that address.

To ensure that Postgres is using an index when querying a table, we can ask it to produce the query execution plan that it's going to use before executing that query. A query plan is a list of steps that Postgres will take in order to get the query result we requested.

Since we have a unique index on the table, we will only see one row with that address.

Togetthe query plan for anyquery, prepend the keyword`EXPLAIN` toany query you're planning on running:

Todouble check that Postgres is using an index, we can checkthe queryexecutionplan. A query plan is a list of steps that Postgres will take to get thequery result we requested. To see the query plan, prepend the keyword`EXPLAIN` tothe query you're running:

The query plan indicates that it will be running an "Index Scan" using the index`usa_house_prices_Address_index` which is exactly what we want.

The plan indicates it will use an "Index Scan" on the index`usa_house_prices_Address_index` which is what we're expecting.

Using`EXPLAIN` doesn't actually run the query, so it's safe to use on production systems.

The ability to create indexes on datasets of any size and to then efficiently query that data is what separates Postgres from most ad-hoc tools like Pandas and Arrow. Postgres can store and querydatasets that would neverbe able tofitinto memory and can doso quicker and more efficiently than most database systemscurrentlyusedacross the industry.

The ability to create indexes on datasets of any size, and to then efficiently query that datawith those indexesis what separates Postgres from most ad-hoc tools like Pandas and Arrow. Postgres can store and querydata that would never fitin memory, anditcan dothat quicker and more efficiently than mostotherdatabase systems usedin the industry.

Indexesare automatically updated whennewdata is addedand old data isremoved. Postgresautomatically ensures that indexes are efficiently organized and are ACID compliant.When using Postgres tables, the systemguarantees that the datawill alwaysbeconsistent, no matter how many concurrent changes are made to the tables.

Postgres indexes require no special maintenance. Theyare automatically updated when data is addedorremoved. Postgresalso ensures that indexes are efficiently organized and are ACID compliant.The databaseguarantees that the datais always consistent, no matter how many concurrent changes are made.