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

CREATE TABLE auction_jsonb (
    id SERIAL NOT NULL,
    title VARCHAR(255) NOT NULL,
    current_price INT DEFAULT NULL,
    item JSONB NOT NULL,
    PRIMARY KEY(id)
);
            

• 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

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

JSON data or value?

Filtering on JSON data

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

Query time in milliseconds

Let's add an index

CREATE INDEX json_author
ON auction_json_indexed ((item->>'author'));
            

Query time in milliseconds

Side note: Tuning the query planner

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

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

Thanks Frank Heikens on stackoverflow for hinting random_page_cost

We can also extract a column

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

Query time in milliseconds

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

CREATE FUNCTION text_to_timestamp(text) RETURNS TIMESTAMP
LANGUAGE sql IMMUTABLE AS
$$
SELECT CASE
WHEN $1 ~ '^\d{4}-\d{2}-\d{2}[ |T]\d{2}:\d{2}:\d{2}(\.\d+)?(\+00:00)?$'
    THEN CAST($1 AS timestamp without time zone)
END
$$;

CREATE TABLE auction_generated_fields (
    id SERIAL NOT NULL,
    start_date TIMESTAMP(0) generated always
        AS (text_to_timestamp(item->>'startDate')) stored,
    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

JSON object contains object (jsonb only)

CREATE INDEX auction_json_gin_idx
ON auction_jsonb_gin USING GIN (item);

SELECT COUNT(*)
FROM auction_jsonb_gin
WHERE item @> '{"author": "David"}';
            

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

Response times in milliseconds, table with 10 million rows

Insert Performance

Jsonb, indexes and generated fields do not come for free

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

-- update, ignore if not exists
UPDATE auction_jsonb
SET item = jsonb_set(item, '{author}', '"value"', false)
WHERE id=42;

-- update or create
UPDATE auction_jsonb
SET item = jsonb_set(item, '{author}', 'value', true)
WHERE id=42;
            

Postgres is replacing the whole JSON, query is not faster

Delete fields in JSON

-- delete attribute
UPDATE auction_jsonb
SET item = item - 'key'
WHERE ID=42;

-- delete attribute alternate
UPDATE auction_jsonb
SET item = jsonb_set_lax(item, '{author}', null, true, 'delete_key')
WHERE ID=42;

-- delete multiple
item - array['key1', 'key2']
            

Versatile set_jsonb_lax

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

#[ORM\Column(type: Types::JSON)]
private array $item;

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

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

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

Define a generated field

#[ORM\Column(
    length: 255,
    nullable: true,
    insertable: false,
    updatable: false,
    columnDefinition: "VARCHAR(255) generated always as (item->>'author') stored",
    generated: "ALWAYS"
)]
private ?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

# config/packages/doctrine.yaml
doctrine:
  orm:
    dql:
      string_functions:
        JSON_GET_TEXT: Scienta\...\Postgresql\JsonGetText
        JSONB_CONTAINS: Scienta\...\Postgresql\JsonbContains

            

->> as DQL function

$qb = $this->createQueryBuilder('a');
$qb->select('COUNT(a)')
    // Need to qualify with a. in front of item
    ->where("JSON_GET_TEXT(a.item, 'author') = :author")
    ->setParameter('author', $authorName)
;

return $qb->getQuery()->getSingleScalarResult();

@> as DQL function

$qb = $this->createQueryBuilder('a');
$connection = $this->getEntityManager()->getConnection();
// Could not figure out how to pass author as parameter.
// Not sure if escaping is safe,
// needs to be verified for direct user input
$author = str_replace('"', '\"', $authorName);
$author = trim($connection->quote($author), "'");
$qb->select('COUNT(a)')
    ->where("JSONB_CONTAINS(a.item, '{\"author\": \"$author\"}') = true")
;

return $qb->getQuery()->getSingleScalarResult();
            

Doctrine/PHP Overhead

• 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