The Young DBA

Now we can create static and dynamic both kind of schema relational table so a new kind of heterogeneous data structure can be created in Relational tables.  Relational database that is much more concerned with standards compliance and extensible than with giving you freedom over how you store data. It uses both dynamic and static schema and allows you to use it for relational data and normalized form storage. Static Relation data structure in Postgres CREATE TABLE Project (  id serial PRIMARY KEY,  name varchar,  mgr integer,  is_active boolean ); CREATE TABLE Task(  id serial PRIMARY KEY, name varchar, status boolean, project_id integer, CONSTRAINT idx_project_id FOREIGN KEY (project_id) REFERENCES project (id) ); Dynamic Relation data structure in Postgres CREATE TABLE IF NOT EXISTS project ( id serial PRIMARY KEY,  name varchar,  mgr integer,  tasks jsonb , is_active boolean ); Keeping JSON Document inside Postgres Table INSERT INTO project VALUES (1,'Young

Hi friend, I am continuing my post over my series article "Is it time to revisit PostgreSQL and MySQL with JSON support"  . Today I am sharing my 4th post which is related to " Changes in RDBMS after JSON " JSON bring changes on various vendors and technologies so new kind of challenges came to introducing this feature in their technology. To cover-up these challenge RDBMS started inclusion of JSON data type in their engine. Sooner or later all major RDBMS vendor (eg PostgreSQL, mySQL and SQL Server) added JSON as a data type to keeping document in relational table.  These are the database version when JASON introduced :- . Technology Postgres mySQL SQL Server Version PostgreSQL 9.3 MySQL 5.7.8 SQL 2016 Now a new heterogeneous database kind of structure started creating with relational engine. Static and dynamic data structure in RDBMS started introducing. Mean Relational Table =Normalization + denormalized (JSON/Array).  JSON data Passing done via stan

For encrypting and decrypting , we must use the bytea data type on the column which we implement. Bcoz bytea will use the pgcrypto method by default. However, you will need to create the pgcrypto extension to enable these functions as they are not pre-defined in PostgreSQL/PPAS. Example CREATE EXTENSION pgcrypto; CREATE TABLE userinfo (username varchar(20), password bytea); >>    Inserting the data in an encrypted format INSERT INTO userinfo VALUES(' suman ',encrypt('111222','password','aes')); select * from userinfo ; >>    Retrieving the data as decrypted format SELECT decrypt(password,decode('password','escape'::text),'aes'::text) FROM userinfo; Thanks for reading Plz dont forget to like Facebook Page.. https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

A running application can have multiple database integrated environment to send receive data. Some time multiple database of Postgres or sometimes other database like sql server/mysql/mongodb. Every database have different-different feature to integrate. Like sql server have Linked Server, mysql have Data federation Postgres have foreign data wrapper. This post is related to Postgres's foreign data wrapper. What is foreign data wrapper in Postgres ? How to access table from other database in Postgres ? Postgres have a  different feature which lets you to create a foreign data wrapper inside Postgres, which lets you feel the object of current connected database. It will help to create object that will part of foreign data or database. So we can easily integrated data. This feature is called foreign data wrapper. What are the components of foreign data wrapper ? 1. Foreign Data wrapper Extension { file_fdw , postgres_fdw } 2. Foreign database server location 3. User Mappi

Hi Friends, This is the 2nd part of my post " Why JSON Came into Picture " .  I am trying to more elaborate how JSON replaces XML and supported in technology upgrade. Background About JSON JSON stands for JavaScript Object Notation, and was first formalized by Douglas Crockford. JSON is a data format interchange - method of storing and transferring data. Mostly its uses such as data conversion (JSON to SQL) and exporting data from proprietary web apps or mobile apps. XML was a big buzzword in the early 2000’s, JSON become the buzzword in later few years. What are the Impacts JSON bring in technology ? NoSQL Document database became popular, mongodb was among one of lucky database vendor.  Technology found JSON as an alternate to xml for data interchange on platform independent. Technology found a supporting for RDBMS to keeping variety of data specially non-structured data in one environment and module. Data explosion and big data came into the platform. Th

What is JSON ? JSON stands for JavaScript Object Notation, and was first formalized by Douglas Crockford. JSON is a data format interchange - method of storing and transferring data. Mostly its uses such as data conversion (JSON to SQL) and exporting data from proprietary web apps or mobile apps. XML was a big buzzword in the early 2000’s, JSON become the buzzword in later few years.  Why JSON came into picture ? After 2005 applications & user requirements started growing rapidly, Hardware and software developed, the advent of Single Page Applications and modern mobile/web apps that we know today needed some kind of data interchange to function seamlessly. To fulfill user requirement technology started shifting into  new language-independent data interchange format that time JSON came into the Picture. JSON gained rapid popularity because it makes transferring data very easy. It’s also lightweight and easy to read and understand. There are few other reasons that JSON make

Recently I got a defiance from a some developers regarding postgresql server is slow performance. In his opinion it is not able to fetch 5 lac record from CSV files. They show there aplication which was using (node.js+sequlize ORM). When executed it was really sucking in middle. 5 lac records are really in huge amount but postgre is alos being for high performance application. There is no question we can say that it can not able to fetch this amount of data. So I suggested him to start importing from short amount of records like top 6 then top 600 then top 6000 then 60000. Yeah he did and really it stopped after 60000 records. It failed from application, program is running, running and keep running.. So boll goes in my bucket then I started digging on database server configuration parameters like effective_cache_size, work_memory, Shared_buffers, Maximum_number_Connections, wal_buffers etc, given appropriate values as per best practices and current system resources. Then re

Last month I got a chance to optimize some queries of a postgre sql database in my current organization. I was totally new to this database. SQL Server, mySQL, MongoDB, and now, postgres. I  never say a 'no' if some work related to database comes to me, and especially, if the database belongs to the RDBMS family, then, I can never say a 'no', as I am always keen to learn new and new trends. My thought for postgres was wrong earlier. I used to think that it is less popular than mySQL, but, I was wrong. When I started working on it, I found it much better mySQL in some scenarios, like - query optimization(using execution plan and statistics). Technically, Postgres is "better SQL" as it is more standardized. With better query planning, so that you can follow the relational model more closely if it suits you. Socially, MySQL has been very popular for a long time and many people are familiar with it. Postgres is neither owned by a major conglomerate with a qu