Postgresql Nosql and integration between relational and non-relational databases

Asked

Viewed 464 times

3

I have been researching about Postgresql Nosql and found the concept of Key-Value Stores in some publications, also from HSTORE. I also researched the integration between Postgresql and the Spring framework and found the following example of code:

package com.jamesward.model;

import net.backtothefront.HstoreUserType;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import java.util.HashMap;
import java.util.Map;

@Entity
@TypeDef(name = "hstore", typeClass = HstoreUserType.class)
public class Contact {

    @Id
    @GeneratedValue
    public Integer id;

    @Column(nullable = false)
    public String name;

    @Type(type = "hstore")
    @Column(columnDefinition = "hstore")
    public Map<String, String> contactMethods = new HashMap<String, String>();

}

As you can see in this entity there are 3 (three) fields id integer, name of the type String and contactMethods which is the field where JSON will be stored.

And searching in a PDF from Enterprisedb I found the following command for creating a table with a single name field json_data which will store JSONB type values.

CREATE TABLE json_data (data JSONB);

In addition to a SELECT like this (in addition to INSERT and other actions using this entity that can be found in the references published at the end of the question):

SELECT DISTINCT 
data->>'name' AS "Product Name",
data->>'price' AS "price"  
FROM json_data 
WHERE data->>'brand' = 'ACME';

And what I’d like to know is based on these questions:

  1. Can I make all Postgresql tables Nosql? That is, only with a JSON field and not two or more fields being an identifier and the other JSON, for example as previously seen in one of the above codes.
  2. When the table has multiple records and clients using the query and the concept Key-Value will be slow as if you had created more fields for the table?
  3. Using only one JSON field in the entity would be able to work correctly with Spring or is it even necessary to have one or more fields being an identifier and the other JSON? Spring couldn’t read the records of multiple lines with only one JSON-type field as if it were document-oriented like Mongodb, Cassandra, Hadoop work?
  4. One of the last three questions, and I believe the most important, can I make a Postgresql database totally Nosql and in the example of Enterprisedb created only one field? Thus replacing the use of major non-relational banks?
  5. And if not, what is the need to create a JSON field by storing, such as name, Cpf, rg if we can create multiple fields to store each of these attributes?
  6. I also read that an application can use relational and non-relational databases together, without problems, how is this integration done? I saw in Oracle SQL Developer, in the left menu, which has a Nosql item, but was never used.

References:

Enterprisedb - Using the Nosql Capabilities in Postgres

James Ward - Nosql Inside SQL with Java, Spring, Hibernate, and Postgresql

James Ward - Nosql Inside SQL with Java, Spring, Hibernate, and Postgresql (Github repository)

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.