Can Postgres cover your NoSQL needs?

SymfonyCon, Vienna, Austria - December, 2024

© David Buchmann

David Buchmann - david@liip.ch

PHP Engineer, Liip AG, Switzerland

What does NoSQL Even Mean?

non-sql
not only sql
actually not-relational

Even Wikipedia only offers a negative definition:
«A database that focuses on data that is modeled in means other than tabular relations»

What do you want from NoSQL?

Schemaless, unstructured or semi-structured data
JSON, XML, key-value or specialized, e.g. graph database
Scalability to manage large data sets.

Repository

Repository at github.com/dbu/postgres-json

SQL JSON syntax

Define column as JSON

1CREATE TABLE auction_jsonb (
2    id SERIAL NOT NULL,
3    title VARCHAR(255) NOT NULL,
4    current_price INT DEFAULT NULL,
5    item JSONB NOT NULL,
6    PRIMARY KEY(id)
7);

Higher storage consumption, repeated keys are not optimized.
If value occurs more than 1 / 80 rows, column uses less space
Querying and joins might be less optimized than for fields

heap.io blog

JSON vs JSONB

JSON: Untyped, stored raw	JSONB: Stored structured
Preserve whitespace Preserve object key order Can handle invalid JSON, preserve duplicate keys	Parsed JSON Faster for querying

=> Use JSONB unless you rely on non-standard behaviour.

item->'author' = '"David"'

or, 2.5 times faster

item->>'author' = 'David'

JSON data or value?

>
The JSON information

>>
Extract primitive type

Filtering on JSON data

1SELECT COUNT(*)
2FROM auction_jsonb
3WHERE item->>'author' = 'David';

Rows	json	jsonb
10k	136	31
100k	319	46
1M	1'280	157
10M	15'505	2'768

Query time in milliseconds

Let's add an index

`1`	`CREATE INDEX json_author`

`2`	`ON auction_json_indexed ((item->>'author'));`

Rows	json	json indexed	jsonb	jsonb indexed
10k	136	25	31	20
100k	319	12	46	13
1M	1'280	3	157	3
10M	15'505	7	2'768	6

Query time in milliseconds

Side note: Tuning the query planner

At 10M rows, postgres stopped using the index and went for full table scan

1-- Value <= 1.15 makes postgres use the index
2ALTER TABLESPACE pg_default
3SET (random_page_cost=1.1);

Thanks Frank Heikens on stackoverflow for hinting random_page_cost

We can also extract a column

1CREATE TABLE auction_generated_fields (
2    id SERIAL NOT NULL,
3    author VARCHAR(255) generated always
4        as (item->>'author') stored,
5    item JSONB NOT NULL, PRIMARY KEY(id));

Rows	jsonb indexed	generated	generated indexed
10k	20	21	18
100k	13	30	10
1M	3	88	3
10M	6	786	4

Query time in milliseconds

Side note: Extract a date (thanks jamie@zomglol.wtf)

01CREATE FUNCTION text_to_timestamp(text) RETURNS TIMESTAMP
02LANGUAGE sql IMMUTABLE AS
03$$
04SELECT CASE
05WHEN $1 ~ '^\d{4}-\d{2}-\d{2}[ |T]\d{2}:\d{2}:\d{2}(\.\d+)?(\+00:00)?$'
06    THEN CAST($1 AS timestamp without time zone)
07END
08$$;
09 
10CREATE TABLE auction_generated_fields (
11    id SERIAL NOT NULL,
12    start_date TIMESTAMP(0) generated always
13        AS (text_to_timestamp(item->>'startDate')) stored,
14    item JSONB NOT NULL, PRIMARY KEY(id));

Too many similar options

item->type
JSON_QUERY(item, '$.author')
item #> '{type}'
item['type']

No sharing of indexes.
=> Decide which construct you use

Complex search in JSON

contains
item @> '{"author": "David"}'

key exists
item ? 'author' jsonb_exists(item, 'author')

any key exists
item ?| array['author','foo'] jsonb_exists_any(item, array['a..', 'f..']

all keys exist
item ?& array['author','title'] jsonb_exists_all(item, array['a..', 'f..']

JSON object contains object (jsonb only)

1CREATE INDEX auction_json_gin_idx
2ON auction_jsonb_gin USING GIN (item);
3 
4SELECT COUNT(*)
5FROM auction_jsonb_gin
6WHERE item @> '{"author": "David"}';

Rows	->> indexed	@> no index	@> with index	@> with GIN
10k	20	5	5	5
100k	13	20	19	5
1M	3	144	149	13
10M	6	1'239	1'235	76

Query time in milliseconds

Generalized Inverted Index (GIN)

Speed up arbitrary JSON contains queries, but not other queries

Side note: value distribution

Data distribution of the indexed column makes a huge difference

Variants	json	json idx	jsonb	jsonb idx	GIN >>	GIN @	gen	gen idx
10	12'372	784	1'895	676	1'882	1'364	1'030	44
1'000	12'424	6	1'583	6	1'260	58	931	3
100'000	13'197	3	1'710	3	1'501	5	838	3

Response times in milliseconds, table with 10 million rows

Insert Performance

Jsonb, indexes and generated fields do not come for free

Rows	json	json idx	jsonb	jsonb idx	GIN	gen	gen idx
10k	60	90	90	120	130	94	143
100k	550	740	820	990	2'110	890	1'000
1M	5'150	8'500	7'670	11'340	24'000	8'440	11'773
10M	55'000	96'000	97'000	115'000	256'000	84'000	116'000

Inserting dummy rows, duration in milliseconds

The impact of data size

Postgres processing time scales linear with size of JSON
Storage consumption scales linear too (no optimization)
With many rows, GIN index becomes larger than the table itself

Update JSON

Partial Update

Manipulate JSON instead of replacing it completely

1-- update, ignore if not exists
2UPDATE auction_jsonb
3SET item = jsonb_set(item, '{author}', '"value"', false)
4WHERE id=42;
5 
6-- update or create
7UPDATE auction_jsonb
8SET item = jsonb_set(item, '{author}', 'value', true)
9WHERE id=42;

Postgres is replacing the whole JSON, query is not faster

Delete fields in JSON

01-- delete attribute
02UPDATE auction_jsonb
03SET item = item - 'key'
04WHERE ID=42;
05 
06-- delete attribute alternate
07UPDATE auction_jsonb
08SET item = jsonb_set_lax(item, '{author}', null, true, 'delete_key')
09WHERE ID=42;
10 
11-- delete multiple
12item - array['key1', 'key2']

Versatile set_jsonb_lax

raise_exception	Fail if value is null
use_json_null	Set JSON field to null (default)
delete_key	Delete the key on null value
return_target	Leave previous value when new is null

`1`	`set_jsonb_lax(item, '{author}', null, true, 'return_target');`

JSON schema validation

Needs custom extension
(pg_jsonschema / postgres-json-schema)
When you serialize PHP models, do you need validation?
Validation discards flexibility in favor of reliability

Some other constructs

|| concat array / merge objects
jsonb_build_object('key', 'value', 'k2', 'v2')
jsonb('{"type":"book", "author": "David"}')
jsonb_serialize(item)
jsonb_each(item)

Doctrine ORM

Declare column

1#[ORM\Column(type: Types::JSON)]
2private array $item;
3 
4#[ORM\Column(type: Types::JSON, options: ['jsonb' => true])]
5private array $item;

Index can not be defined in Doctrine.
You have to manually add them to the migration.

`1`	`$this->addSql('CREATE INDEX json_author ON auction_json_indexed ((item->>\'author\'))');`

Define a generated field

1#[ORM\Column(
2    length: 255,
3    nullable: true,
4    insertable: false,
5    updatable: false,
6    columnDefinition: "VARCHAR(255) generated always as (item->>'author') stored",
7    generated: "ALWAYS"
8)]
9private ?string $author = null;

Note: the column definition is native SQL, not DQL

DQL does not support JSON syntax

You can create native queries with Doctrine DBAL

Or use scienta/doctrine-json-functions

1# config/packages/doctrine.yaml
2doctrine:
3  orm:
4    dql:
5      string_functions:
6        JSON_GET_TEXT: Scienta\...\Postgresql\JsonGetText
7        JSONB_CONTAINS: Scienta\...\Postgresql\JsonbContains

Register boolean functions as string and compare with = true

->> as DQL function

1$qb = $this->createQueryBuilder('a');
2$qb->select('COUNT(a)')
3    // Need to qualify with a. in front of item
4    ->where("JSON_GET_TEXT(a.item, 'author') = :author")
5    ->setParameter('author', $authorName)
6;
7 
8return $qb->getQuery()->getSingleScalarResult();

@> as DQL function

01$qb = $this->createQueryBuilder('a');
02$connection = $this->getEntityManager()->getConnection();
03// Could not figure out how to pass author as parameter.
04// Not sure if escaping is safe,
05// needs to be verified for direct user input
06$author = str_replace('"', '\"', $authorName);
07$author = trim($connection->quote($author), "'");
08$qb->select('COUNT(a)')
09    ->where("JSONB_CONTAINS(a.item, '{\"author\": \"$author\"}') = true")
10;
11 
12return $qb->getQuery()->getSingleScalarResult();

Doctrine/PHP Overhead

Rows	jsonb	dbal	dbal, batch 1k	entities
10k	60	4519	163	6290
100k	550	49964	1587	53823

Unfair: jsonb measurement is unrealistic
Prepared DBAL statements not much faster than EM
Unless you batch 1'000 inserts together
If you insert single elements, it does not matter
With entities, best was flush & clear every 5 entities

MySQL / MariaDB

Many of the functions are the same or there are equivalent
SQL:2016 defines JSON functionality on string fields
SQL:2023 defines JSON data type and operations
MySQL uses a dedicated type for JSON, MariaDB not
Operators like ->> are not standard
(MySQL supports them too, not MariaDB)

Conclusions

Postgres can handle JSON quite well
Harder to support Postgres and MySQL with the same code
Caveats - but so does learning a NoSQL database
Use jsonb
Essential data better in dedicated columns

Performance depends a lot on how your data looks
Test on real or at least realistic data in realistic quantities!
Don't take things for granted (ever, with databases)
If you have less than 10k rows: Do whatever you want
Scaling Postgres is possible, but first look into configuration and optimizing queries
If you need frequent or complex searches, you could use a dedicated search engine like Elasticsearch or talk with the MongoDB people in the hallways

Thank you!

github.com/dbu/postgres-json

Mastodon: @dbu@phpc.social

David Buchmann, Liip AG

Scaling/Replicating postgres

Workarounds

Optimizing postgres configuration can get you a long way
Clear out older data, or create a table per time interval to limit growth
Put unrelated tables into different databases
Move those onto separate servers to spread the load
Doctrine can work with multiple entity managers

Read replicas

Enable hot-standby mode
Can be used for read only queries
All or nothing: Need to replicate all databases of the server with all their content

Logical replicas

Mirrors queries to replica server
Can limit to some tables or even specific rows
Or limit which columns of a table (since postgres 15)
Can configure replica server to use foreign data wrappers (FDW) to query tables from upstream server
Combining these techniques, you can scale postgres horizontally

See pgdaash.io

json_each(item)

jsonb_each(item)

SELECT *
FROM jsonb_each
((SELECT item FROM auction_jsonb WHERE id=1));